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;
- }
- }
- /**
- * 数据库常用操作封装
- */
- 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;
- }
- }