MyBatis — Cheat Sheet

Tags: mybatis, java, sql, orm, mapper, persistence

Level: intermediate

TL;DR: MyBatis is a persistence framework for Java that maps SQL statements to Java methods via XML or annotations, offering fine-grained control over SQL execution and result mapping.

Key concepts

SqlSessionFactory creates SqlSession instances and should be configured as an application singleton.

SqlSession represents a single database connection to execute mapped statements and manage transactions.

Mapper interfaces bind Java methods to specific SQL queries defined in XML or annotations.

XML Mapper files isolate SQL statements from Java code and allow complex result mappings.

ResultMap is a powerful element to map complex queries, joins, and nested properties to Java objects.

Dynamic SQL enables conditional query building using XML tags like <if>, <choose>, and <foreach>.

First-level cache is enabled by default and operates at the SqlSession level.

Second-level cache operates at the Mapper namespace level and must be explicitly enabled.

Typical questions and ready answers

Q: What is the difference between #{} and ${} in MyBatis?R: #{} uses prepared statements to prevent SQL injection, while ${} performs direct string substitution. I always use #{} for parameters and ${} only for dynamic metadata like table names.

Q: How do you map a one-to-many relationship?R: I use the <collection> tag within a <resultMap> in the XML file to map the joined query results into a Java List of child objects.

Q: What is the N+1 select problem in MyBatis?R: It happens when fetching a list of entities and then executing a separate query for each entity's relations. I solve it by writing a single SQL join and using nested ResultMaps.

Pitfalls and common mistakes

Using ${} for user-provided data, opening the application to SQL injection attacks.

Forgetting to commit or close a SqlSession when managing connections manually outside of a framework like Spring.

Overusing complex dynamic SQL tags resulting in unreadable XML files instead of splitting logic.

Ignoring the N+1 problem when relying on lazy loading for collections.

Snippets and commands

<!-- UserMapper.xml -->
<mapper namespace="com.example.UserMapper">
    <resultMap id="UserResultMap" type="User">
        <id property="id" column="user_id" />
        <result property="username" column="username" />
        <collection property="roles" ofType="Role">
            <id property="id" column="role_id" />
            <result property="name" column="role_name" />
        </collection>
    </resultMap>

    <select id="getUserWithRoles" resultMap="UserResultMap">
        SELECT u.id as user_id, u.username, r.id as role_id, r.name as role_name
        FROM users u
        LEFT JOIN user_roles ur ON u.id = ur.user_id
        LEFT JOIN roles r ON ur.role_id = r.id
        WHERE u.id = #{id}
    </select>
</mapper>

Java

// UserMapper.java
public interface UserMapper {
    // Linked to the XML mapper
    User getUserWithRoles(@Param("id") Long id);

    // Inline annotation-based query
    @Select("SELECT * FROM users WHERE status = #{status}")
    List<User> getUsersByStatus(@Param("status") String status);
}
Jarvis Interviews
Jarvis