mybatis——mysql和oracle插入数据主键冲突时解决存在即更新不存在即插入操作

一、mybatis+mysql数据库

单个新增或编辑:

<insert id="insert" parameterType="com.pagoda.batchprocessingdata.dto.user.User">
	insert into admin.t_user (ID, NAME, AGE, PASSWORD)
	values (#{id}, #{name}, #{age}, #{password})
	ON DUPLICATE KEY UPDATE
	NAME = values(NAME),
	AGE = values(AGE),
	PASSWORD = values(PASSWORD)
</insert>

连接mysql数据库执行测试结果: 

批量新增或编辑:

<insert id="multi_insert" parameterType="com.pagoda.batchprocessingdata.dto.user.User">
	insert into t_user ( ID, NAME, AGE, PASSWORD )
	values
	<foreach collection="list" item="item" index="index" separator="," >  
		 (#{item.id}, #{item.name}, #{item.password})
	</foreach>
	 ON DUPLICATE KEY UPDATE
			NAME = VALUES( NAME ),
			AGE = VALUES( AGE ),
			PASSWORD = VALUES( PASSWORD )
</insert>

测试结果: 

INSERT INTO t_user ( ID, NAME, AGE, PASSWORD )
VALUES
('1', '悟空','20', '2'),
('2', '八戒','20', 's'),
('3', '唐僧','20', 's'),
('4', '沙僧','20', '2'),
('5', '白龙马','20', '2')
    ON DUPLICATE KEY UPDATE 
NAME = VALUES( NAME ),
AGE = VALUES( AGE ),
PASSWORD = VALUES( PASSWORD )

二、mybatis+oracle数据库
单个新增或编辑:单个是我根据批量改的(可以用)

<!-- 插入数据 -->
    <!-- 入参定义:实体类,会自动解析属性到对应的值-->
    <insert id="insert" parameterType="com.pagoda.batchprocessingdata.dto.user.User">
        merge into admin.t_user t
                <!-- insert 和update中所有的数据都需要从using中获取 -->
                using(
                select #{id} as ID, #{name} as NAME, #{age} as AGE, #{password} as PASSWORD
                    from dual
                ) tmp
                <!-- on后面的括弧不能省 -->
                on ( tmp.ID = t.ID)
        when matched THEN
                update set
                <!-- ORA-38104: 在on条件中的列是不可以更新的 t.ID = tmp.ID, -->
                t.NAME = tmp.NAME,
                t.AGE = tmp.AGE,
                t.PASSWORD = tmp.PASSWORD
        when not matched THEN
                insert
                <trim prefix="(" suffix=")" suffixOverrides=",">
                    ID, NAME, AGE, PASSWORD
                </trim>
                <trim prefix="values (" suffix=")" suffixOverrides=",">
                    tmp.ID, tmp.NAME, tmp.AGE, tmp.PASSWORD
                </trim>
    </insert>

批量新增或编辑:

<!-- 入参定义:使用List集合对象 -->
    <insert id="multiInsert" parameterType="java.util.List">
        merge into admin.t_user t
            <!-- insert 和update中所有的数据都需要从using中获取 -->
            using(
            <!-- item:list中的具体对象 -->
            <foreach collection="list" index="index" item="item" open="" close="" separator="union">
                select
                    #{item.id,jdbcType=INTEGER} as ID,
                    #{item.name,jdbcType=VARCHAR} as NAME,
                    #{item.age,jdbcType=VARCHAR} as AGE,
                    #{item.password,jdbcType=INTEGER} as PASSWORD
                from dual
            </foreach>
            ) tmp
            <!-- on后面的括弧不能省 -->
            on ( tmp.ID = t.ID)
        when matched THEN
            update set
            <!-- ORA-38104: 在on条件中的列是不可以更新的 t.ID = tmp.ID -->
            t.NAME = tmp.NAME,t.AGE = tmp.AGE,t.PASSWORD = tmp.PASSWORD
        when not matched THEN
            insert
            <trim prefix="(" suffix=")" suffixOverrides=",">
                ID,NAME,AGE, PASSWORD
            </trim>
            <trim prefix="values (" suffix=")" suffixOverrides=",">
                tmp.ID,tmp.NAME,tmp.AGE,tmp.PASSWORD
            </trim>
    </insert>

 

<think>我们在SQLXML环境下操作,通常是在MyBatis框架中编写映射文件(XML)来实现。 需求:根据主键判断数据是否存在,循环插入更新数据到表中(即批量操作存在更新存在插入) 在MySQL中,我们可以使用ON DUPLICATE KEY UPDATE语法来实现单条数据的“存在更新存在插入”。对于批量操作,同样可以使用此语法,但需要将多条数据用VALUES()函数分别指定更新值。 步骤: 1. 在Mapper接口中定义方法,参数为List(要操作数据列表) 2. 在XML映射文件中,使用<insert>标签,并编写SQL语句 3. SQL语句使用INSERT INTO ... VALUES ... ON DUPLICATE KEY UPDATE ... 的格式 4. 对于批量操作,使用MyBatis的<foreach>标签遍历列表,生成多条VALUES记录 注意:该操作依赖于主键或唯一索引。当插入数据导致主键或唯一索引冲突,就会执行更新操作。 假设我们有一个表`product`,主键为`id`(或者有唯一索引的字段,比如`code`),我们以`code`作为判断依据(唯一索引)。 示例XML配置(参考引用[3]并修改为批量操作): 注意:在批量操作中,ON DUPLICATE KEY UPDATE后面跟的更新字段,如果要使用插入的值,可以使用VALUES(字段名)来获取(这样即使批量插入,每个记录更新也会使用对应插入记录的值)。 具体实现如下:</think>### 在 SQL MyBatis XML 中实现循环插入/更新操作 根据主键判断数据是否存在并执行插入更新操作,在 MySQL 中可通过 `ON DUPLICATE KEY UPDATE` 语法实现。以下是具体实现方案: #### 1. SQL 核心语法 ```sql INSERT INTO 表名 (字段1, 字段2, 主键字段) VALUES (值1, 值2, 主键值), (值1, 值2, 主键值) ON DUPLICATE KEY UPDATE 字段1 = VALUES(字段1), 字段2 = VALUES(字段2); ``` - **执行逻辑**:当主键冲突自动转为更新操作 - **VALUES() 函数**:动态引用 INSERT 语句中的值 - **必要条件**:必须存在主键或唯一索引约束[^1][^3] #### 2. MyBatis XML 实现 ```xml <insert id="insertOrUpdateBatch"> INSERT INTO your_table (id, name, status, update_time) VALUES <foreach item="item" collection="list" separator=","> ( #{item.id}, #{item.name}, #{item.status}, NOW() ) </foreach> ON DUPLICATE KEY UPDATE name = VALUES(name), status = VALUES(status), update_time = NOW() </insert> ``` #### 3. 关键配置说明 | 组件 | 作用 | |----------------|----------------------------------------------------------------------| | `<foreach>` | 遍历 List 参数生成批量 VALUES 子句 | | `VALUES(字段)` | 引用 INSERT 传入的值,避免硬编码更新值 | | `NOW()` | 自动设置更新间戳 | | 主键约束 | 表中必须存在 PRIMARY KEY 或 UNIQUE 索引 | #### 4. 性能优化建议 1. **批量分段**:单次处理 500-1000 条数据,避免超大 SQL ```java // Java 中分批处理示例 List<List<Item>> batches = Lists.partition(fullList, 500); batches.forEach(batch -> mapper.insertOrUpdateBatch(batch)); ``` 2. **索引优化**:确保判断字段有索引 3. **事务控制**:在 Service 层添加 `@Transactional` 注解 #### 5. 使用场景示例 ```java // 实体类 public class Product { private Long id; // 主键 private String name; private Integer status; } // Mapper 接口 void insertOrUpdateBatch(List<Product> products); ``` > **注意**:SQL Server 可使用 `MERGE INTO` 语法,Oracle 用 `MERGE` 语句实现相同功能,语法结构有所同[^1]。 --- ### 相关问题 1. 如何优化 MyBatis 批量操作的性能? 2. `ON DUPLICATE KEY UPDATE` `REPLACE INTO` 有什么区别? 3. 在多数据库兼容的场景下如何实现存在更新? 4. 批量操作如何避免死锁问题? 5. 如何通过 MyBatis 注解方式实现存在更新逻辑? [^1]: MySQL 官方文档:ON DUPLICATE KEY UPDATE 语法 [^2]: MyBatis 批量操作最佳实践 [^3]: 数据库唯一约束与索引设计原则
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值