package com.wbg; 
 
import org.omg.Messaging.SYNC_WITH_TRANSPORT; 
 
import java.sql.*; 
import java.util.ArrayList; 
import java.util.List; 
 
public class DBSQLite { 
    //驱动 
    static String DBDRIVERSQLITE="org.sqlite.JDBC"; 
    //文件绝对地址 
    static String DBURLSQLITE="B:\\lagou.db"; 
    static Connection CONNSQLITE; 
    static PreparedStatement PSTATSQLITE; 
 
    //驱动 
    static String DBDRIVERMYSQL="com.mysql.jdbc.Driver"; 
    //数据库 
    static String DBURLMYSQL="jdbc:mysql://localhost:3306/ii?rewriteBatchedStatements=true";//comoany是数据库 
    static Connection CONNMYSQL; 
    static PreparedStatement PSTATMYSQL; 
 
    static { 
        try { 
            Class.forName(DBDRIVERSQLITE); 
            CONNSQLITE= DriverManager.getConnection("jdbc:sqlite:"+DBURLSQLITE); 
 
            Class.forName(DBDRIVERMYSQL); 
            CONNMYSQL=DriverManager.getConnection(DBURLMYSQL,"root","123456"); 
 
        } catch (ClassNotFoundException e) { 
            e.printStackTrace(); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
    } 
    /** 
     * 获取数据库中所有表 
     */ 
    public static List<String> getTable(){ 
        System.out.println("开始获取数据表"); 
        //数据库 
        DatabaseMetaData md=null; 
        List<String> list=new ArrayList<>(); 
        try { 
            //获取数据库 
            md = CONNSQLITE.getMetaData(); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
        try { 
            //进行查询表 
            ResultSet rs=md.getTables(null,null,null,null); 
            if (rs!= null) { 
                list = new ArrayList<String>(); 
            } 
            //往list添加查询到的表 
            while (rs.next()){ 
                list.add(rs.getString("TABLE_NAME")); 
            } 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
        System.out.println("查询数据表成功"); 
        return list; 
    } 
    /** 
     * 向mysql创建数据表 
     */ 
    public static void createTable(){ 
        ResultSetMetaData rsmd = null; 
        //获取所有数据库 
        List<String> list=DBSQLite.getTable(); 
        for (int i = 0; i < list.size(); i++) { 
            long srop=System.currentTimeMillis(); 
            System.out.println("开始创建第" + (i + 1 )+ "数据表..."); 
            //查询表 
            String sql = "select * from " + list.get(i); 
            int size = 0; 
            try { 
                PSTATSQLITE = CONNSQLITE.prepareStatement(sql); 
                //获取数据表 
                rsmd = PSTATSQLITE.getMetaData(); 
                //创建语句 
                String create = "create table " + list.get(i) + "("; 
                //fh目的最后一个没有逗号 
                String fh = ""; 
                //循环表中所有字段 
                size = rsmd.getColumnCount(); 
                for (int j = 0; j < size; j++) { 
                    //字段名 类型 
                    create += fh + rsmd.getColumnName(j + 1) + "  " + rsmd.getColumnTypeName(j + 1); 
                    //判断长度是否不为0  比如varchar(10)  integer 
                    if (rsmd.getPrecision(j + 1) != 0) { 
                        //不为0就给个长度 
                        create += "(" + rsmd.getPrecision(j + 1) + ")"; 
                    } 
                    fh = ","; 
                } 
                create += ");"; 
                execute(create); 
            } catch (SQLException e) { 
                e.printStackTrace(); 
            } 
            System.out.println("开始向(" + list.get(i) + ")数据表添加数据..."); 
           if (insertTable( list.get(i), size)){ 
                System.out.println("第" + (i + 1 ) + "个数据表数据录入完毕"); 
            } 
            else { 
                System.out.println("第" + (i + 1 ) + "个数据表数据录入失败"); 
            } 
        } 
    } 
    /** 
     * 向mysql数据表录入数据 
     * sql  查询语句  size 字段长度 
     */ 
    public static boolean insertTable(String sql,int size){ 
        ResultSet rs=executeQuerySQLITE("select * from "+sql); 
        try { 
            String fh=""; 
            String sqladd="insert into "+sql+" values("; 
            for (int i = 0; i < size; i++) { 
                sqladd+=(fh+"?"); 
                fh=","; 
            } 
            sqladd+=")"; 
            //关闭自动提交 
            CONNMYSQL.setAutoCommit(false); 
            PSTATMYSQL=CONNMYSQL.prepareStatement(sqladd); 
 
            while (rs.next()){ 
 
                for (int i = 0; i < size; i++) { 
                    PSTATMYSQL.setObject(i+1,rs.getObject(i+1)); 
                } 
                PSTATMYSQL.addBatch(); 
            } 
            PSTATMYSQL.executeBatch(); 
            //提交 
            CONNMYSQL.commit(); 
            //打开自动提交 
            CONNMYSQL.setAutoCommit(true); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
        return true; 
    } 
    public static ResultSet executeQuerySQLITE(String  sql){ 
        try { 
            PSTATSQLITE=CONNSQLITE.prepareStatement(sql); 
            return PSTATSQLITE.executeQuery(); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
        return null; 
    } 
    public static void execute(String sql){ 
        try { 
            PSTATMYSQL=CONNMYSQL.prepareStatement(sql); 
            PSTATMYSQL.execute(); 
        } catch (SQLException e) { 
            System.out.println("创建数据库失败"); 
        } 
    } 
    public static int executeUpdate(String sql ,Object[]in){ 
        try { 
            PSTATMYSQL =CONNMYSQL.prepareStatement(sql); 
            for (int i = 0; i <in.length; i++){ 
                PSTATMYSQL.setObject(i+1,in[i]); 
            } 
            return  PSTATMYSQL.executeUpdate(); 
 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
        return 0; 
    } 
 
    public static void main(String[] args) { 
        long start= System.currentTimeMillis(); 
        System.out.println("开始迁移数据"); 
         createTable(); 
        System.out.println("迁移完毕,耗时:"+(System.currentTimeMillis()-start)/1000); 
    } 
}