一、Oracle部分
1、在Oracle新建自定义类型,对应要传入oracle的字段,类型不一定一致,因为时间类型不好处理,为了处理方便将date/number类型均对应为varchar2类型
CREATE OR REPLACE TYPE alarm_content_type AS OBJECT
(
con_addr varchar2 (10) ,
acq_no varchar2 (5) ,
mea_no varchar2 (5) ,
alarm_day varchar2 (20) ,
alarm_value varchar2 (5) ,
data_type varchar2 (5) ,
alarm_data varchar2 (15)
)
其中acq_no对应为number,alarm_day对应为date类型
2、在Oracle新建对应的自定义类型数组
CREATE OR REPLACE TYPE alarm_content_array AS TABLE OF alarm_content_type
3、在Oracle建立package及 package bodies
CREATE OR REPLACE PACKAGE alarm_content_pck AS
PROCEDURE insert_object(d alarm_content_array, tbname varchar2);
END alarm_content_pck;
CREATE OR REPLACE PACKAGE BODY alarm_content_pck AS
PROCEDURE insert_object (d alarm_content_array, tbname varchar2) AS
SqlText varchar2 (500);
BEGIN
FOR i IN d.FIRST..d.LAST
LOOP
SqlText := 'INSERT INTO ' || tbname
|| ' VALUES(''' || d(i).con_addr
|| ''', ' || d(i).acq_no || ', ' || d(i).mea_no
|| ', decode(' || d(i).alarm_day || ', null , null, to_date(''' ||d(i).alarm_day || ''', ''yyyy-mm-dd''' || '))'
|| ', ''' || d(i).alarm_value || ''', ''' || d(i).data_type
|| ''', ' || d(i).alarm_data
|| ')';
EXECUTE IMMEDIATE SqlText;
END LOOP;
END insert_object;
END alarm_content_pck;
其中参数d 为传入的数据, tbname 为插入Oracle的表名
二、Java部分
1、Oracle的链接、关闭等
- package com.techen.database;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.ArrayList;
- import java.util.List;
- import oracle.jdbc.OracleCallableStatement;
- import oracle.sql.ARRAY;
- import oracle.sql.ArrayDescriptor;
- import oracle.sql.STRUCT;
- import oracle.sql.StructDescriptor;
- import com.techen.database.DataDb;
- import com.techen.database.ProcedureParam;
- /**
- * Oracle接口类
- *
- */
- public class OracleDb
- {
- public static Connection connection = null; // 连接到数据库的对象
- private static Statement state = null; // Statement对象
- private static String URL = null;
- private static String userName;
- private static String passWord;
- /**
- * 建立到内存数据库的连接
- *
- * @return 成功与否
- */
- public OracleDb(String aURL, String aUserName, String aPassWord)
- {
- URL = aURL;
- userName = aUserName;
- passWord = aPassWord;
- }
- /**
- * 建立与Oracle数据库的连接
- *
- * @return 成功与否
- */
- public boolean createConnection()
- {
- boolean bFlag = false;
- if (connection != null)
- closeConnection();
- if (connection == null)
- {
- try
- {
- // 加载Oracle数据库JDBC驱动
- Class.forName("oracle.jdbc.driver.OracleDriver");
- connection = DriverManager.getConnection(
- URL, userName, passWord);
- state = connection.createStatement();
- bFlag = true;
- } catch (SQLException e)
- {
- e.printStackTrace();
- } catch (ClassNotFoundException e)
- {
- e.printStackTrace();
- }
- }
- return bFlag;
- }
- /**
- * 释放到Oracle数据库的连接
- */
- public void closeConnection()
- {
- if (connection != null)
- try
- {
- connection.close();
- if (state != null)
- {
- try
- {
- state.close();
- } catch (SQLException e)
- {
- e.printStackTrace();
- }
- state = null;
- }
- } catch (SQLException e)
- {
- e.printStackTrace();
- }
- connection = null;
- }
- /**
- * 查询
- * @param sql 查询sql语句
- * @return 数据集
- */
- public ResultSet executeQuery(String sql)
- {
- ResultSet rs = null;
- try
- {
- rs = state.executeQuery(sql);
- } catch (SQLException e)
- {
- e.printStackTrace();
- }
- return rs;
- }
- /**
- * 除查询外的其他Sql语句操作
- *
- * @param sql 查询sql语句
- * @return 成功与否
- */
- public boolean executeUpdate(String sql)
- {
- boolean bFlag = false;
- try
- {
- state.executeUpdate(sql);
- bFlag = true;
- } catch (SQLException e)
- {
- e.printStackTrace();
- }
- return bFlag;
- }
2、调用Oracle建立的package处理
-
- /**
- * 将内存数据库各表数据转到Oracle
- *
- * @return 成功与否
- */
- @SuppressWarnings({ "unchecked", "unchecked" })
- public boolean hsqlToOracle()
- {
- boolean bFlag = false;
- if (connection != null)
- {
- try
- {
- List lTb = new ArrayList();
- //获取内存中所有的主站数据表
- lTb = DataDb.getTable();
- if ((lTb != null) && (lTb.size() > 0))
- {
- //循环对各表操作
- for (int iTbCount = 0; iTbCount < lTb.size(); iTbCount++)
- {
- //当前表名
- String curTb = (String)lTb.get(iTbCount);
- //调用存储过程
- if (callProcedure(curTb, 1))
- {
- //如果添加成功则删除HSQLDB中对应的表数据
- String sqlText = "delete from " + curTb;
- DataDb.createConnection();
- DataDb.executeUpdate(sqlText);
- //DataDb.closeConnection();
- }
- }
- bFlag = true;
- }
- } catch (Exception e)
- {
- e.printStackTrace();
- }
- }
- return bFlag;
- }
- * 调用Oracle存储过程
- * @param tbName 表名
- * @param kind 类型: 1 取内存表数据;2 取XML数据
- * @return 成功与否
- */
- @SuppressWarnings("unchecked")
- public boolean callProcedure(String tbName, int kind)
- {
- boolean bFlag = false;
- //获取当前表的记录集
- List theTbDatas = null;
- if (kind == 1)
- {
- theTbDatas = DataDb.GetRecsFromHsql(tbName);
- }
- else
- {
- theTbDatas = XmlFile.GetRecsFromXml(tbName);
- }
- if (theTbDatas.size() > 0)
- {
- StructDescriptor st;
- //根据表名获取存储过程参数
- ProcedureParam thePro = new ProcedureParam(tbName);
- try
- {
- st = new StructDescriptor(
- thePro.data_type, connection);
- STRUCT[] dataArray = new STRUCT[theTbDatas.size()];
- //将表中记录转换为Oracle中自定义类型
- for (int i = 0; i < theTbDatas.size(); i++)
- {
- STRUCT s = new STRUCT(st, connection,
- (Object[]) theTbDatas.get(i));
- dataArray[i] = s;
- }
- //转换为数组形式
- ArrayDescriptor arrayData = ArrayDescriptor
- .createDescriptor(thePro.type_array, connection);
- ARRAY dataArrayObject = new ARRAY(arrayData,
- connection, dataArray);
- //调用Oracle包中存储过程
- OracleCallableStatement callStatement = null;
- callStatement = (OracleCallableStatement) connection
- .prepareCall(thePro.call_Statement);
- //参数1:数据
- ((OracleCallableStatement) callStatement).setArray(
- 1, dataArrayObject);
- //参数2:表名
- ((OracleCallableStatement) callStatement)
- .setString(2, tbName);
- callStatement.executeUpdate();
- connection.commit();
- callStatement.close();
- bFlag = true;
- } catch (SQLException e)
- {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
- return bFlag;
- }
- /**
- * Oracle存储过程各类型表所对应的参数
- *
- * @author GavinPan
- *
- */
- public class ProcedureParam
- {
- public String data_type; // 数据类型
- public String type_array; // 相应类型的数组
- public String call_Statement; // 调用包内过程的字符串
- /**
- * 构造函数 根据表名初始化各成员
- *
- * @param tableName 表名
- */
- public ProcedureParam(String tableName)
- {
- int iTb = OracleDb.getTableType(tableName);
- switch (iTb)
- {
- case 1:
- {
- data_type = "MSTDATA_TYPE";
- type_array = "MSTDATA_ARRAY";
- call_Statement = "{call mst_pck.INSERT_OBJECT(?,?)}";
- break;
- }
- case 2:
- {
- data_type = "MAXNEED_TYPE";
- type_array = "MAXNEED_ARRAY";
- call_Statement = "{call maxneed_pck.INSERT_OBJECT(?,?)}";
- break;
- }
- case 3:
- {
- data_type = "ENERGY_TYPE";
- type_array = "ENERGY_ARRAY";
- call_Statement = "{call energy_pck.INSERT_OBJECT(?,?)}";
- break;
- }
- case 4:
- {
- data_type = "VOLTAGE_ELIGIBILITY_TYPE";
- type_array = "VOLTAGE_ELIGIBILITY_ARRAY";
- call_Statement = "{call voltage_eligibility_pck.INSERT_OBJECT(?,?)}";
- break;
- }
- case 5:
- {
- data_type = "ALARM_DATA_TYPE";
- type_array = "ALARM_DATA_ARRAY";
- call_Statement = "{call alarm_data_pck.INSERT_OBJECT(?,?)}";
- break;
- }
- case 6:
- {
- data_type = "ALARM_CONTENT_TYPE";
- type_array = "ALARM_CONTENT_ARRAY";
- call_Statement = "{call alarm_content_pck.INSERT_OBJECT(?,?)}";
- break;
- }
- }
- }
- }
- /**
- * 将内存中的相应表的电表数据记录读出
- * @param tableName 表名
- * @return 记录的List列表
- */
- @SuppressWarnings("unchecked")
- public static List GetRecsFromHsql(String tableName)
- {
- List rtndata = new ArrayList();
- try
- {
- // 加载HSQLDB数据库JDBC驱动
- Class.forName("org.hsqldb.jdbcDriver");
- // 在内存中建立临时数据库score,用户名为sa,密码为空
- @SuppressWarnings("unused")
- Connection connect = DriverManager.getConnection(
- "jdbc:hsqldb:mem:MstDataDb", "sa", "");
- Statement state = connect.createStatement();
- String ssqltext = "select * from " + tableName;
- ResultSet rs = state.executeQuery(ssqltext);
- while (rs.next())
- {
- ResultSetMetaData rsmd = rs.getMetaData(); // 获取字段名
- Object[] tmpl = new Object[rsmd.getColumnCount()];
- for (int i = 1; i <= rsmd.getColumnCount(); i++)
- {
- tmpl[i-1] = rs.getString(i); //获取数据赋值
- if (tmpl[i-1] == null)
- tmpl[i-1] = "null";
- }
- rtndata.add(tmpl);
- }
- } catch (SQLException e1)
- {
- e1.printStackTrace();
- } catch (Exception e2)
- {
- e2.printStackTrace();
- }
- return rtndata;
- }
3、测试
- OracleDb TechenDb = new OracleDb(
- "jdbc:oracle:thin:@192.168.1.200:1521:techen",
- "techen", "techen");
- if (TechenDb.createConnection())
- {
- if (TechenDb.hsqlToOracle())
- MessageDialog.openInformation(null, "title", "OK!");
- }