MyBatis 3的自增ID的Bug(续)

MyBatis 3的自增ID的Bug(续)

 

作者：chszs

转载请注明出处！

自上次我提出MyBatis 3的Bug之后，见：http://blog.csdn.net/chszs/archive/2011/03/31/6292719.aspx

我把Bug提交到MyBatis的官方网站，见http://code.google.com/p/mybatis/issues/detail?id=287

问题描述如下：

Eclipse Java Project:
1. SqlMapConfig.xml
<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE configuration  
    PUBLIC "-//mybatis.org//DTD Config 3.0//EN"  
    "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver" />
				<property name="url" value="jdbc:mysql://localhost:3306/test?zeroDateTimeBehavior=convertToNull" />
				<property name="username" value="root" />
				<property name="password" value="adminadmin" />
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<mapper resource="com/sqlmap/WorkerMapper.xml" />
	</mappers>
</configuration>  

2. mysql.sql
USE test;
CREATE TABLE `worker` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `pin` VARCHAR(64) DEFAULT NULL,
  `firstname` VARCHAR(64) DEFAULT NULL,
  `lastname` VARCHAR(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

3. WorkerMapper
package com.dao;

import com.entity.Worker;
public interface WorkerMapper {
	int insertWorker(Worker worker);
}

4. Worker.java
package com.entity;

public class Worker {
    private Integer id;

    private String pin;

    private String firstname;

    private String lastname;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getPin() {
        return pin;
    }

    public void setPin(String pin) {
        this.pin = pin == null ? null : pin.trim();
    }

    public String getFirstname() {
        return firstname;
    }

    public void setFirstname(String firstname) {
        this.firstname = firstname == null ? null : firstname.trim();
    }

    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname == null ? null : lastname.trim();
    }
}

5. WorkerMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.dao.WorkerMapper" >
  <resultMap id="BaseResultMap" type="com.entity.Worker" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="pin" property="pin" jdbcType="VARCHAR" />
    <result column="firstname" property="firstname" jdbcType="VARCHAR" />
    <result column="lastname" property="lastname" jdbcType="VARCHAR" />
  </resultMap>

  <insert id="insertWorker" parameterType="com.entity.Worker" >
  	<selectKey resultType="int" keyProperty="id" order="AFTER"> 
			select LAST_INSERT_ID()
		</selectKey>
    insert into worker
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="pin != null" >
        pin,
      </if>
      <if test="firstname != null" >
        firstname,
      </if>
      <if test="lastname != null" >
        lastname,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="pin != null" >
        #{pin,jdbcType=VARCHAR},
      </if>
      <if test="firstname != null" >
        #{firstname,jdbcType=VARCHAR},
      </if>
      <if test="lastname != null" >
        #{lastname,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>

</mapper>

6. Test.java
/**
 * @Author: Li.Qiang
 * @Date:   2011-3-9
 */
package com.test;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.entity.Worker;

public class Test {
	public static void main(String[] args) throws Exception{
		String resource = "conf/SqlMapConfig.xml";
		Reader reader = Resources.getResourceAsReader(resource);
		SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
		SqlSession session = factory.openSession();
		
		Worker worker = new Worker();
		worker.setPin("123456");
		worker.setFirstname("Tom");
		worker.setLastname("Jack");

		try{
			for(int i=0; i<20; i++){
				int tmpId = session.insert("insertWorker", worker);
				session.commit();
				System.out.println("Worker ID: " + tmpId);
			}
		} finally{
			session.close();
		}
		
	}
}

7. Test.java Output:
Worker ID: 1
Worker ID: 1
Worker ID: 1
Worker ID: 1
Worker ID: 1
Worker ID: 1
Worker ID: 1
Worker ID: 1
Worker ID: 1
Worker ID: 1
Worker ID: 1
Worker ID: 1
Worker ID: 1
Worker ID: 1
Worker ID: 1
Worker ID: 1
Worker ID: 1
Worker ID: 1
Worker ID: 1
Worker ID: 1

can't get the real id.

得到MyBatis作者的反馈，<insert>语句本身有int型的返回值，表示插入的条数。故在<insert>语句内想使用 <selectKey>返回自增ID自动生成的ID值是不可能的。

可以单独用<select>语句来完成返回自增ID自动生成的ID值。