如何在mybatis中使用 ON DUPLICATE KEY UPDATE写代码

关注

原创 2025-11-23 15:01:56 382 阅读 AI 写同款· GEO 优化 ›

MySQL中ON DUPLICATE KEY UPDATE的介绍与使用、批量更新、存在即更新不存在则插入基本用法：ON DUPLICATE KEY UPDATE是一种MySQL的语法，它在插入新数据时，如果遇到唯一键冲突（即已存在相同的唯一键值），则会执行更新操作，而不是抛出异常或忽略该条数据。这个语法可以大大简化我们的代码，减少不必要的判断和查询操作。用法总结1：on duplicate key update 语句根据主键id或唯一键来判断当前插入是否已存在。2：记录已存在时，只会更新on duplicate key update之后指定的字段。阅读详情

如何写Insert SQL

XML SQL 实现

IRedisDataVersionHistoryCustD1Mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxx.outapi.bigdata.unit.provider.dao.mapper.IRedisDataVersionHistoryCustD1Mapper">
    <insert id="upsert" parameterType="java.util.List" useGeneratedKeys="true">
        INSERT INTO redis_data_version_history_d1 (
            redis_key,
            version,
            table_name,
            state
        ) VALUES
         <foreach collection="tables" item="table" separator=",">
            (
            #{table.redisKey,jdbcType=VARCHAR},
            #{table.version, jdbcType=INTEGER},
            #{table.tableName, jdbcType=VARCHAR},
            #{table.state, jdbcType=INTEGER}
            )
        </foreach>
        ON DUPLICATE KEY UPDATE
        version =VALUES(version),
        state = VALUES(state)
    </insert>
</mapper>

JAVA interface mapper

mapper interface 定义
IRedisDataVersionHistoryCustD1Mapper.Java
注意让main方法去扫描这些mapper定义接口

package com.xxx.outapi.bigdata.unit.provider.dao.mapper;
import java.util.List;
import com.xxx.outapi.bigdata.unit.provider.dao.model.generator.RedisDataVersionHistoryD1;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
@Mapper
public interface IRedisDataVersionHistoryCustD1Mapper {
    int upsert(@Param("tables") List<RedisDataVersionHistoryD1> tables);
}

DAO JAVA

DAO interface定义

IRedisDataVersionHistoryD1Dao.java

package com.xxx.outapi.bigdata.unit.provider.dao;
import java.util.List;
import com.xxx.outapi.bigdata.unit.provider.dao.model.generator.RedisDataVersionHistoryD1;
public interface IRedisDataVersionHistoryD1Dao {
    int upsert(List<RedisDataVersionHistoryD1> table);
}

DAO实现类

RedisDataVersionHistoryDao1Impl.java

package com.xxx.outapi.bigdata.unit.provider.dao.impl;
import static org.mybatis.dynamic.sql.SqlBuilder.isEqualTo;
import static org.mybatis.dynamic.sql.SqlBuilder.isGreaterThan;
import static org.mybatis.dynamic.sql.SqlBuilder.isIn;
import static org.mybatis.dynamic.sql.SqlBuilder.isNotEqualTo;
import java.util.*;
import com.xxx.outapi.bigdata.unit.provider.dao.IRedisDataVersionHistoryD1Dao;
import com.xxx.outapi.bigdata.unit.provider.dao.mapper.IRedisDataVersionHistoryCustD1Mapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
@Slf4j
public class RedisDataVersionHistoryDao1Impl implements IRedisDataVersionHistoryD1Dao {
    @Autowired
    private IRedisDataVersionHistoryCustD1Mapper custMapper;

    @Override
    public int upsert(List<RedisDataVersionHistoryD1> table) {
        return custMapper.upsert(table);
    }
}

JAVA Main 方法

注意

在main class加入 @MapperScan({“com.xxx.outapi.bigdata.unit.provider.dao.mapper.**”})
让spring boot去扫描mapper定义类的目录（那些带有@Mapper的java类型）
包含SQL实现的XML mapper实现文件放在 resource 目录下

@Import({LogAspect.class, TraceIdUtils.class})
@MapperScan({"com.xxx.outapi.bigdata.unit.provider.dao.mapper.**"})
@SpringBootApplication(exclude = {
        DataSourceAutoConfiguration.class,
        RedisAutoConfiguration.class,
        RedisRepositoriesAutoConfiguration.class
})
@EnableApolloConfig
@EnableMetric
@EnableFeignClients
@EnableRetry
@EnableScheduling
public class UnitBigdataOutapiProviderApplication {
    public static void main(String[] args) {
        SpringApplication.run(UnitBigdataOutapiProviderApplication.class, args);
        System.out.println("==========spring start end========");
    }