由于MybatisPlus对Oracle批量插入支持很差(不能自动获取序列值返回，SQL语句拼写出错)，同样是不支持主键自增，Pgsql支持就比较好，可能时oracle sql语法特殊，然后作者又没有专门对其优化。因此项目中使用oracle批量插入还是遇到了问题。
上篇文章介绍了通过sql注入器的方式可以拼写成正确的插入语句，但是主键通过序列自增回显的问题还存在。上篇文章之所以能插入成功，是因为框架内获取主键是通过ASSIN_ID的方式，也就是雪花算法获取主键值，长度19位。所以本篇文章介绍下如何解决通过序列自增的方式插入并回写主键。
一.实现方式
1. 不要求回写主键的情况
这种情况下可以插入语句时获取序列值的方式：
<insert id="addByUser" parameterType="java.util.List" useGeneratedKeys="false">
insert into ts_app_dxdz(ID, USERID,TYPEID, PZID, ZUID,DQID)
select seq_ts_app_dxdz_id.nextval,cd.* from(
<foreach collection="list" item="item" index="index" separator="union all">
select
#{item.USERID} USERID,
#{item.TYPEID} TYPEID,
#{item.PZ} PZ,
#{item.ZU} ZU,
#{item.DQ} DQ
from dual
</foreach>
) cd
</insert>
2.回写主键的情况
这种情况我的做法是先批量获取主键值，再手动赋值，进行插入
2.1 批量获取序列值
<select id="getSeqList" parameterType="int" resultType="java.lang.Long">
SELECT SEQ_ZL_ZLFLB_ID.nextval FROM (select level from dual connect by level <= #{size})
</select>
2.2 插入
INSERT ALL
INTO emp (id, empno, ename) VALUES (1, 1001, '张三')
INTO emp (id, empno, ename) VALUES (2, 1002, '李四')
INTO emp (id, empno, ename) VALUES (3, 1003, '王五')
SELECT * FROM dual;
把下面的插入语句编写成xml格式即可
二.具体实现
我这里是采用第二种方式，因为很多情况都需要获取插入后的主键值。我采用注入器方式，全局注入了两个方法：1.批量获取序列，2.插入操作
1.批量获取序列
public class MySelectSeqBatch extends AbstractMethod {
@Override
public String getMethod(SqlMethod sqlMethod) {
// 自定义 mapper 方法名
return "selectSeqBatch";
}
@Override
public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
if (tableInfo.getEntityType().equals(Map.class)||null==tableInfo.getKeySequence()) {
return null;
}
final String sql = "<script>SELECT %s.nextval FROM (select level from dual connect by level <= #{size})</script>";
String sequence = tableInfo.getKeySequence().value();
final String sqlResult = String.format(sql, sequence);
SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
return this.addSelectMappedStatementForOther(mapperClass, "selectSeqBatch", sqlSource, Long.class);
}
}
2.插入操作
这里我对批量插入进行了一个优化成和单条插入一样可以过滤掉null值(适合数据库字段有默认值的情况)
public class MyInsertBatchSomeColumn extends InsertBatchSomeColumn {
@Setter
@Accessors(chain = true)
private Predicate<TableFieldInfo> predicate;
private final String INSERT_BATCH_SQL="<script>\nINSERT ALL \n %s\n</script>";
@SuppressWarnings("Duplicates")
@Override
public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
//pojo类型为Map时禁用
if (tableInfo.getEntityType().equals(Map.class)) {
return null;
}
//return super.injectMappedStatement(mapperClass,modelClass,tableInfo);
KeyGenerator keyGenerator = new NoKeyGenerator();
SqlMethod sqlMethod = SqlMethod.INSERT_ONE;
List<TableFieldInfo> fieldList = tableInfo.getFieldList();
String insertSqlColumn = tableInfo.getKeyColumn() +
dynamicField(fieldList, predicate, TableFieldInfo::getInsertSqlColumn);
String columnScript = LEFT_BRACKET + insertSqlColumn + RIGHT_BRACKET;
String keyInsertSqlProperty = tableInfo.getKeyInsertSqlProperty(ENTITY_DOT, false);
if(keyInsertSqlProperty.length()>0){
keyInsertSqlProperty=keyInsertSqlProperty.substring(0,keyInsertSqlProperty.length()-1);
}
String insertSqlProperty = keyInsertSqlProperty+
dynamicField(fieldList, predicate, i -> i.getInsertSqlProperty(ENTITY_DOT));
insertSqlProperty=LEFT_BRACKET + insertSqlProperty + RIGHT_BRACKET;
String valuesScript = convertForeach(insertSqlProperty, "list", tableInfo.getTableName(),columnScript, ENTITY, NEWLINE);
String keyProperty = null;
String keyColumn = null;
// 表包含主键处理逻辑,如果不包含主键当普通字段处理
if (tableInfo.havePK()) {
if (tableInfo.getIdType() == IdType.AUTO) {
/* 自增主键 */
keyGenerator = new Jdbc3KeyGenerator();
keyProperty = tableInfo.getKeyProperty();
keyColumn = tableInfo.getKeyColumn();
} else {
if (null != tableInfo.getKeySequence()) {
// keyGenerator = TableInfoHelper.genKeyGenerator(getMethod(sqlMethod), tableInfo, builderAssistant);
keyProperty = tableInfo.getKeyProperty();
keyColumn = tableInfo.getKeyColumn();
}
}
}
String sql = String.format(INSERT_BATCH_SQL, valuesScript);
SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
return this.addInsertMappedStatement(mapperClass, modelClass, getMethod(sqlMethod), sqlSource, keyGenerator, keyProperty, keyColumn);
}
public static String convertForeach(final String sqlScript, final String collection, final String tableName,final String columns, final String item, final String separator) {
StringBuilder sb = new StringBuilder("<foreach");
if (StringUtils.isNotBlank(collection)) {
sb.append(" collection=\"").append(collection).append("\"");
}
if (StringUtils.isNotBlank(item)) {
sb.append(" item=\"").append(item).append("\"");
}
if (StringUtils.isNotBlank(separator)) {
sb.append(" separator=\"").append(separator).append("\"");
}
sb.append(">").append("\n");
if (StringUtils.isNotBlank(tableName)) {
sb.append(" INTO ").append(tableName).append(" ");
}
if (StringUtils.isNotBlank(columns)) {
sb.append(columns).append(" VALUES ");
}
return sb.append(sqlScript).append("\n").append("</foreach>\n").append(" SELECT 1 FROM dual").toString();
}
private String dynamicField(List<TableFieldInfo> fieldList, Predicate<TableFieldInfo> predicate,
Function<TableFieldInfo, String> function){
StringBuilder sb=new StringBuilder();
for (TableFieldInfo fieldInfo : fieldList) {
if(predicate.test(fieldInfo)&&fieldInfo.getInsertStrategy()== FieldStrategy.NOT_NULL){
String columnProperty = fieldInfo.getProperty();
String apply = function.apply(fieldInfo);
sb.append(" <if test=\"").append("et.").append(columnProperty).append(" != null\">");
sb.append(",").append(apply,0,apply.length()-1).append("</if>");
}
}
return sb.toString();
}
}
3.插入工具类编写
这里我把获取序列值和插入操作聚合成一个操作，方便使用，使用时只需传入数据集合和对应mapper
public class DBUtils {
/**
* 批量插入
* @param list
* @param consumer
* @param <T>
*/
public static <T> void insertBatchGroup(List<T> list, int batchSize, Consumer<List<T>> consumer){
int listSize = list.size();
for (int i = 0; i < listSize; i += batchSize) {
int endIndex = Math.min(i + batchSize, listSize);
List<T> subList = list.subList(i, endIndex);
// 执行操作
consumer.accept(subList);
}
}
public static <T> void insertBatch(List<T> list, MyBaseMapper<T> baseMapper) {
Assert.notEmpty(list,"A collection of bulk inserted data cannot be empty");
TableInfo tableInfo = TableInfoHelper.getTableInfo(list.get(0).getClass());
//获取并设置主键
if(null!=tableInfo.getKeySequence()){
List<Long> seqList = baseMapper.selectSeqBatch(list.size());
for (T t : list) {
Class<?> cclass = list.get(0).getClass();
try {
Field keyField= cclass.getDeclaredField(tableInfo.getKeyColumn());
keyField.setAccessible(true);
keyField.set(t,seqList.get(list.indexOf(t)));
}catch (Exception e){
throw new RuntimeException("key sequence set error");
}
}
}
insertBatchGroup(list,100, baseMapper::insertBatchSomeColumn);;
}
}
总结
写工具类或者全局的方法就需要考虑的更多，对开发技术和思想要求更高，同时逼迫自己阅读更多的阅读源码。
959
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
