mybatis多参数使用方法且其中有的参数是多个值使用in查询

1.当只有一个参数时且参数类型是List

List<AnalysisInfo> listInfo(@Param("orderIds") List<Integer> orderIds);
我这里对参数重命名为"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. 当只有一个参数时且参数类型是Array

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

如果参数类型是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.注意当查询的参数有多个时，例如

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>