拷贝数据库中的表以及表中的数据

继上篇博客写的拷贝数据库的表里面的数据，这次又来了新要求，在库里没有这个表的情况下，要拷贝这个表以及这个表里面的数据。

步骤大概是，先去库1里面查数据，然后把库1里面的建表sql 拿到，然后去库2里面取这个表名，有就直接insert 数据，没有这个表名，就先把刚才库1的建表语句在库2执行一遍，然后再insert 到库2的表里数据。

这里有个缺点就是，每次操作数据库都要重新连接一次，我对jdbc的原生操作不多，不太熟悉，后面再优化这里。

代码如下：

package com.sitech.module.utils;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;

public class SQLite_To_MySQL {
    private Connection getIteconn(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            return DriverManager.getConnection("jdbc:mysql://ip:端口/test1?useUnicode=true&characterEncoding=utf-8&useSSL=false&autoReconnect=true&failOverReadOnly=false","test1","test1");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    private Connection getMysqlconn(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            return DriverManager.getConnection("jdbc:mysql://ip:端口/test2?useUnicode=true&characterEncoding=utf-8&useSSL=false&autoReconnect=true&failOverReadOnly=false","test1","test1");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public void deal(String tableName) throws SQLException {
        Connection iteconn = getIteconn();
        Statement itestmt =iteconn.createStatement();

        String c = "select * from " + tableName ;
//        ResultSet iters = itestmt.executeQuery("select * from tableName");
        ResultSet iters = itestmt.executeQuery(c);

        Connection iteconn2 = getIteconn();
        Statement itestmt2 =iteconn2.createStatement();

        String d = "SHOW CREATE TABLE  " + tableName;
//        String d = "select * from   sdn_system_user ";
        ResultSet iter2 = itestmt2.executeQuery(d);
        String createSql = null;
        while (iter2.next()){
            String table = iter2.getString("Table");
            createSql = iter2.getString("Create Table");

        }

        //结果集获取到的长度
        int size = iters.getMetaData().getColumnCount();
        //先去检查库里有没有这个表
        Connection mysqlconn2 = getMysqlconn();
        DatabaseMetaData dbmd = mysqlconn2.getMetaData();
        ResultSet rs1 = dbmd.getTables(null, null, tableName, new String[]{"TABLE"});

        if(rs1.next()){
            //有这个表
            String tname=rs1.getString(3);

        }else{
            //没有这个表，先建表

            Connection mysqlconn3 = getMysqlconn();
            Statement statement3 =mysqlconn3.createStatement();
            System.out.println("createSql" + createSql);
//            statement3.execute(createSql);
            if(statement3.execute(createSql)){
                System.out.println("1" + "true");
            }else {
                System.out.println("2" + "false");
            }

        }

            //比较懒，拼接insert into 语句
        StringBuffer sbf =new StringBuffer();
//        sbf.append("insert ignore into sdn_system_menu values ("); replace into
        String b = "insert ignore into " +  tableName + " values( ";
        sbf.append(b);
        String link ="";
        for (int i = 0; i <size ; i++) {
            sbf.append(link).append("?");
            link=",";
        }
        sbf.append(")");
        //MySQL数据库
        Connection mysqlconn = getMysqlconn();
        PreparedStatement mysqlpstmt = mysqlconn.prepareStatement(sbf.toString());

        //取出结果集并向MySQL数据库插入数据 ( 使用批处理 )
        //完成条数
        int count =0;
        int num=0;
        //取消事务(不写入日志)
        mysqlconn.setAutoCommit(false);
        long start = System.currentTimeMillis();
        while (iters.next()) {
            ++count;
            for (int i=1;i<= size;i++) {
                mysqlpstmt.setObject(i, iters.getObject(i));
            }

            //将预先语句存储起来，这里还没有向数据库插入
            mysqlpstmt.addBatch();
            //当count 到达 20000条时 向数据库提交
            if (count % 20000 ==0 ){
                ++num;
                mysqlpstmt.executeBatch();
                System.out.println("第"+num+"次提交,耗时:"+(System.currentTimeMillis()-start)/1000.0+"s");
            }
        }
        //防止有数据未提交
        mysqlpstmt.executeBatch();
        //提交
        mysqlconn.commit();
        System.out.println("完成 "+count+" 条数据,耗时:"+(System.currentTimeMillis()-start)/1000.0+"s");
        //恢复事务
        // mysqlconn.setAutoCommit(true);

        //关闭资源
        close(mysqlconn,mysqlpstmt,null);
        close(iteconn,itestmt,iters);

    }

    public  void  close(Connection conn, Statement stmt, ResultSet rs){

        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {

    Map<String,String> map = new HashMap<String,String>();
       
        map.put("tableName","user");



        SQLite_To_MySQL test = new SQLite_To_MySQL();
            try {
            test.deal(map.get("tableName"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}

这里学到的点：1，操作数据库拿到test1库的建表语句，通过SHOW CREATE TABLE  tableName

2，DatabaseMetaData类的学习，里面可以获取到很多表的信息，表结构等等

     参考连接：https://www.cnblogs.com/dnn179/p/DatabaseMetaData.html

        DatabaseMetaData dbmd = mysqlconn2.getMetaData();
        ResultSet rs1 = dbmd.getTables(null, null, tableName, new String[]{"TABLE"});