The difference between useGeneratedKeys and <selectKey> in MyBatis primary key generation strategy

useGeneratedKeys

Mechanism and Principles

• JDBC Features：useGeneratedKeysUsing JDBCgetGeneratedKeys()Method, which obtains the primary key value automatically generated by the database after executing the INSERT statement.
• Automatically set properties: MyBatis will automatically set the obtained primary key value to the defined in the mapping filekeyPropertyon the object attribute pointed to.
• Applicable scenarios: Suitable for databases that support autoincremental primary keys or sequences (such as AUTO_INCREMENT of MySQL, SERIAL of PostgreSQL).

Examples and details

Consider a simple user table:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100)
);

Corresponding Java entity class:

public class User {
    private Integer id;
    private String username;
    private String email;

    // getters and setters...
}

Mapper XML configuration:

<insert  useGeneratedKeys="true" keyProperty="id">
  INSERT INTO users (username, email)
  VALUES (#{username}, #{email})
</insert>

Things to note：

• Transaction Management: Make sure your insert operation is performed in a transaction to avoid partial success.
• Batch Insert: For batch inserts, you may need to handle them specifically becausegetGeneratedKeys()The returned result set may contain multiple generated keys. Can be passed<foreach>Tags to implement batch insertion and throughResultSetGet the generated keys one by one.

<insert  useGeneratedKeys="true" keyProperty="id" keyColumn="id">
  INSERT INTO users (username, email)
  <foreach collection="list" item="user" separator=",">
    (#{}, #{})
  </foreach>
</insert>

Primary key processing after batch insertion：

List<User> users = // ... your list of users to insert
SqlSession sqlSession = ();
try {
    UserMapper mapper = ();
    for (User user : users) {
        (user);
        // MyBatis will automatically set the generated ID into the 'user' object
    }
    ();
} finally {
    ();
}

Exception handling

• Catch exceptions: If the insertion fails, such as violating the uniqueness constraint, MyBatis will throw an exception, and you need to catch and handle these exceptions.
• Rollback mechanism: Ensure that transactions can be rolled back correctly in the event of an exception to maintain consistency of the data.

try {
    ("insertUser", user);
    ();
} catch (PersistenceException e) {
    ();
    throw e;
} finally {
    ();
}

Performance considerations

• Reduce the number of queries: Minimize additional queries, such as reasonable use when batch insertsgetGeneratedKeys()。
• Optimize connection pools: Ensure that the JDBC connection pool is properly configured to cope with performance requirements in high concurrency situations.
• Batch Optimization: For batch operations, you can use the batch function of JDBC to improve performance.

<insert  parameterType="">
  INSERT INTO users (username, email)
  VALUES
  <foreach collection="list" item="user" separator=",">
    (#{}, #{})
  </foreach>
</insert>

<selectKey>

Mechanism and Principles

• Custom SQL：<selectKey>Allows you to write arbitrary SQL to generate primary key values ​​and can specify whether the query is executed before or after INSERT.
• flexibility: It is suitable for any situation where specific logic is required to generate primary keys, or when the database does not support self-increment fields.

Examples and details

Suppose we have an Oracle database and use sequencesuser_seqTo generate the primary key:

Mapper XML configuration:

<insert >
  <selectKey keyProperty="id" resultType="int" order="BEFORE">
    SELECT user_seq.NEXTVAL FROM dual
  </selectKey>
  INSERT INTO users (id, username, email)
  VALUES (#{id}, #{username}, #{email})
</insert>

For some databases (such as PostgreSQL), you can use it directlyRETURNINGClauses to simplify the code:

<insert  parameterType="User">
  INSERT INTO users (username, email)
  VALUES (#{username}, #{email})
  RETURNING id
</insert>

Performance considerations

• Reduce the number of queries: Minimize additional queries, such as usingRETURNINGClause substitution<selectKey>。
• Cache sequence: If using sequences to generate primary keys, consider implementing sequence caching at the application layer to reduce the pressure on the database.
• Concurrent control: Considering the sequence allocation problem in concurrency environment, ensure that your sequence design can correctly handle high concurrency scenarios.

Exception handling

• Null value check: Make sure to be called<selectKey>After checking whether the returned primary key is empty or invalid.
• Concurrent control: Considering the sequence allocation problem in concurrency environment, ensure that your sequence design can correctly handle high concurrency scenarios.
• Transaction Management: Ensure that transactions can be rolled back correctly in the event of an exception to maintain consistency of the data.

<selectKey keyProperty="id" resultType="int" order="BEFORE">
  SELECT COALESCE(MAX(id), 0) + 1 FROM users -- Simple example，This method should be avoided in actual production environments
</selectKey>

Database compatibility

Different databases have different characteristics and limitations, such as Oracle's sequence mechanism, MySQL's self-incremental field, PostgreSQL'sRETURNINGSyntax, etc. When designing primary key generation strategies, it is important to consider the specific characteristics of the target database.

• Oracle: Sequences and triggers are usually used to generate primary keys.
• MySQL: Use the self-increment field.
• PostgreSQL:supportSERIALTypes andRETURNINGclause.
• SQL Server: Use IDENTITY column or SEQUENCE object.

Monitoring and logging

In a production environment, it is very important to monitor the operations related to primary key generation. Good logging can help quickly locate and resolve issues, especially when it comes to concurrency control or performance bottlenecks.

("Generated ID for new user: {}", ());

Comprehensive comparison

Things to note

Batch insertion and primary key generation

For batch inserts,useGeneratedKeysand<selectKey>They all have their own specific challenges. foruseGeneratedKeys, you need to make sure that multiple primary key values ​​returned in the result set are processed correctly. And for<selectKey>, you may need to generate a primary key individually for each record you want to insert, which can cause performance issues. Therefore, in the case of batch insertion, it is best to evaluate specific performance requirements and choose the most appropriate strategy.

Transaction boundaries and consistency

Whether it isuseGeneratedKeysstill<selectKey>, all should ensure that their operations are completed within a clear transaction boundary to prevent partially successful data inconsistencies. Especially in distributed systems, cross-service transaction management is particularly important.

Database compatibility

Different databases have different characteristics and limitations, such as Oracle's sequence mechanism, MySQL's self-incremental field, PostgreSQL'sRETURNINGSyntax, etc. When designing primary key generation strategies, it is important to consider the specific characteristics of the target database.

Optimization suggestions

• Batch operation: Use JDBC's batch function to improve the performance of batch insertion.
• Sequence cache: For databases that use sequences to generate primary keys, sequence caching can be implemented at the application level to reduce the overhead of frequent access to the database.
• Asynchronous processing: For large-scale data insertion, asynchronous processing can be considered to relieve database pressure.
• Index optimization: Ensure that the insertion operation does not cause a large number of index reconstructions and affects performance.

This is the end of this article about the difference between useGeneratedKeys and <selectKey> in the MyBatis primary key generation strategy. For more related contents of MyBatis useGeneratedKeys and <selectKey>, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!