使用

mapper继承BatchExtMapper，获得Integer insertBatchSomeColumn(Collection<T> entityList);和Integer updateBatchById(@Param(SqlMethod.COLLECTION) List<? extends T> dataList, @Param(Constants.WRAPPER) BatchUpdateWrapper<? extends T> column);两个方法
方法使用方式如下

// 第一个参数集合，第二个参数指定需要更新的列或者除了id（需要提供id的lambda）以外的所有列
mapper.updateBatchById(batchList, new BatchUpdateWrapper<MediationBatchDO>().setSqlExcludeId(MediationBatchDO::getId));
mapper.updateBatchById(batchList, new BatchUpdateWrapper<MediationBatchDO>().set(MediationBatchDO::getDeadline));
mapper.updateBatchById(batchList, new BatchUpdateWrapper<MediationBatchDO>().set(MediationBatchDO::getDeadline).set(MediationBatchDO::getMediationTenantId));
// 插入集合，更新所有非空属性，可以手动指定id
mapper.insertBatchSomeColumn(batchList);

框架代码

数据库连接配置

如果是mysql数据库，在spring.datasource.url=xxx?yyy&allowMultiQueries=true添加这个参数

BatchExtMapper

/*
 * Ant Group
 * Copyright (c) 2004-2021 All Rights Reserved.
 */
package com.antgroup.antchain.donpa.infrastructure.config.database.mybatis.plus.injector;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import org.apache.ibatis.annotations.Param;

import java.util.Collection;
import java.util.List;

/**
 * 批量操作mapper基类
 *
 * @author
 * @version
 */
public interface BatchExtMapper<T> extends BaseMapper<T> {


    /**
     * 批量插入 仅适用于mysql
     *
     * @param entityList 实体列表
     * @return 影响行数
     */
    Integer insertBatchSomeColumn(Collection<T> entityList);

    /**
     * 根据id批量更新
     *
     * @param dataList 数据
     * @param column   设置的列
     * @return 更新条数
     */
    Integer updateBatchById(@Param(SqlMethod.COLLECTION) List<? extends T> dataList, @Param(Constants.WRAPPER) BatchUpdateWrapper<? extends T> column);

}

BatchSqlInjector

/*
 * Ant Group
 * Copyright (c) 2004-2021 All Rights Reserved.
 */
package com.antgroup.antchain.donpa.infrastructure.config.database.mybatis.plus.injector;

import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
import com.baomidou.mybatisplus.extension.injector.methods.InsertBatchSomeColumn;

import java.util.List;

/**
 * 批量方法
 *
 * @author
 * @version 
 */
public class BatchSqlInjector extends DefaultSqlInjector {
    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
        List<AbstractMethod> methodList = super.getMethodList(mapperClass);
        methodList.add(new InsertBatchSomeColumn());
        methodList.add(new UpdateBatchById());
        return methodList;
    }
}

BatchUpdateWrapper

/*
 * Ant Group
 * Copyright (c) 2004-2021 All Rights Reserved.
 */
package com.antgroup.antchain.donpa.infrastructure.config.database.mybatis.plus.injector;

import com.baomidou.mybatisplus.core.conditions.AbstractLambdaWrapper;
import com.baomidou.mybatisplus.core.conditions.update.Update;
import com.baomidou.mybatisplus.core.toolkit.Assert;
import com.baomidou.mybatisplus.core.toolkit.LambdaUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.core.toolkit.support.ColumnCache;
import com.baomidou.mybatisplus.core.toolkit.support.LambdaMeta;
import com.baomidou.mybatisplus.core.toolkit.support.SFunction;
import com.google.common.collect.Sets;
import lombok.Getter;
import lombok.Setter;
import lombok.experimental.Accessors;
import org.apache.ibatis.reflection.property.PropertyNamer;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 与{@link UpdateBatchById}适配
 *
 * @author
 * @version
 */
public class BatchUpdateWrapper<T> extends AbstractLambdaWrapper<T, BatchUpdateWrapper<T>>
        implements Update<BatchUpdateWrapper<T>, SFunction<T, ?>> {

    /**
     * SQL 更新字段内容，例如：name='1', age=2
     */
    private final List<String> sqlSet;
    private Map<String, ColumnCache> columnMap = null;
    private boolean initColumnMap = false;
    @Getter
    private final Set<String> column = Sets.newHashSet();

    /**
     * 属性为null时是否写入
     */
    @Setter
    @Getter
    @Accessors(chain = true)
    private boolean writeWhenNull = false;

    public BatchUpdateWrapper() {
        // 如果无参构造函数，请注意实体 NULL 情况 SET 必须有否则 SQL 异常
        super.initNeed();
        this.sqlSet = new ArrayList<>();
    }

    @Override
    protected BatchUpdateWrapper<T> instance() {
        return new BatchUpdateWrapper<>();
    }

    @Override
    public BatchUpdateWrapper<T> set(boolean condition, SFunction<T, ?> column, Object val, String mapping) {
        throw new RuntimeException("批量updateBatch禁止调用带有val参数的方法");
    }

    @Override
    public BatchUpdateWrapper<T> setSql(boolean condition, String sql) {
        if (condition && StringUtils.isNotBlank(sql)) {
            sqlSet.add(sql);
        }
        return typedThis;
    }

    public BatchUpdateWrapper<T> set(SFunction<T, ?> column) {
        String columnName = columnToString(column);
        set(columnName);
        return typedThis;
    }

    public BatchUpdateWrapper<T> setSqlExcludeId(SFunction<T, ?> id) {
        String idColumnName = columnToString(id);
        columnMap.forEach((key, value) -> {
            if (!value.getColumn().equals(idColumnName)) {
                set(value.getColumn());
            }
        });
        return typedThis;
    }

    @Override
    protected String columnToString(SFunction<T, ?> column) {
        return columnToString(column, true);
    }

    protected String columnToString(SFunction<T, ?> column, boolean onlyColumn) {
        ColumnCache cache = getColumnCache(column);
        return onlyColumn ? cache.getColumn() : cache.getColumnSelect();
    }

    protected ColumnCache getColumnCache(SFunction<T, ?> column) {
        LambdaMeta meta = LambdaUtils.extract(column);
        String fieldName = PropertyNamer.methodToProperty(meta.getImplMethodName());
        tryInitCache(meta.getInstantiatedClass());
        return getColumnCache(fieldName, meta.getInstantiatedClass());
    }

    protected ColumnCache getColumnCache(String fieldName, Class<?> lambdaClass) {
        ColumnCache columnCache = columnMap.get(LambdaUtils.formatKey(fieldName));
        Assert.notNull(columnCache, "can not find lambda cache for this property [%s] of entity [%s]",
                fieldName, lambdaClass.getName());
        return columnCache;
    }

    protected void tryInitCache(Class<?> lambdaClass) {
        if (!initColumnMap) {
            final Class<T> entityClass = getEntityClass();
            if (entityClass != null) {
                lambdaClass = entityClass;
            }
            columnMap = LambdaUtils.getColumnMap(lambdaClass);
            Assert.notNull(columnMap, "can not find lambda cache for this entity [%s]", lambdaClass.getName());
            initColumnMap = true;
        }
    }

    protected BatchUpdateWrapper<T> set(String columnName) {
        column.add(columnName);
        return typedThis;
    }
}

SqlMethod

/*
 * Copyright (c) 2004-2021 All Rights Reserved.
 */
package com.antgroup.antchain.donpa.infrastructure.config.database.mybatis.plus.injector;

/**
 * 与{@link com.baomidou.mybatisplus.core.enums.SqlMethod}相似
 *
 * @author
 * @version
 */
public enum SqlMethod {
    UPDATE_BATCH_BY_ID("updateBatchById", "根据 whereEntity set 与 每条数据的id，批量更新记录", "<script>\n" +
            "<foreach collection='dataList' item='item' separator=';'>\n" +
            "UPDATE %s %s %s %s" +
            "\n</foreach>" +
            "\n</script>"),
    ;

    private final String method;
    private final String desc;
    private final String sql;

    public static final String ITEM = "item";
    public static final String COLLECTION = "dataList";

    SqlMethod(String method, String desc, String sql) {
        this.method = method;
        this.desc = desc;
        this.sql = sql;
    }

    public String getMethod() {
        return method;
    }

    public String getDesc() {
        return desc;
    }

    public String getSql() {
        return sql;
    }
}

UpdateBatchById

/*
 * Ant Group
 * Copyright (c) 2004-2021 All Rights Reserved.
 */
package com.antgroup.antchain.donpa.infrastructure.config.database.mybatis.plus.injector;

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.toolkit.Constants;
import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;

import java.util.List;

/**
 * 根据updateWrapper和固定的id条件批量更新数据
 *
 * @author
 * @version 
 */
public class UpdateBatchById extends AbstractMethod {

    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        SqlMethod sqlMethod = SqlMethod.UPDATE_BATCH_BY_ID;
        String sql = String.format(sqlMethod.getSql(), tableInfo.getTableName(),
                sqlSet(true, false, tableInfo, false, SqlMethod.ITEM, SqlMethod.ITEM + "."),
                sqlWhereEntityWrapper(true, tableInfo), sqlComment());
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
        return this.addUpdateMappedStatement(mapperClass, modelClass, sqlMethod.getMethod(), sqlSource);
    }

    @Override
    protected String sqlSet(boolean logic, boolean ew, TableInfo table, boolean judgeAliasNull, String alias, String prefix) {
        String sqlScript = genNotNullSetWrapSql(table);
        if (ew) {
            sqlScript += NEWLINE;
            sqlScript += SqlScriptUtils.convertIf(SqlScriptUtils.unSafeParam(U_WRAPPER_SQL_SET),
                    String.format("%s != null and %s != null", WRAPPER, U_WRAPPER_SQL_SET), false);
        }
        sqlScript = SqlScriptUtils.convertSet(sqlScript);
        return sqlScript;
    }

    private String genNotNullSetWrapSql(TableInfo table) {
        List<TableFieldInfo> fieldList = table.getFieldList();
        // propertyName, columnName, columnName, propertyName
        String wrapIfTemplate = "<if test=\"(item.%s != null or ew.writeWhenNull ) and ew.column.contains('%s') \">%s=#{item.%s},</if>\n";
        return fieldList.stream()
                .filter(fieldInfo -> !fieldInfo.getColumn().equals(table.getKeyColumn()))
                .filter(fieldInfo -> !fieldInfo.equals(table.getLogicDeleteFieldInfo()))
                .map(fieldInfo -> String.format(wrapIfTemplate,
                        // item.%s
                        fieldInfo.getProperty(),
                        // contains('%s')
                        fieldInfo.getColumn(),
                        // %s=
                        fieldInfo.getColumn(),
                        // {item.%s}
                        fieldInfo.getProperty()))
                .reduce((last, next) -> last + next).get();
    }

    @Override
    protected String sqlWhereEntityWrapper(boolean newLine, TableInfo table) {
        if (table.isWithLogicDelete()) {
            String sqlScript = table.getAllSqlWhere(true, true, WRAPPER_ENTITY_DOT);
            sqlScript = SqlScriptUtils.convertIf(sqlScript, String.format("%s != null", WRAPPER_ENTITY),
                    true);
            sqlScript += (NEWLINE + table.getLogicDeleteSql(true, true) + NEWLINE);
            String normalSqlScript = SqlScriptUtils.convertIf(String.format("AND ${%s}", WRAPPER_SQLSEGMENT),
                    String.format("%s != null and %s != '' and %s", WRAPPER_SQLSEGMENT, WRAPPER_SQLSEGMENT,
                            WRAPPER_NONEMPTYOFNORMAL), true);
            normalSqlScript += NEWLINE;
            normalSqlScript += SqlScriptUtils.convertIf(String.format(" ${%s}", WRAPPER_SQLSEGMENT),
                    String.format("%s != null and %s != '' and %s", WRAPPER_SQLSEGMENT, WRAPPER_SQLSEGMENT,
                            WRAPPER_EMPTYOFNORMAL), true);
            sqlScript += normalSqlScript;
            sqlScript = SqlScriptUtils.convertChoose(String.format("%s != null", WRAPPER), sqlScript,
                    table.getLogicDeleteSql(false, true));
            // 添加 id = #{item.id}
            sqlScript = sqlScript + "and" + NEWLINE + table.getKeyColumn() + "=#{" + SqlMethod.ITEM + Constants.DOT + table.getKeyProperty() + "}";
            sqlScript = SqlScriptUtils.convertWhere(sqlScript);
            return newLine ? NEWLINE + sqlScript : sqlScript;
        } else {
            String sqlScript = table.getAllSqlWhere(false, true, WRAPPER_ENTITY_DOT);
            sqlScript = SqlScriptUtils.convertIf(sqlScript, String.format("%s != null", WRAPPER_ENTITY), true);
            sqlScript += NEWLINE;
            sqlScript += SqlScriptUtils.convertIf(String.format(SqlScriptUtils.convertIf(" AND", String.format("%s and %s", WRAPPER_NONEMPTYOFENTITY, WRAPPER_NONEMPTYOFNORMAL), false) + " ${%s}", WRAPPER_SQLSEGMENT),
                    String.format("%s != null and %s != '' and %s", WRAPPER_SQLSEGMENT, WRAPPER_SQLSEGMENT,
                            WRAPPER_NONEMPTYOFWHERE), true);
            // 添加 id = #{item.id}
            sqlScript = sqlScript + "and" + NEWLINE + table.getKeyColumn() + "=#{" + SqlMethod.ITEM + Constants.DOT + table.getKeyProperty() + "}";
            sqlScript = SqlScriptUtils.convertWhere(sqlScript) + NEWLINE;
            sqlScript += SqlScriptUtils.convertIf(String.format(" ${%s}", WRAPPER_SQLSEGMENT),
                    String.format("%s != null and %s != '' and %s", WRAPPER_SQLSEGMENT, WRAPPER_SQLSEGMENT,
                            WRAPPER_EMPTYOFWHERE), true);
            sqlScript = SqlScriptUtils.convertIf(sqlScript, String.format("%s != null", WRAPPER), true);
            return newLine ? NEWLINE + sqlScript : sqlScript;
        }
    }
}

测试

/*
 * Ant Group
 * Copyright (c) 2004-2021 All Rights Reserved.
 */
package com.antgroup.antchain.donpa.infrastructure.database.asset;

import com.antgroup.antchain.donpa.infrastructure.AbstractTestBase;
import com.antgroup.antchain.donpa.infrastructure.config.database.mybatis.plus.injector.BatchUpdateWrapper;
import com.antgroup.antchain.donpa.infrastructure.database.asset.data.AssetPackageDO;
import com.antgroup.antchain.donpa.infrastructure.database.asset.mapper.AssetPackageDOMapper;
import com.antgroup.antchain.donpa.infrastructure.database.mediation.data.MediationBatchDO;
import com.antgroup.antchain.donpa.infrastructure.database.mediation.mapper.MediationBatchMapper;
import com.google.common.collect.Lists;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.time.LocalDateTime;

/**
 * 测试别名、逻辑删除
 *
 * @author 
 * @version
 */
@Transactional
public class UpdateBatchByIdOnAssetTest extends AbstractTestBase {
    @Autowired
    AssetPackageDOMapper mapper;
    @Autowired
    MediationBatchMapper batchMapper;

    @Test
    public void testLogicDeletedAndAlias() {
        AssetPackageDO asset1 = new AssetPackageDO();
        asset1.setId(1L);
        asset1.setStatus(1);
        asset1.setTransferType(0);
        asset1.setExcelFileId(0L);
        asset1.setDataFileId("");
        AssetPackageDO asset2 = new AssetPackageDO();
        asset2.setId(2L);

        asset2.setBizCount(0);
        asset2.setAccountCount(0);
        asset2.setOverdueAmount(new BigDecimal("0"));
        asset2.setOverduePrincipal(new BigDecimal("0"));
        asset2.setOverdueInterest(new BigDecimal("0"));
        asset2.setOtherFee(new BigDecimal("0"));
        asset2.setIsDeleted(1);
        asset2.setGmtCreate(LocalDateTime.now());
        asset2.setGmtModified(LocalDateTime.now());
        mapper.updateBatchById(Lists.newArrayList(asset1, asset2),
                new BatchUpdateWrapper<AssetPackageDO>().setWriteWhenNull(false).setSqlExcludeId(AssetPackageDO::getId));

    }

    @Test
    public void testOrdinaryEntity() {
        MediationBatchDO mediationBatchDO = new MediationBatchDO();
        mediationBatchDO.setId(10000L);
        mediationBatchDO.setAssetId(0L);
        mediationBatchDO.setAssetType(0);
        mediationBatchDO.setAssetName("");
        mediationBatchDO.setDataFileIdList("");
        mediationBatchDO.setGmtCreate(LocalDateTime.now());
        mediationBatchDO.setGmtModified(LocalDateTime.now());
        mediationBatchDO.setOperatorTenantId("");
        batchMapper.updateBatchById(Lists.newArrayList(mediationBatchDO),
                new BatchUpdateWrapper<MediationBatchDO>().set(MediationBatchDO::getAssetId).set(MediationBatchDO::getAssetName));

    }
}

网站首页 > 技术文章正文

mybatisplus批量sql方法提供（mybatis-plus批量insert）

使用

框架代码

数据库连接配置

BatchExtMapper

BatchSqlInjector

BatchUpdateWrapper

SqlMethod

UpdateBatchById

测试

猜你喜欢

网站首页 > 技术文章 正文

mybatisplus批量sql方法提供（mybatis-plus批量insert）

使用

框架代码

数据库连接配置

BatchExtMapper

BatchSqlInjector

BatchUpdateWrapper

SqlMethod

UpdateBatchById

测试

猜你喜欢

网站首页 > 技术文章正文