mybatis事务中获取自增主键

来源：https://blog.youkuaiyun.com/shenyunsese/article/details/45890985

数据库：mysql

mapper:

<insert id="insert" parameterType="cn.cq.shenyun.entity.domin.User" >  
  <selectKey resultType="java.lang.Integer" keyProperty="id" order="AFTER" >  
    SELECT LAST_INSERT_ID()  
  </selectKey>  
  insert into user ( id,create_time, email,  
    login_name, nick_name, password,   
    phone_num, city_id, recode_date  
    )  
  values ( #{id,jdbcType=INTEGER}, #{createTime,jdbcType=TIMESTAMP}, #{email,jdbcType=VARCHAR},  
    #{loginName,jdbcType=VARCHAR}, #{nickName,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR},   
    #{phoneNum,jdbcType=VARCHAR}, #{cityId,jdbcType=VARCHAR}, #{recodeDate,jdbcType=TIMESTAMP}  
    )  
</insert>

注意：order是AFTER，或者这一项可以不写，默认order的值就是AFTER,

对于mysql,sqlserver，这类数据库，设置了自增，插入之后就有个自增的值，用order=AFTER

对于orcale,这类，使用序列的数据库，要在之前查询，所以使用order=before

sqlSessionFactory代码

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">  
    <property name="dataSource" ref="dataSource" />  
    <property name="configLocation" value="classpath:mybatis/mybatis.xml" />  
    <property name="mapperLocations" value="classpath:cn/cq/shenyun/dao/mapper/*.xml"></property>  
</bean> 

扫描mapper.xml的时候，要用mapperLocations，不要用configLocation，我也不知道原因，反正就是因为这个原因困扰了我一年多。

最后找到原因，不能设置defaultExecutorType=BATCH,不然无法获取自增主键

<configuration>  
<settings>  
<!--这儿不能配置defaultExecutorType=BATCH可以不配置这个属性-->  
<setting name="defaultExecutorType" value="BATCH"/>  
</settings>  
</configuration>

java代码：

@Transactional  
public User register(String email,String password) throws EmailRepeatException {  
  
    User user=new User();  
    user.setEmail(email);  
    user.setCreateTime(new Date(System.currentTimeMillis()));  
    user.setPassword(confusionPassword(password));  
    Integer id=userDao.save(user);  
    System.out.println("id:"+id);//这儿表示影响的行数  
    System.out.println("userid:"+user.getId());//  这样就能获取自增的主键了 ,  
  
    return user;  
}  

打印的日志：

13:49:27.319 [main] DEBUG org.mybatis.spring.SqlSessionUtils - Creating a new SqlSession  
13:49:27.325 [main] DEBUG org.mybatis.spring.SqlSessionUtils - Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2c48d1fc]  
13:49:27.332 [main] DEBUG o.m.s.t.SpringManagedTransaction - JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@313d6965] will be managed by Spring  
13:49:27.384 [main] DEBUG c.c.s.dao.mapper.UserMapper.insert - ==>  Preparing: insert into user ( id,create_time, email, login_name, nick_name, password, phone_num, city_id, recode_date ) values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )   
13:49:27.427 [main] DEBUG c.c.s.dao.mapper.UserMapper.insert - ==> Parameters: null, 2015-05-21 13:49:27.314(Timestamp), 2asdlyluoyongqq@qq.com(String), null, null, e9e371ca63793877ceac8f6eacd8d3b5(String), null, null, null  
13:49:27.431 [main] DEBUG c.c.s.dao.mapper.UserMapper.insert - <==    Updates: 1  
13:49:27.432 [main] DEBUG c.c.s.d.m.U.insert!selectKey - ==>  Preparing: SELECT LAST_INSERT_ID()   
13:49:27.433 [main] DEBUG c.c.s.d.m.U.insert!selectKey - ==> Parameters:   
13:49:27.449 [main] DEBUG c.c.s.d.m.U.insert!selectKey - <==      Total: 1  
13:49:27.451 [main] DEBUG org.mybatis.spring.SqlSessionUtils - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2c48d1fc]  
id:1  
userid:13  
13:49:50.913 [main] DEBUG org.mybatis.spring.SqlSessionUtils - Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2c48d1fc]  
13:49:50.940 [main] DEBUG org.mybatis.spring.SqlSessionUtils - Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2c48d1fc] 

注意查看：insert 语句和 SELECT LAST_INSERT_ID() 语句在一个事务中，并且SELECT LAST_INSERT_ID()在insert之后。