Mybatis中and和循环or混用操作(or转换成in)

这次项目用到一个and和or混用的场景 , 因为用到多个or(循环), 没想到好的办法

最终转换成用 IN实现：

用left join链接多个表, 多个条件and筛选, 其中状态(state)条件筛选出多个可选状态的条目,

本来想用and 和 or 但是 or的条件是个数组参数, 需要遍历states , 可能0个可能多个, 拼了半天没有成功 , 最后发现用 IN 和FOREACH就可以了

List<OrderInfoForm> selectOrdersByStatesSelective( @Param(value='order')Order order, @Param(value='states')Integer[] states);Mybatis实现

<select 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循环

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()));

以上为个人经验，希望能给大家一个参考，也希望大家多多支持好吧啦网。