Oracle中得blob欄位匯入到DB2的Blob欄位

1.背景

在項目中使用blob大欄位來儲存報文，最近有客戶需要使用DB2資料庫來部署應用，所有只得把oracle中初始化指令碼匯入到DB2中，在製作DB2的初始化指令碼

2.問題

怎麼樣把oracle中得含blob欄位的表匯入到DB2中呢，在網上轉了一下沒有發現，決定寫程式解決

3.解決問題

使用JDBC串連先查出oracle中得表，然後插入到DB2中。先建立java project，然後添加oracle和DB2的驅動包，如：

DB2ConnectionFactory源碼為：

package com.ylink.export;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class DB2ConnectionFactory {final static String DRIVER = "com.ibm.db2.jcc.DB2Driver";final static String CONSTR = "jdbc:db2://172.168.6.212:50000/tps";final static String USERNAME = "db2admin";final static String USERPASS = "db2admin";/** * 獲得資料庫連接 * @return */public static Connection getConnection(){Connection con = null;try {Class.forName(DRIVER);con = DriverManager.getConnection(CONSTR,USERNAME,USERPASS);con.setAutoCommit(false);  //設定不自動認可事務}catch (SQLException e) {System.out.println("sql語句錯誤"+e.getMessage());} catch (ClassNotFoundException e) {System.out.println(e.getMessage());}return con;}}

IOperationCore介面源碼為：

/** * 資料庫常用操作封裝 */package com.ylink.export;import java.lang.reflect.InvocationTargetException;import java.sql.ResultSet;import java.sql.SQLException;import java.util.List;/** 類名：IOperationCore<br> *  * 作用: 該介面封裝了資料庫操作的大部分方法<br> *  */public interface IOperationCore {   /** sql更新語句    *     * @param queryString 查詢語句    * @return 返回一個<code>ResultSet</code>結果集    *     * @exception SQLException */   ResultSet executeQuery(String queryString) throws SQLException;   /**      * sql更新語句    *     * @param updateString 資料庫更新語句    * @return 更新資料庫影響行數    *     * @exception SQLException */   int executeUpdate(String updateString) throws SQLException;         @SuppressWarnings("unchecked")public <T> List<T> queryForList(String sql, Class<T> clazz,Object... params)throws SQLException, InstantiationException,IllegalAccessException, InvocationTargetException,ClassNotFoundException;     /**     * 釋放系統串連資源    *     * @exception SQLException 如果關閉失敗將拋出<code>SQLException</code>*/   void dispose() throws SQLException;}

OperationCoreImpl源碼為：

package com.ylink.export;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;/** * 類名：OperationCoreImplements<br> *  * 作用: 該類實現IOperationCore介面的所有方法<br> */public class OperationCoreImpl implements IOperationCore {protected Connection aConnection = null;protected Statement ps = null;protected ResultSet rs = null;protected ResultSetMetaData rsmd = null;protected static OperationCoreImpl m_instance = null;/** * Singleton 即單例(態)模式,用來產生對象唯一執行個體的方法 *  * @return OperationCoreImplements的一個執行個體 * @throws Exception */public static OperationCoreImpl createFactory() throws Exception {if (m_instance == null)m_instance = new OperationCoreImpl();return m_instance;}/** @exception Exception */public OperationCoreImpl() throws Exception {init();}private void init() throws Exception {aConnection = OracleConnectionFactory.getConnection();}/** * 釋放系統串連資源 */public void dispose() {try {if (rs != null)rs.close();} catch (SQLException e) {e.printStackTrace();}try {if (ps != null)ps.close();} catch (SQLException e) {e.printStackTrace();}try {if (aConnection != null)aConnection.close();} catch (SQLException e) {e.printStackTrace();}}/** * 返回ResultSet對象 *  * @param queryString *            查詢語句 * @return 返回一個<code>ResultSet</code>結果集 *  * @exception SQLException */public ResultSet executeQuery(String queryString) {try {ps = aConnection.createStatement();rs = ps.executeQuery(queryString);} catch (SQLException e) {rs = null;e.printStackTrace();}return rs;}//返回list通用   JDBC直連@SuppressWarnings("unchecked")public <T> List<T> queryForList(String sql, Class<T> clazz,Object... params) throws SQLException, InstantiationException,IllegalAccessException, InvocationTargetException,ClassNotFoundException {if (clazz == null) {throw new IllegalArgumentException("clazz is null");}ResultSet rs = null;PreparedStatement ps = null;try {List<T> resultList = new ArrayList<T>();ps = aConnection.prepareStatement(sql);if (params != null) {for (int i = 0; i < params.length; i++) {ps.setObject(i + 1, params[i]);}}rs = ps.executeQuery();T t = null;Method[] allMethod = clazz.getMethods();List<Method> setterMethodList = new ArrayList<Method>();for (Method m : allMethod) {if (m.getName().startsWith("set")) {setterMethodList.add(m);}}String columnName = null;Class parameterType = null;if (rs != null) {while (rs.next()) {t = clazz.newInstance();for (Method m : setterMethodList) {columnName = m.getName().substring(3, 4).toLowerCase()+ m.getName().substring(4, m.getName().length());parameterType = m.getParameterTypes()[0];if (parameterType.isPrimitive()) {if (parameterType == Boolean.TYPE) {m.invoke(t, rs.getBoolean(columnName));} else if (parameterType == Byte.TYPE) {m.invoke(t, rs.getByte(columnName));} else if (parameterType == Short.TYPE) {m.invoke(t, rs.getShort(columnName));} else if (parameterType == Character.TYPE) {m.invoke(t, rs.getString(columnName).charAt(0));} else if (parameterType == Integer.TYPE) {m.invoke(t, rs.getInt(columnName));} else if (parameterType == Long.TYPE) {m.invoke(t, rs.getLong(columnName));} else if (parameterType == Float.TYPE) {m.invoke(t, rs.getFloat(columnName));} else if (parameterType == Double.TYPE) {m.invoke(t, rs.getDouble(columnName));}} else {m.invoke(t, rs.getObject(columnName));}}resultList.add(t);}}return resultList;} finally {dispose();}}/** * 增、刪、改操作 *  * @param updateString *            資料庫更新語句 * @return 更新資料庫影響行數 *  * @exception SQLException */public int executeUpdate(String updateString) {int effectedRows = 0;try {aConnection.setAutoCommit(false);ps = aConnection.createStatement();effectedRows = ps.executeUpdate(updateString);aConnection.commit();} catch (SQLException ex) {System.out.println("資料庫寫操作失敗!");if (aConnection != null) {try {aConnection.rollback();System.out.println("JDBC交易回復成功");} catch (SQLException e) {System.out.println("JDBC交易回復失敗");e.printStackTrace();}}}return effectedRows;}}

OracleConnectionFactory源碼為：

package com.ylink.export;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class OracleConnectionFactory {final static String DRIVER = "oracle.jdbc.driver.OracleDriver";final static String CONSTR = "jdbc:oracle:thin:@172.168.9.70:1521:tps";final static String USERNAME = "tps";final static String USERPASS = "tps";/** * 獲得資料庫連接 * @return */public static  Connection getConnection(){Connection con = null;try {Class.forName(DRIVER);con = DriverManager.getConnection(CONSTR,USERNAME,USERPASS);con.setAutoCommit(false);  //設定不自動認可事務}catch (SQLException e) {System.out.println("sql語句錯誤"+e.getMessage());} catch (ClassNotFoundException e) {System.out.println(e.getMessage());}return con;}}

運行export即可把oracle的表中資料匯入到DB2中