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中