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.uj5u.com/shujuku/499437.html
標籤：MySQL
上一篇：資料庫
下一篇：04-Redis常用命令