用于批量添加或更新计数数字,表中需要设置唯一索引,insert时出现重复键错误时就执行update,需要手工生成id
表结构:
索引:
mapper xml:
<insert id="updateBatch">
insert into sx_count(id,room_id,time_id,video_id,week,count_date,nums)
values
<foreach collection="list" item="counter" index="index" separator=",">
(#{counter.id}, #{counter.roomId},#{counter.timeId},#{counter.videoId},#{counter.week},#{counter.countDate},#{counter.nums})
</foreach>
on DUPLICATE KEY update
room_id=values(room_id),time_id=values(time_id),video_id=values(video_id),week=values(week),count_date=values(count_date),nums=nums+values(nums)
</insert>
mapper:
/**
* 批量更新计数
* @param list
*/
void updateBatch(@Param("list") List<Counter> list);
service:
/**
* 更新计数记录
*
* @param list
*/
@Override
public void updateCount(List<Counter> list) {
mapper.updateBatch(list);
}
测试:
@Test
public void testBatch(){
List<Counter> list=new ArrayList<>();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date currentDate = new Date();
try {
currentDate = dateFormat.parse(dateFormat.format(new Date()));
} catch (ParseException e) {
e.printStackTrace();
}
DefaultIdentifierGenerator defaultIdentifierGenerator = new DefaultIdentifierGenerator();
Counter c1 = new Counter();
c1.setId(String.valueOf(defaultIdentifierGenerator.nextId(new Object())));
c1.setRoomId("1490951884859432962");
c1.setTimeId("1491605610842529793");
c1.setVideoId("1491690768320892930");
c1.setWeek(1);
c1.setCountDate(currentDate);
c1.setNums(1);
list.add(c1);
Counter c2 = new Counter();
c2.setId(String.valueOf(defaultIdentifierGenerator.nextId(new Object())));
c2.setRoomId("1490951884859432962");
c2.setTimeId("1491605610842529793");
c2.setVideoId("1491690768320892930");
c2.setWeek(1);
c2.setCountDate(currentDate);
c2.setNums(3);
list.add(c2);
counterService.updateCount(list);
}
结果:
执行时,第一个对象是insert,nums =1,第二个对象时是update,nums = 1 +3 =4