useGenerateKeys 사용하기 예제

Inserting auto generated id using mybatis and return the generated id to java

In our previous tutorial of MyBatis, we see how to configure MyBatis in in your project and insert data to database using Mybatis proxy functionality. You have notice that, in real life projects the value of id columns are generated at runtime to identify the object uniquely, most of the time it is generated while the object is created in java. To generate such auto generated id values in java needs extra calculation of finding out a unique id in system and may cause a bottleneck while more data are saved.

So, what will be the solution?

The solution is that, we can get this facility from underlying database that saves data. We can use database specific facilities to generate unique id and then associate that id with the object.

We can use:

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.

Each database server has such facility that can be used to generate unique id for new records.

In this tutorial, we will modify our previous example to include auto generated id and return that id to java.

This tutorial is assuming that you have read previous tutorial of Configuring MyBatis 3. If you have not read it, it is recommended to read.

MyBatis version 3.0.4 has a bug in regarding auto generated id, you should use MyBatis version 3.1.0 instead.

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:

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

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