百木园1.selectKey介绍及作用
<selectKey>标签有如下属性
resultType：sql返回的java类型
statementType：STATEMENT|PREPARED|CALLABLE三种默认PREPARED
keyProperty：列名对应的java属性名，可逗号分隔
keyColumn：列名，可逗号分隔
order：BEFORE|AFTER，BEFORE表示<selectKey>里的sql先执行然后再把获取到的值进行设置，AFTER则表示后执行，获取自增主键并设置肯定是需要用AFTER的，毕竟先等主sql插入才能获取到自增Id~
databaseId：数据库Id一般不需要填
mybatis的<selectKey>标签主要可以用来获取自增主键id的值并进行设置，SELECT LAST_INSERT_ID() 该sql的作用返回最近一次插入的id通常用来配合<selectKey>标签来使用 ,但要注意假如用insert同时插入多条sql，其只能返回插入的第一条记录的自增主键id因此<selectKey>是不支持批量插入获取主键值的
2.selectKey测试及解析
测试代码
#mapper
int insert(UserDO userDO);
#mapper.xml
<insert id=\"insert\">
<selectKey keyProperty=\"userId\" keyColumn=\"user_id\" order=\"AFTER\" resultType=\"integer\">
select last_insert_id()
</selectKey>
insert into user(username, password, nickname)
values(#{username}, #{password}, #{nickname})
</insert>
#java测试代码
public class Test {
public static void main(String[] args) throws IOException {
try (InputStream inputStream = Resources.getResourceAsStream(\"mybatis-config.xml\")) {
// 构建session工厂 DefaultSqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
UserDO userDO = new UserDO();
userDO.setUsername(\"monian\");
userDO.setPassword(\"123\");
userDO.setNickname(\"monianx\");
userMapper.insert(userDO);
System.out.println(\"自增主键userId:\" + userDO.getUserId());
}
}
}
从输出可以看到成功获取到自增主键userId并已经设置到userDO参数对象中了，下面来看看<selectKey>具体解析
public class PreparedStatementHandler extends BaseStatementHandler {
public PreparedStatementHandler(Executor executor, MappedStatement mappedStatement, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {
super(executor, mappedStatement, parameter, rowBounds, resultHandler, boundSql);
}
@Override
public int update(Statement statement) throws SQLException {
PreparedStatement ps = (PreparedStatement) statement;
ps.execute();
int rows = ps.getUpdateCount();
Object parameterObject = boundSql.getParameterObject();
KeyGenerator keyGenerator = mappedStatement.getKeyGenerator();
keyGenerator.processAfter(executor, mappedStatement, ps, parameterObject);
return rows;
}
}当为<insert><delete><update>标签时会调用此update方法，执行完sql后调用 keyGenerator.processAfter(executor, mappedStatement, ps, parameterObject); 而根据<selectKey>标签解析出来的keyGenerator为SelectKeyGenerator，下面具体分析下这个类它是怎么获取主键值并设置的。
public class SelectKeyGenerator implements KeyGenerator {
public static final String SELECT_KEY_SUFFIX = \"!selectKey\";
private final boolean executeBefore;
private final MappedStatement keyStatement;
public SelectKeyGenerator(MappedStatement keyStatement, boolean executeBefore) {
// 主sql前面执行还是后面执行
this.executeBefore = executeBefore;
this.keyStatement = keyStatement;
}
@Override
public void processBefore(Executor executor, MappedStatement ms, Statement stmt, Object parameter) {
if (executeBefore) {
processGeneratedKeys(executor, ms, parameter);
}
}
@Override
public void processAfter(Executor executor, MappedStatement ms, Statement stmt, Object parameter) {
if (!executeBefore) {
processGeneratedKeys(executor, ms, parameter);
}
}
// 处理生成的键
private void processGeneratedKeys(Executor executor, MappedStatement ms, Object parameter) {
try {
if (parameter != null && keyStatement != null && keyStatement.getKeyProperties() != null) {
// 获取需要设置的属性值 如id
String[] keyProperties = keyStatement.getKeyProperties();
final Configuration configuration = ms.getConfiguration();
final MetaObject metaParam = configuration.newMetaObject(parameter);
// Do not close keyExecutor.
// The transaction will be closed by parent executor.
Executor keyExecutor = configuration.newExecutor(executor.getTransaction(), ExecutorType.SIMPLE);
// 查询sql如 select last_insert_id()获取主键id
List<Object> values = keyExecutor.query(keyStatement, parameter, RowBounds.DEFAULT, Executor.NO_RESULT_HANDLER);
if (values.size() == 0) {
throw new ExecutorException(\"SelectKey returned no data.\");
} else if (values.size() > 1) {
throw new ExecutorException(\"SelectKey returned more than one value.\");
} else {
MetaObject metaResult = configuration.newMetaObject(values.get(0));
// 将主键id的值设置到parameter参数中
if (keyProperties.length == 1) {
if (metaResult.hasGetter(keyProperties[0])) {
setValue(metaParam, keyProperties[0], metaResult.getValue(keyProperties[0]));
} else {
// no getter for the property - maybe just a single value object
// so try that
setValue(metaParam, keyProperties[0], values.get(0));
}
} else {
// 若查询的属性有多个则分别设置
handleMultipleProperties(keyProperties, metaParam, metaResult);
}
}
}
} catch (ExecutorException e) {
throw e;
} catch (Exception e) {
throw new ExecutorException(\"Error selecting key or setting result to parameter object. Cause: \" + e, e);
}
}
private void handleMultipleProperties(String[] keyProperties,
MetaObject metaParam, MetaObject metaResult) {
String[] keyColumns = keyStatement.getKeyColumns();
if (keyColumns == null || keyColumns.length == 0) {
// no key columns specified, just use the property names
for (String keyProperty : keyProperties) {
setValue(metaParam, keyProperty, metaResult.getValue(keyProperty));
}
} else {
if (keyColumns.length != keyProperties.length) {
throw new ExecutorException(\"If SelectKey has key columns, the number must match the number of key properties.\");
}
for (int i = 0; i < keyProperties.length; i++) {
setValue(metaParam, keyProperties[i], metaResult.getValue(keyColumns[i]));
}
}
}
private void setValue(MetaObject metaParam, String property, Object value) {
if (metaParam.hasSetter(property)) {
metaParam.setValue(property, value);
} else {
throw new ExecutorException(\"No setter found for the keyProperty \'\" + property + \"\' in \" + metaParam.getOriginalObject().getClass().getName() + \".\");
}
}
}可以看到上述代码会先查询sql获取返回结果之后再把值设置到参数对象中，但可以看到当查询结果value.size() > 1的时候就会抛出异常，因此<selectKey>标签中的sql返回行数不能大于1。从这边也能看出<selectKey>不支持批量获取主键值
3. useGeneratedKeys
那么有什么办法可以获取到批量插入的主键id呢，答案是有的可以使用<insert>标签中的useGeneratedKeys、keyProperty、keyColumn属性进行设置
#mapper
void batchInsert(@Param(\"userDOList\") List<UserDO> userDOList);
#mapper.xml
insert into user(username, password, nickname)
values
<foreach collection=\"userDOList\" item=\"userDO\" separator=\",\">
(#{userDO.username}, #{userDO.password}, #{userDO.nickname})
</foreach>
</insert>
#java测试代码
public class Test {
public static void main(String[] args) throws IOException {
try (InputStream inputStream = Resources.getResourceAsStream(\"mybatis-config.xml\")) {
// 构建session工厂 DefaultSqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
UserDO userDO = new UserDO();
userDO.setUsername(\"monian\");
userDO.setPassword(\"123\");
userDO.setNickname(\"monianx\");
UserDO userDO1 = new UserDO();
userDO1.setUsername(\"monian\");
userDO1.setPassword(\"123\");
userDO1.setNickname(\"monianx\");
userMapper.batchInsert(Arrays.asList(userDO, userDO1));
System.out.println(\"自增主键userId:\" + Arrays.asList(userDO.getUserId(), userDO1.getUserId()));
}
}
}输出结果可以看到批量插入成功获取到主键userId的值了，原理的话感兴趣的同学可以去阅读下Jdbc3KeyGenerator这个类的源码，这里就不细说啦
4.selectKey和useGeneratedKeys
最后谈谈笔者对这两个的理解，selectKey可以自定义查询的sql更加的灵活不单单只是获取自增主键但查询结果行数不能有多行否则会抛出异常，而useGeneratorKeys主要是获取自动生成主键且能支持多行支持批量插入获取主键值，至于在实际开发中使用哪一种就看业务需求是怎样的了。
来源：https://www.cnblogs.com/monianxd/p/16481802.html
本站部分图文来源于网络，如有侵权请联系删除。