原理:先手动添加几个数据库,通过查询已经添加的数据集合,通过<foreach>遍历添加,
mapper.xml代码
<foreach> 这里集合数据不应超过500,300即可,如果集合过大<foreach>添加效率反而降低;
<insert id="batchSave" parameterType="java.util.List">
INSERT INTO w_href
(name, csdnHref, createDate, type, status)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.name},
#{item.csdnHref},
#{item.createDate},
#{item.type},
#{item.status}
)
</foreach>
</insert>
<!-- 查询all -->
<select id="selectAll" resultType="HrefUrlEntity">
select * from w_href a WHERE a.id < 301
</select>
dao层
public void batchSave(@Param("list") List<HrefUrlEntity> list);
public List<HrefUrlEntity> selectAll();
service层
public void batchSave(List<HrefUrlEntity> hrefUrlEntities){
hrefUrlDao.batchSave(hrefUrlEntities);
}
public List<HrefUrlEntity> selectAll(){
return hrefUrlDao.selectAll();
}
Controller层,先查询到已经添加的数据,通过for循环遍历,将查询的数据再次添加,依次循环,短时间内可以添加上百万数据
@Test
public void getFi() throws ParseException {
for (int i = 0;i<2000;i++) {
List<HrefUrlEntity> hrefUrlEntities =hrefUrlService.selectAll();
hrefUrlService.batchSave(hrefUrlEntities);
}
}
//批量添加数据 返回主键
mapper 接口
// 批量添加用户
int insertBatchUser( @Param("list") List<SysUser> list );
xml 的 代码
<!-- 批量添加用户 -->
<insert id="insertBatchUser" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="userId">
insert into sys_user(
dept_id,
user_name,
nick_name,
major_name,
sex,
password,
status,
create_by,
create_time
)values
<foreach collection="list" item="item" index="index" separator="," >
(
#{item.deptId},
#{item.userName},
#{item.nickName },
#{item.majorName},
#{item.sex},
#{item.password },
#{item.status},
#{item.createBy},
sysdate()
)
</foreach>
</insert>
返回数据 的 list 里面会有用户id(返回的主键)
service层调用接口
List<SysUser> list = new ArrayList<>();
int userId =userMapper.insertBatchUser( list );