jdbc批量插入10万/100万条数据
package arthur.dy.lee.mybatisplusdemo.jdbc;
/**
* @auther arthur.dy.lee
* @since 2019/4/20 20:06
*/
import lombok.extern.slf4j.Slf4j;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
@Slf4j
public class InsertJobBatch extends Thread {
public static void main(String[] args) throws Exception {
for (int i = 1; i <= 10; i++) {
new InsertJobBatch().start();
}
}
@Override
public void run() {
String url = "jdbc:mysql://localhost:3306/xxl_job?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=false";
String name = "com.mysql.jdbc.Driver";
String user = "root";
String password = "root";
Connection conn = null;
try {
Class.forName(name);//指定连接类型
conn = DriverManager.getConnection(url, user, password);//获取连接
if (conn != null) {
System.out.println("获取连接成功");
insert(conn);
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static void insert(Connection conn) {
// 开始时间
Long begin = System.currentTimeMillis();
// sql前缀
String prefix = "INSERT INTO xxl_job_info (job_group,job_cron,job_desc,add_time,update_time,author,alarm_email,"
+ "executor_route_strategy,executor_handler,executor_param,executor_block_strategy,executor_timeout,"
+ "executor_fail_retry_count,glue_type,glue_source,glue_remark,glue_updatetime,child_jobid,trigger_status,"
+ "trigger_last_time,trigger_next_time) VALUES ";
try {
// 保存sql后缀
StringBuffer sqlVal = new StringBuffer();
// 设置事务为非自动提交
conn.setAutoCommit(false);
// 比起st,pst会更好些
DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
// 外层循环,总提交事务次数
for (int i = 1; i <= 1; i++) {
sqlVal = new StringBuffer();
// 第j次提交步长
for (int j = 1; j <= 10; j++) {
//int id = i*j;
//Sequence sequence = new Sequence();
//int id = (int) sequence.nextId();
int jobGroup = 3;
String jobCron = "*/20 * * * * ?";
String jobDesc = "批量测试-" + i;
Date addTime = new Date();
String nowTime = format.format(addTime);
String author = "lee";
String alarmEmail = "arthur@1.com";
String executorRouteStrategy = "CONSISTENT_HASH"; //FIRST, CONSISTENT_HASH
String executorHandler = "financeHandler";
String executor_param = "paramArthur";
String executorBlockStrategy = "DISCARD_LATER"; //DISCARD_LATER, COVER_EARLY , SERIAL_EXECUTION
int executorTimeout = 30;
String executorFailRetryCount = "3";
String glueType = "BEAN";
String glueSource = "";
String glueRemark = "GLUE代码初始化";
String glueUpdatetime = nowTime;
String childJobid = "";
int triggerStatus = 1;
Long triggerLastTime = 0L;
Long triggerNextTime = 0L;
sqlVal.append("(");
//sqlVal.append(id).append(",");
sqlVal.append(jobGroup).append(",");
sqlVal.append("'" + jobCron + "',");
sqlVal.append("'" + jobDesc + "',");
sqlVal.append("'" + nowTime + "',");
sqlVal.append("'" + nowTime + "',");
sqlVal.append("'" + author + "',");
sqlVal.append("'" + alarmEmail + "',");
sqlVal.append("'" + executorRouteStrategy + "',");
sqlVal.append("'" + executorHandler + "',");
sqlVal.append("'" + executor_param + "',");
sqlVal.append("'" + executorBlockStrategy + "',");
sqlVal.append(executorTimeout).append(",");
sqlVal.append(executorFailRetryCount).append(",");
sqlVal.append("'" + glueType + "',");
sqlVal.append("'" + glueSource + "',");
sqlVal.append("'" + glueRemark + "',");
sqlVal.append("'" + glueUpdatetime + "',");
sqlVal.append("'" + childJobid + "',");
sqlVal.append(triggerStatus).append(",");
sqlVal.append(triggerLastTime).append(",");
sqlVal.append(triggerNextTime);
sqlVal.append("),");
}
// 构建完整SQL
String sql = prefix + sqlVal.substring(0, sqlVal.length() - 1);
// 添加执行SQL
PreparedStatement pst = (PreparedStatement) conn.prepareStatement(sql);
pst.addBatch(sql);
// 执行操作
pst.executeBatch();
// 提交事务
conn.commit();
pst.close();
}
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
// 结束时间
Long end = System.currentTimeMillis();
// 耗时
System.out.println("数据插入花费时间 : " + (end - begin) / 1000 + " s");
System.out.println("插入完成");
}
//生成固定长度的随机字符串(以大小写字母和数字)
public static String getRandomString(int length) {
String str = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
Random random = new Random();
StringBuffer sb = new StringBuffer();
for (int i = 0; i < length; i++) {
int number = random.nextInt(62);
sb.append(str.charAt(number));
}
return sb.toString();
}
}