关于MySQL提供的Last_insert_id()函数使用的理解
Last_insert_id()是MYSQL提供的返回当前客户端最后一个insert或update查询中设置为AUTO_INCREMENT列的值
Last_insert_id()不受其他客户端影响，所以是线程安全的，当前客户端只能拿到当前客户端的最新值，不需加锁处理
mybatis解析配置文件，执行SQL，转换结果的过程：
SqlSessionBuilder --> SqlSessionFacotry --> SqlSession --> Executor --> StatementHandler --> ResultSetHandler
其中userGeneratedKeys="true" keyProperty="id"获取自增id是在执行SQL的这一步(StatementHandle)来做的：
类图：
可以看到，最终执行的SQL都是交给SimpleStatementHandler，PreparedStatementHandler，CallableStatementHandler中的某一个，这三个类都有一个构造方法， 里边都是执行super()，会调用到BaseStatementHandler的构造方法，如上所示，在此构造方法中会先生成自增主键(如果selectkey配置的是before)，然后得到SQL语句，并设置parameterHandler和resultSetHandler等。部分源码如下：
BaseStatementHandler:
1 protected BaseStatementHandler(Executor executor, MappedStatement mappedStatement, Object parameterObject, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) { 2 this.configuration = mappedStatement.getConfiguration(); 3 this.executor = executor; 4 this.mappedStatement = mappedStatement; 5 this.rowBounds = rowBounds; 6 7 this.typeHandlerRegistry = configuration.getTypeHandlerRegistry(); 8 this.objectFactory = configuration.getObjectFactory(); 9 10 if (boundSql == null) { // issue #435, get the key before calculating the statement 11 generateKeys(parameterObject); 12 boundSql = mappedStatement.getBoundSql(parameterObject); 13 } 14 15 this.boundSql = boundSql; 16 17 this.parameterHandler = configuration.newParameterHandler(mappedStatement, parameterObject, boundSql); 18 this.resultSetHandler = configuration.newResultSetHandler(executor, mappedStatement, rowBounds, parameterHandler, resultHandler, boundSql); 19 } 20 21 protected void generateKeys(Object parameter) { 22 KeyGenerator keyGenerator = mappedStatement.getKeyGenerator(); 23 ErrorContext.instance().store(); 24 keyGenerator.processBefore(executor, mappedStatement, null, parameter); 25 ErrorContext.instance().recall(); 26 }
SimpleStatementHandler:
1 public int update(Statement statement) 2 throws SQLException { 3 String sql = boundSql.getSql(); 4 Object parameterObject = boundSql.getParameterObject(); 5 KeyGenerator keyGenerator = mappedStatement.getKeyGenerator(); 6 int rows; 7 if (keyGenerator instanceof Jdbc3KeyGenerator) { //使用Jdbc3KeyGenerator类型得到自增主键 8 statement.execute(sql, Statement.RETURN_GENERATED_KEYS); 9 rows = statement.getUpdateCount(); 10 keyGenerator.processAfter(executor, mappedStatement, statement, parameterObject); 11 } else if (keyGenerator instanceof SelectKeyGenerator) { //使用selectKeyGenerator(<selectKey></selectKey>)， 对于MYSQL则使用 select LAST_INSERT_ID() 来实现获取自增主键ID 12 statement.execute(sql); 13 rows = statement.getUpdateCount(); 14 keyGenerator.processAfter(executor, mappedStatement, statement, parameterObject); 15 } else { 16 statement.execute(sql); 17 rows = statement.getUpdateCount(); 18 } 19 return rows; 20 }
1 public <E> List<E> query(Statement statement, ResultHandler resultHandler) 2 throws SQLException { 3 String sql = boundSql.getSql(); 4 statement.execute(sql); 5 return resultSetHandler.<E>handleResultSets(statement); 6 }
PreparedStatementHandler:
1 public int update(Statement statement) throws SQLException { 2 PreparedStatement ps = (PreparedStatement) statement; 3 ps.execute(); 4 int rows = ps.getUpdateCount(); 5 Object parameterObject = boundSql.getParameterObject(); 6 KeyGenerator keyGenerator = mappedStatement.getKeyGenerator(); 7 keyGenerator.processAfter(executor, mappedStatement, ps, parameterObject); 8 return rows; 9 }
1 public <E> List<E> query(Statement statement, ResultHandler resultHandler) throws SQLException { 2 PreparedStatement ps = (PreparedStatement) statement; 3 ps.execute(); 4 return resultSetHandler.<E> handleResultSets(ps); 5 }
可以看到，对于自增主键的操作可能发生在执行SQL前，或者执行SQL后，主要通过KeyGenerator接口子类来实现,类图：
KeyGenerator接口只有两个方法，processBefore()是在执行SQL之前执行， processAfter()是在执行SQL语句之后执行
1. JDBC3KeyGenerator(返回表中自动生成的自增主键)
1 public void processBefore(Executor executor, MappedStatement ms, Statement stmt, Object parameter) { 2 // do nothing 3 }
1 public void processAfter(Executor executor, MappedStatement ms, Statement stmt, Object parameter) { 2 List<Object> parameters = new ArrayList<Object>(); 3 parameters.add(parameter); 4 processBatch(ms, stmt, parameters); 5 } 6 7 public void processBatch(MappedStatement ms, Statement stmt, List<Object> parameters) { 8 ResultSet rs = null; 9 try { 10 rs = stmt.getGeneratedKeys(); 11 final Configuration configuration = ms.getConfiguration(); 12 final TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry(); 13 final String[] keyProperties = ms.getKeyProperties(); 14 final ResultSetMetaData rsmd = rs.getMetaData(); 15 TypeHandler<?>[] typeHandlers = null; 16 if (keyProperties != null && rsmd.getColumnCount() >= keyProperties.length) { 17 for (Object parameter : parameters) { 18 if (!rs.next()) break; // there should be one row for each statement (also one for each parameter) 19 final MetaObject metaParam = configuration.newMetaObject(parameter); 20 if (typeHandlers == null) typeHandlers = getTypeHandlers(typeHandlerRegistry, metaParam, keyProperties); 21 populateKeys(rs, metaParam, keyProperties, typeHandlers); //将生成的自增主键，可能是多个，设置到对象相对应的属性中 22 } 23 } 24 } catch (Exception e) { 25 throw new ExecutorException("Error getting generated key or setting result to parameter object. Cause: " + e, e); 26 } finally { 27 if (rs != null) { 28 try { 29 rs.close(); 30 } catch (Exception e) { 31 // ignore 32 } 33 } 34 } 35 }
2.selectKeyGenerator(一般在不支持自动生成自增主键的数据库如oracle里使用，可以在执行之前使用，可以在执行之后使用)
1 public void processBefore(Executor executor, MappedStatement ms, Statement stmt, Object parameter) { 2 if (executeBefore) { 3 processGeneratedKeys(executor, ms, parameter); 4 } 5 }
1 public void processAfter(Executor executor, MappedStatement ms, Statement stmt, Object parameter) { 2 if (!executeBefore) { 3 processGeneratedKeys(executor, ms, parameter); 4 } 5 } 6 7 private void processGeneratedKeys(Executor executor, MappedStatement ms, Object parameter) { 8 try { 9 if (parameter != null && keyStatement != null && keyStatement.getKeyProperties() != null) { 10 String[] keyProperties = keyStatement.getKeyProperties(); 11 final Configuration configuration = ms.getConfiguration(); 12 final MetaObject metaParam = configuration.newMetaObject(parameter); 13 if (keyProperties != null) { 14 // Do not close keyExecutor. 15 // The transaction will be closed by parent executor. 16 Executor keyExecutor = configuration.newExecutor(executor.getTransaction(), ExecutorType.SIMPLE); 17 List<Object> values = keyExecutor.query(keyStatement, parameter, RowBounds.DEFAULT, Executor.NO_RESULT_HANDLER); //若是selectKey，则执行selectkey中的SQL语句查询，对于MySQL一般是select LAST_INSERT_ID() 18 if (values.size() == 0) { 19 throw new ExecutorException("SelectKey returned no data."); 20 } else if (values.size() > 1) { 21 throw new ExecutorException("SelectKey returned more than one value."); 22 } else { 23 MetaObject metaResult = configuration.newMetaObject(values.get(0)); 24 if (keyProperties.length == 1) { 25 if (metaResult.hasGetter(keyProperties[0])) { 26 setValue(metaParam, keyProperties[0], metaResult.getValue(keyProperties[0])); 27 } else { 28 // no getter for the property - maybe just a single value object 29 // so try that 30 setValue(metaParam, keyProperties[0], values.get(0)); 31 } 32 } else { 33 handleMultipleProperties(keyProperties, metaParam, metaResult); 34 } 35 } 36 } 37 } 38 } catch (ExecutorException e) { 39 throw e; 40 } catch (Exception e) { 41 throw new ExecutorException("Error selecting key or setting result to parameter object. Cause: " + e, e); 42 } 43 }
从上变可以看出：
1. 不支持自动生成主键的数据库(如oracle等)，需在执行SQL语句前，先生成主键，然后再执行SQL语句插入记录
2. 需要在执行SQL语句之前生成自增主键的，只能使用SelectKeyGenerator，并且在配置<selectkey></selectkey>时需要配置属性order="Before"
3.JDBC3KeyGenerator的processBefore()方法什么也不干，所以不能在不支持自动生成自增主键的数据库中使用， MySQL中一般是使用此KeyGenerator来处理主键
4.NoKeyGenerator里边是两个空方法
