解决版本:3.0.6
原因分析:mybatis-plus默认使用Jdbc3KeyGenerator进行添加,但是sqlserver不支持批量返回id，所以会抛出如下异常
org.apache.ibatis.exceptions.PersistenceException:
### Error flushing statements. Cause: org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be executed before any results can be obtained.
### Cause: org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be executed before any results can be obtained.
解决方案: 重写默认saveBatch和saveOrUpdateBatch(缺点是批量添加不能返回id，对于不需要返回id的场景适用)将Jdbc3KeyGenerator替换为NoKeyGenerator
第一步: 建立NoahSqlMethod(也可以不写,但是项目尽量不出现魔法值)
/**
* @date: 2018/12/10
* @time: 17:22
*/
public enum NoahSqlMethod {
/**
* 插入
*/
INSERT_BATCH("insertBatch", "插入一条数据（选择字段插入）", "<script>\nINSERT INTO %s %s VALUES %s\n</script>"),
;
private final String method;
private final String desc;
private final String sql;
NoahSqlMethod(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;
}
}
第二步: 建立InsertBatch对象
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.toolkit.StringUtils;
import com.baomidou.mybatisplus.core.toolkit.TableInfoHelper;
import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils;
import net.xinhuamm.noah.api.common.enums.NoahSqlMethod;
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;
/**
* @email yangyongping@xinhuamm.net
* @author: 杨永平
* @date: 2018/12/10
* @time: 15:33
*/
public class InsertBatch extends AbstractMethod {
@Override
public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
KeyGenerator keyGenerator = new NoKeyGenerator();
NoahSqlMethod sqlMethod = NoahSqlMethod.INSERT_BATCH;
String columnScript = SqlScriptUtils.convertTrim(tableInfo.getAllInsertSqlColumn(false),
LEFT_BRACKET, RIGHT_BRACKET, null, COMMA);
String valuesScript = SqlScriptUtils.convertTrim(tableInfo.getAllInsertSqlProperty(false, null),
LEFT_BRACKET, RIGHT_BRACKET, null, COMMA);
String keyProperty = null;
String keyColumn = null;
// 表包含主键处理逻辑,如果不包含主键当普通字段处理
if (StringUtils.isNotEmpty(tableInfo.getKeyProperty())) {
if (tableInfo.getIdType() == IdType.AUTO) {
/** 自增主键 关键点(将Jdbc3KeyGenerator更改为NoKeyGenerator)*/
keyGenerator = new NoKeyGenerator();
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(sqlMethod.getSql(), tableInfo.getTableName(), columnScript, valuesScript);
SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
return this.addInsertMappedStatement(mapperClass, modelClass,sqlMethod.getMethod(), sqlSource, keyGenerator, keyProperty, keyColumn);
}
}
第三步: 建立NoahSqlInjector对象
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.AbstractSqlInjector;
import com.baomidou.mybatisplus.core.injector.methods.*;
import net.xinhuamm.noah.user.common.mybatisplus.InsertBatch;
import java.util.List;
import java.util.stream.Stream;
import static java.util.stream.Collectors.toList;
/**
* @email yangyongping@xinhuamm.net
* @author: 杨永平
* @date: 2018/12/10
* @time: 15:35
*/
public class NoahSqlInjector extends AbstractSqlInjector {
@Override
public List<AbstractMethod> getMethodList() {
return Stream.of(
new InsertBatch(),
new Insert(),
new Delete(),
new DeleteByMap(),
new DeleteById(),
new DeleteBatchByIds(),
new Update(),
new UpdateById(),
new SelectById(),
new SelectBatchByIds(),
new SelectByMap(),
new SelectOne(),
new SelectCount(),
new SelectMaps(),
new SelectMapsPage(),
new SelectObjs(),
new SelectList(),
new SelectPage()
).collect(toList());
}
}
第四步: 重写ServiceImpl超类为AbstractNoahServiceImpl
import com.baomidou.mybatisplus.core.enums.SqlMethod;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.toolkit.*;
import com.baomidou.mybatisplus.extension.service.IService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.baomidou.mybatisplus.extension.toolkit.SqlHelper;
import net.xinhuamm.noah.api.common.enums.NoahSqlMethod;
import org.apache.ibatis.binding.MapperMethod;
import org.apache.ibatis.session.SqlSession;
import org.springframework.transaction.annotation.Transactional;
import java.io.Serializable;
import java.util.Collection;
import java.util.Objects;
/**
* @email yangyongping@xinhuamm.net
* @author: 杨永平
* @date: 2018/12/10
* @time: 17:17
*/
public abstract class AbstractNoahServiceImpl<E extends BaseMapper<T>, T> extends ServiceImpl<E,T> implements IService<T> {
@Transactional(rollbackFor = Exception.class)
@Override
public boolean saveBatch(Collection<T> entityList, int batchSize) {
int i = 0;
String sqlStatement = SqlHelper.table(currentModelClass()).getSqlStatement(NoahSqlMethod.INSERT_BATCH.getMethod());
try (SqlSession batchSqlSession = sqlSessionBatch()) {
for (T anEntityList : entityList) {
batchSqlSession.insert(sqlStatement, anEntityList);
if (i >= 1 && i % batchSize == 0) {
batchSqlSession.flushStatements();
}
i++;
}
batchSqlSession.flushStatements();
}
return true;
}
@Transactional(rollbackFor = Exception.class)
@Override
public boolean saveOrUpdateBatch(Collection<T> entityList, int batchSize) {
if (CollectionUtils.isEmpty(entityList)) {
throw new IllegalArgumentException("Error: entityList must not be empty");
}
Class<?> cls = currentModelClass();
TableInfo tableInfo = TableInfoHelper.getTableInfo(cls);
int i = 0;
try (SqlSession batchSqlSession = sqlSessionBatch()) {
for (T anEntityList : entityList) {
if (null != tableInfo && StringUtils.isNotEmpty(tableInfo.getKeyProperty())) {
Object idVal = ReflectionKit.getMethodValue(cls, anEntityList, tableInfo.getKeyProperty());
if (StringUtils.checkValNull(idVal) || Objects.isNull(getById((Serializable) idVal))) {
batchSqlSession.insert(SqlHelper.table(currentModelClass()).getSqlStatement(NoahSqlMethod.INSERT_BATCH.getMethod()), anEntityList);
} else {
MapperMethod.ParamMap<T> param = new MapperMethod.ParamMap<>();
param.put(Constants.ENTITY, anEntityList);
batchSqlSession.update(sqlStatement(SqlMethod.UPDATE_BY_ID), param);
}
//不知道以后会不会有人说更新失败了还要执行插入 😂😂😂
if (i >= 1 && i % batchSize == 0) {
batchSqlSession.flushStatements();
}
i++;
} else {
throw ExceptionUtils.mpe("Error: Can not execute. Could not find @TableId.");
}
batchSqlSession.flushStatements();
}
}
return true;
}
}
第五步: 将业务service继承类改为AbstractNoahServiceImpl
import net.xinhuamm.noah.api.model.entity.Area;
import net.xinhuamm.noah.user.common.mybatisplus.AbstractNoahServiceImpl;
import net.xinhuamm.noah.user.mapper.IAreaDAO;
import net.xinhuamm.noah.user.service.IAreaService;
import org.springframework.stereotype.Service;
@Service("areaService")
public class AreaServiceImpl extends AbstractNoahServiceImpl<IAreaDAO, Area> implements IAreaService {
}
第六步: 将SqlInjector注入系统中
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@MapperScan("net.xinhuamm.noah.user.mapper")
@Configuration
public class MapperConfig {
@Bean
public NoahSqlInjector logicSqlInjector() {
return new NoahSqlInjector();
}
}
