MyBatis Mapper — Cheat Sheet

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

Level: intermediate

TL;DR: MyBatis mappers bind Java interface methods directly to SQL statements via XML or annotations, giving full control over query execution and result mapping.

Key concepts

A mapper is a Java interface mapped to SQL statements without implementation classes.

Mapping occurs either through XML files or Java annotations (@Select, @Insert, @Update, @Delete).

XML namespace must match the fully qualified name of the Java mapper interface.

XML statement IDs correspond directly to the method names declared in the mapper interface.

#\{param\} creates a parameterized prepared statement, preventing SQL injection vulnerabilities.

$\{param\} performs direct string substitution, suitable only for table names or column names.

resultMap handles complex property-to-column mappings, associations, and collection relationships.

Dynamic SQL tags like <if>, <choose>, <where>, and <foreach> build conditional queries cleanly.

SqlSession manages the mapper execution lifecycle and transactional boundaries in standard environments.

Spring Boot integrates mappers automatically via @Mapper or @MapperScan.

Typical questions and ready answers

Q: What is the difference between #{param} and ${param} in MyBatis mapper definitions?R: I use #{} for values because it binds parameters via JDBC PreparedStatement, escaping inputs and stopping SQL injection. I reserve ${} strictly for structural elements like dynamic table or column names, ensuring input is strictly validated beforehand.

Q: How do you map one-to-many associations in a MyBatis XML mapper?R: I declare a <resultMap> containing a <collection> tag specifying the nested ofType or resultMap. I either join tables directly in a single SQL query or configure a sub-query using the select attribute.

Q: When would you choose XML mappers over Java annotations?R: I prefer XML mappers when queries involve dynamic SQL tags, nested collections, or extensive result maps, because XML keeps complex SQL readable and decoupled from Java code.

Pitfalls and common mistakes

Mismatch between the XML namespace and the interface fully qualified class name.

Using ${} with user-supplied input, leading to SQL injection.

Returning primitive types on queries that might return null, throwing exceptions.

Forgetting the <where> tag and ending up with invalid SQL syntax like WHERE AND.

Missing @Param annotations when passing multiple primitive parameters without a wrapper object.

Snippets and commands

@Mapper
public interface UserMapper {
    User findById(@Param("id") Long id);
    List<User> findActiveUsers(@Param("status") String status);
    int insertUser(User user);
}

XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.UserMapper">

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

    <select id="findById" resultMap="UserResultMap">
        SELECT user_id, username, email
        FROM users
        WHERE user_id = #{id}
    </select>

    <select id="findActiveUsers" resultMap="UserResultMap">
        SELECT user_id, username, email
        FROM users
        <where>
            <if test="status != null">
                AND status = #{status}
            </if>
        </where>
    </select>
</mapper>

To review

[ ] Complex result mapping with <association> and <collection>

[ ] Dynamic SQL tag usage: <set>, <trim>, <foreach>

[ ] Configuration of local (first-level) and second-level cache mechanics

Jarvis Interviews
Jarvis