Mybatis中and和循环or混用操作(or转换成in)
作者:NO0b 时间:2023-09-19 11:08:34
Mybatis and和循环or混用
这次项目用到一个and和or混用的场景 , 因为用到多个or(循环), 没想到好的办法
最终转换成用 IN实现:
场景
用left join链接多个表, 多个条件and筛选, 其中状态(state)条件筛选出多个可选状态的条目,
本来想用and 和 or 但是 or的条件是个数组参数, 需要遍历states , 可能0个可能多个, 拼了半天没有成功 , 最后发现用 IN 和FOREACH就可以了
DAO层接口
List<OrderInfoForm> selectOrdersByStatesSelective(
@Param(value="order")Order order,
@Param(value="states")Integer[] states);
Mybatis实现
<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>
这里的重点是:
<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 语句实现
SELECT * FROM somc_operation_plan
WHERE ( title LIKE '%测试%' AND ( charge_user = 'xxx' OR execute_user = 'xxx' ) )
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
标签:Mybatis,and,or
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Java BigDecimal案例详解
2021-09-15 12:03:43
![](https://img.aspxhome.com/file/2023/9/63509_0s.png)
Java中Map的遍历方法及性能测试
2023-07-14 08:54:15
Android编程实现对电池状态的监视功能示例
2023-11-16 08:40:03
Java中包装类和Arrays类的详细介绍
2023-12-03 22:04:13
分享我的第一次java Selenium自动化测试框架开发过程
2021-05-30 01:16:25
![](https://img.aspxhome.com/file/2023/5/66485_0s.png)
C#中的尾递归与Continuation详解
2022-07-27 04:14:05
SpringBoot如何使用Fastjson解析Json数据
2023-11-25 11:55:58
![](https://img.aspxhome.com/file/2023/1/60041_0s.png)
C# 获取打印机当前状态的方法
2021-09-29 19:54:43
关于Android中Gradle和jar包下载慢的问题及解决方法
2022-08-29 00:50:21
![](https://img.aspxhome.com/file/2023/8/137358_0s.png)
Android运动健康睡眠自定义控件的实现
2021-07-17 22:52:35
![](https://img.aspxhome.com/file/2023/4/138414_0s.png)
mybatis 对于生成的sql语句 自动加上单引号的情况详解
2023-09-21 15:25:00
![](https://img.aspxhome.com/file/2023/2/61342_0s.jpg)
C#实现温度转换功能
2021-10-06 22:54:18
![](https://img.aspxhome.com/file/2023/2/69802_0s.jpg)
Android中解决EditText放到popupWindow中,原有复制、粘贴、全选、选择功能失效问题
2021-07-12 10:51:49
![](https://img.aspxhome.com/file/2023/8/139358_0s.png)
springboot拦截器Interceptor的使用,你都了解吗
2023-01-01 21:53:40
hashCode方法的使用讲解
2022-11-12 15:29:37
Java 程序内部是如何执行的?
2022-04-29 20:18:36
![](https://img.aspxhome.com/file/2023/0/89480_0s.png)
SpringMVC执行步骤、Model的使用详解
2022-02-13 05:38:45
![](https://img.aspxhome.com/file/2023/2/62352_0s.png)
mybatis防止SQL注入的方法实例详解
2022-08-14 03:06:57
Spring如何使用注解的方式创建bean
2022-01-29 03:45:49
C#归并排序的实现方法(递归,非递归,自然归并)
2023-10-03 01:00:36
![](https://img.aspxhome.com/file/2023/3/119503_0s.png)