mybatis 实用mapper写法

mapper语句

1. 批量更新，每条记录要更新的字段的值不一样

原始效果如下

UPDATE merchant_black_text set SET nums = nums + 10 where id = 1;
UPDATE merchant_black_text set SET nums = nums + 12 where id = 2;

但这种更新一般都来自于业务代码中的循环，无法一次性一个update执行。可以变相的通过case when实现

如下所示

参数类

@Data
@Accessors(chain = true)
public class UpdateBlackTextBo {
 
    /**
     * id
     */
    private Integer id;
 
    /**
     * 增加的命中次数
     */
    private Integer incrementHitNums;
}

mapper接口

int updateHitByIds(@Param("list") List<UpdateBlackTextBo> list);

mapper.xml

<update id="updateHitByIds" parameterType="com.company.content.risk.order.model.bo.UpdateBlackTextBo">
    update merchant_black_text
    set nums = case id
    <foreach collection="list" close="end" item="item">
        when #{item.id} then nums + #{item.incrementHitNums}
    </foreach>
    where id in
    <foreach collection="list" open="(" close=")" separator="," item="item">
        #{item.id}
    </foreach>
</update>

2. 批量根据唯一索引如果数据存在则忽略，不存在则插入

mapper接口

Integer batchInsert(@Param("list") List<FlowFetchBill> dataList);

mapper.xml

<insert id="batchInsert" parameterType="java.util.List">
        <!-- 依赖于订单号的唯一主键 -->
        insert ignore flow_fetch_bill(id, merchant_id, device_number, mobile, trade_no, amount, order_time,
             mark, target_account_no, target_account_name, sequence, biz_type, payment_method, payment_method_id) values
        <foreach collection="list" item="item" index="index" separator=",">
            (
                #{item.id}, #{item.merchantId}, #{item.deviceNumber}, #{item.mobile},
                #{item.tradeNo}, #{item.amount}, #{item.orderTime},
                #{item.mark}, #{item.targetAccountNo}, #{item.targetAccountName}, #{item.sequence}, #{item.bizType},
                #{item.paymentMethod}, #{item.paymentMethodId}
             )
        </foreach>
    </insert>

3. 根据唯一索引，如果数据存在则更新，不存在则插入(INSERTON DUPLICATE KEY UPDATE)

mapper接口

Integer batchInsertOrUpdate(@Param("value") MerchantPaymentMethodLimit data);

mapper.xml

    <insert id="insertOrUpdate" parameterType="com.company.pay.core.model.datao.MerchantPaymentMethodLimit">
        insert merchant_payment_method_limit
            (id, payment_method, payment_method_id, account_name, single_order_amount, daily_amount, balance)
        values (#{value.id}, #{value.paymentMethod}, #{value.paymentMethodId}, #{value.accountName}, #{value.singleOrderAmount},
                #{value.dailyAmount}, #{value.balance})
        ON DUPLICATE KEY
            UPDATE single_order_amount = #{value.singleOrderAmount}, daily_amount = #{value.dailyAmount},
                   balance = #{balance}
    </insert>

4. 根据唯一索引，如果数据存在则更新，不存在则插入(INSERTON DUPLICATE KEY UPDATE), 附带更新条件

mapper接口

Integer insertOrUpdateRequestCmd(@Param("obj") MerchantBaseDeviceRunningState data);

mapper.xml

<insert id="insertOrUpdateRequestCmd">
 
        insert
            merchant_base_device_running_state(id, device_id, cmd, request_id, request_time, response_time, status)
        values(#{obj.id}, #{obj.deviceId}, #{obj.cmd}, #{obj.requestId}, #{obj.requestTime}, #{obj.responseTime}, #{obj.status})
            ON DUPLICATE KEY
                UPDATE
                    request_time = if(request_time <![CDATA[
                            <
                       ]]> #{obj.requestTime}, #{obj.requestTime}, request_time),
 
                    request_id = if(request_time <![CDATA[
                            <
                       ]]> #{obj.requestTime}, #{obj.requestId}, request_id),
 
                    response_time = if(request_time <![CDATA[
                            <
                       ]]> #{obj.requestTime}, #{obj.responseTime}, response_time),
 
                    status = if(request_time <![CDATA[
                            <
                       ]]> #{obj.requestTime}, #{obj.status}, status);
 
    </insert>

5. 根据唯一索引，如果数据存在则更新，不存在则插入(INSERTON DUPLICATE KEY UPDATE批量形式)

mapper接口

Integer batchInsertOrUpdate(@Param("list") List<MerchantPaymentBankCardLimit> dataList);

mapper.xml

更新当前行的时候同时传入的数据行和数据库中存在的进行比较

     <insert id="batchInsertOrUpdate" parameterType="java.util.List">
        insert merchant_payment_bank_card_limit(id, merchant_id, bank_card_number, single_order_amount,
        daily_amount, remain_amount, update_by)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.id}, #{item.merchantId}, #{item.bankCardNumber},
            #{item.singleOrderAmount}, #{item.dailyAmount}, #{item.remainAmount}, #{item.updateBy})
        </foreach>
        ON DUPLICATE KEY UPDATE merchant_id = VALUES(merchant_id),
        bank_card_number = VALUES(bank_card_number),
        single_order_amount = if(VALUES(single_order_amount) > 0, VALUES(single_order_amount), single_order_amount),
        daily_amount = if(VALUES(daily_amount) > 0, VALUES(daily_amount), daily_amount),
        remain_amount = VALUES(remain_amount),
        update_by = VALUES(update_by)
    </insert>

6. 传入的字段值和定义的枚举进行判断

枚举类

package com.company.demo.common.enumration;
 
import com.company.demo.common.utils.Preconditions;
 
import java.util.Arrays;
import java.util.Map;
import java.util.stream.Collectors;
 
/**
* <p>通用逻辑状态</p >
*
* @author Snowball
* @version 1.0
* @date 2020/10/23 11:24
*/
public enum CommonLogic {
 
    /**
     * 无业务意义，前端对接使用，传入时代表全部
     */
    ALL(-1),
 
    /**
     * 下架
     */
    FALSE(0),
 
    /**
     * 上架
     */
    TRUE(1)
 
    ;
 
    private final Integer logic;
 
    static Map<Integer, CommonLogic> valueMappings;
 
    static {
        valueMappings = Arrays.stream(values()).collect(Collectors.toMap(CommonLogic::getLogic, value -> value));
    }
 
    CommonLogic(Integer logic) {
        this.logic = logic;
    }
 
    public Integer getLogic() {
        return logic;
    }
 
    public static CommonLogic getByLogic(Integer status) {
        final CommonLogic contentLibraryStatus = valueMappings.get(status);
        Preconditions.checkArgument(contentLibraryStatus != null, 400, "逻辑参数不正确");
        return contentLibraryStatus;
    }
}
 

mapper接口，仅仅为了展示参数对应关系

List<XXX> listByPageRequest(@Param("request") XXX request);

mapper.xml

<!-- 在条件中使用 -->
<if test="request.auditStatus != null and ${request.auditStatus != @com.company.demo.common.enumration.CommonLogic@ALL.logic">
    live.audit_status = #{request.auditStatus} and
</if>
<!-- 在where条件中直接使用 -->
and status = ${@com.company.demo.common.enumration.CommonLogic@ALL.getLogic()}