使用jdk进行数据迁移(sqlite迁移mysql)

直接粘贴代码

注意:rewriteBatchedStatements=true(加快连接速度)

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);
    }
}