Mybatis中and和循環or混用操作(or轉換成in)

Posted on by

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

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。

推薦閱讀：