Mybatis and和循环or混用
这次项目用到一个and和or混用的场景 , 因为用到多个or(循环), 没想到好的办法
最终转换成用 IN实现:
场景
用left join链接多个表, 多个条件and筛选, 其中状态(state)条件筛选出多个可选状态的条目,
本来想用and 和 or 但是 or的条件是个数组参数, 需要遍历states , 可能0个可能多个, 拼了半天没有成功 , 最后发现用 IN 和FOREACH就可以了
DAO层接口
1
2
3
|
List<OrderInfoForm> selectOrdersByStatesSelective(
@Param(value= "order" ) Order order ,
@Param(value= "states" ) Integer [] states);
|
Mybatis实现
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
< select id= "selectOrdersByStatesSelective" resultMap= "AllResultMap" >
select
<include refid= "All_Column_List" />
from order_list
LEFT JOIN product_method ON product_method.`code` = order_list.purchase_method
LEFT JOIN product_color ON product_color.`code` = order_list.color
LEFT JOIN product_guarantee ON product_guarantee.`code` = order_list.guarantee
LEFT JOIN product_info ON order_list.product_id = product_info.id
LEFT JOIN product_model ON product_info.model = product_model.`code`
LEFT JOIN product_standard ON product_info.standard = product_standard.`code`
LEFT JOIN product_state ON product_state.`code` = order_list.order_state
LEFT JOIN product_apperance ON product_apperance.`code` = order_list.apperance
LEFT JOIN product_brand ON product_brand.`code` = product_info.brand
< where >
<if test= "order.orderNum != null " >
order_num like "%" #{ order .orderNum,jdbcType= VARCHAR } "%"
</if>
<if test= "order.operator != null " >
and operator like "%" #{ order .operator,jdbcType= VARCHAR } "%"
</if>
<if test= "order.purchaseTime != null" >
and purchase_time = #{ order .purchaseTime,jdbcType= DATE }
</if>
<if test= "order.orderState != null" >
and order_state = #{ order .orderState,jdbcType= VARCHAR }
</if>
<if test= "order.serialNum != null" >
and serial_num like "%" #{ order .serialNum,jdbcType= VARCHAR } "%"
</if>
<if test= "states != null and states.length >0" >
<foreach collection= "states" item= "state" separator= "," open = " and order_state in (" close = ")" >
#{state,jdbcType= BIGINT }
</foreach>
</if>
</ where >
</ select >
|
这里的重点是:
1
2
3
4
5
|
<if test= "states != null and states.length >0" >
<foreach collection= "states" item= "state" separator= "," open = " and order_state in (" close = ")" >
#{state,jdbcType= BIGINT }
</foreach>
</if>
|
把多个state的or关系转化为 states in (state1,state2,state3...)
in中用foreach循环
mybatis plus and 和or合并写法
记录一下and 和 or 混合使用
sql 语句实现
1
2
|
SELECT * FROM somc_operation_plan
WHERE ( title LIKE '%测试%' AND ( charge_user = 'xxx' OR execute_user = 'xxx' ) )
|
1
2
3
|
LambdaQueryWrapper<SomcOperationPlan> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper. like (StringUtils.isNotEmpty(operationPlan.getTitle()), SomcOperationPlan::getTitle, operationPlan.getTitle())
. and (wrapper -> wrapper.eq(StringUtils.isNotEmpty(operationPlan.getChargeUser()), SomcOperationPlan::getChargeUser, operationPlan.getChargeUser()). or ().eq(StringUtils.isNotEmpty(operationPlan.getExecuteUser()), SomcOperationPlan::getExecuteUser, operationPlan.getExecuteUser()));
|
以上为个人经验,希望能给大家一个参考,也希望大家多多支持服务器之家。
原文链接:https://blog.csdn.net/q5706503/article/details/86534687