MyBatis: Gets the self-increment primary key for the inserted record

We know that the primary key in MySQL can be set to auto_increment, that is, the self-increment primary key, whenever a new record, the primary key value automatically added 1.

In MyBatis, insert is executed, and the return value is the number of statement rows affected, which is consistent with JDBC.

But if you need to return the primary key of the inserted value in your app, there are two ways:

First: Add Usegeneratedkey and Keyproperty properties to the Insert node of the Mapper

Second way: How to add a selectkey child node under the Mapper Insert node

Here are specific examples of how to use them:

The users table structure of mybatis database in MySQL

+----------+--------------+------+-----+---------+----------------+| field    |  Type         | null | key | default  | extra          |+----------+--------------+---- --+-----+---------+----------------+| id       | int (one)        | no   | pri | null    |  auto_increment | |  name     | varchar (  | YES  |  )    | NULL    |                 | |  password | varchar (255)  | yes  |     | null     |                |+----------+--------------+------+-----+---------+--- -------------+

Second, mybatis configuration file Mybatis-config.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><!--  attributes defined under the Properties node can be referenced in the global The specific reference is in the  ${property name} --><properties><property name= "Driver"  value= " Com.mysql.jdbc.Driver "/><property name=" url " value=" Jdbc:mysql://localhost:3306/mybatis "/> <property name= "username"  value= "root"/><property name= "password"  value= "ZZLIBDMT /></properties><!--  alias for domain, note: You can only set aliases for JavaBean, not id -->< in mapper files Typealiases><package name= "Cn.kolbe.mybatis.domain"/></typealiases><!--  Environments can have multiple environment nodes, representing a different development environment, as long as there is a match with the default can be  --><!--  specific in development, Development configuration can be used at development time, production configuration is used on-line  --><environments default= "development" >< Environment id= "Development" ><!--&NBSP;JDBC means directly using the underlying JDBC transaction management  --><transactionmanager type= "JDBC" > </transactionManager><!--  Configuration pooled will use MyBatis's own database connection pool  --><!--  Specific property names You can view the setter method for this class  --><!-- org.apache.ibatis.datasource.pooled.PooledDataSource --> <datasource type= "Pooled" ><property name= "Driver"  value= "${driver}"/><property  name= "url"  value= "${url}"/><property name= "username"  value= "${username}"/>< Property name= "Password"  value= "${password}"/></datasource></environment></ Environments><mappers><mapper resource= "Cn/kolbe/mybatis/domain/usermapper.xml"/></ Mappers></configuration>

Third, MyBatis mapper mapping file Usermapper.xml

<?xml version= "1.0"  encoding= "UTF-8"? ><! doctype mapper public  "-//mybatis.org//dtd mapper 3.0//en"   "http://mybatis.org/ Mybatis-3-mapper.dtd "><mapper namespace=" Cn.kolbe.mybatis.domain.UserMapper "><insert id= "GetGeneratedKey1"  parametertype= "Cn.kolbe.mybatis.domain.User" >insert into users (name,  password)  values (#{name}, #{password}) </insert><!-- usegeneratedkeys default value is False, The value of Keyproperty corresponds to the primary key name in the user class  --><insert id= "GetGeneratedKey2"  parametertype= " Cn.kolbe.mybatis.domain.User " usegeneratedkeys=" true " keyproperty=" id ">insert into  Users (Name, password)  values (#{name}, #{password}) </insert><insert id= " GetGeneratedKey3 " parametertype=" Cn.kolbe.mybatis.domain.User "><!--  Use this method to be aware that the resulttype is to be set, Otherwise the exception will be thrown, the value corresponding to the primary key type in the user class  --><!-- keyproperty corresponds to the primary key name in the user class, assuming that it is named user_id, then this side will be changed to user_id --><selectkey keyproperty= "id"  resulttype= "int" >select last_insert_id () < /selectkey>insert into users (Name, password)  values (#{name}, #{password}) </ Insert></mapper>

Iv. Common Java entity class user

Package Cn.kolbe.mybatis.domain;public class User {private Integer id;private string Name;private string password;public User (string name, string password) {this.name = Name;this.password = password;} Public Integer GetId () {return ID;} public void SetId (Integer id) {this.id = ID;} Public String GetName () {return name;} public void SetName (String name) {this.name = name;} Public String GetPassword () {return password;} public void SetPassword (String password) {this.password = password;} @Overridepublic String toString () {return "User [id=" + ID + ", name=" + name + ", password=" + password + "]";}}

Five, test code

package cn.kolbe.mybatis;import java.io.fileinputstream;import java.io.filenotfoundexception; import java.io.inputstream;import org.apache.ibatis.session.sqlsession;import  org.apache.ibatis.session.sqlsessionfactory;import org.apache.ibatis.session.sqlsessionfactorybuilder; import org.junit.before;import org.junit.test;import cn.kolbe.mybatis.domain.user;public  class mybatistest {/** *  Normal insert operation  *  *  @throws   filenotfoundexception */@Testpublic  void testgetgeneratedkey1 ()  throws  Filenotfoundexception {sqlsession session = factory.opensession (); string statement =  "Cn.kolbe.mybatis.domain.UserMapper.getGeneratedKey1"; User user = new user ("user_01", "123456"); Int result = session.update ( Statement, user);// result is the number of rows affected Session.commit (); System.out.println (user);//  output  user [id=nulL,&NBSP;NAME=USER_02,&NBSP;PASSWORD=123456]SYSTEM.OUT.PRINTLN ("Result:"  + result);                  //  input  result:  1}/** *  added  usegeneratedkeys= "true"  keyproperty= "id"   Property Insert action in insert  *   *  @throws  filenotfoundexception */@Testpublic  void testgetgeneratedkey2 ()  throws filenotfoundexception {sqlsession session = factory.opensession (); string statement =  "Cn.kolbe.mybatis.domain.UserMapper.getGeneratedKey2"; User user = new user ("user_02", "123456");//  at this time MyBatis not only returned the number of rows affected, but also assigned the self-increment ID to the passed in user/ /  at this time can see the user print out the ID is a value!!! Int result = session.update (Statement, user);//  the number of rows affected Session.commit (); System.out.println (user);                                //  Output  user [id=2, &NBSP;NAME=USER_02,&NBSP;PASSWORD=123456]SYSTEM.OUT.PRINTLN ("Result:"  + result);                  //  Output  result:  1}/** *  Add a child node in insert <selectKey> insert operation for child nodes  * <selectkey order= "after"  keyproperty= "id" > *select select last_insert_id ()  * </selectKey>  *  *  @throws  filenotfoundexception */@Testpublic  void testgetgeneratedkey3 ( )  throws filenotfoundexception {sqlsession session = factory.opensession (); string statement =  "Cn.kolbe.mybatis.domain.UserMapper.getGeneratedKey3"; User user = new user ("user_03",  "123456");//  at this time MyBatis not only returned the number of rows affected, It also assigns the increment ID to the passed in user//  at this time can see the user print out the ID is a value!!! Int resuLt = session.update (Statement, user);//  the number of rows affected Session.commit (); System.out.println (user);                                //  Output  user [id=3, name=user_03, password=123456]system.out.println ("Result:"  +  Result);                    Output  result: 1}/** *  Initialize sessionfactory *  @throws  exception */@ Beforepublic void init ()  throws Exception {InputStream in = new  FileInputStream ("Src/main/java/mybatis-config.xml"); Factory = new sqlsessionfactorybuilder (). Build (in);} Private sqlsessionfactory factory;}

Note:

1) Use Selectkey to get the primary key, equivalent to call the MySQL last_insert_id () function, meaning that the query last inserted ID, equivalent to execute two statements

2) The primary key acquisition method in both ways is to automatically assign the ID value to the incoming JavaBean by MyBatis, instead of returning through the update statement, which is the same JDBC as the update returns (the number of rows affected). The way to get the ID is to call the User.getid () method after the update is done, and see the example above.

MyBatis: Gets the self-increment primary key for the inserted record