java实现数据库之间批量插入数据

java实现数据库之间批量插入数据

 

package comnf147Package;

import java.sql.*;

public class DateMigrationLagou {
    
    //连接SQLite
    private Connection getSqlite() throws Exception {
        Class.forName("org.sqlite.JDBC");
        return DriverManager.getConnection("jdbc:sqlite:E:\\data\\lagou.db");
    }

    //连接MariaDB
    private Connection getMariaDb() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        return DriverManager.getConnection("jdbc:mysql://localhost:3306/lagouDB?rewriteBatchedStatements=true", "root", "666666");
    }

    //释放资源
    private void release(Connection coon, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (coon != null) {
            try {
                coon.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void OperatingControl() {

        //从SQLite中取数据
        Connection SQliteConn = null;
        Statement SQliteSt = null;
        ResultSet SQliteRS = null;

        //添加到MaiiaDb
        Connection MariaDbConn = null;
        PreparedStatement MariaDbPs = null;


        try {
            //获取数据
            SQliteConn = this.getSqlite();
            SQliteSt = SQliteConn.createStatement();
            SQliteRS = SQliteSt.executeQuery("select * from lagou_position");

            //添加数据
            MariaDbConn = this.getMariaDb();
            MariaDbPs = MariaDbConn.prepareStatement("insert into lagou_position values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

            // 关闭事务自动提交 ,这一行必须加上，否则每插入一条数据会向log插入一条日志
            MariaDbConn.setAutoCommit(false);

            int i = 0;
            //计时开始
            long startime = System.currentTimeMillis();
            //设置批量处理的数量

            while (SQliteRS.next()) {
                for (int j = 1; j < 20; j++) {
                    MariaDbPs.setObject(j, SQliteRS.getObject(j));
                }
                MariaDbPs.addBatch();
                //把若干sql语句装载到一起，然后一次送到数据库执行，执行需要很短的时间
                // 每 10000 条，向数据库发送一次执行请求
                if (++i % 10000 == 0) {
                    MariaDbPs.executeBatch();
                }
            }
            //执行批量处理语句；
            MariaDbPs.executeBatch();
             提交事务
            MariaDbConn.commit();

            //结束时间
            long stoptime = System.currentTimeMillis();
            //输出结果
            System.out.println("总数据" + i);
            System.out.println("插入用时" + (stoptime - startime) / 1000.0 + " 秒 ");
        } catch (Exception e) {
            try {
                if (MariaDbConn != null) {
                    MariaDbConn.rollback();
                }
            } catch (SQLException e1) {
            }
        } finally {
            this.release(SQliteConn, SQliteSt, SQliteRS);
            this.release(MariaDbConn, MariaDbPs, null);
        }
    }

}

    public static void main(String[] args) {

        //调用方式
        DateMigrationLagou dateMigrationLagou = new  DateMigrationLagou();
        dateMigrationLagou.OperatingControl();
    }
    

 

 

 

 

 

 

 

 

posted @ 2018-11-22 08:49 梓鸿 阅读(...) 评论(...) 编辑 收藏