MyBatis SQL Mapping

MyBatis is an advanced persistence framework based on JDBC, whose core value lies in decoupling complex SQL statements from Java objects. Through SQL mapping files (XML) or annotations, developers can write clear database operation logic directly without facing the tedious JDBC code boilerplate. In this tutorial, we will explore in depth how MyBatis achieves efficient SQL mapping through XML configuration, mastering the complete process from entity class definition to result set retrieval.
Before using MyBatis, it is essential to ensure that project dependencies are correctly introduced. In a Maven project, simply adding mybatis-spring-boot-starter allows for rapid integration. The core lies in establishing the mapping relationship between Java entity classes and database tables, which is typically accomplished through annotations or XML tags.

Taking the user table as an example, create a standard User entity class. MyBatis utilizes reflection mechanisms to automatically associate database fields with Java properties:

public class User {
    private Long id;
    private String username;
    private Integer age;
    // getters and setters...
}

In the XML mapping file, the resultMap tag is used to define complex column-to-object mappings. While MyBatis can automatically match field names with property names in simple scenarios, explicit configuration of id, property, and column attributes becomes crucial when dealing with multi-table joins or special naming conventions:

<resultMap id="UserResultMap" type="com.example.User">
    <id property="id" column="user_id" />
    <result property="username" column="username" />
    <result property="age" column="age" />
</resultMap>

Implementation of Basic CRUD Operations

MyBatis SQL mapping primarily implements CRUD operations through four core tags: select, insert, update, and delete. These tags are directly wrapped within the <mapper> element and associated with specific Mapper interface methods.

Query Operations: From Simple to Dynamic

The simplest query involves retrieving a single record, such as finding a user by ID. The corresponding SQL statement can be written directly in the XML:

<select id="selectById" resultMap="UserResultMap">
    SELECT * FROM users WHERE user_id = #{id}
</select>

When calling this in Java code, simply inject the UserMapper interface and pass the parameters:

public User getUser(Long id) {
    return userMapper.selectById(id);
}

For more complex query scenarios, such as pagination or conditional filtering, MyBatis provides powerful <where> and <if> tags. For example, to query a list of users based on username and age range:

<select id="selectByConditions" resultMap="UserResultMap">
    SELECT * FROM users 
    <where>
        <if test="username != null and username != ''">
            AND username = #{username}
        </if>
        <if test="ageMin > 0">
            AND age >= #{ageMin}
        </if>
    </where>
</select>

This dynamic SQL mechanism makes the code both flexible and easy to maintain, avoiding redundancy and errors caused by hardcoding.

Insert and Update Operations

When inserting new records, the insert tag needs to handle cases involving auto-incrementing primary keys or manually generated IDs. If useGeneratedKeys="true" is used with a specified keyProperty, MyBatis will automatically return the newly generated ID:

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

Update operations similarly rely on the update tag, which dynamically constructs the SET clause using the <set> tag to avoid SQL syntax errors:

<update id="updateUser">
    UPDATE users 
    <set>
        <if test="username != null">
            username = #{username},
        </if>
        <if test="age != null">
            age = #{age}
        </if>
    </set>
    WHERE user_id = #{id}
</update>

Multi-Table Joins and Result Set Mapping

In practical applications, data is often distributed across multiple tables. MyBatis supports one-to-one and one-to-many relationship mappings through association and collection tags.

Assuming there exists an orders table and an order_items table (line items), the goal is to query a user's orders along with the list of products contained within them:

<resultMap id="UserOrderResultMap" type="com.example.User">
    <id property="id" column="user_id" />
    <result property="username" column="username" />
    
    <!-- One-to-many relationship -->
    <collection property="orders" ofType="com.example.OrderItem">
        <id property="id" column="order_id" />
        <result property="productName" column="product_name" />
        <result property="quantity" column="quantity" />
    </collection>
</resultMap>

The corresponding SQL statement must include JOIN operations and specify fetchType="JOIN" to ensure associated data is loaded in a single batch:

<select id="getUserOrders" resultMap="UserOrderResultMap">
    SELECT u.user_id, u.username, o.order_id, p.product_name, oi.quantity
    FROM users u
    LEFT JOIN orders o ON u.user_id = o.user_id
    LEFT JOIN order_items oi ON o.order_id = oi.order_id
    LEFT JOIN products p ON oi.product_id = p.product_id
</select>

Best Practices and Performance Optimization Recommendations

When using MyBatis for SQL mapping, adhering to certain best practices can significantly improve code quality and system performance. First, avoid hardcoding strings directly in SQL statements; always use #{} placeholders to prevent SQL injection attacks. Second, for queries involving large data volumes, it is crucial to use pagination parameters reasonably, such as the LIMIT clause within <where> tags.

Additionally, pay attention to index usage. While the SQL generated by MyBatis is clear, its performance depends entirely on the database optimization status. When writing complex JOIN statements, ensure that fields participating in the join have appropriate indexes; otherwise, it may lead to full table scans, severely impacting query speed. Finally, regularly review slow query logs and utilize MyBatis's logImpl configuration to output execution plans, enabling you to identify and optimize bottlenecks.

By mastering the core concepts and techniques of SQL mapping outlined above, developers can more efficiently build enterprise-level Java Web applications that are highly maintainable and high-performance.