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

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

转载作者：qq735679552 更新时间：2022-09-29 22:32:09

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章Mybatis中and和循环or混用操作(or转换成in)由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

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