mybatis学习之路-批量插入和删除，查找

最新推荐文章于 2023-08-09 14:43:52 发布

Sky786905664

最新推荐文章于 2023-08-09 14:43:52 发布

阅读量292

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/Sky786905664/article/details/79766763

版权

mysql 专栏收录该内容

43 篇文章 0 订阅

订阅专栏

最近再做项目时，为了优化性能，对以前的SQL语句做了优化，主要是批量增加和删除，下面就把我研究的结果写出来做记录和以后参考

1，批量增加

批量增加常用的是一个Foreach循环，但是我们也可以再SQL中进行双重Foreach循环

Dao层接口

boolean insertBatchOrgApps(@Param("orgids")List<String> orgids,@Param("appids")List<String> appids,@Param("spid")String spid);

<insert id="insertBatchOrgApps">
   insert into org_apps(orgid,appid,spid) values
   <foreach collection="appids" item="appid" separator=",">  
   <foreach collection="orgids" item="item" separator=",">  
         (#{item},#{appid},#{spid})  
     </foreach>  
    </foreach>  
</insert>

2，批量删除

批量删除就是IN关键字和Foreach组合使用

int deleteBatchOrgAppsBySpidAndAppidAndOrgid(@Param("spid")String spid,@Param("appids")List<String> appids,@Param("orgids")List<String> orgids);

<delete id="deleteBatchOrgAppsBySpidAndAppidAndOrgid">
   delete from  org_apps  where  spid=#{spid} and
   appid in 
   <foreach collection="appids" index="i" item="appid" open="(" separator="," close=")">   
              #{appid}   
    </foreach>
     and orgid in
   <foreach collection="orgids" index="index" item="orgid" open="(" separator="," close=")">   
              #{orgid}   
    </foreach>
</delete>

3，批量查找

批量查找就是传入的条件也是多个，并且是集合

List<Map<String,Object>> findAppUseCountGroupByDepartment(@Param("departmentDns")List<String> departmentDns,@Param("spid")String spid);

<select id="findAppUseCountGroupByDepartment"  resultType="java.util.HashMap">
   select orgid,count(DISTINCT a.appid)c from org_apps  a INNER JOIN app_info b on a.appid=b.appid
   <where>
      <if test="spid!=null and spid!=''">
              and a.spid=#{spid}
           </if>
           
           <if test="departmentDns!=null and departmentDns.size()>0">
               and a.orgid in
         <foreach collection="departmentDns" index="index" item="item" open="(" separator="," close=")">   
              #{item}   
          </foreach>
           </if>
   </where>
   <!-- binary  区分大小写 -->
    GROUP BY binary orgid 
</select>