In database operations, we often need to retrieve auto-generated primary key values after inserting records. MyBatis provides two approaches to accomplish this: using the useGeneratedKeys attribute and the selectKey element.

Using useGeneratedKeys Attribute

The useGeneratedKeys attribute enables automatic retrieval of database-generated keys:

<insert id="addStudentWithGeneratedKey" useGeneratedKeys="true" 
        keyProperty="studentId" parameterType="Student">
    INSERT INTO student(student_name, student_age, student_score) 
    VALUES(#{name}, #{age}, #{score})
</insert>

Key configuration points:

• useGeneratedKeys="true" enables auto-generated key retrieval
• keyProperty="studentId" specifeis the entity property to receive the generated key
• The parameter type should match your entity class
• No return type specification is needed in the mapper interface
• The entity class must have appropriate setter and getter methods for the key property

Using selectKey Element

An alternative approach uses the selectKey element for more control:

<insert id="addStudentWithKeySelection" parameterType="Student">
    INSERT INTO student(student_name, student_age, student_score) 
    VALUES(#{name}, #{age}, #{score})
    
    <selectKey resultType="int" keyProperty="studentId" order="AFTER">
        SELECT @@IDENTITY
    </selectKey>
</insert>

Or using MySQL-specific function:

<insert id="addStudentWithKeySelection" parameterType="Student">
    INSERT INTO student(student_name, student_age, student_score) 
    VALUES(#{name}, #{age}, #{score})
    
    <selectKey resultType="int" keyProperty="studentId" order="AFTER">
        SELECT LAST_INSERT_ID()
    </selectKey>
</insert>

Important considerations for selectKey:

• The outer insert element doesn't require a result type
• The inner selectKey element must specify a result type
• order="AFTER" executes the key selection after the insert operation
• SELECT @@IDENTITY and SELECT LAST_INSERT_ID() both retrieve the last inserted ID
• The entity class requires proper setter and getter methods

Returning Affected Row Count

MyBatis mapper methods can return the number of affected rows while still retrieving generated keys:

// Mapper interface method
int addStudentWithGeneratedKey(Student studentEntity);

The implementation retrieves both the affected row count and populates the entity with the generated key:

public int addStudentWithGeneratedKey(Student studentEntity) {
    SqlSession session = null;
    try {
        session = SessionFactory.getSqlSession();
        int affectedRows = session.insert("addStudentWithGeneratedKey", studentEntity);
        session.commit();
        return affectedRows;
    } finally {
        if (session != null) {
            session.close();
        }
    }
}

Database Configuration Notes

For MySQL connections, insure proper driver configuration:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.21</version>
</dependency>

In XML configuration files, escape special characters properly:

<property name="connectionUrl" 
          value="jdbc:mysql://localhost:3306/testdb?characterEncoding=utf-8&amp;serverTimezone=UTC"/>