MyBatis Framework--mybatis Insert data back to primary key (MySQL, Oracle)

When inserting data into a database, most of the time the self-increment column or UUID is used as the primary key. The value of the primary key is not known until it is inserted, but in many cases we need to use the primary key that we just inserted after inserting the data, such as inserting data into two tables A, B (A's primary key is the foreign key of B), inserting data into table A, and inserting data into table B requires a primary key.

For example, add a user and return the user ID that was obtained after inserting the user:

1. /** * 添加用户信息 * @param user * @throws Exception */ public int insertUser(User user) throws Exception { SqlSession session=sqlSessionFactory.openSession(); session.insert("com.danny.mybatis.insertUser", user); session.commit(); return user.getUserId();//返回插入数据库后得到的用户id }

Here's a summary of the 4 things that return primary keys when inserting data: MySQL environment, primary key MyBatis, MySQL environment primary key is UUID, MySQL environment primary key self-increment, MySQL environment primary key is UUID.

The following text shows an example of the user entity, with the UserID, userName, sex, birthday, address five attributes, where the UserID may be of type int, or it may be of type string.

Database for MySQL

Primary key is self-increment (primary key is numeric type and self-increment)

Using the MySQL LAST_INSERT_ID() method to get the primary key of the inserted record, select LAST_INSERT_ID() you can query and return the primary key of the data you just inserted after inserting the data (but executing this statement alone will only return 0).

<insert id= "Insertuser" parametertype=" Com.danny.mybatis.po.User ">  <selectkey keyproperty= " UserId "order=" after " Resulttype= "Java.lang.Integer" > select last_insert_id () </< Span class= "Hljs-title" >selectkey> insert INTO T_user (username,birthday,sex,address) VALUES (#{username},#{ Birthday},#{sex},#{address}) </INSERT>    

ParameterType: Specifies that the Insert execution statement receives a parameter of type Pojo (user here).

Keyproperty: Sets the queried primary key value to parametertype which property of the specified object.

Order: The <selectKey> SQL statement within the tag is insert executed in relation to the statement, AFTER indicating that select LAST_INSERT_ID() the statement insert executes after the statement.

When the primary key is a UUID (the primary key must be a character type)

Using the MySQL method UUID() method to get the random UUID as the primary key, select UUID() you can generate a random uuid before inserting the data and assign a value to the primary key that will be inserted into the record by Keyproperty.

<insert id="insertPerson" parameterType="com.danny.mybatis.po.User">        <selectKey keyProperty="userId" order="BEFORE" resultType="java.lang.String">            select UUID()        </selectKey>        insert into user(userId,userName,birthday,sex,address) values (#{id},#{userName},#{birthday},#{sex},#{address}) </insert>

The property value you can see in the preceding code order is BEFORE that the UUID was generated before insertion, and the UUID has been assigned to the user's ID.

Background database for Oracle:

Primary key is self-increment (primary key is numeric type):

To implement primary key auto-increment in Oracle, you need to create a sequence that is equivalent to creating a global variable that stores the current maximum value of the primary key of the corresponding table (when the primary key is a numeric type).

Create a sequence for the user table first:

CREATE SEQUENCE USER_ID_SEQ

INCREMENT BY 1 -- 每次递增1

START WITH 1 -- 从1开始

MINVALUE 1 -- 最小值=1

NOCYCLE; -- 不循环

Take advantage USER_ID_SEQ.NEXTVAL of getting the primary key to insert the data:

<insert id="insertUser" parameterType="com.danny.mybatis.po.User">        <selectKey keyProperty="userId" order="BEFORE" resultType="java.lang.Integer"> select USER_ID_SEQ.NEXTVAL as userId from DUAL </selectKey> insert into T_USER(userId,userName,birthday,sex,address) values (#{userId},#{userName},#{birthday},#{sex},#{address}) </insert>

When the primary key is UUID

SYS_GUID()obtain a random GUID as the primary key using Oracle's own method:

<insert id="insertUser" parameterType="com.danny.mybatis.po.User">        <selectKey keyProperty="userId" order="BEFORE" resultType="java.lang.Integer"> select SYS_GUID() as userId from DUAL </selectKey> insert into T_USER(userId,userName,birthday,sex,address) values (#{userId},#{userName},#{birthday},#{sex},#{address}) </insert>

MyBatis Framework--mybatis Insert data back to primary key (MySQL, Oracle)