Mybatis get self incrementing primary key

Entity class attributes are as follows

public class User {
    private Integer id;
    private String username;
    private String userEmail;
    private String userCity;
    private Integer age;
    //Omit the get and set methods
}

If the database supports fields that automatically generate primary keys (such as MySQL and SQL Server), you can set usegeneratedkeys = “true” and then set keyproperty to the target property. Keyproperty sets the properties that need to be mapped to the entity class.

Insert a single record

The interface is defined as follows

public int addUser(User user);

Mapper corresponding to the interface The XML definition is as follows

<insert id="addUser"
        parameterType="com.example.mybatis.entity.User"
        useGeneratedKeys="true"
        keyProperty="id">
    insert into user
    (username, user_email, user_city, age)
    values (#{username}, #{userEmail}, #{userCity}, #{age})
</insert>

Mybatis will set the obtained self incrementing primary key to the ID attribute in the user class

Insert multiple records

The interface is defined as follows

public int addUserList(List<User> userList); 

Mapper corresponding to the interface The XML definition is as follows

<insert id="addUserList"
        parameterType="com.example.mybatis.entity.User"
        useGeneratedKeys="true"
        keyProperty="id">
    insert into user
    (username, user_email, user_city, age)
    values
    <foreach item="item" collection="list" separator=",">
        (#{item.username}, #{item.userEmail}, #{item.userCity}, #{item.age})
    </foreach>
</insert>

exceptional case

Mybatis has another method to generate primary keys for databases that do not support automatic generation of primary keys or JDBC drivers that may not support automatic generation of primary keys. Here is a simple example throughselect unix_timestamp(now())You can get the current timestamp

<insert id="addUser">
    <selectKey keyProperty="id" resultType="int" order="BEFORE">
        select unix_timestamp(now())
    </selectKey>
    insert into user
    (id, username, user_email, user_city, age)
    values (#{id}, #{username}, #{userEmail}, #{userCity}, #{age})
</insert>

In the example above,selectKeyThe statement in the element will be run first, the user ID will be set, and then the insert statement will be called. This can provide you with a behavior similar to the automatic generation of primary keys in the database, while maintaining the simplicity of Java code.

The description of the selectkey element is as follows:

<selectKey
  keyProperty="id"
  resultType="int"
  order="BEFORE"
  statementType="PREPARED">

attribute	describe
keyProperty	The target attribute of the result of the selectkey statement should be set. If you want multiple generated columns, you can also have a comma separated list of attribute names.
keyColumn	The name of the column in the returned result set that matches the property. If you want multiple generated columns, you can also have a comma separated list of attribute names.
resultType	Type of result. Mybatis can usually be inferred, but it won’t be a problem to write it for more accuracy. Mybatis allows any simple type to be used as the type of primary key, including string. If you want to act on multiple generated columns, you can use an object or a map that contains the desired attributes.
order	This can be set to before or after. If it is set to before, it will first generate the primary key, set the keyproperty, and then execute the insert statement. If it is set to after, the insert statement is executed first, and then the statement in the selectkey – this is similar to the behavior of Oracle database. There may be embedded index calls inside the insert statement.
statementType	As before, mybatis supports the mapping types of state, prepared and callable statements, representing Preparedstatement and callablestatement types respectively.

This work adoptsCC agreement, reprint must indicate the author and the link to this article