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

packagecomnf147Package;import java.sql.*;public classDateMigrationLagou {//连接SQLite

private Connection getSqlite() throwsException {

Class.forName("org.sqlite.JDBC");return DriverManager.getConnection("jdbc:sqlite:E:\\data\\lagou.db");

}//连接MariaDB

private Connection getMariaDb() throwsException {

Class.forName("com.mysql.jdbc.Driver");return DriverManager.getConnection("jdbc:mysql://localhost:3306/lagouDB?rewriteBatchedStatements=true", "root", "666666");

}//释放资源

private voidrelease(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 voidOperatingControl() {//从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);

}

}

}