mybatis 批量插入
方法1.for循环插入
long start = System.currentTimeMillis();
for(int i = 0 ;i < 100000; i++) {
Person person = new Person ();
person .setPersonName("Name"+i);
person .setPersonnelNumber("Number"+i);
person .setDeptName("DeptName"+i);
personMapper.insert(person);
}
long end = System.currentTimeMillis();
System.out.println("---------------" + (start - end) + "---------------");
方法2. batch模式
SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);//跟上述sql区别
PersonMapper personMapper= sqlSession.getMapper(PersonMapper.class);
long start = System.currentTimeMillis();
for(int i = 0 ;i < 100000; i++) {
Person person = new Person ();
person .setPersonName("Name"+i);
person .setPersonnelNumber("Number"+i);
person .setDeptName("DeptName"+i);
personMapper.insert(person);
}
long end = System.currentTimeMillis();
System.out.println("---------------" + (start - end) + "---------------");
方法3.批量foreach插入
<insert id="insertBatch" parameterType="java.util.List">
INSERT INTO Person
(personName, personnelNumber, deptName)
VALUES
<foreach collection ="personList" item="person" separator =",">
(#{person.personName}, #{person.personnelNumber}, #{person.deptName})
</foreach >
</insert>
long start = System.currentTimeMillis();
List<User> personList = new ArrayList<>();
for (int i = 0; i < 100000; i++) {
Person person= new Person();
person.setId("name" + i);
person.setName("personNumber" + i);
person.setPassword("depNumber" + i);
personList .add(person);
}
personMapper.insertBatch(personList);
long end = System.currentTimeMillis();
System.out.println("---------------" + (start - end) + "---------------");
批量更新的使用,mybatis中批量更新有很多种方法,可以把数据一条条更新,也可以传入一个数据集一次性更新,对于数据量不多的情况下(只有几千条)这种一次性更新就可以。数据量多可以使用多线程将数据分成多个集合后进行数据分批更新。
<!-- 全量更新身份证 -->
<update id="updateIdCard">
update personal_data
<set>
<trim prefix="data_info = case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.dataInfo != null">
when id=#{item.id} then #{item.dataInfo}
</if>
</foreach>
</trim>
</set>
where id in
<foreach collection="list" separator="," item="item" open="(" close=")">
#{item.id}
</foreach>
</update>
<select id="dataInfoList"
resultType="com.keypersonnelinformation.keypersonnel.model.dto.UpdateIdCardDTO">
select id,data_info as dataInfo from personal_data
</select>
@Test
void contextLoads() {
List<UpdateIdCardDTO> updateIdCardDTOS = personalDataMapper.dataInfoList();
updateIdCardDTOS.parallelStream().forEach(j ->{
j.setDataInfo(upCase(j.getDataInfo()));
});
Integer integer = personalDataMapper.updateIdCard(updateIdCardDTOS);
System.out.println("integer = " + integer);
}
public static String upCase(String json) {
StringBuilder stringBuilder = new StringBuilder();
//正则匹配规则,提取身份证号
String regex = "(?<=\"id_card\": \").*?(?=\")|(?<=\"certificate_number\": \").*?(?=\")";
Matcher matcher = Pattern.compile(regex).matcher(json);
while (matcher.find()){
matcher.appendReplacement(stringBuilder,matcher.group().toUpperCase());
}
matcher.appendTail(stringBuilder);
return String.valueOf(stringBuilder);
}