MySQL进行 批量插入，批量删除，批量更新，批量查询

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://HdhCmsTestcnblogs测试数据/javalanger/p/10899088.html

 

MySQL进行 批量插入，批量删除，批量更新，批量查询

标签：highlight   _id   value   The   属性   ref   log   sts   target   

查看更多关于MySQL进行 批量插入，批量删除，批量更新，批量查询的详细内容...