代码&优雅着&生活

导航

不同数据源之间的数据同步jdbc解决方案

最近项目中用到的数据要从一个数据源获取存进另一个数据源,简单的jdbc解决方案。

package com.sh.ideal.test.syns;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class TableSyns {
    
    public static String[] TABLE={"TB_XSCJ_RWD","TB_XSCJ_HFD_CS"};
    
    public static String[] TABLE_TARGET={"T_TEST","T_TEST1"};
    
    public static void main(String[] args) {
        
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs=null;
    
    Connection conn1 = null;
    Statement stmt1 = null;
    try {
        //String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //加载SQLServerJDBC驱动 
        Class.forName("oracle.jdbc.driver.OracleDriver");    //实例化oracle数据库驱动程序(建立中间件)
        String url = "jdbc:oracle:thin:@192.168.1.99:9083:PBD";    //@localhost为服务器名,sjzwish为数据库实例名
        conn = DriverManager.getConnection(url, "pbd", "pbd");    //连接数据库,a代表帐户,a代表密码
        stmt = conn.createStatement();    //提交sql语句,创建一个Statement对象来将SQL语句发送到数据库
        
        
        String url1 = "jdbc:oracle:thin:@192.168.1.99:9083:PBD";    //@localhost为服务器名,sjzwish为数据库实例名
        conn1 = DriverManager.getConnection(url1, "pbd", "pbd");    //连接数据库,a代表帐户,a代表密码
        stmt1 = conn1.createStatement();    //提交sql语句,创建一个Statement对象来将SQL语句发送到数据库
        
        
        for (int i = 0; i < TABLE.length; i++) {
            
        
        //查询数据用executeQuery
        String sql="select * from "+TABLE[i];//执行查询,(ruby)为表名
        rs = stmt.executeQuery(sql);
        
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        StringBuffer sbf=new StringBuffer();
        
        for (int m = 1; m <= columnCount; m++) {
            if(m==columnCount){
                sbf.append(rsmd.getColumnName(m));
            }else{
                sbf.append(rsmd.getColumnName(m)+",");
            }
        }
        while (rs.next()) {
            StringBuffer sb=new StringBuffer();
            for (int j = 1; j <= columnCount; j++) {
                Object o=rs.getObject(j);
                if(o==null){
                    o="";
                }
                if(j==(columnCount)){
                    sb.append("'"+o+"'");
                }else{
                    sb.append("'"+o+"'").append(",");
                }
            }
            String insertsql="insert into "+TABLE_TARGET[i]+"("+sbf.toString()+") values("+sb.toString()+")";
            System.out.println(insertsql);
            
            stmt1.execute(insertsql);
            
        }
        
        
        //1代表当前记录的第一个字段的值,可以写成字段名。
        //2代表当前记录的第二个字段的值,可以写成字段名。

        //添加数据用executeUpdate
        //stmt.executeUpdate("insert into ss values(7,'张学友')");

        //修改数据用executeUpdate
        //stmt.executeUpdate("update ss set name = '张曼玉' where id = 5");

        //删除 数据用executeUpdate
        //stmt.executeUpdate("delete from ss where id = 6");
        
        }

    } catch (SQLException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e1) {
        e1.printStackTrace();
    }finally{
        try {
            //关闭数据库,结束进程
            rs.close();
            stmt.close();
            conn.close();
            
            stmt1.close();
            conn1.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}
}

 

posted on 2015-10-15 10:49  幸运的凌人  阅读(1506)  评论(0编辑  收藏  举报