方案一:
我们可以使用mybatis批量新增
xml:
<insert id="addBatch" useGeneratedKeys="true" keyColumn="id" parameterType="list">
INSERT INTO user_test(userName,age,info)
VALUES
<foreach collection="list" separator="," item="user" index="index">
(
#{user.userName},
#{user.age},
#{user.info}
)
</foreach>
如果数量过大,建议在mysql侧执行一下接收数据大小的命令
show variables like ‘%max_allowed_packet%’
set global max_allowed_packet = 10010241024
调整接收数据大小,防止因为数据量过大报错
Packet for query is too large (1706 > 1024). You can change this value on the server by setting the max_allowed_packet' variable.
方案二:
使用多线程批量插入:
private void submitInsertTask(List<UserTest> userTestList) {
ExecutorService executor = Executors.newFixedThreadPool(5); // 根据实际情况调整线程池大小
Future<?> future = executor.submit(() -> userTestMapper.addBatch(userTestList));
// 如果需要等待所有任务完成(可选)
try {
future.get();
} catch (InterruptedException | ExecutionException e) {
// 处理异常
e.printStackTrace();
} finally {
executor.shutdown();
}
}
示例代码:
sql脚本:
CREATE TABLE `user_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`age` int(11) NOT NULL COMMENT '1男 2女',
`info` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
user实体:
@Data
public class UserTest {
private Integer id;
private String userName;
private Integer age;
private String info;
}
mapper:
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
@Mapper
public interface UserTestMapper {
void addBatch(@Param("list") List<UserTest> userTestList);
}
mapper xml:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.rh.serviceproduct.test.UserTestMapper">
<insert id="addBatch" useGeneratedKeys="true" keyColumn="id" parameterType="list">
INSERT INTO user_test(userName,age,info)
VALUES
<foreach collection="list" separator="," item="user" index="index">
(
#{user.userName},
#{user.age},
#{user.info}
)
</foreach>
</insert>
</mapper>
service:
public interface UserTestService {
void addBatch();
void addBatch2();
}
service实现类:
@Service
public class UserTestServiceImpl implements UserTestService{
@Autowired
private UserTestMapper userTestMapper;
@Autowired
private SqlSessionTemplate sqlSessionTemplate;
@Override
public void addBatch() {
List<UserTest> userTestList=new ArrayList<>();
String userName="";
for (int i = 0; i < 50000; i++) {
userName="test"+i;
Integer age= new Random().nextInt(3-1)+1;
UserTest userTest = new UserTest();
userTest.setUserName(userName);
userTest.setAge(age);
userTestList.add(userTest);
}
userTestMapper.addBatch(userTestList);
}
//这里还可以优化成定时任务插入,切割一下userTestList,分批次插入
@Override
public void addBatch2() {
List<UserTest> userTestList = new ArrayList<>();
String userName = "";
for (int i = 0; i < 50000; i++) {
userName = "test" + i;
Integer age = new Random().nextInt(3 - 1) + 1;
UserTest userTest = new UserTest();
userTest.setUserName(userName);
userTest.setAge(age);
userTestList.add(userTest);
}
// 使用线程池执行批量插入
submitInsertTask(userTestList);
}
//多线程操作
private void submitInsertTask(List<UserTest> userTestList) {
ExecutorService executor = Executors.newFixedThreadPool(5); // 根据实际情况调整线程池大小
Future<?> future = executor.submit(() -> userTestMapper.addBatch(userTestList));
// 如果需要等待所有任务完成(可选)
try {
future.get();
} catch (InterruptedException | ExecutionException e) {
// 处理异常
e.printStackTrace();
} finally {
executor.shutdown();
}
}
}
controller:
@RestController
public class UserTestController {
@Autowired
private UserTestService userTestService;
@RequestMapping("/addBatch")
public String addBatch(){
long start = System.currentTimeMillis();
userTestService.addBatch();
long end = System.currentTimeMillis();
return "success耗时"+(end-start)/1000+"秒"; //经测试耗时2秒
}
@RequestMapping("/addBatch2")
public String addBatch2(){
long start = System.currentTimeMillis();
userTestService.addBatch2();
long end = System.currentTimeMillis();
return "success耗时"+(end-start)/1000+"秒"; //经测试耗时1秒
}
}