问题背景:
多个定时任务定期运行,各任务从若干张表中取出数据处理后形成千万级别数据再入库表😖,任务之间有关联关系,任务B依赖任务A产出的表,任务C依赖任务B产出的表....。
任务之间有依赖关系,数据又是千万级别数量,那数据入库速度至少得1s处理几万条数据吧。
想要实现这种飞一般的处理速度,就需要用到JDBC两个对象:PrepareStatement、Statement
简单介绍PreparedStatement 和 Statement 的区别
PreparedStatement在使用时只需要编译一次,就可以运行多次,Statement每运行一次就编译一次,所以PreparedStatement的效率更高
PreparedStatement需要的sql语句为用?(占位符)来替换值,Statement所需要的sql语句为字符串拼接
PreparedStatement解决了sql注入的问题,Statement没有解决,因为PreparedStatement有一个预编译的过程,就算传入占位符的数据中有sql关键字也都被认为是值。Statement所需要的是字符串拼接,传入的整个字符串被默认为sql语句,如果用户手动拼接了字符串,那么会导致语句的改变
从区别中看出,preparedStatement的效率会更高点,就使用它了。
要想使用preparedStatement,我们得自己手搓数据库连接😆😆😆,不能依赖springboot集成mysql。
要想使用preparedStatement,我们得自己手搓数据库连接😆😆😆,不能依赖SpringBoot集成Mysql。(目前这套方法已经在Mysql、ClickHouse数据库上有明显效果,本文着重介绍Mysql使用方法,感兴趣的同学可以尝试其他数据库如何使用。)
编写JDBC数据库连接DBHelper辅助工具类
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBHelper {
private static final Logger log = LoggerFactory.getLogger(DBHelper.class);
private static final String dbUrl = "jdbc:mysql://localhost:3306/animalhome?serverTimezone=UTC&rewriteBatchedStatements=true";
private static final String username = "root"; //用户名
private static final String password = ""; //密码
private static final String driverClassName = "com.mysql.jdbc.Driver"; //连接类型
public Connection conn = null; //数据库连接对象
public DBHelper() {}
/**
* 提供功能接口,用于获取连接对象
* @return Connection
*/
public Connection getConnection(){
return conn;
}
//开启数据库连接
public void openConnection() {
try {
Class.forName(driverClassName);//指定连接类型
conn = DriverManager.getConnection(dbUrl, username, password);//获取连接
conn.setAutoCommit(false);//关闭自动提交
} catch (Exception e) {
log.error("数据库连接失败,请联系相关人员排查!",e);
}
}
//关闭数据库连接
public void closeConnection() {
try {
this.conn.close();
} catch (SQLException e) {
log.error("关闭数据库连接失败,请联系相关人员排查!",e);
}
}
}
定义User对象,后续测试类作为处理对象使用。
public class User {
private String username;
private String password;
private String address;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "user{" +
"username='" + username + '\'' +
", password='" + password + '\'' +
", address='" + address + '\'' +
'}';
}
}
关键代码编写
定义数据处理类DataHandler,增加无参构造方法实现开启数据库连接、预编译sql语句返回PreparedStatement实例功能。(注释已在代码中)
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DataHandler {
private static final Logger log = LoggerFactory.getLogger(DataHandler.class);
//自定义数据库连接对象
private DBHelper db;
private PreparedStatement statement;
//prepareStatement的sql语句是使用?(占位符)赋值。
private String insertUser = "insert into user (username,password,address) values(?,?,?)";
public DataHandler() {
//new一个
db = new DBHelper();
//开启数据库连接
db.openConnection();
try {
//预编译sql语句,返回PreparedStatement实例
statement = db.conn.prepareStatement(insertUser);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
接下来的数据处理中,我们又会用到JDBC批量处理语句的三个方法:
addBatch(String):添加需要批量处理的SQL语句或是参数;
executeBatch():执行批量处理语句;
clearBatch():清空缓存的数据;
setObject():使用给定对象设置指定参数的值。
增加处理对象数据方法,解析添加到批量sql中,达到入库最大批次后批量入库。(注释已在代码中)
//入库总数
private long totalLine = 0;
//入库数量
private int counter = 0;
//最大入库数量
private int maxBatch =10000;
/**
* 处理数据:解析添加到批量SQL中,达到入库最大批次后批量入库
* @author xiafan
* @param user 处理数据
* @param count 行标识
* @throws Exception
*/
public void insertUser(User user, long count) throws Exception {
try {
totalLine++;
counter++;
if (user != null) {
statement.setObject(1, user.getUsername());
statement.setObject(2, user.getPassword());
statement.setObject(3, user.getAddress());
statement.addBatch();//将数据转为一条sql语句
// 达到一个批次最大值,入库
if (counter == maxBatch) {
// 批量处理
statement.executeBatch();
// 清空缓存数据,结合最大入库数量的判断,做到双重防止内存溢出问题。
statement.clearBatch();
// 记录读取数量重置为0,重新累加最大入库数量。
counter = 0;
}
}
//数据量小于最大批次入库量,且是最后一行则收尾。
if (count < maxBatch && count == 1) {
statement.executeBatch();
statement.clearBatch();
//关闭PreparedStatement实例
statement.close();
//为了防止预编译过程中程序异常,故改为手动提交sql
db.conn.commit();
//关闭数据库连接
db.closeConnection();
log.info(Thread.currentThread().getName() + " parse total line is:" + totalLine);
}
} catch (Exception e){
throw new Exception("预编译插入语句出现问题,请联系运维人员排查!",e);
}
}
最后编写测试类,开始大力出奇迹! 🙏 🙏 🙏
import java.util.ArrayList;
import java.util.List;
public class demo {
public static void main(String[] args) throws Exception {
DataHandler dataHandler = new DataHandler();
List<User> list = new ArrayList<>();
User usercs = new User();
usercs.setUsername("孙悟空");
usercs.setPassword("No1");
usercs.setAddress("南京市");
for (int i = 0; i < 1000000; i++) {
list.add(usercs);
}
long count = list.size();
long begin = System.currentTimeMillis();
System.out.println("开始时间为:" + begin);
for (User user: list) {
dataHandler.insertUser(user,count);
count--;
}
long end = System.currentTimeMillis();
System.out.println("结束时间为:" + (end-begin) / 1000 + "秒");
}
}
本机电脑运行结果如下:
数据库如下:
收货满满的成就感!!!
结语:
入库100w条数据仅仅耗时8秒,对于目前我所做需求来说绰绰有余,使用时只需根据不同电脑性能控制每次执行sql要导入的数据量即可,欢迎大家有更好的建议,亦或者更高的效率入库方法可以跟我讨论。