MySQL进行 批量插入,批量删除,批量更新,批量查询
1、批量插入
ServiceImpl层
List<Person> addPeople = new ArrayList<>(); //addPeople存放多个Person对象 personMapper.insetPeopleReturnIds(addPeople);
Dao层接口(这里的注解param中的list对应xml中的 collection的值, 两者要保持一致! )
int insetPeopleReturnIds(@Param("list") List<Person> addPeople);
Mapper.xml
(keyColumn是数据库的字段,keyProperty对应的是实体类的属性,为的是让ID自增长)
<insert id="insetPeopleReturnIds" keyColumn="person_id" keyProperty="personId" parameterType="java.util.List" useGeneratedKeys="true"> insert into person (person_name, id_type, id_num, phone,org_id) values <foreach collection="list" index="index" item="item" separator=","> (#{item.personName,jdbcType=VARCHAR}, #{item.idType,jdbcType=INTEGER}, #{item.idNum,jdbcType=VARCHAR}, #{item.phone,jdbcType=VARCHAR},#{item.orgId,jdbcType=INTEGER}) </foreach> </insert>
2、批量删除
ServiceImpl层
List<String> list; //list中作者存放的是字符串,格式["123","456"] uploadListMapper.deleteByPrimaryUUid(list);
Dao层接口
int deleteByPrimaryUUid(@Param("lists") List<String> list);
Mapper.xml
<delete id="deleteByPrimaryUUid" parameterType="java.util.List"> delete from upload_list where uuid in <foreach close=")" collection="lists" index="index" item="item" open="(" separator=","> #{item,jdbcType=VARCHAR} </foreach> </delete>
3、批量更新
ServiceImpl层
List<Person> oldPeople = new ArrayList<>();//oldPeople存放多个person对象 personMapper.updateBatch(oldPeople);
Dao层接口
int updateBatch(@Param("list") List<Person> list);
Mapper.xml
<update id="updateBatch" parameterType="java.util.List"> update person <trim prefix="set" suffixOverrides=","> <trim prefix="person_name =case" suffix="end,"> <foreach collection="list" index="index" item="item"> when person_id = #{item.personId} then #{item.personName} </foreach> </trim> <trim prefix="id_type =case" suffix="end,"> <foreach collection="list" index="index" item="item"> when person_id = #{item.personId} then #{item.idType} </foreach> </trim> <trim prefix="id_num =case" suffix="end,"> <foreach collection="list" index="index" item="item"> when person_id = #{item.personId} then #{item.idNum} </foreach> </trim> <trim prefix="phone =case" suffix="end,"> <foreach collection="list" index="index" item="item"> when person_id = #{item.personId} then #{item.phone} </foreach> </trim> </trim> where person_id in <foreach close=")" collection="list" item="item" open="(" separator=","> #{item.personId} </foreach> </update>
4、批量查询
ServiceImpl层
List<String> list; //list中作者存放的是字符串,格式["123","456"]
List<UploadList> uploadLists = uploadListMapper.selectByPrimaryUUid(list);
Dao层接口
List<UploadList> selectByPrimaryUUid(@Param("lists") List<String> list);
Mapper.xml
<select id="selectByPrimaryUUid" resultMap="BaseResultMap"> select * from upload_list where uuid in <foreach close=")" collection="lists" index="index" item="item" open="(" separator=","> #{item,jdbcType=VARCHAR} </foreach> </select>
参考文章https://www.cnblogs.com/javalanger/p/10899088.html
https://blog.csdn.net/q957967519/article/details/88669552