Mybatis-plus如何注入自定义的SQL（foreach批量插入ORACLE数据库）

参考https://www.yht7.com/news/194845

• 重写getMethodList

@Component
public class CustomizedSqlInjector extends DefaultSqlInjector {
    /**
     * 如果只需增加方法，保留mybatis plus自带方法，
     * 可以先获取super.getMethodList()，再添加add
     */
    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass,TableInfo tableInfo) {
        List<AbstractMethod> methodList = super.getMethodList(mapperClass,tableInfo);
        methodList.add(new InsertBatch());
        return methodList;
    }
}

• 定义自己的SQL方法类

import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import org.apache.ibatis.executor.keygen.NoKeyGenerator;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

/**
 * @author shidulin
 * @version 1.0
 * @date 2022/9/27 11:22
 */
@Component
public class InsertBatch 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 sql = "<script> INSERT INTO  %s  %s  %s </script>";
        final String tableName = tableInfo.getTableName();
        final String insertKeySql = prepareInsertKeySql(tableInfo);
        final String modelValuesSql = prepareModelValuesSql(tableInfo);
        final String sqlResult = String.format(sql, tableName, insertKeySql,modelValuesSql);
        //System.out.println("savaorupdatesqlsql="+sqlResult);
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
        return this.addInsertMappedStatement(mapperClass, modelClass, "insertBatch", sqlSource, new NoKeyGenerator(), null, null);
    }

    /**
     * Insert Key sql
     * @param tableInfo
     * @return
     */
    private String prepareInsertKeySql(TableInfo tableInfo) {
        StringBuilder fieldSql = new StringBuilder();
        if(!StringUtils.isEmpty(tableInfo.getKeyColumn())) {
            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();
    }

    /**
     * Insert Value sql
     * @param tableInfo
     * @return
     */
    private String prepareInsertValueSql(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("t2.")
                    .append(x.getColumn())
                    .append(",");
        });
        duplicateKeySql.delete(duplicateKeySql.length() - 1, duplicateKeySql.length());
        duplicateKeySql.insert(0, "(");
        duplicateKeySql.append(")");
        return duplicateKeySql.toString();
    }



    /**
     * foreach sql
     * @param tableInfo
     * @return
     */
    private String prepareModelValuesSql(TableInfo tableInfo){
        final StringBuilder valueSql = new StringBuilder();
        valueSql.append("<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"\" separator=\"union all\" close=\"\"> (SELECT ");
        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(" FROM DUAL )</foreach>");
        return valueSql.toString();
    }

}

• 定义添加了自定义方法的Mapper类

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * @author shidulin
 * @version 1.0
 * @date 2022/9/27 11:19
 * 根Mapper，给表Mapper继承用的，可以自定义通用方法
 * {@link BaseMapper}
 * {@link com.baomidou.mybatisplus.extension.service.IService}
 * {@link com.baomidou.mybatisplus.extension.service.impl.ServiceImpl}

 */
@Component
public interface RootMapper <T> extends BaseMapper<T> {
    /**
     * 自定义批量插入
     * 如果要自动填充，@Param(xx) xx参数名必须是 list/collection/array 3个的其中之一
     */
    int insertBatch(@Param("list") List<T> list);

}

• 调用，mapper接口继承了RootMapper就可以直接使用自定义的方法了

@Component
public interface PurStoreProductsPricingMapper extends RootMapper<PurStoreProductsPricing> {

}


controller类
@Autowired
    private PurStoreProductsCatalogMapper purStoreProductsCatalogMapper;
public void test(){
	purStoreProductsCatalogMapper.insertBatch();
}

foreach只在字段少的时候效率比较高，慎重使用