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

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

2021-10-07 11:27NO0b Java教程

这篇文章主要介绍了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()));