mybatisplus批量插入编辑及sql打印
一、自定义数据方法注入 EasySqlInjector
import com.baomidou.mybatisplus.core.injector.AbstractMethod; import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector; import com.baomidou.mybatisplus.extension.injector.methods.additional.InsertBatchSomeColumn; import java.util.List; /** * @ClassName EasySqlInjector * @Author ZhangRF * @CreateDate 2021/01/19 * @Decription 自定义数据方法注入(改写mybatis-plus批量插入) */ public class EasySqlInjector extends DefaultSqlInjector { @Override public List<AbstractMethod> getMethodList(Class<?> mapperClass) { List<AbstractMethod> methodList = super.getMethodList(mapperClass); methodList.add(new InsertBatchSomeColumn());//批量插入 methodList.add(new InsertIgnoreBatchAllColumn());//批量插入sql模板(忽略唯一索引冲突) methodList.add(new InsertOrUpdateBathColumn());//自定义批量插入sql模板(insert数据存在进行update操作) return methodList; } }
二、自定义批量插入sql模板(忽略唯一索引冲突)
import com.baomidou.mybatisplus.annotation.IdType; import com.baomidou.mybatisplus.core.enums.SqlMethod; import com.baomidou.mybatisplus.core.injector.AbstractMethod; import com.baomidou.mybatisplus.core.metadata.TableFieldInfo; import com.baomidou.mybatisplus.core.metadata.TableInfo; import com.baomidou.mybatisplus.core.metadata.TableInfoHelper; import com.baomidou.mybatisplus.core.toolkit.StringUtils; import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils; import lombok.Setter; import lombok.experimental.Accessors; import org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator; import org.apache.ibatis.executor.keygen.KeyGenerator; import org.apache.ibatis.executor.keygen.NoKeyGenerator; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.mapping.SqlSource; import java.util.List; import java.util.function.Predicate; /** * @ClassName InsertIgnoreBatchAllColumn * @Author ZhangRF * @CreateDate 2021/06/25 * @Decription 自定义批量插入sql模板(忽略唯一索引冲突) */ public class InsertIgnoreBatchAllColumn extends AbstractMethod { /** * mapper 对应的方法名 */ private static final String MAPPER_METHOD = "insertIgnoreBatchAllColumn"; /** * 字段筛选条件 */ @Setter @Accessors(chain = true) private Predicate<TableFieldInfo> predicate; @SuppressWarnings("Duplicates") @Override public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) { KeyGenerator keyGenerator = new NoKeyGenerator(); SqlMethod sqlMethod = SqlMethod.INSERT_ONE; String sqlTemplate = "<script>\nINSERT IGNORE INTO %s %s VALUES %s\n</script>"; List<TableFieldInfo> fieldList = tableInfo.getFieldList(); String insertSqlColumn = tableInfo.getKeyInsertSqlColumn(false) + this.filterTableFieldInfo(fieldList, predicate, TableFieldInfo::getInsertSqlColumn, EMPTY); String columnScript = LEFT_BRACKET + insertSqlColumn.substring(0, insertSqlColumn.length() - 1) + RIGHT_BRACKET; String insertSqlProperty = tableInfo.getKeyInsertSqlProperty(ENTITY_DOT, false) + this.filterTableFieldInfo(fieldList, predicate, i -> i.getInsertSqlProperty(ENTITY_DOT), EMPTY); insertSqlProperty = LEFT_BRACKET + insertSqlProperty.substring(0, insertSqlProperty.length() - 1) + RIGHT_BRACKET; String valuesScript = SqlScriptUtils.convertForeach(insertSqlProperty, "list", null, ENTITY, COMMA); String keyProperty = null; String keyColumn = null; // 表包含主键处理逻辑,如果不包含主键当普通字段处理 if (StringUtils.isNotEmpty(tableInfo.getKeyProperty())) { if (tableInfo.getIdType() == IdType.AUTO) { /* 自增主键 */ keyGenerator = new Jdbc3KeyGenerator(); keyProperty = tableInfo.getKeyProperty(); keyColumn = tableInfo.getKeyColumn(); } else { if (null != tableInfo.getKeySequence()) { keyGenerator = TableInfoHelper.genKeyGenerator(tableInfo, builderAssistant, sqlMethod.getMethod(), languageDriver); keyProperty = tableInfo.getKeyProperty(); keyColumn = tableInfo.getKeyColumn(); } } } String sql = String.format(sqlTemplate, tableInfo.getTableName(), columnScript, valuesScript); SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass); return this.addInsertMappedStatement(mapperClass, modelClass, MAPPER_METHOD, sqlSource, keyGenerator, keyProperty, keyColumn); } }
三、自定义批量插入sql模板(insert数据存在进行update操作)
import com.baomidou.mybatisplus.annotation.IdType; import com.baomidou.mybatisplus.core.injector.AbstractMethod; import com.baomidou.mybatisplus.core.metadata.TableInfo; import com.baomidou.mybatisplus.core.metadata.TableInfoHelper; import org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator; import org.apache.ibatis.executor.keygen.KeyGenerator; import org.apache.ibatis.executor.keygen.NoKeyGenerator; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.mapping.SqlSource; import org.springframework.util.StringUtils; /** * @ClassName InsertOrUpdateBathColumn * @Author ZhangRF * @CreateDate 2021/07/20 * @Decription 自定义批量插入sql模板(insert数据存在进行update操作) */ public class InsertOrUpdateBathColumn extends AbstractMethod { /** * mapper 对应的方法名 */ private static final String MAPPER_METHOD = "insertOrUpdateBathColumn"; @Override public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) { final String sqlTemplate = "<script>insert into %s %s values %s ON DUPLICATE KEY UPDATE %s</script>"; final String tableName = tableInfo.getTableName(); final String filedSql = prepareFieldSql(tableInfo); final String modelValuesSql = prepareModelValuesSql(tableInfo); final String duplicateKeySql = prepareDuplicateKeySql(tableInfo); KeyGenerator keyGenerator = new NoKeyGenerator(); String keyProperty = null; String keyColumn = null; // 表包含主键处理逻辑,如果不包含主键当普通字段处理 if (com.baomidou.mybatisplus.core.toolkit.StringUtils.isNotEmpty(tableInfo.getKeyProperty())) { if (tableInfo.getIdType() == IdType.AUTO) { /* 自增主键 */ keyGenerator = new Jdbc3KeyGenerator(); keyProperty = tableInfo.getKeyProperty(); keyColumn = tableInfo.getKeyColumn(); } else { if (null != tableInfo.getKeySequence()) { keyGenerator = TableInfoHelper.genKeyGenerator(tableInfo, builderAssistant, sqlTemplate, languageDriver); keyProperty = tableInfo.getKeyProperty(); keyColumn = tableInfo.getKeyColumn(); } } } final String sqlResult = String.format(sqlTemplate, tableName, filedSql, modelValuesSql, duplicateKeySql); SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass); return this.addInsertMappedStatement(mapperClass, modelClass, MAPPER_METHOD, sqlSource, keyGenerator, keyProperty, keyColumn); } /** * 准备ON DUPLICATE KEY UPDATE sql * * @param tableInfo * @return */ private String prepareDuplicateKeySql(TableInfo tableInfo) { final StringBuilder duplicateKeySql = new StringBuilder(); //编辑时不修改id、创建人、创建时间（由于创建人、创建时间自动补全，所以无法屏蔽，目前直支持不修改id） if (!StringUtils.isEmpty(tableInfo.getKeyColumn()) && !tableInfo.getKeyColumn().equals("id") && !tableInfo.getKeyColumn().equals("gmt_create") && !tableInfo.getKeyColumn().equals("create_user")) { duplicateKeySql.append(tableInfo.getKeyColumn()).append("=values(").append(tableInfo.getKeyColumn()).append("),"); } tableInfo.getFieldList().forEach(x -> { duplicateKeySql.append(x.getColumn()) .append("=values(") .append(x.getColumn()) .append("),"); }); duplicateKeySql.delete(duplicateKeySql.length() - 1, duplicateKeySql.length()); return duplicateKeySql.toString(); } /** * 准备属性名 * * @param tableInfo * @return */ private String prepareFieldSql(TableInfo tableInfo) { StringBuilder fieldSql = new StringBuilder(); fieldSql.append(tableInfo.getKeyColumn()).append(","); tableInfo.getFieldList().forEach(x -> { fieldSql.append(x.getColumn()).append(","); }); fieldSql.delete(fieldSql.length() - 1, fieldSql.length()); fieldSql.insert(0, "("); fieldSql.append(")"); return fieldSql.toString(); } private String prepareModelValuesSql(TableInfo tableInfo) { final StringBuilder valueSql = new StringBuilder(); valueSql.append("<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"(\" separator=\"),(\" close=\")\">"); if (!StringUtils.isEmpty(tableInfo.getKeyProperty())) { valueSql.append("#{item.").append(tableInfo.getKeyProperty()).append("},"); } tableInfo.getFieldList().forEach(x -> valueSql.append("#{item.").append(x.getProperty()).append("},")); valueSql.delete(valueSql.length() - 1, valueSql.length()); valueSql.append("</foreach>"); return valueSql.toString(); } }
四、完整sql打印
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils; import lombok.extern.slf4j.Slf4j; import org.apache.ibatis.executor.Executor; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.mapping.ParameterMapping; import org.apache.ibatis.plugin.*; import org.apache.ibatis.reflection.MetaObject; import org.apache.ibatis.session.Configuration; import org.apache.ibatis.session.ResultHandler; import org.apache.ibatis.session.RowBounds; import org.apache.ibatis.type.TypeHandlerRegistry; import org.springframework.context.annotation.Profile; import java.text.DateFormat; import java.util.Date; import java.util.List; import java.util.Locale; import java.util.Properties; import java.util.regex.Matcher; /** * @ClassName PrintSqlInterceptor * @Author ZhangRF * @CreateDate 2021/06/29 * @Decription */ @Profile({"dev", "test"}) @Intercepts({ @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}), @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})} ) @SuppressWarnings({"unchecked", "rawtypes"}) @Slf4j public class PrintSqlInterceptor implements Interceptor { @Override public Object intercept(Invocation invocation) throws Throwable { try { // 获取xml中的一个select/update/insert/delete节点，是一条SQL语句 MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0]; Object parameter = null; // 获取参数，if语句成立，表示sql语句有参数，参数格式是map形式 if (invocation.getArgs().length > 1) { parameter = invocation.getArgs()[1]; // log.info("parameter = " + parameter); } String sqlId = mappedStatement.getId(); // 获取到节点的id,即sql语句的id // log.info("sqlId = " + sqlId); BoundSql boundSql = mappedStatement.getBoundSql(parameter); // BoundSql就是封装myBatis最终产生的sql类 Configuration configuration = mappedStatement.getConfiguration(); // 获取节点的配置 String sql = getSql(configuration, boundSql); // 获取到最终的sql语句 log.debug(" 完整的 sql = {}", sql); } catch (Exception e) { e.printStackTrace(); } // 执行完上面的任务后，不改变原有的sql执行过程 return invocation.proceed(); } // 封装了一下sql语句，使得结果返回完整xml路径下的sql语句节点id + sql语句 public static String getSql(Configuration configuration, BoundSql boundSql, String sqlId) { String sql = showSql(configuration, boundSql); StringBuilder str = new StringBuilder(100); str.append(sqlId); str.append(":"); str.append(sql); return str.toString(); } // 封装了一下sql语句，使得结果返回完整xml路径下的sql语句 public static String getSql(Configuration configuration, BoundSql boundSql) { return showSql(configuration, boundSql); } // 如果参数是String，则添加单引号， 如果是日期，则转换为时间格式器并加单引号； 对参数是null和不是null的情况作了处理 private static String getParameterValue(Object obj) { String value = null; if (obj instanceof String) { value = "'" + obj.toString() + "'"; } else if (obj instanceof Date) { DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA); value = "'" + formatter.format(obj) + "'"; } else { if (obj != null) { value = obj.toString(); } else { value = ""; } } return value; } // 进行？的替换 public static String showSql(Configuration configuration, BoundSql boundSql) { // 获取参数 Object parameterObject = boundSql.getParameterObject(); List<ParameterMapping> parameterMappings = boundSql.getParameterMappings(); // sql语句中多个空格都用一个空格代替 String sql = boundSql.getSql().replaceAll("[\\s]+", " "); if (CollectionUtils.isNotEmpty(parameterMappings) && parameterObject != null) { // 获取类型处理器注册器，类型处理器的功能是进行java类型和数据库类型的转换 TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry(); // 如果根据parameterObject.getClass(）可以找到对应的类型，则替换 if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) { sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(parameterObject))); } else { // MetaObject主要是封装了originalObject对象，提供了get和set的方法用于获取和设置originalObject的属性值,主要支持对JavaBean、Collection、Map三种类型对象的操作 MetaObject metaObject = configuration.newMetaObject(parameterObject); for (ParameterMapping parameterMapping : parameterMappings) { String propertyName = parameterMapping.getProperty(); if (metaObject.hasGetter(propertyName)) { Object obj = metaObject.getValue(propertyName); sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj))); } else if (boundSql.hasAdditionalParameter(propertyName)) { // 该分支是动态sql Object obj = boundSql.getAdditionalParameter(propertyName); sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj))); } else { // 打印出缺失，提醒该参数缺失并防止错位 sql = sql.replaceFirst("\\?", "缺失"); } } } } return sql; } @Override public Object plugin(Object target) { if (target instanceof Executor) { return Plugin.wrap(target, this); } return target; } @Override public void setProperties(Properties properties) { } }
五、创建自定义mapper EasyBaseMapper
import com.baomidou.mybatisplus.core.mapper.BaseMapper; import java.util.List; /** * @ClassName EasyBaseMapper * @Author ZhangRF * @CreateDate 2021/01/19 * @Decription 扩展自带 BaseMapper */ public interface EasyBaseMapper<T> extends BaseMapper<T> { /** * 批量插入 仅适用于mysql * * @param entityList 实体列表 * @return 影响行数 */ Integer insertBatchSomeColumn(List<T> entityList); /** * 批量插入(忽略唯一索引冲突) 仅适用于mysql * * @param entityList 实体列表 * @return 影响行数 */ Integer insertIgnoreBatchAllColumn(List<T> entityList); /** * 自定义批量插入sql模板(insert数据存在进行update操作) * * @param entityList 实体列表 * @return 影响行数 */ Integer insertOrUpdateBathColumn(List<T> entityList); }
六、把创建的自定义工具注入bean
@Bean public EasySqlInjector easySqlInjector() { return new EasySqlInjector(); }
