mybatis批量操作——更新插入同时进行，有则更新，无则新增

1.实体类

package com.example.demo.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import javax.persistence.Id;
import javax.persistence.Table;
import java.math.BigDecimal;
import java.util.Date;

/**
 * @author qzz
 * @date 2023/3/21
 */
@Data
@AllArgsConstructor
@Table(name = "user")
public class User {

    /**
     * 用户id
     */
    @Id
    private Integer id;
    /**
     * 名称
     */
    private String name;
    /**
     * sex
     */
    private String sex;
    /**
     * 年龄
     */
    private Integer age;
    /**
     * 部门
     */
    private String department;
    /**
     * 薪资
     */
    private BigDecimal salary;

    private Date ctime;
}

2.Dao层

package com.example.demo.dao;

import com.example.demo.common.MyMapper;
import com.example.demo.entity.User;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * @author qzz
 * @date 2025/2/10
 */
public interface UserMapper extends MyMapper<User> {

    /**
     * 批量新增更新数据
     * @param userList 需要插入数据集合
     * @return
     */
    Integer insertBatch(@Param("userList") List<User> userList);
}

3.Mapper层

<?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.example.demo.dao.UserMapper">

  <insert id="insertBatch">

    INSERT INTO user (
       id,name,sex,age,department,salary,ctime
    ) VALUES
    <foreach collection="userList"  item="item" open="(" separator="),(" close=")">
      #{item.id},#{item.name},#{item.sex},#{item.age},#{item.department},#{item.salary},#{item.ctime}
    </foreach>
    ON DUPLICATE KEY UPDATE
    age = VALUES(age),department = VALUES(department),salary = VALUES(salary),ctime=now()
  </insert>
</mapper>

提示：
mysql支持批量操作前提需要在配置文件链接数据库地址后面增加
&allowMultiQueries=true参数，否则无法进行批量操作

# 数据源配置
spring:
  datasource:
    name: test
    url: jdbc:mysql://localhost:3306/db_user?&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSl=false
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver