jdbc操作 数据库同步，全量，加入线程，批处理

动态数据库的全量、增量同步，多线程增加性能，批处理。

代码可以直接运行：

同步的类：

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
public class SynchronizationController{
    //同步源
    static  String url_source="jdbc:mysql://localhost:3306/zntz?user=root&password=123456&useUnicode=true&characterEncoding=UTF8";
    //目标库
    static String url_destination="jdbc:mysql://localhost:3306/xx01?user=root&password=123456&useUnicode=true&characterEncoding=UTF8";

    static Connection conn_source = null;

    static Connection conn_destination = null;

    static String sql_read;

    static String sql_insert;

    static final int batchSize = 15000;

    static final int max_thread_size=5;

    public static void init(){

    }

    public static void writeData(){

    }

    public static void main(String[] args) throws SQLException, InterruptedException {
        try {
             Class.forName("com.mysql.jdbc.Driver");
             conn_source = DriverManager.getConnection(url_source);
             conn_destination= DriverManager.getConnection(url_destination);
             conn_destination.setAutoCommit(false); 
             synchronizationTables(conn_source, conn_destination);

             addData(conn_source, conn_destination);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }catch (SQLException e) {
            e.printStackTrace();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        finally {
                while(true){
                    if(InsertThread.getThreadCounts()>0){
                        Thread.sleep(1000);
                    }else{
                        break;
                    }
                }
            conn_source.close();
            conn_destination.close();
        }

    }



    //本地获取表名获取表名
    public static Set<String> getTableName(Connection con) {
        Set<String> set = new HashSet<String>();
        try {
            DatabaseMetaData meta = con.getMetaData();
            ResultSet rs = meta.getTables(null, null, null,new String[] { "TABLE" });
            while (rs.next()) {
                  set.add(rs.getString("TABLE_NAME"));
//                String s = rs.getString("TABLE_NAME");
//                String type = rs.getString("TABLE_TYPE");
//                System.out.println(s+"======"+type);
//                getTableDDL(rs.getString("TABLE_NAME"), con);
            }

        } catch (Exception e) {           
            e.printStackTrace();
        }
        return set;
    }
    //目标数据库
    public static Map<String,String> getTableNameToMap(Connection con) {
        Map<String,String> map=new HashMap<String,String>();
        try {
            DatabaseMetaData meta = con.getMetaData();
            ResultSet rs = meta.getTables(null, null, null,new String[] { "TABLE" });
            while (rs.next()) {
                map.put(rs.getString("TABLE_NAME"),"1");
            }

        } catch (Exception e) {           
            e.printStackTrace();
        }
        return map;
    }

    //创建表
    public static void createTable(String sql_ddl) throws SQLException {
            Statement stmt = conn_destination.createStatement();
            int result = stmt.executeUpdate(sql_ddl);// executeUpdate语句会返回一个受影响的行数，如果返回-1就没有成功
            if (result != -1) {
                System.out.println("表创建成功");
            }else{
                System.out.println("表创建失败："+sql_ddl);
            }
    }

    //创建sql
    public static String getTableField(String tableName,Connection con) throws SQLException{
           String sql = "select * from "+tableName;
           Statement state = con.createStatement();
           ResultSet rs = state.executeQuery(sql);
           ResultSetMetaData rsd = rs.getMetaData() ;
           StringBuffer sql_model=new StringBuffer("insert into "+ tableName +" (");
           StringBuffer sql_param=new StringBuffer(" VALUES(");
           for(int i = 1; i <= rsd.getColumnCount(); i++) {  
               sql_model.append(rsd.getColumnName(i));
               sql_param.append("?");
               if (i < rsd.getColumnCount()) {
                   sql_model.append(",");
                   sql_param.append(",");
               }
              }
           sql_model.append(") ");sql_param.append(") ");
           System.out.println(sql_model.toString()+sql_param.toString());
          return sql_model.toString()+sql_param.toString();
    }

    public static void getTableField2(String tableName,Connection conn) throws SQLException{

            ResultSet rs = conn.getMetaData().getColumns(null, conn.getMetaData().getUserName(),tableName.toUpperCase(), "%");

            while(rs.next()){  
                String colName = rs.getString("COLUMN_NAME"); 
                String remarks = rs.getString("REMARKS");  
                String dbType = rs.getString("TYPE_NAME");  

                System.out.println(colName+","+remarks+","+dbType);
            }
    }

    //获取表结构ddl
    public static String getTableDDL(String tableName,Connection conn) throws SQLException{
        ResultSet rs = null;
        PreparedStatement ps = null;
        ps = conn.prepareStatement("show create table "+tableName);
        rs = ps.executeQuery();
        StringBuffer ddl=new StringBuffer(); 
        while (rs.next()) {
            ddl.append(rs.getString(rs.getMetaData().getColumnName(2)));
        }
        return ddl.toString();
    }

    /**
     * 检查本地库所有表在B库里是否存在，是否一致
     * A本地库  B目标库
     */
    public static void synchronizationTables(Connection conA,Connection conB) throws SQLException{
        Set<String> a_set=getTableName(conA);
        Map<String,String> b_map=getTableNameToMap(conB);

        Iterator<String> it=a_set.iterator();
        while(it.hasNext()){
            String n=it.next();
            if(b_map.get(n)==null){
                System.out.println("表名："+n+"   不在目标库里");
                String create_table_ddl=getTableDDL(n, conA);
                createTable(create_table_ddl);
            }
        }
    }

    //清楚表数据
    public static boolean clearTableData(String tableName,Connection con){
        try {
            Statement stmt = con.createStatement();
            String sql = "TRUNCATE TABLE  "+tableName;
            stmt.executeUpdate(sql);
            System.out.println(tableName+":表数据已被清空");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("异常表："+tableName+"----数据清空失败");
            return false;
        }
        return true;
    }


    public static void addData(Connection conA,Connection conB) throws SQLException, InterruptedException{
        Statement stmt_source = conA.createStatement();
         Set<String> tableNameSet=getTableName(conn_source);
         Iterator<String> it = tableNameSet.iterator(); 
         //遍历表
         while (it.hasNext()) {  
              long start = System.currentTimeMillis();
              String str = it.next();  
              if(!clearTableData(str, conB)){
                  continue;
              }
              while(true){
                  if(InsertThread.getThreadCounts()>0){
                      Thread.sleep(3000);
                  }else{
                      break;
                  }
              }
              String sql_insert=getTableField(str, conA);
              //获取总条数 分页查询
              String sql_count="select count(*) from "+ str;
              ResultSet rs = stmt_source.executeQuery(sql_count);
              rs.next();
              int totalCount=rs.getInt(1);
              if(totalCount>batchSize){
                  int max=totalCount%batchSize==0 ? totalCount/batchSize : totalCount/batchSize+1;
                  for(int i=0;i<max;i++){
                      synchronized (InsertThread.class) { 
                          String sql_data="select * from "+str+" limit "+ i*batchSize + " , "+batchSize;
                          int tCount = InsertThread.getThreadCounts();  
                          while (tCount >= max_thread_size) {  
                                System.out.println("系统当前线程数为：" + tCount+ "，已达到最大线程数 "+max_thread_size+"，请等待其他线程执行完毕并释放系统资源");  
                                InsertThread.class.wait();  
                                tCount = InsertThread.getThreadCounts();  
                            }  
                            // 重新启动一个子线程  
                            Thread td = new InsertThread(sql_data, sql_insert, conB, conA);
                            td.start();  
                            System.out.println("已创建新的子线程: " + td.getName());  
                      }      
                  }
              }else{
                  String sql_data="select * from "+str;
                  Thread td = new InsertThread(sql_data, sql_insert, conB, conA);
                  td.start();  
              }
              long end = System.currentTimeMillis();
              System.out.println(str+"  表数据导入完成,耗时："+(end-start)/1000+"秒,"+(end-start)/60000+"分钟");

         }
    }

}

线程控制类：

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

public class InsertThread extends Thread {

    private String sql_data;

    private String sql_insert;

    private Connection conB;

    private Connection conA;

    static  int batchSize = 2500;

    // 线程计数器  
    static private int threadCounts;  
    // 线程名称池  
    static private String threadNames[];  

    static {  
        // 假设这里允许系统同时运行最大线程数为10个  
        int maxThreadCounts = 5;  
        threadNames = new String[maxThreadCounts];  
        // 初始化线程名称池  
        for (int i = 1; i <= maxThreadCounts; i++) {  
            threadNames[i - 1] = "子线程_" + i;  
        }  
    }  

    public InsertThread() {  
        // 临界资源锁定  
        synchronized (InsertThread.class) {  
            // 线程总数加1  
            threadCounts++;  
            // 从线程名称池中取出一个未使用的线程名  
            for (int i = 0; i < threadNames.length; i++) {  
                if (threadNames[i] != null) {  
                    String temp = threadNames[i];  
                    // 名被占用后清空  
                    threadNames[i] = null;  
                    // 初始化线程名称  
                    this.setName(temp);  
                    break;  
                }  
            }  
        }  
    }  

    public void run() {  
        try {
            Long start = System.currentTimeMillis();
            Statement stmt_source = conA.createStatement();
            ResultSet rs_sql_data = stmt_source.executeQuery(sql_data);
            ResultSetMetaData rsmd = rs_sql_data.getMetaData();
            PreparedStatement ps = conB.prepareStatement(sql_insert);
            int columnCount=rsmd.getColumnCount();
            int count=1;
            while (rs_sql_data.next()) {
                count++;
                for(int k=1;k<=columnCount;k++){
                  ps.setString(k, rs_sql_data.getString(k));
                }                
                ps.addBatch();
                if(count % batchSize == 0) {
                    ps.executeBatch();
                    conB.commit();
                }
            }
            ps.executeBatch();
            conB.commit();
            Long end = System.currentTimeMillis();
            System.out.println(this.getName()+",耗时："+(end-start)/1000 + "秒");
            stmt_source.close();
            rs_sql_data.close();
            ps.close();
        } catch (Exception e) {  
            System.out.println(e);  
        } finally {  
            synchronized (InsertThread.class) {  

                // 释放线程名称  
                String[] threadName = this.getName().split("_");  
                // 线程名使用完后放入名称池  
                threadNames[Integer.parseInt(threadName[1]) - 1] = this.getName();  

                // 线程运行完毕后减1  
                threadCounts--;  
                /* 
                 * 通知其他被阻塞的线程，但如果其他线程要执行，则该同步块一定要运行结束（即直 
                 * 到释放占的锁），其他线程才有机会执行，所以这里的只是唤醒在此对象监视器上等待 
                 * 的所有线程，让他们从等待池中进入对象锁池队列中，而这些线程重新运行时它们一定 
                 * 要先要得该锁后才可能执行，这里的notifyAll是不会释放锁的，试着把下面的睡眠语 
                 * 句注释去掉，即使你已调用了notify方法，发现CreateThread中的同步块还是好 
                 * 像一直处于对象等待状态，其实调用notify方法后，CreateThread线程进入了对象锁 
                 * 池队列中了，只要它一获取到锁，CreateThread所在线程就会真真的被唤醒并运行。 
                 */  
                InsertThread.class.notifyAll();  

                System.out.println("----" + this.getName() + " 所占用资源释放完毕，当前系统正在运行的子线程数："  
                        + threadCounts);  
            }  
        }  
    }  


    static public int getThreadCounts() {  
        synchronized (InsertThread.class) {  
            return threadCounts;  
        }  
    }

    public InsertThread(String sql_data, String sql_insert, Connection conB, Connection conA) {
        super();
        this.sql_data = sql_data;
        this.sql_insert = sql_insert;
        this.conB = conB;
        this.conA = conA;
        // 临界资源锁定  
        synchronized (InsertThread.class) {  
            // 线程总数加1  
            threadCounts++;  
            // 从线程名称池中取出一个未使用的线程名  
            for (int i = 0; i < threadNames.length; i++) {  
                if (threadNames[i] != null) {  
                    String temp = threadNames[i];  
                    // 名被占用后清空  
                    threadNames[i] = null;  
                    // 初始化线程名称  
                    this.setName(temp);  
                    break;  
                }  
            }  
        }  
    }





}