单表查询操作

单表查询是最基础的数据库操作，下面梳理常见查询模式。

按 ID 查询

XML

<select id="selectById" resultType="com.example.entity.User">
    SELECT id, username, email, create_time 
    FROM user WHERE id = #{id}
</select>

Java

User user = mapper.selectById(1);
if (user == null) {
    // 记录不存在
}

条件查询

XML

<select id="selectByCondition" resultType="com.example.entity.User">
    SELECT id, username, email, create_time FROM user
    <where>
        <if test="username != null and username != ''">
            AND username LIKE CONCAT('%', #{username}, '%')
        </if>
        <if test="email != null and email != ''">
            AND email = #{email}
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
    </where>
    ORDER BY create_time DESC
</select>

分页查询

XML

<select id="selectByPage" resultType="com.example.entity.User">
    SELECT id, username, email FROM user
    ORDER BY id DESC
    LIMIT #{offset}, #{pageSize}
</select>

Java

// 计算偏移量
int pageNum = 2;      // 第 2 页
int pageSize = 10;    // 每页 10 条
int offset = (pageNum - 1) * pageSize;

List<User> users = mapper.selectByPage(offset, pageSize);

注意：LIMIT 参数为偏移量和每页数量，推荐封装分页对象传递参数。

模糊查询

XML

<select id="searchUsers" resultType="com.example.entity.User">
    SELECT * FROM user
    <where>
        <if test="keyword != null and keyword != ''">
            AND (username LIKE CONCAT('%', #{keyword}, '%')
                 OR email LIKE CONCAT('%', #{keyword}, '%'))
        </if>
    </where>
</select>

排序查询

XML

<select id="selectOrderByCreateTime" resultType="com.example.entity.User">
    SELECT * FROM user
    ORDER BY 
    <choose>
        <when test="orderBy == 'create_time'">create_time</when>
        <when test="orderBy == 'username'">username</when>
        <otherwise>id</otherwise>
    </choose>
    ${sortDirection}
</select>

注意：排序字段使用 ${} 动态拼接时需防 SQL 注入，推荐白名单校验。

要点总结

按 ID 查询最简单，直接 resultType 映射
条件查询使用 where 和 if 标签动态拼接条件
分页查询使用 LIMIT offset, pageSize
排序字段动态拼接需防 SQL 注入