【mysql批量插入或更新方法实现】

自定义批量插入或更新

1.创建接口，替代baseMapper

public interface RootMapper<T> extends BaseMapper<T> {

    /**
     * 自定义批量插入
     *
     * @param list
     * @return
     */
    int insertBatch(@Param("list") List<T> list);

    /**
     * 自定义批量新增或更新
     *
     * @param list
     * @return
     */
    int insertOrUpdateBatch(@Param("list") List<T> list);

2.添加方法实现类

public class InsertBatchMethod extends AbstractMethod {
    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        final String sql = "<script>insert into %s %s values %s</script>";
        final String fieldCpcSql = prepareFieldCpcSql(tableInfo);
        final String valueCpcSql = prepareValuesCpcSql(tableInfo);
        final String sqlCpcResult = String.format(sql, tableInfo.getTableName(), fieldCpcSql, valueCpcSql);
        SqlSource sqlCpcSource = languageDriver.createSqlSource(configuration, sqlCpcResult, modelClass);
        // 第三个参数必须和RootMapper的自定义方法名一致
        return this
                .addInsertMappedStatement(mapperClass, modelClass, "insertBatch", sqlCpcSource, new NoKeyGenerator(), null,
                        null);
    }

    private String prepareFieldCpcSql(TableInfo tableInfo) {
        StringBuilder fieldCpcSql = new StringBuilder();
        fieldCpcSql.append(tableInfo.getKeyColumn()).append(",");
        tableInfo.getFieldList().forEach(x -> fieldCpcSql.append(x.getColumn()).append(","));
        fieldCpcSql.delete(fieldCpcSql.length() - 1, fieldCpcSql.length());
        fieldCpcSql.insert(0, "(");
        fieldCpcSql.append(")");
        return fieldCpcSql.toString();
    }

    private String prepareValuesCpcSql(TableInfo tableInfo) {
        final StringBuilder valueCpcSql = new StringBuilder();
        valueCpcSql.append(
                "<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"(\" separator=\"),(\" close=\")\">");
        valueCpcSql.append("#{item.").append(tableInfo.getKeyProperty()).append("},");
        tableInfo.getFieldList().forEach(x -> valueCpcSql.append("#{item.").append(x.getProperty()).append("},"));
        valueCpcSql.delete(valueCpcSql.length() - 1, valueCpcSql.length());
        valueCpcSql.append("</foreach>");
        return valueCpcSql.toString();
    }
}

public class InsertOrUpdateBath extends AbstractMethod {

    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        final String sql = "<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);
        final String sqlResult = String.format(sql, tableName, filedSql, modelValuesSql,duplicateKeySql);
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
        return this.addInsertMappedStatement(mapperClass, modelClass, "insertOrUpdateBatch", sqlSource, new NoKeyGenerator(), null, null);
    }

    /**
     * 准备ON DUPLICATE KEY UPDATE sql
     * @param tableInfo
     * @return
     */
    private String prepareDuplicateKeySql(TableInfo tableInfo) {
        final StringBuilder duplicateKeySql = new StringBuilder();
        if(!StringUtils.isEmpty(tableInfo.getKeyColumn())) {
            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 fieldCpcSql = new StringBuilder();
        fieldCpcSql.append(tableInfo.getKeyColumn()).append(",");
        tableInfo.getFieldList().forEach(x -> fieldCpcSql.append(x.getColumn()).append(","));
        fieldCpcSql.delete(fieldCpcSql.length() - 1, fieldCpcSql.length());
        fieldCpcSql.insert(0, "(");
        fieldCpcSql.append(")");
        return fieldCpcSql.toString();
    }

    private String prepareModelValuesSql(TableInfo tableInfo){
        final StringBuilder valuepCpcSql = new StringBuilder();
        valuepCpcSql.append("<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"(\" separator=\"),(\" close=\")\">");
        if(!StringUtils.isEmpty(tableInfo.getKeyProperty())) {
            valuepCpcSql.append("#{item.").append(tableInfo.getKeyProperty()).append("},");
        }
        tableInfo.getFieldList().forEach(x -> valuepCpcSql.append("#{item.").append(x.getProperty()).append("},"));
        valuepCpcSql.delete(valuepCpcSql.length() - 1, valuepCpcSql.length());
        valuepCpcSql.append("</foreach>");
        return valuepCpcSql.toString();
    }
}

3.在实际使用的mapper上修改继承类为创建的RootMapper
4.然后就可以调用批量插入或更新方法了