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中