废话不多说,直接上代码:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
public class TestService {
public void test() {
try {
/* 插入数据批次大小 */
int pitchSize = 10000;
/* 加载驱动 */
Class.forName("oracle.jdbc.OracleDriver");
/* 数据库连接串 */
String url = "testUrl";
/* 用户名 */
String user = "testUser";
/* 数据库密码 */
String password = "testPassword";
/* 获取数据库连接 */
Connection queryConnection = DriverManager.getConnection(url, user, password);
/* 获取入表的数据库连接 */
Connection connection = DriverManager.getConnection(url, user, password);
/* 自动提交设为false */
connection.setAutoCommit(false);
/* 注意:查询的字段列表和插入的参数列表要完全一致,包括数量、字段名 */
String querySql = "SELECT * FROM SHCEMA1.TABLE1";
String insertSql = "INSERT INTO SHCEMA1.TABLE2 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
/* 开启流式读取 */
PreparedStatement preparedStatement = queryConnection.prepareStatement(querySql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
/* 每次读取的记录条数 */
preparedStatement.setFetchSize(5000);
/* 执行查询语句,获取查询结果 */
ResultSet resultSet = preparedStatement.executeQuery();
/* 插入语句预编译 */
PreparedStatement insertStatement = connection.prepareStatement(insertSql);
/* 创建线程池 */
ExecutorService threadPool = Executors.newFixedThreadPool(20);
/* 控制批次,确定何时提交 */
int count = 1;
/* 循环读取查询结果 */
while (resultSet.next()) {
/* 设置参数 */
int columnCount = resultSet.getMetaData().getColumnCount();
for (int i = 1; i <= columnCount; ++i) {
insertStatement.setObject(i, resultSet.getObject(i));
}
/* 添加批次 */
insertStatement.addBatch();
if (count % pitchSize == 0) {
/* 如果达到设定的批次大小,提交线程池,执行入库操作 */
TransThread transThread = new TransThread(insertStatement, connection);
threadPool.submit(transThread);
/* 为每个线程新建连接 */
connection = DriverManager.getConnection(url, user, password);
connection.setAutoCommit(false);
insertStatement = connection.prepareStatement(insertSql);
}
++count;
}
/* 不满一批的任务提交 */
if (count % pitchSize != 0 && count != 1) {
TransThread transThread = new TransThread(insertStatement, connection);
threadPool.submit(transThread);
}
/* 资源关闭 */
if (!insertStatement.isClosed()){
insertStatement.close();
}
if (!connection.isClosed()){
connection.close();
}
resultSet.close();
preparedStatement.close();
queryConnection.close();
threadPool.shutdown();
} catch (Exception e) {
/* 异常处理 */
e.printStackTrace();
}
}
}
import java.sql.Connection;
import java.sql.PreparedStatement;
public class TransThread implements Runnable {
private PreparedStatement preparedStatement;
private Connection connection;
public TransThread(PreparedStatement preparedStatement, Connection connection) {
this.preparedStatement = preparedStatement;
this.connection = connection;
}
@Override
public void run() {
try {
long startTime = System.currentTimeMillis();
/* 执行批量SQL */
preparedStatement.executeBatch();
preparedStatement.clearBatch();
/* 提交事务 */
connection.commit();
/* 关闭资源 */
preparedStatement.close();
connection.close();
System.out.println("****** 当前批次耗时:" + (System.currentTimeMillis() - startTime) + "ms ******");
} catch (Exception e) {
/* 异常处理 */
e.printStackTrace();
}
}
}
有一些资源处理、异常处理逻辑都不严谨,只是简单处理。
测试的400万数据传输需要6分钟,效率还行吧。获取数据库连接可以使用数据库连接池,效率应该还会提升,可以自行实践。
插入SQL的传参也可以优化,可以根据列名来传参,可以不受顺序影响,这里只是最简单的实现方式。
插一嘴,spring boot可以通过@Value注解获取配置文件的数据库信息:
@Value("${spring.datasource.url:}") private String url;