Detailed explanation of two implementation modes of primary key backfilling in MyBatis

Primary key backfilling is actually a very common requirement, Especially in the process of adding data, we often need to get the newly added data id after adding data. Both Jdbc and various database frameworks provide relevant support for this. In this paper, I will share with you two implementation ideas of database primary key backfilling in MyBatis.

Native writing

The framework comes from the basic knowledge we have learned. Primary key backfilling is actually a writing method supported in JDBC. Some small partners may not know this point, so let me first talk about how to implement primary key backfilling in JDBC.

The implementation of primary key backfilling in JDBC is actually very easy, mainly specifying that primary key backfilling is required when constructing PreparedStatement, and then querying id that has just inserted data after successful insertion. The example code is as follows:

public int insert(Person person) {
  Connection con = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  con = DBUtils.getConnection();
  ps = con.prepareStatement("INSERT INTO person(username,password,money) VALUES(?,?,?)", PreparedStatement.RETURN_GENERATED_KEYS);
  ps.setObject(1, person.getUsername());
  ps.setObject(2, person.getPassword());
  ps.setObject(3, person.getMoney());
  int i = ps.executeUpdate();
  rs = ps.getGeneratedKeys();
  int id = -1;
  if (rs.next()) {
    id = rs.getInt(1);
  }
  return id;
}

The difference between SQL and ordinary insertion is mainly reflected in two places:

The first is that when constructing PreparedStatement, there is one more parameter, which specifies that primary key backfill is required. After the update operation is completed, getGeneratedKeys is called, and then an ResultSet object is obtained. From this cursor set, id with the data just inserted can be obtained.

This is a native writing, in MyBatis, this requirement provides two different implementation, the following look at separately.

Frame writing

1 In general, there are two ways to generate primary keys:

Primary key self-growth Custom primary key (1 can generally use UUID or class UUID)

If it is the second type, the primary key 1 is generally generated in Java code, and then passed into the database to perform insertion operation. If the first primary key grows itself, Java may need to know the primary key after the data is successfully added.

Needless to say, the basic usage of MyBatis is not the focus of this article. Let's take a look at two different implementations of primary key backfilling in MyBatis.

Mode 1

The first method is relatively simple, and it is also an implementation method recommended by Songge:

<insert id="insertBook" useGeneratedKeys="true" keyProperty="id">
  insert into t_book (b_name,author) values (#{name},#{author});
</insert>

This is a simple way to add the useGeneratedKeys attribute to the insert node and set the attribute to receive the return primary key. After the configuration is completed, we perform an insertion operation, and an object is passed in during insertion. After insertion is completed, the id of this object will be automatically assigned, and the value is the id that has just been successfully inserted.

It is recommended that you use this method for the simple reason that it is simple and convenient to realize.

Mode 2

The second way is to query the just inserted id by using the last_insert_id () function that comes with MySQL. The example code is as follows:

<insert id="insertBook">
  <selectKey keyProperty="id" resultType="java.lang.Integer">
    SELECT LAST_INSERT_ID()
  </selectKey>
  insert into t_book (b_name,author) values (#{name},#{author});
</insert>

In this way, selectKey is added to insert node to realize primary key backfilling. In fact, this method is more functional, Because SQL in selectKey node can be executed before or after insertion (it can be realized by setting Order attribute of node to AFTER or BEFORE), when to execute it depends on specific requirements. If it is primary key backfilling, we certainly need to execute SQL in selectKey node after inserting SQL.

Note that Mode 2, Mode 1 also specifies which property to bind the queried data to by setting keyProperty.

Summarize

Ok, this article introduces two ways of primary key backfilling in MyBatis. Have you ever arrived at get? If you have any questions, please leave a message for discussion.