根据自己的需求,相应的更改
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZoneOffset;
import java.util.Random;
class BatchInsertExample {
public static final Random random = new Random();
private static Logger logger = LoggerFactory.getLogger(BatchInsertExample.class);
private static final String DB_URL = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8&serverTimezone=UTC&useSSL=false&rewriteBatchedStatements=true";
private static final String USER = "root";
private static final String PASSWORD = "root";
public static void main(String[] args) {
LocalDateTime startTime = LocalDateTime.of(2020, 1, 1, 0, 0, 0);
LocalDateTime endTime = LocalDateTime.of(2023, 7, 10, 23, 59, 59);
long start = System.currentTimeMillis();//开始计时【单位:毫秒】
Connection connection = null;
PreparedStatement statement = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection(DB_URL, USER, PASSWORD);
connection.setAutoCommit(false); // 关闭自动提交
String insertQuery = "INSERT INTO practitioners(practitioners_id, practitioners_name,practitioners_Idcard,practitioners_tel,practitioners_address,practitioners_sex,create_time) VALUES (null, ?,?,?,?,?,?)";
statement = connection.prepareStatement(insertQuery);
int batchSize = 1000; // 每次批量提交的记录数
int count = 0;
for (int i = 0; i < 1000000; i++) {
statement.setString(1, generateRandomName());
statement.setString(2, generateRandomID());
statement.setString(3, generateRandomPhoneNumber());
statement.setString(4, generateRandomAddress());
statement.setString(5, generateRandomGender());
statement.setString(6, generateRandomCreationTime(startTime, endTime));
statement.addBatch();
if (i % 1000 == 0) {
statement.executeBatch(); // 执行最后一批(不满batchSize的记录)
connection.commit(); // 提交事务
}
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
if (connection != null) {
try {
connection.rollback(); // 回滚事务
} catch (SQLException ex) {
ex.printStackTrace();
}
}
} finally {
try {
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
logger.info("百万条数据插入用时:" + (System.currentTimeMillis() - start) + "【单位:毫秒】");
}
//==============================================================================================================
// 姓氏列表
private static final String[] surnames = {
"赵", "钱", "孙", "李", "周", "吴", "郑", "王", "冯", "陈", "褚", "卫", "蒋", "沈", "韩", "杨", "朱", "秦", "尤", "许",
"何", "吕", "施", "张", "孔", "曹", "严", "华", "金", "魏", "陶", "姜", "戚", "谢", "邹", "喻", "柏", "水", "窦", "章",
"云", "苏", "潘", "葛", "奚", "范", "彭", "郎", "鲁", "韦", "昌", "马", "苗", "凤", "花", "方", "俞", "任", "袁", "柳",
"邓", "鲍", "史", "唐", "费", "廉", "岑", "薛", "雷", "贺", "倪", "汤", "滕", "殷", "罗", "毕", "郝", "邬", "安", "常",
"乐", "于", "时", "傅", "皮", "卞", "齐", "康", "伍", "余", "元", "卜", "顾", "孟", "平", "黄", "和", "穆", "萧", "尹",
"姚", "邵", "湛", "汪", "祁", "毛", "禹", "狄", "米", "贝", "明", "臧", "计", "伏", "成", "戴", "谈", "宋", "茅", "庞",
"熊", "纪", "舒", "屈", "项", "祝", "董", "梁"
}
// 随机生成姓名
public static String generateRandomName() {
Random random = new Random();
String surname = surnames[random.nextInt(surnames.length)];
String givenName = getRandomChineseCharacters(random.nextInt(2) + 1); // 生成1-2个汉字的名字
return surname + givenName;
}
// 随机生成指定长度的汉字字符串
private static String getRandomChineseCharacters(int length) {
StringBuilder stringBuilder = new StringBuilder();
for (int i = 0; i < length; i++) {
stringBuilder.append(getRandomChineseCharacter());
}
return stringBuilder.toString();
}
// 随机生成一个汉字字符
private static char getRandomChineseCharacter() {
String str = "";
int highPos, lowPos;
Random random = new Random();
highPos = (176 + Math.abs(random.nextInt(39)));
lowPos = (161 + Math.abs(random.nextInt(93)));
byte[] bArr = new byte[2];
bArr[0] = (new Integer(highPos)).byteValue();
bArr[1] = (new Integer(lowPos)).byteValue();
try {
str = new String(bArr, "GB2312");
} catch (Exception e) {
e.printStackTrace();
}
return str.charAt(0);
}
//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
// 随机生成地区码
private static String getRandomAreaCode() {
// 根据实际需求返回合法的地区码,如根据规则选择特定省份的地区码
return "610900"; // 北京市
}
// 随机生成生日日期(格式:yyyyMMdd)
private static String getRandomBirthday() {
int year = getRandomNumberInRange(1900, 2023);
int month = getRandomNumberInRange(1, 12);
int day = getRandomNumberInRange(1, 28); // 假设每个月都取28号
return String.format("%04d%02d%02d", year, month, day);
}
// 随机生成顺序码(格式:三位数字)
private static String getRandomSequenceCode() {
int sequence = getRandomNumberInRange(1, 999);
return String.format("%03d", sequence);
}
// 通过前17位生成校验位
private static char getCheckDigit(String prefix17) {
int[] coefficient = {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2};
int sum = 0;
for (int i = 0; i < prefix17.length(); i++) {
char c = prefix17.charAt(i);
int digit = Character.digit(c, 10);
sum += coefficient[i] * digit;
}
int remainder = sum % 11;
char[] checkDigits = {'1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2'};
return checkDigits[remainder];
}
// 获取指定范围内的随机数
private static int getRandomNumberInRange(int min, int max) {
Random random = new Random();
return random.nextInt(max - min + 1) + min;
}
// 生成随机的身份证号码
public static String generateRandomID() {
String areaCode = getRandomAreaCode();
String birthday = getRandomBirthday();
String sequenceCode = getRandomSequenceCode();
String prefix17 = areaCode + birthday + sequenceCode;
char checkDigit = getCheckDigit(prefix17);
return prefix17 + checkDigit;
}
//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
// 随机生成手机号码
public static String generateRandomPhoneNumber() {
// 手机号码前三位
String[] phonePrefix = { "130", "131", "132", "133", "134", "135", "136", "137", "138", "139",
"150", "151", "152", "153", "155", "156", "157", "158", "159",
"186", "187", "188", "189" };
Random random = new Random();
String prefix = phonePrefix[random.nextInt(phonePrefix.length)];
String suffix = String.format("%08d", random.nextInt(100000000));
return prefix + suffix;
}
//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
// 城市列表
private static final String[] cities = {
"旬阳县"
};
// 街道名称列表
private static final String[] streets = {
"中山路", "人民路", "建设路", "解放路", "和平路", "文化路", "学府路", "工业路", "胜利街", "新华街", "长江街", "光明街", "迎宾街",
"友谊街", "环城路", "幸福路", "自由路", "红旗路", "民主路", "广场街", "平安街", "凯旋路", "太阳路", "世纪街", "教育路", "科技路",
"鼓楼街", "东风路", "金海路", "朝阳路", "海滨路", "山东路", "济南街", "水木街", "湖光路", "荣华街", "双龙路", "新城街", "幸福街",
"和平街", "白云路", "西安街", "长安街", "青年街", "人民街", "红星路", "珠江路", "黄河路", "昆仑路", "长城街"
};
// 随机生成住址
public static String generateRandomAddress() {
Random random = new Random();
String city = cities[random.nextInt(cities.length)];
String street = streets[random.nextInt(streets.length)];
int buildingNumber = random.nextInt(100) + 1;
int unitNumber = random.nextInt(20) + 1;
int roomNumber = random.nextInt(10) + 1;
return city + street + buildingNumber + "号" + unitNumber + "单元" + roomNumber + "室";
}
//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
// 随机生成性别
public static String generateRandomGender() {
Random random = new Random();
int genderCode = random.nextInt(2); // 0代表男性,1代表女性
if (genderCode == 0) {
return "男";
} else {
return "女";
}
}
// +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
// 随机生成指定时间段内的创建时间
public static String generateRandomCreationTime(LocalDateTime startTime, LocalDateTime endTime) {
Random random = new Random();
long startMillis = startTime.toInstant(ZoneOffset.UTC).toEpochMilli();
long endMillis = endTime.toInstant(ZoneOffset.UTC).toEpochMilli();
// 随机生成开始时间和结束时间之间的时间毫秒数
long randomMillis = startMillis + (long) (random.nextDouble() * (endMillis - startMillis));
return String.valueOf(LocalDateTime.ofInstant(Instant.ofEpochMilli(randomMillis), ZoneId.systemDefault()));
}
}