MyBatis动态SQL案例

wen java案例 2026-08-10 173

本文目录导读：

MyBatis动态SQL案例

基础准备
动态SQL完整示例
Mapper接口
使用示例
注意事项和最佳实践

我来给你提供一个完整的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 &gt; #{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 &gt;= #{minAge}
            </if>
            <if test="maxAge != null">
                AND u.age &lt;= #{maxAge}
            </if>
        </where>
        GROUP BY u.id
        HAVING COUNT(DISTINCT r.id) &gt;= #{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的常用场景,你可以根据实际需求进行调整和扩展。

本文地址： https://bmjup.com/post/42458.html