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