快捷導(dǎo)航

mybatis分割字符串并循環(huán),實現(xiàn)in多個參數(shù)的操作

更新時間：2021年06月10日 08:37:57 作者：思想永無止境

這篇文章主要介紹了mybatis分割字符串并循環(huán),實現(xiàn)in多個參數(shù)的操作，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

mybatis分割字符串并循環(huán),實現(xiàn)in多個參數(shù)

mybatis xml代碼：

  <select id="selectInXh" resultMap="BaseResultMap" parameterType="java.lang.String">
    select *
    from carinfo
    where
    xh in
 <if test="param1 != null and param1 != ''">
  <foreach item="item" index="index" collection="param1.split(',')" open="(" separator="," close=")">
   #{item}
  </foreach>
 </if>
  </select>

mybatis sql打?。?/p>

==>  Preparing: select * from carinfo where xh in ( ? , ? ) 
==> Parameters: 1(String), 2(String)

mybatis多參數(shù)使用方法且其中有的參數(shù)是多個值使用in查詢

1.當(dāng)只有一個參數(shù)時且參數(shù)類型是List

List<AnalysisInfo> listInfo(@Param("orderIds") List<Integer> orderIds);

我這里對參數(shù)重命名為"orderIds",所以下面foreach中collection="orderIds"，如果未重命名則foreach中collection="list"

<select id="listInfo" resultType="com.ieou.retail.module.H5.dto.AnalysisInfo">
       select materials_name as materialsName,sum(num) as totalNum,
       sum(price) as totalSale
       from sales_order_detail
       where shipment_result = 'SUCCESS' and refunds_time is null
       and sales_order_id in
       <foreach collection="orderIds" index="index" item="item" open="(" separator="," close=")">
           #{item}
      </foreach>
      group by materials_id order by totalNum desc limit 5
  </select>

2. 當(dāng)只有一個參數(shù)時且參數(shù)類型是Array

List<AnalysisInfo> listInfo(Long[] orderIds);

如果參數(shù)類型是Array則collection屬性為array

<select id="listInfo" resultType="com.ieou.retail.module.H5.dto.AnalysisInfo">
       select materials_name as materialsName,sum(num) as totalNum,
       sum(price) as totalSale
       from sales_order_detail
       where shipment_result = 'SUCCESS' and refunds_time is null
       and sales_order_id in
       <foreach collection="array" index="index" item="item" open="(" separator="," close=")">
           #{item}
      </foreach>
      group by materials_id order by totalNum desc limit 5
  </select>

3.注意當(dāng)查詢的參數(shù)有多個時，例如

List<AnalysisInfo> listInfo(List<Integer> orderIds, Integer num);

這種情況下傳參要使用Map方式，這樣在collection屬性可以指定名稱

Map<String, Object> params = new HashMap<>();
params.put("orderIds",orderIds);
params.put("num",num);
List<AnalysisInfo> listInfo(params);

XML如下：

<select id="listInfo" resultType="com.ieou.retail.module.H5.dto.AnalysisInfo">
    select materials_name as materialsName,sum(num) as totalNum,
    sum(price) as totalSale
    from sales_order_detail
    where shipment_result = 'SUCCESS' and refunds_time is null and num = #{num}
    and sales_order_id in
    <foreach collection="orderIds" index="index" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
    group by materials_id order by totalNum desc limit 5
</select>

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: