mybatis divides the string and loops to realize the operation of multiple parameters of in
- 2021-09-11 20:30:18
- OfStack
mybatis divides strings and loops, realizing multiple parameters of in
mybatis xml code:
<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 Print:
==> Preparing: select * from carinfo where xh in ( ? , ? )
==> Parameters: 1(String), 2(String)
The mybatis multi-parameter use method and some of the parameters are multiple values using the in query
1. When there is only 1 parameter and the parameter type is List
List<AnalysisInfo> listInfo(@Param("orderIds") List<Integer> orderIds);
I rename the parameter here to "orderIds", so collection= "orderIds" in foreach below, and collection= "list" in foreach if not renamed
<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. When there is only 1 parameter and the parameter type is Array
List<AnalysisInfo> listInfo(Long[] orderIds);
If the parameter type is Array, the collection property is 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. Note that when the query has multiple parameters, such as
List<AnalysisInfo> listInfo(List<Integer> orderIds, Integer num);
In this case, the Map method should be used for passing parameters, so that the name can be specified in the collection attribute
Map<String, Object> params = new HashMap<>();
params.put("orderIds",orderIds);
params.put("num",num);
List<AnalysisInfo> listInfo(params);
XML is as follows:
<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>