首先建一张user测试表
CREATE TABLE `user` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`age` int(10) DEFAULT 0 NOT NULL,
`name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`create_date` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
1.for循环插入
原理:
循环一条一条数据插入,每次插入都创建一次Sqlsession,在数据量时插入的效率很差
insert into user(id,age,name,create_date) values (#{id},#{age},#{name},#{create_date})
insert into user(id,age,name,create_date) values (#{id},#{age},#{name},#{create_date})
insert into user(id,age,name,create_date) values (#{id},#{age},#{name},#{create_date})
代码演示:
mapper.xml
<insert id="insert" parameterType="com.test.entity.User">
insert into user(id,age,name,create_date)
values(#{id},#{age},#{name},#{createDate})
</insert>
controller
@PostMapping("/test")
public void test(){
long start = System.currentTimeMillis();
for (int i=1; i<=10000; i++){
User user = new User();
user.setId(i);
user.setAge(i);
user.setName("test"+i);
user.setCreateDate(new Date());
subjectMapper.insert(user);
}
long end = System.currentTimeMillis();
log.info((end-start)+"ms");
}
结果:
循环插入1万数据大概耗时36秒左右
2.foreach标签
原理:
将原来的逐条插入
insert into user(id,age,name,create_date) values (#{id},#{age},#{name},#{create_date})
insert into user(id,age,name,create_date) values (#{id},#{age},#{name},#{create_date})
insert into user(id,age,name,create_date) values (#{id},#{age},#{name},#{create_date})
改为:
insert into user(id,age,name,create_date) values (#{id},#{age},#{name},#{create_date})
,(#{id},#{age},#{name},#{create_date})
,(#{id},#{age},#{name},#{create_date})
这样做的好处是,只需要创建一次Sqlsession,来提高效率。
代码演示:
mapper.xml:
<insert id="batchInsert" parameterType="java.util.List">
insert into user(id,age,name,create_date) values
<foreach collection="userList" item="item" index="index" separator=",">
(#{item.id},#{item.age},#{item.name},#{item.createDate})
</foreach>
</insert>
controller:
@PostMapping("/test")
public void test(){
long start = System.currentTimeMillis();
List userList = new ArrayList();
for (int i=1; i<=10000; i++){
User user = new User();
user.setId(i);
user.setAge(i);
user.setName("test"+i);
user.setCreateDate(new Date());
userList.add(user);
}
subjectMapper.batchInsert(userList);
long end = System.currentTimeMillis();
log.info((end-start)+"ms");
}
结果:
插入一万条数据,不到一秒的时间,可见foreach循环插入的效率是非常高的。