DB/Mysql

MyBatis mysql selectKey LAST_INSERT_ID() 설명

Request 2015. 8. 6. 21:43

출처 - http://www.raistudies.com/mybatis/inserting-auto-generated-id-using-mybatis-return-id-to-java/

 

 

 

  1. AUTO_INCREMENT facility in MySQL Database
  2. IDENTITY facility in MS SQL Server.
  3. A combination of sequence and trigger to generate new unique id in Oracle server.

 

Tools Used:

  1. MyBatis 3.1.0
  2. MySql Java Connector 5.1

 

First of all, we have to change the schema of table Product to make the id field auto generated. Following is the DDL command to make the Product table with auto generated id:

1 CREATE TABLE Product(id BIGINT NOT NULL AUTO_INCREMENT , brand VARCHAR(20),
2 model VARCHAR(20), name VARCHAR(30) , PRIMARY KEY (id));

Here, I have used AUTO_INCREMENT to generate unique id in MySQL database.

Now, to use auto generated id in our mapping file ProductServices.xml to modify our <insert/> command to use it:

1 <?xml version="1.0" encoding="UTF-8"?>
2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
3 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
4  
5 <mapper namespace="com.raistudies.services.ProductServices">
6  
7     <insert id="save" parameterType="product" useGeneratedKeys="true" keyProperty="id"�  keyColumn="id">
8         INSERT INTO Product (brand,model,name)
9         VALUE (#{brand}, #{model}, #{name} )
10         <selectKey keyProperty="id" resultType="long" order="AFTER">
11             SELECT LAST_INSERT_ID();
12         </selectKey>
13     </insert>
14  
15 </mapper>
  • You can identify three changes that we have to made in <insert/> command tag to use auto generated id and then return the newly generated id to java.
  • We have added useGeneratedKeys=”true”, this specify mybatis that the id will be auto generated by database.We also have to specify keyProperty=”id” and keyColumn=”id” which tells the property name of POJO class Product that will be used as ID and also the column name of table product that is used as id.
  • We have to remove id field from insert DDL, so that database can generate a new id for the column and put it.
  • We have used : 
    <selectKey keyProperty=”id” resultType=”long” order=”AFTER”>
                SELECT LAST_INSERT_ID();
            </selectKey>
    That will return newly generated id and save it to the id property of POJO class Product. The query written in between <selectKey/> tags is database specific and it may be change according to database. In this example, we have used the query that will work for MySQL database server only.

To test the functionality, we have made a little modification in our runner class:

1 package com.raistudies.runner;
2  
3 import java.io.IOException;
4 import java.io.Reader;
5  
6 import org.apache.ibatis.io.Resources;
7 import org.apache.ibatis.session.SqlSession;
8 import org.apache.ibatis.session.SqlSessionFactory;
9 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
10  
11 import com.raistudies.domain.Product;
12 import com.raistudies.services.ProductServices;
13  
14 public class AppTester {
15 private static SqlSessionFactory sessionFac = null;
16 private static Reader reader;
17 private static String CONFIGURATION_FILE = "sqlmap-config.xml";
18  
19     static{
20         try {
21             reader = Resources.getResourceAsReader(CONFIGURATION_FILE);
22             sessionFac = new SqlSessionFactoryBuilder().build(reader);
23         } catch (IOException e) {
24             e.printStackTrace();
25         }
26     }
27  
28     public static void main(String[] args) {
29         SqlSession session = sessionFac.openSession();
30         try {
31         ProductServices productServiceObj = session.getMapper(ProductServices.class);
32         Product product = new Product();
33         product.setBrand("LG");
34         product.setModel("P500");
35         product.setName("Optimus One");
36         productServiceObj.save(product);
37         System.out.println("The new id is : " + product.getId());
38         session.commit();
39  
40         } finally {
41             session.close();
42         }
43     }
44  
45 }

The new code will not specify the value of id field and also print the new generated id in console.

Run the code and you will get output like this:

Auto generated id in MyBatis and return the new id to java

Using Auto Generated ID in MyBatis and return the new ID to java

You can also try the example yourself. Just download the code from bellow links, import the project in Eclipse and create the product table using given DDL command:

Code + lib: Download

Code: Download

저작자표시