本文目录导读：
我来给你提供一个完整的MyBatis动态SQL案例,涵盖各种常用场景。
基础准备
实体类
public class User {
private Long id;
private String username;
private String email;
private Integer age;
private String status;
private List<Long> roleIds;
// getter/setter 省略
}
数据库表
CREATE TABLE `user` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL, `email` varchar(100) DEFAULT NULL, `age` int(11) DEFAULT NULL, `status` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
动态SQL完整示例
UserMapper.xml 中的动态SQL示例
<?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">
<!-- ==================== 1. IF 条件判断 ==================== -->
<select id="selectByCondition" parameterType="map" resultType="User">
SELECT * FROM user
WHERE 1 = 1
<if test="username != null and username != ''">
AND username LIKE CONCAT('%', #{username}, '%')
</if>
<if test="age != null">
AND age = #{age}
</if>
<if test="status != null and status != ''">
AND status = #{status}
</if>
</select>
<!-- ==================== 2. WHERE 动态条件 ==================== -->
<select id="selectWithWhere" parameterType="User" resultType="User">
SELECT * FROM user
<where>
<if test="username != null and username != ''">
AND username = #{username}
</if>
<if test="email != null and email != ''">
AND email = #{email}
</if>
<if test="age != null">
AND age > #{age}
</if>
</where>
</select>
<!-- ==================== 3. SET 动态更新 ==================== -->
<update id="updateById" parameterType="User">
UPDATE user
<set>
<if test="username != null and username != ''">
username = #{username},
</if>
<if test="email != null and email != ''">
email = #{email},
</if>
<if test="age != null">
age = #{age},
</if>
</set>
WHERE id = #{id}
</update>
<!-- ==================== 4. CHOOSE/WHEN/OTHERWISE 多分支选择 ==================== -->
<select id="selectWithChoose" parameterType="User" resultType="User">
SELECT * FROM user
<where>
<choose>
<when test="username != null and username != ''">
username = #{username}
</when>
<when test="email != null and email != ''">
email = #{email}
</when>
<otherwise>
status = 'ACTIVE'
</otherwise>
</choose>
</where>
</select>
<!-- ==================== 5. FOREACH 批量操作 ==================== -->
<!-- 5.1 批量插入 -->
<insert id="batchInsert" parameterType="list">
INSERT INTO user (username, email, age, status)
VALUES
<foreach collection="list" item="user" separator=",">
(#{user.username}, #{user.email}, #{user.age}, #{user.status})
</foreach>
</insert>
<!-- 5.2 批量查询（IN 条件） -->
<select id="selectByIds" resultType="User">
SELECT * FROM user
WHERE id IN
<foreach collection="ids" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</select>
<!-- 5.3 批量删除 -->
<delete id="batchDelete" parameterType="list">
DELETE FROM user
WHERE id IN
<foreach collection="ids" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
<!-- 5.4 FOR 循环批量更新（使用CASE WHEN） -->
<update id="batchUpdate">
UPDATE user
<set>
<trim prefix="age = CASE id" suffix="END">
<foreach collection="list" item="user">
<if test="user.age != null">
WHEN #{user.id} THEN #{user.age}
</if>
</foreach>
</trim>
</set>
WHERE id IN
<foreach collection="list" item="user" open="(" separator="," close=")">
#{user.id}
</foreach>
</update>
<!-- ==================== 6. TRIM 动态拼接 ==================== -->
<!-- 6.1 TRIM 替代 WHERE -->
<select id="selectWithTrim" parameterType="User" resultType="User">
SELECT * FROM user
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="username != null and username != ''">
AND username = #{username}
</if>
<if test="email != null and email != ''">
AND email = #{email}
</if>
<if test="age != null">
AND age = #{age}
</if>
</trim>
</select>
<!-- 6.2 TRIM 替代 SET -->
<update id="updateWithTrim" parameterType="User">
UPDATE user
<trim prefix="SET" suffixOverrides=",">
<if test="username != null and username != ''">
username = #{username},
</if>
<if test="email != null and email != ''">
email = #{email},
</if>
<if test="age != null">
age = #{age},
</if>
</trim>
WHERE id = #{id}
</update>
<!-- ==================== 7. 动态SQL组合使用 ==================== -->
<select id="selectComplex" parameterType="map" resultType="User">
SELECT u.*, r.role_name
FROM user u
LEFT JOIN user_role ur ON u.id = ur.user_id
LEFT JOIN role r ON ur.role_id = r.id
<where>
<if test="user.username != null and user.username != ''">
AND u.username LIKE CONCAT('%', #{user.username}, '%')
</if>
<if test="user.email != null and user.email != ''">
AND u.email = #{user.email}
</if>
<if test="roleIds != null and roleIds.size() > 0">
AND r.id IN
<foreach collection="roleIds" item="roleId" open="(" separator="," close=")">
#{roleId}
</foreach>
</if>
<if test="minAge != null">
AND u.age >= #{minAge}
</if>
<if test="maxAge != null">
AND u.age <= #{maxAge}
</if>
</where>
GROUP BY u.id
HAVING COUNT(DISTINCT r.id) >= #{minRoleCount}
ORDER BY u.created_time DESC
LIMIT #{offset}, #{limit}
</select>
<!-- ==================== 8. 动态SQL配合分页 ==================== -->
<select id="selectByPage" resultType="User">
SELECT * FROM user
<where>
<if test="condition.username != null and condition.username != ''">
AND username LIKE CONCAT('%', #{condition.username}, '%')
</if>
<if test="condition.status != null and condition.status != ''">
AND status = #{condition.status}
</if>
</where>
ORDER BY id DESC
</select>
<!-- ==================== 9. 动态SQL配合多表关联 ==================== -->
<select id="selectUserWithRoles" resultMap="userRoleMap">
SELECT u.*, r.id as role_id, r.role_name, r.description
FROM user u
<choose>
<when test="includeRoles != null and includeRoles">
LEFT JOIN user_role ur ON u.id = ur.user_id
LEFT JOIN role r ON ur.role_id = r.id
</when>
<otherwise>
LEFT JOIN user_role ur ON u.id = ur.user_id
LEFT JOIN role r ON ur.role_id = r.id
</otherwise>
</choose>
<where>
<if test="userId != null">
AND u.id = #{userId}
</if>
</where>
</select>
</mapper>
Mapper接口
public interface UserMapper {
// 条件查询（多条件可选）
List<User> selectByCondition(Map<String, Object> params);
// WHERE动态条件
List<User> selectWithWhere(User user);
// 动态更新
int updateById(User user);
// 多分支选择
List<User> selectWithChoose(User user);
// 批量插入
int batchInsert(List<User> users);
// 批量查询
List<User> selectByIds(List<Long> ids);
// 批量删除
int batchDelete(List<Long> ids);
// 批量更新
int batchUpdate(List<User> users);
// TRIM动态条件
List<User> selectWithTrim(User user);
// 复杂查询
List<User> selectComplex(Map<String, Object> params);
// 分页查询
List<User> selectByPage(@Param("condition") User condition,
@Param("offset") int offset,
@Param("limit") int limit);
}
使用示例
@SpringBootTest
public class UserMapperTest {
@Autowired
private UserMapper userMapper;
@Test
public void testDynamicSql() {
// 1. 条件查询
Map<String, Object> params = new HashMap<>();
params.put("username", "张");
params.put("age", 25);
List<User> users = userMapper.selectByCondition(params);
// 2. 批量插入
List<User> userList = Arrays.asList(
new User(null, "张三", "zhangsan@example.com", 25, "ACTIVE"),
new User(null, "李四", "lisi@example.com", 30, "ACTIVE")
);
userMapper.batchInsert(userList);
// 3. 批量查询
List<Long> ids = Arrays.asList(1L, 2L, 3L);
List<User> usersByIds = userMapper.selectByIds(ids);
// 4. 动态更新
User updateUser = new User();
updateUser.setId(1L);
updateUser.setEmail("newemail@example.com"); // 只更新email字段
int rows = userMapper.updateById(updateUser);
// 5. 复杂查询
Map<String, Object> complexParams = new HashMap<>();
User condition = new User();
condition.setUsername("张");
complexParams.put("user", condition);
complexParams.put("roleIds", Arrays.asList(1L, 2L));
complexParams.put("minAge", 20);
complexParams.put("maxAge", 40);
complexParams.put("minRoleCount", 1);
complexParams.put("offset", 0);
complexParams.put("limit", 10);
List<User> complexResult = userMapper.selectComplex(complexParams);
}
}
注意事项和最佳实践
动态SQL使用注意事项：
- NULL值判断：使用
<if>标签时，注意对 NULL 和空字符串的判断 - SQL注入防护：使用 而不是 拼接参数
- 性能优化：复杂动态SQL可能需要使用索引优化
- 代码复用：可以使用
<sql>标签提取公共SQL片段
公共SQL片段示例：
<sql id="userColumns">
id, username, email, age, status
</sql>
<sql id="baseSelect">
SELECT <include refid="userColumns"/> FROM user
</sql>
<select id="selectById" resultType="User">
<include refid="baseSelect"/>
WHERE id = #{id}
</select>
这个完整案例涵盖了MyBatis动态SQL的常用场景,你可以根据实际需求进行调整和扩展。
