MyBatis: An Introduction to SQL Mapping in Java
Table of Contents
Understanding resultMap in MyBatis
In MyBatis, a resultMap is a powerful feature that defines how SQL query results are mapped to Java objects (typically POJOs). It provides flexibility in handling complex mappings between database columns and Java fields, ensuring proper conversion of data.
The resultMap is particularly useful when column names in the database differ from field names in Java objects or when working with nested objects. It allows fine-grained control over how each field is populated.
Below is an example of a MyBatis XML mapper file.
1<mapper namespace="com.example.mapper.UserMapper">
2
3 <!-- Define the resultMap to map SQL query result to Java object -->
4 <resultMap id="userResultMap" type="com.example.model.User">
5 <id property="id" column="id" />
6 <result property="name" column="name" />
7 <result property="email" column="email" />
8 </resultMap>
9
10 <!-- Query using the resultMap -->
11 <select id="findById" resultMap="userResultMap">
12 SELECT id, name, email FROM users WHERE id = #{id}
13 </select>
14 </mapper>Here, property refers to the field in the Java object where the value will be stored, while column represents the corresponding database column being selected. Note: The resultType or resultMap attribute defines the Java class to which each row of the result set will be mapped.
Using if, choose, and foreach in MyBatis
Using the if Statement in MyBatis
The if element in MyBatis allows conditional SQL generation based on the provided parameters. This is useful when filtering queries dynamically.
1<select id="findActiveBlogLike"
2 resultType="Blog">
3 SELECT * FROM BLOG WHERE state = ‘ACTIVE’
4 <if test="title != null">
5 AND title like #{title}
6 </if>
7 <if test="author != null and author.name != null">
8 AND author_name like #{author.name}
9 </if>
10</select>If there is no predefined WHERE condition, MyBatis provides a way to handle it dynamically using the <where> element.
1<select id="findActiveBlogLike"
2 resultType="Blog">
3 SELECT * FROM BLOG
4 <where>
5 <if test="state != null">
6 state = #{state}
7 </if>
8 <if test="title != null">
9 AND title like #{title}
10 </if>
11 <if test="author != null and author.name != null">
12 AND author_name like #{author.name}
13 </if>
14 </where>
15</select>Using the choose Statement in MyBatis
In certain scenarios, we may need to evaluate multiple conditions but apply only one that matches, similar to a switch statement in Java. MyBatis provides the <choose> element to handle such cases, allowing us to define a series of <when> conditions and a default <otherwise> case. This ensures that only the first matching condition is applied, improving query flexibility and efficiency.
1<select id="findActiveBlogLike"
2 resultType="Blog">
3 SELECT * FROM BLOG WHERE state = ‘ACTIVE’
4 <choose>
5 <when test="title != null">
6 AND title like #{title}
7 </when>
8 <when test="author != null and author.name != null">
9 AND author_name like #{author.name}
10 </when>
11 <otherwise>
12 AND featured = 1
13 </otherwise>
14 </choose>
15</select>Using the foreach Statement in MyBatis
The <foreach> element in MyBatis allows us to iterate over a collection, making it particularly useful for dynamically constructingIN conditions in SQL queries.
1<select id="selectPostIn" resultType="domain.blog.Post">
2 SELECT *
3 FROM POST P
4 <where>
5 <if test="postIdList != null and postIdList.size() > 0">
6 AND post_id IN
7 <foreach collection="postIdList" item="pId" open="(" close=")" separator=",">
8 #{pId}
9 </foreach>
10 </if>
11 </where>
12</select>Within the <foreach> element, you can specify a collection and define item and index variables for use inside the loop. It also allows you to define opening and closing strings, as well as a separator for formatting the output properly. MyBatis ensures that extra separators are not mistakenly appended.
Note: The collection parameter in <foreach> supports any iterable object such as a set or list , as well as arrays and maps. When iterating over an array or iterable,index represents the current loop iteration number, and item holds the element at that position. When using a Map, index refers to the key, while item contains the corresponding value.
Dynamic SQL in MyBatis: Include
The <include> statement in MyBatis allows you to reuse predefined SQL fragments, making queries more modular and easier to maintain. This helps reduce redundancy by defining common SQL conditions once and including them wherever needed.
1<sql id="userFilter">
2 <where>
3 <if test="status != null">
4 AND status = #{status}
5 </if>
6 <if test="createdAfter != null">
7 AND created_at >= #{createdAfter}
8 </if>
9 </where>
10</sql>1<select id="getActiveUsers" resultType="User">
2 SELECT * FROM users
3 <include refid="userFilter"/>
4</select>
5
6<select id="countUsers" resultType="int">
7 SELECT COUNT(*) FROM users
8 <include refid="userFilter"/>
9</select>QueryWrapper and MyBatis-Plus Enhancements
The QueryWrapper is a utility class that helps construct SQL queries by providing a fluent API in adding query conditions. You can add conditions like equals, like, in between, orderby without writing raw SQL.
1@Autowired
2private UserMapper userMapper;
3
4public List<User> getUsers() {
5 QueryWrapper<User> queryWrapper = new QueryWrapper<>();
6 queryWrapper.eq("name", "John")
7 .between("age", 20, 30)
8 .orderByDesc("age");
9
10 return userMapper.selectList(queryWrapper);
11}
12
13public IPage<User> getUsersWithPagination() {
14 QueryWrapper<User> queryWrapper = new QueryWrapper<>();
15 queryWrapper.eq("status", 1);
16
17 Page<User> page = new Page<>(1, 10);
18 return userMapper.selectPage(page, queryWrapper);
19}Writing a .xml Mapper in MyBatis
In MyBatis, you typically define a method in your mapper interface like this:
1public interface ProductMapper {
2 List<Product> queryProducts(Map<String, Object> filters);
3}This method accepts a single Map<String, Object> as a parameter. In the corresponding MyBatis XML mapper file, the keys of this map can be accessed directly using their names via #{} or ${} syntax.
1Map<String, Object> filters = new HashMap<>();
2filters.put("category", "Electronics");
3filters.put("minPrice", 100);
4filters.put("maxPrice", 1000);
5filters.put("brand", "BrandX");
6filters.put("sortBy", "price");
7filters.put("order", "ASC");
8filters.put("limit", 10);
9filters.put("offset", 0);1<mapper namespace="com.example.mapper.ProductMapper">
2
3 <!-- Reusable SQL fragment for dynamic filtering -->
4 <sql id="productFilter">
5 <where>
6 <if test="category != null">
7 AND category = #{category}
8 </if>
9 <if test="minPrice != null">
10 AND price >= #{minPrice}
11 </if>
12 <if test="maxPrice != null">
13 AND price <= #{maxPrice}
14 </if>
15 <if test="brand != null">
16 AND brand = #{brand}
17 </if>
18 </where>
19 </sql>
20
21 <!-- Main SELECT query with dynamic conditions and pagination -->
22 <select id="queryProducts" resultType="Product">
23 SELECT * FROM products
24 <include refid="productFilter"/>
25 ORDER BY
26 <choose>
27 <when test="sortBy != null and order != null">
28 ${sortBy} ${order}
29 </when>
30 <otherwise>
31 created_at DESC
32 </otherwise>
33 </choose>
34 LIMIT #{limit} OFFSET #{offset}
35 </select>
36
37</mapper>Key Notes:
- MyBatis treats keys in a Map as named parameters, accessible directly by their key names.
- Use #{} for safe parameter binding, which helps prevent SQL injection.
- ${} is used for dynamic SQL parts such as column names, table names, or order by clauses that cannot be parameterized. It must be used cautiously, as it performs raw string substitution at runtime.
- Using <if> conditions allows for clean, conditional query construction, avoiding redundant clauses.
- <sql> and <include> promote reusability and reduce duplication in XML mapper files.