带有 on duplicate key update 的批量插入 mybatisPlus

  1 package com.autewifi.dataaods.common.data.datascope;
  2 
  3 import com.baomidou.mybatisplus.annotation.IdType;
  4 import com.baomidou.mybatisplus.core.enums.SqlMethod;
  5 import com.baomidou.mybatisplus.core.injector.AbstractMethod;
  6 import com.baomidou.mybatisplus.core.metadata.TableFieldInfo;
  7 import com.baomidou.mybatisplus.core.metadata.TableInfo;
  8 import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
  9 import com.baomidou.mybatisplus.core.toolkit.sql.SqlInjectionUtils;
 10 import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils;
 11 import lombok.Setter;
 12 import lombok.experimental.Accessors;
 13 import org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator;
 14 import org.apache.ibatis.executor.keygen.KeyGenerator;
 15 import org.apache.ibatis.executor.keygen.NoKeyGenerator;
 16 import org.apache.ibatis.mapping.MappedStatement;
 17 import org.apache.ibatis.mapping.SqlSource;
 18 
 19 import java.util.List;
 20 import java.util.function.Predicate;
 21 
 22 import static java.util.stream.Collectors.joining;
 23 
 24 
 25 /**
 26  * 扩展支持批量插入并且带有 on duplicate key update做更新
 27  *
 28  * @author dujl
 29  * @date 2023/11/15
 30  */
 31 public class InsertBatchOnUpdate extends AbstractMethod {
 32 
 33     /**
 34      * 字段筛选条件
 35      */
 36     @Setter
 37     @Accessors(chain = true)
 38     private Predicate<TableFieldInfo> predicate;
 39 
 40     protected InsertBatchOnUpdate() {
 41         super("insertBatchOnUpdate");
 42     }
 43 
 44     /**
 45      * 默认方法名
 46      *
 47      * @param predicate 字段筛选条件
 48      */
 49     public InsertBatchOnUpdate(Predicate<TableFieldInfo> predicate) {
 50         super("insertBatchOnUpdate");
 51         this.predicate = predicate;
 52     }
 53 
 54     /**
 55      * @param name      方法名
 56      * @param predicate 字段筛选条件
 57      * @since 3.5.0
 58      */
 59     public InsertBatchOnUpdate(String name, Predicate<TableFieldInfo> predicate) {
 60         super(name);
 61         this.predicate = predicate;
 62     }
 63 
 64 
 65     @SuppressWarnings("Duplicates")
 66     @Override
 67     public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
 68         KeyGenerator keyGenerator = NoKeyGenerator.INSTANCE;
 69         SqlMethod sqlMethod = SqlMethod.INSERT_ONE;
 70         List<TableFieldInfo> fieldList = tableInfo.getFieldList();
 71         String insertSqlColumn = tableInfo.getKeyInsertSqlColumn(true, null, false) +
 72                 this.filterTableFieldInfo(fieldList, predicate, TableFieldInfo::getInsertSqlColumn, EMPTY);
 73         String columnScript = LEFT_BRACKET + insertSqlColumn.substring(0, insertSqlColumn.length() - 1) + RIGHT_BRACKET;
 74         String insertSqlProperty = tableInfo.getKeyInsertSqlProperty(true, ENTITY_DOT, false) +
 75                 this.filterTableFieldInfo(fieldList, predicate, i -> getInsertSqlProperty(i,ENTITY_DOT), EMPTY);
 76         insertSqlProperty = LEFT_BRACKET + insertSqlProperty.substring(0, insertSqlProperty.length() - 1) + RIGHT_BRACKET;
 77         String valuesScript = SqlScriptUtils.convertForeach(insertSqlProperty, "list", null, ENTITY, COMMA);
 78         String keyProperty = null;
 79         String keyColumn = null;
 80         // 表包含主键处理逻辑,如果不包含主键当普通字段处理
 81         if (tableInfo.havePK()) {
 82             if (tableInfo.getIdType() == IdType.AUTO) {
 83                 /* 自增主键 */
 84                 keyGenerator = Jdbc3KeyGenerator.INSTANCE;
 85                 keyProperty = tableInfo.getKeyProperty();
 86                 // 去除转义符
 87                 keyColumn = SqlInjectionUtils.removeEscapeCharacter(tableInfo.getKeyColumn());
 88             } else {
 89                 if (null != tableInfo.getKeySequence()) {
 90                     keyGenerator = TableInfoHelper.genKeyGenerator(this.methodName, tableInfo, builderAssistant);
 91                     keyProperty = tableInfo.getKeyProperty();
 92                     keyColumn = tableInfo.getKeyColumn();
 93                 }
 94             }
 95         }
 96         StringBuilder stringBuilder = new StringBuilder();
 97         stringBuilder.append(valuesScript);
 98         stringBuilder.append(" on duplicate key update ");
 99         List<TableFieldInfo> fields = tableInfo.getFieldList();
100         for (int i = 0; i < fields.size(); i++) {
101             TableFieldInfo field = fields.get(i);
102             stringBuilder.append(field.getColumn());
103             stringBuilder.append(" = values(");
104             stringBuilder.append(field.getColumn());
105             stringBuilder.append(")");
106             if (i < fields.size()-1){
107                 stringBuilder.append(",");
108             }
109         }
110         valuesScript = stringBuilder.toString();
111         String sql = String.format(sqlMethod.getSql(), tableInfo.getTableName(), columnScript, valuesScript);
112 
113 
114         SqlSource sqlSource = super.createSqlSource(configuration, sql, modelClass);
115         return this.addInsertMappedStatement(mapperClass, modelClass, methodName, sqlSource, keyGenerator, keyProperty, keyColumn);
116     }
117 
118     public InsertBatchOnUpdate setPredicate(final Predicate<TableFieldInfo> predicate) {
119         this.predicate = predicate;
120         return this;
121     }
122 
123 
124     private String getInsertSqlProperty(TableFieldInfo tableFieldInfo,final String prefix) {
125         String newPrefix = prefix == null ? "" : prefix;
126         String elPart = SqlScriptUtils.safeParam(newPrefix + tableFieldInfo.getEl());
127         //属性为空时使用默认值
128         String result =  SqlScriptUtils.convertIf(elPart,
129                 String.format("%s != null", newPrefix + tableFieldInfo.getEl()),false)
130                 + SqlScriptUtils.convertIf("default",
131                 String.format("%s == null", newPrefix + tableFieldInfo.getEl()),false);
132         return result + ",";
133     }
134 
135 
136 }