on duplicate key update 语句根据主键id或唯一键来判断当前插入是否已存在。

记录已存在时，只会更新on duplicate key update之后指定的字段。

我们在编码时这部分可能需要自己写，当然有的生成工具可能已经生成好了，字段太多的话，自己写起来很烦，以下我写了一个生成这部分代码的工具类。

假如我们的实体类为：

package com.itjing.springboot.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;


/**

`
`
* `
  `用户信息
  /
  @TableName(value = "person_info")
  public class PersonInfo {
  /*
  `
  `

  * 主键id
    */
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;


  `
  `

  /**
  `
  `

  * 名称
    */
    @TableField(value = "user_name")
    private String userName;


  `
  `

  /**
  `
  `

  * 年龄
    */
    @TableField(value = "user_age")
    private Integer userAge;


  `
  `

  /**
  `
  `

  * 地址
    */
    @TableField(value = "user_address")
    private String userAddress;


  `
  `

  public static final String COL_ID = "id";
  `
  `

  public static final String COL_USER_NAME = "user_name";
  `
  `

  public static final String COL_USER_AGE = "user_age";
  `
  `

  public static final String COL_USER_ADDRESS = "user_address";
  `
  `

  /**
  `
  `

  * 获取主键id


  * 


  * @return id - 主键id
    */
    public Integer getId() {
    return id;
    }


  `
  `

  /**
  `
  `

  * 设置主键id


  * 


  * @param id 主键id
    */
    public void setId(Integer id) {
    this.id = id;
    }


  `
  `

  /**
  `
  `

  * 获取名称


  * 


  * @return user_name - 名称
    */
    public String getUserName() {
    return userName;
    }


  `
  `

  /**
  `
  `

  * 设置名称


  * 


  * @param userName 名称
    */
    public void setUserName(String userName) {
    this.userName = userName == null ? null : userName.trim();
    }


  `
  `

  /**
  `
  `

  * 获取年龄


  * 


  * @return user_age - 年龄
    */
    public Integer getUserAge() {
    return userAge;
    }


  `
  `

  /**
  `
  `

  * 设置年龄


  * 


  * @param userAge 年龄
    */
    public void setUserAge(Integer userAge) {
    this.userAge = userAge;
    }


  `
  `

  /**
  `
  `

  * 获取地址


  * 


  * @return user_address - 地址
    */
    public String getUserAddress() {
    return userAddress;
    }


  `
  `

  /**
  `
  `

  * 设置地址


  * 


  * @param userAddress 地址
    */
    public void setUserAddress(String userAddress) {
    this.userAddress = userAddress == null ? null : userAddress.trim();
    }


  `
  `

  @Override
  public String toString() {
  StringBuilder sb = new StringBuilder();
  sb.append(getClass().getSimpleName());
  sb.append(" [");
  sb.append("Hash = ").append(hashCode());
  sb.append(", id=").append(id);
  sb.append(", userName=").append(userName);
  sb.append(", userAge=").append(userAge);
  sb.append(", userAddress=").append(userAddress);
  sb.append("]");
  return sb.toString();
  }
  `
  `

  @Override
  public boolean equals(Object that) {
  if (this == that) {
  return true;
  }
  if (that == null) {
  return false;
  }
  if (getClass() != that.getClass()) {
  return false;
  }
  PersonInfo other = (PersonInfo) that;
  return (this.getId() == null ? other.getId() == null : this.getId().equals(other.getId()))
  && (this.getUserName() == null ? other.getUserName() == null : this.getUserName().equals(other.getUserName()))
  && (this.getUserAge() == null ? other.getUserAge() == null : this.getUserAge().equals(other.getUserAge()))
  && (this.getUserAddress() == null ? other.getUserAddress() == null : this.getUserAddress().equals(other.getUserAddress()));
  }
  `
  `

  @Override`
  `public` `int` `hashCode()` {
  `final` `int` `prime` `=` `31`;
  `int` `result` `=` `1`;
  result = prime * result + ((getId() == `null`) ? `0` : getId().hashCode());
  result = prime * result + ((getUserName() == `null`) ? `0` : getUserName().hashCode());
  result = prime * result + ((getUserAge() == `null`) ? `0` : getUserAge().hashCode());
  result = prime * result + ((getUserAddress() == `null`) ? `0` : getUserAddress().hashCode());
  `return` result;
  }
  }
  `

生成代码：

package com.itjing.springboot.test;

import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.itjing.springboot.entity.PersonInfo;


import java.lang.reflect.Field;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import java.util.concurrent.CopyOnWriteArrayList;


/**

`
`
* 
  生成MyBatis批量插入时，无数据插入，有数据更新的on duplicate key update语句





* 


* 
  @author lijing






* `
  `@date 2024-04-04
  */
  public class GenerateCode {
  `
  `

  public static void main(String[] args) {
  `
  `

       Class<?> entityClass = PersonInfo.class;
       <span class="hljs-comment">// 获取表名</span>
       <span class="hljs-type">TableName</span> <span class="hljs-variable">tableName</span> <span class="hljs-operator">=</span> entityClass.getAnnotation(TableName.class);
       <span class="hljs-comment">// 获取属性</span>
       Field[] declaredFields = entityClass.getDeclaredFields();
       List&lt;String&gt; dbFieldNameList = <span class="hljs-keyword">new</span> <span class="hljs-title class_">CopyOnWriteArrayList</span>&lt;&gt;();
       <span class="hljs-comment">// 按属性设置sql字段</span>
       Arrays.stream(declaredFields).forEach(field -&gt; {
           <span class="hljs-type">TableField</span> <span class="hljs-variable">tableField</span> <span class="hljs-operator">=</span> field.getAnnotation(TableField.class);
           <span class="hljs-keyword">if</span> (Objects.nonNull(tableField)) {
               dbFieldNameList.add(tableField.value());
           }
       });
       <span class="hljs-type">StringBuilder</span> <span class="hljs-variable">updateSql</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">StringBuilder</span>();
       updateSql.append(<span class="hljs-string">"on duplicate key update"</span>).append(<span class="hljs-string">"\n"</span>);
       <span class="hljs-comment">// String formatStr = "`{}` = if(isnull(values(`{}`)), `{}`, values(`{}`)),";</span>
       <span class="hljs-type">String</span> <span class="hljs-variable">formatStr</span> <span class="hljs-operator">=</span> <span class="hljs-string">"`{}` = values(`{}`),"</span>;
       dbFieldNameList.stream().forEachOrdered(fieldName -&gt; {
           updateSql.append(StrUtil.format(formatStr, fieldName, fieldName, fieldName, fieldName)).append(<span class="hljs-string">"\n"</span>);
       });
       <span class="hljs-comment">// 去除最后一个逗号</span>
       updateSql.delete(updateSql.length() - <span class="hljs-number">2</span>, updateSql.length());
       System.out.println(updateSql);


  `
  `

  `}
  }
  `

结果：

on duplicate key update
`user_name` = values(`user_name`),
`user_age` = values(`user_age`),
`user_address` = values(`user_address`)