跳到主要内容

注解开发

除了 XML 映射文件，MyBatis 还支持使用注解来定义 SQL 语句。注解方式更加简洁，适合简单的 CRUD 操作。

注解 vs XML​

特性注解XML
简洁性高中
可读性简单SQL好复杂SQL好
维护性SQL分散SQL集中
动态SQL支持（Script）支持（更好）
适用场景简单CRUD复杂查询

基础注解​

@Select​

用于定义查询语句：

public interface UserMapper {
    
    @Select("SELECT * FROM user WHERE id = #{id}")
    User selectById(Long id);
    
    @Select("SELECT * FROM user ORDER BY id DESC")
    List<User> selectAll();
    
    @Select("SELECT COUNT(*) FROM user")
    long count();
    
    @Select("SELECT * FROM user WHERE username = #{username} AND status = #{status}")
    User selectByUsernameAndStatus(@Param("username") String username, 
                                    @Param("status") Integer status);
}

@Insert​

用于定义插入语句：

public interface UserMapper {
    
    @Insert("INSERT INTO user (username, password, email, phone) " +
            "VALUES (#{username}, #{password}, #{email}, #{phone})")
    int insert(User user);
    
    @Insert("INSERT INTO user (username, password) VALUES (#{username}, #{password})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertWithKey(User user);
}

@Update​

用于定义更新语句：

public interface UserMapper {
    
    @Update("UPDATE user SET username = #{username}, email = #{email} WHERE id = #{id}")
    int update(User user);
    
    @Update("UPDATE user SET status = #{status} WHERE id = #{id}")
    int updateStatus(@Param("id") Long id, @Param("status") Integer status);
}

@Delete​

用于定义删除语句：

public interface UserMapper {
    
    @Delete("DELETE FROM user WHERE id = #{id}")
    int deleteById(Long id);
    
    @Delete("DELETE FROM user WHERE status = #{status}")
    int deleteByStatus(@Param("status") Integer status);
}

参数注解​

@Param​

用于给参数命名，在 SQL 中通过名称引用：

public interface UserMapper {
    
    // 单个参数不需要 @Param
    @Select("SELECT * FROM user WHERE id = #{id}")
    User selectById(Long id);
    
    // 多个参数需要 @Param
    @Select("SELECT * FROM user WHERE username = #{username} AND email = #{email}")
    User selectByUsernameAndEmail(@Param("username") String username,
                                   @Param("email") String email);
    
    // 对象参数不需要 @Param
    @Insert("INSERT INTO user (username, password, email) VALUES (#{username}, #{password}, #{email})")
    int insert(User user);
    
    // 混合使用
    @Select("SELECT * FROM user WHERE status = #{status} AND username LIKE #{keyword}")
    List<User> search(@Param("status") Integer status, @Param("keyword") String keyword);
}

参数传递规则​

参数类型是否需要 @ParamSQL 中引用方式
单个基本类型不需要#{任意名称}
多个基本类型需要#{参数名}
对象类型不需要#{属性名}
Map 类型不需要#{key}
List/数组不需要#{list[0]} 或 #{array[0]}

结果映射注解​

@Results​

定义结果映射，类似于 XML 中的 resultMap：

public interface UserMapper {
    
    @Results(id = "userResultMap", value = {
        @Result(column = "id", property = "id", id = true),
        @Result(column = "username", property = "username"),
        @Result(column = "password", property = "password"),
        @Result(column = "email", property = "email"),
        @Result(column = "phone", property = "phone"),
        @Result(column = "create_time", property = "createTime"),
        @Result(column = "update_time", property = "updateTime")
    })
    @Select("SELECT * FROM user WHERE id = #{id}")
    User selectById(Long id);
}

@ResultMap​

引用已定义的结果映射：

public interface UserMapper {
    
    @Results(id = "userResultMap", value = {
        @Result(column = "id", property = "id", id = true),
        @Result(column = "username", property = "username"),
        @Result(column = "create_time", property = "createTime")
    })
    @Select("SELECT * FROM user WHERE id = #{id}")
    User selectById(Long id);
    
    // 引用上面的结果映射
    @ResultMap("userResultMap")
    @Select("SELECT * FROM user WHERE username = #{username}")
    User selectByUsername(String username);
}

@One 一对一关联​

public interface OrderMapper {
    
    @Results(id = "orderResultMap", value = {
        @Result(column = "id", property = "id", id = true),
        @Result(column = "order_no", property = "orderNo"),
        @Result(column = "user_id", property = "userId"),
        @Result(column = "user_id", property = "user",
                one = @One(select = "com.example.mybatis.mapper.UserMapper.selectById"))
    })
    @Select("SELECT * FROM orders WHERE id = #{id}")
    Order selectById(Long id);
}

@Many 一对多关联​

public interface UserMapper {
    
    @Results(id = "userWithOrdersMap", value = {
        @Result(column = "id", property = "id", id = true),
        @Result(column = "username", property = "username"),
        @Result(column = "id", property = "orders",
                many = @Many(select = "com.example.mybatis.mapper.OrderMapper.selectByUserId"))
    })
    @Select("SELECT * FROM user WHERE id = #{id}")
    User selectWithOrders(Long id);
}

动态 SQL 注解​

@SelectProvider​

使用 Provider 类动态生成 SQL：

public class UserSqlProvider {
    
    public String selectByCondition(UserQuery query) {
        return new SQL() {{
            SELECT("*");
            FROM("user");
            if (query.getUsername() != null) {
                WHERE("username LIKE CONCAT('%', #{username}, '%')");
            }
            if (query.getEmail() != null) {
                WHERE("email = #{email}");
            }
            if (query.getStatus() != null) {
                WHERE("status = #{status}");
            }
            ORDER_BY("id DESC");
        }}.toString();
    }
    
    public String updateSelective(User user) {
        return new SQL() {{
            UPDATE("user");
            if (user.getUsername() != null) {
                SET("username = #{username}");
            }
            if (user.getEmail() != null) {
                SET("email = #{email}");
            }
            if (user.getPhone() != null) {
                SET("phone = #{phone}");
            }
            WHERE("id = #{id}");
        }}.toString();
    }
}

public interface UserMapper {
    
    @SelectProvider(type = UserSqlProvider.class, method = "selectByCondition")
    List<User> selectByCondition(UserQuery query);
    
    @UpdateProvider(type = UserSqlProvider.class, method = "updateSelective")
    int updateSelective(User user);
}

SQL 工具类​

MyBatis 提供了 SQL 类来构建动态 SQL：

public class UserSqlProvider {
    
    // SELECT 语句
    public String selectById() {
        return new SQL() {{
            SELECT("id, username, email, phone");
            FROM("user");
            WHERE("id = #{id}");
        }}.toString();
    }
    
    // INSERT 语句
    public String insert() {
        return new SQL() {{
            INSERT_INTO("user");
            VALUES("username", "#{username}");
            VALUES("password", "#{password}");
            VALUES("email", "#{email}");
        }}.toString();
    }
    
    // UPDATE 语句
    public String update() {
        return new SQL() {{
            UPDATE("user");
            SET("username = #{username}");
            SET("email = #{email}");
            WHERE("id = #{id}");
        }}.toString();
    }
    
    // DELETE 语句
    public String deleteById() {
        return new SQL() {{
            DELETE_FROM("user");
            WHERE("id = #{id}");
        }}.toString();
    }
    
    // 多表 JOIN
    public String selectUserWithOrders() {
        return new SQL() {{
            SELECT("u.*, o.id as order_id, o.order_no");
            FROM("user u");
            LEFT_OUTER_JOIN("orders o ON u.id = o.user_id");
            WHERE("u.id = #{id}");
        }}.toString();
    }
}

SQL 类方法​

方法说明
SELECT(String... columns)SELECT 子句
FROM(String table)FROM 子句
JOIN(String join)INNER JOIN
LEFT_OUTER_JOIN(String join)LEFT JOIN
RIGHT_OUTER_JOIN(String join)RIGHT JOIN
WHERE(String... conditions)WHERE 子句
OR()OR 条件
AND()AND 条件
GROUP_BY(String... columns)GROUP BY 子句
HAVING(String... conditions)HAVING 子句
ORDER_BY(String... columns)ORDER BY 子句
INSERT_INTO(String table)INSERT 语句
VALUES(String column, String value)VALUES 子句
UPDATE(String table)UPDATE 语句
SET(String... sets)SET 子句
DELETE_FROM(String table)DELETE 语句

其他 Provider 注解​

public interface UserMapper {
    
    @InsertProvider(type = UserSqlProvider.class, method = "insert")
    int insert(User user);
    
    @UpdateProvider(type = UserSqlProvider.class, method = "update")
    int update(User user);
    
    @DeleteProvider(type = UserSqlProvider.class, method = "deleteById")
    int deleteById(Long id);
}

@Options 注解​

用于设置语句的额外选项：

public interface UserMapper {
    
    // 主键回填
    @Insert("INSERT INTO user (username, password) VALUES (#{username}, #{password})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertWithKey(User user);
    
    // 超时设置
    @Select("SELECT * FROM user")
    @Options(timeout = 30)
    List<User> selectAll();
    
    // 缓存设置
    @Select("SELECT * FROM user WHERE id = #{id}")
    @Options(useCache = true, flushCache = Options.FlushCachePolicy.FALSE)
    User selectById(Long id);
}

@Options 属性​

属性说明
useGeneratedKeys使用自动生成的主键
keyProperty主键属性名
keyColumn主键列名
timeout超时时间（秒）
flushCache刷新缓存策略
useCache是否使用缓存
fetchSize获取数量
statementType语句类型

@CacheNamespace​

开启二级缓存：

@CacheNamespace(
    implementation = LruCache.class,
    eviction = LruCache.class,
    flushInterval = 60000,
    size = 1024,
    readWrite = true
)
public interface UserMapper {
    
    @Select("SELECT * FROM user WHERE id = #{id}")
    User selectById(Long id);
}

使用 script 标签​

在注解中可以使用 <script> 标签编写动态 SQL：

public interface UserMapper {
    
    @Select("<script>" +
            "SELECT * FROM user" +
            "<where>" +
            "  <if test='username != null'> AND username = #{username}</if>" +
            "  <if test='email != null'> AND email = #{email}</if>" +
            "  <if test='status != null'> AND status = #{status}</if>" +
            "</where>" +
            "</script>")
    List<User> selectByCondition(UserQuery query);
    
    @Update("<script>" +
            "UPDATE user" +
            "<set>" +
            "  <if test='username != null'>username = #{username},</if>" +
            "  <if test='email != null'>email = #{email},</if>" +
            "  <if test='phone != null'>phone = #{phone},</if>" +
            "</set>" +
            "WHERE id = #{id}" +
            "</script>")
    int updateSelective(User user);
    
    @Select("<script>" +
            "SELECT * FROM user WHERE id IN" +
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "  #{id}" +
            "</foreach>" +
            "</script>")
    List<User> selectByIds(@Param("ids") List<Long> ids);
}

混合使用​

注解和 XML 可以混合使用：

// 简单 SQL 使用注解
public interface UserMapper {
    
    @Select("SELECT * FROM user WHERE id = #{id}")
    User selectById(Long id);
    
    @Insert("INSERT INTO user (username, password) VALUES (#{username}, #{password})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(User user);
    
    // 复杂 SQL 使用 XML（在 UserMapper.xml 中定义）
    List<User> selectByCondition(UserQuery query);
    
    int updateSelective(User user);
}
<!-- UserMapper.xml -->
<mapper namespace="com.example.mybatis.mapper.UserMapper">
    
    <select id="selectByCondition" resultType="User">
        SELECT * FROM user
        <where>
            <if test="username != null and username != ''">
                AND username LIKE CONCAT('%', #{username}, '%')
            </if>
            <if test="email != null">
                AND email = #{email}
            </if>
            <if test="status != null">
                AND status = #{status}
            </if>
        </where>
        ORDER BY id DESC
    </select>
    
    <update id="updateSelective">
        UPDATE user
        <set>
            <if test="username != null">username = #{username},</if>
            <if test="email != null">email = #{email},</if>
            <if test="phone != null">phone = #{phone},</if>
        </set>
        WHERE id = #{id}
    </update>
    
</mapper>

完整示例​

@CacheNamespace
public interface UserMapper {
    
    // ========== 查询 ==========
    
    @Results(id = "userResultMap", value = {
        @Result(column = "id", property = "id", id = true),
        @Result(column = "username", property = "username"),
        @Result(column = "password", property = "password"),
        @Result(column = "email", property = "email"),
        @Result(column = "phone", property = "phone"),
        @Result(column = "status", property = "status"),
        @Result(column = "create_time", property = "createTime"),
        @Result(column = "update_time", property = "updateTime")
    })
    @Select("SELECT * FROM user WHERE id = #{id}")
    User selectById(Long id);
    
    @ResultMap("userResultMap")
    @Select("SELECT * FROM user ORDER BY id DESC")
    List<User> selectAll();
    
    @ResultMap("userResultMap")
    @Select("SELECT * FROM user WHERE username = #{username}")
    User selectByUsername(String username);
    
    @Select("<script>" +
            "SELECT * FROM user" +
            "<where>" +
            "  <if test='username != null'> AND username LIKE CONCAT('%', #{username}, '%')</if>" +
            "  <if test='email != null'> AND email = #{email}</if>" +
            "  <if test='status != null'> AND status = #{status}</if>" +
            "</where>" +
            "ORDER BY id DESC" +
            "</script>")
    List<User> selectByCondition(UserQuery query);
    
    // ========== 插入 ==========
    
    @Insert("INSERT INTO user (username, password, email, phone, status) " +
            "VALUES (#{username}, #{password}, #{email}, #{phone}, #{status})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(User user);
    
    @Insert("<script>" +
            "INSERT INTO user (username, password, email, phone, status) VALUES " +
            "<foreach collection='list' item='user' separator=','>" +
            "  (#{user.username}, #{user.password}, #{user.email}, #{user.phone}, #{user.status})" +
            "</foreach>" +
            "</script>")
    int insertBatch(List<User> users);
    
    // ========== 更新 ==========
    
    @Update("UPDATE user SET username = #{username}, email = #{email}, phone = #{phone} WHERE id = #{id}")
    int update(User user);
    
    @Update("<script>" +
            "UPDATE user" +
            "<set>" +
            "  <if test='username != null'>username = #{username},</if>" +
            "  <if test='email != null'>email = #{email},</if>" +
            "  <if test='phone != null'>phone = #{phone},</if>" +
            "  <if test='status != null'>status = #{status},</if>" +
            "</set>" +
            "WHERE id = #{id}" +
            "</script>")
    int updateSelective(User user);
    
    // ========== 删除 ==========
    
    @Delete("DELETE FROM user WHERE id = #{id}")
    int deleteById(Long id);
    
    @Delete("<script>" +
            "DELETE FROM user WHERE id IN " +
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "  #{id}" +
            "</foreach>" +
            "</script>")
    int deleteByIds(@Param("ids") List<Long> ids);
    
    // ========== 关联查询 ==========
    
    @Results(id = "userWithOrdersMap", value = {
        @Result(column = "id", property = "id", id = true),
        @Result(column = "username", property = "username"),
        @Result(column = "id", property = "orders",
                many = @Many(select = "com.example.mybatis.mapper.OrderMapper.selectByUserId"))
    })
    @Select("SELECT * FROM user WHERE id = #{id}")
    User selectWithOrders(Long id);
}

小结​

本章介绍了 MyBatis 注解开发方式：

  • 基础注解：@Select、@Insert、@Update、@Delete
  • 参数注解：@Param
  • 结果映射注解：@Results、@ResultMap、@One、@Many
  • 动态 SQL 注解：@SelectProvider、@InsertProvider、@UpdateProvider、@DeleteProvider
  • 其他注解：@Options、@CacheNamespace

注解方式适合简单 SQL，复杂 SQL 推荐使用 XML 方式或混合使用。

下一章将详细介绍动态 SQL。