Mybatis-基于注解开发基础介绍

注解开发

本文只讲解基础注解开发方式，如果想查看全特性请参考映射器注解

注解和XML标签对应关系

注解	作用对象	XML标签	说明
@CacheNamespace	类	<cache>	为给定的命名空间（比如类）配置缓存
@Insert	方法	<insert>	value：标签内SQL
@Update	方法	<update>	value：标签内SQL
@Delete	方法	<delete>	value：标签内SQL
@Select	方法	<select>	value：标签内SQL
@Results	方法	<resultMap>	value:是一个 Result 注解的数组 id :是结果映射的名称
@Result	@Results	<result>|<id>	id：Boolean，是否是<id>标签 column：SQL column one：@One注解 many：@Many注解
@One	@Results	<association>	select：statementId，参考xml的select属性 fetchType：指定懒加载方式
@Many	@Results	<collection>	select：statementId，参考xml的select属性 fetchType：指定懒加载方式
@ResultMap	方法	N/A	1. 指向XML中<resultMap>的id，复用XML配置 2. 指向@Results的id 存在 @Results 或者 @ConstructorArgs 注解，这两个注解将被此注解覆盖
@Options	方法	指定SQL执行属性	useCache=true flushCache=FlushCachePolicy.DEFAULT resultSetType=DEFAULT statementType=PREPARED fetchSize=-1 timeout=-1 useGeneratedKeys=false keyProperty=""

CURD

public interface AnnotationUserDao {

    @Insert("insert into user values (#{id},#{username},#{password},#{birthday});")
    int add(User user);
    // 执行SQL：insert into user values (?,?,?,?);

    @Delete("delete from user where id=#{id};")
    int remove(Integer id);
    // 执行SQL：delete from user where id=?;

    // 此处迁移XMLSQL，保留动态SQL特性
    @Update("<script>update user\n" +
            "        <set>\n" +
            "            <if test=\"username !=null\">username = #{username},</if>\n" +
            "            <if test=\"password !=null\">password = #{password},</if>\n" +
            "            <if test=\"birthday !=null\">birthday = #{birthday},</if>\n" +
            "        </set>\n" +
            "        where id =#{id};" +
            "</script>")
    int update(User user);
    // 执行SQL：update user set password = ? where id = ?;

    @Select("select * from user where id=#{id};")
    User getById(Integer id);
    // 执行SQL：select * from user where id=？;
    // 输出 User{id=5, username='testAnno', password='23d5', birthday='null'}
}

一对一

public interface AnnotationUserDao {
    @Select("select * from user where id=#{id}")
    User getById(Integer id);
}

public interface AnnotationOrderDao {
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "ordertime", column = "ordertime"),
            @Result(property = "total", column = "total"),
            @Result(property = "user", column = "id", javaType = User.class, one = @One(select = "com.wjy.dao.AnnotationUserDao.getById"))
    })
    @Select("select * from orders ")
    List<Order> list();
}

以上代码 调用 AnnotationOrderDao.list()按照以下步骤执行

1. 执行SQL：select * from orders; 1次
2. 执行SQL：select * from user where id=?; 1次

返回结果：

order{id=1, ordertime='2019-12-12', total=3000, user=User{id=1, username='lucy', password='1234', birthday='2019-12-12'}}
order{id=2, ordertime='2019-12-12', total=4000, user=User{id=2, username='tom', password='123', birthday='2019-12-12'}}

一对多

public interface AnnotationRoleDao {

    @Select("SELECT r.* FROM sys_role r INNER JOIN sys_user_role ur ON ur.roleid=r.id WHERE ur.userid=#{id};")
    List<Role> getByUserId(Integer id);
}

public interface AnnotationOrderDao {

    @Select("select * from orders where uid = #{id}")
    List<Order> getByUserId(Integer id);

}

public interface AnnotationUserDao {
    @Results({
            @Result(property = "id", column = "id", id = true),
            @Result(property = "username", column = "username"),
            @Result(property = "password", column = "password"),
            @Result(property = "birthday", column = "birthday"),
            @Result(property = "orders", column = "id", many = @Many(select = "com.wjy.dao.AnnotationOrderDao.getByUserId")),
            @Result(property = "roles", column = "id", many = @Many(select = "com.wjy.dao.AnnotationRoleDao.getByUserId"))
    })
    @Select("select * from user")
    List<User> list();
}

以上代码 调用 AnnotationUserDao.list()按照以下步骤执行

1. 执行SQL：select * from user; 1次
2. 执行SQL：select * from orders where uid = ?; 上一句SQL有几条结果，执行几次
3. 执行SQL：SELECT r.* FROM sys_role r INNER JOIN sys_user_role ur ON ur.roleid=r.id WHERE ur.userid=?; 第一条SQL有几条结果，执行几次

返回结果：

User{id=1, username='lucy', password='1234', birthday='2019-12-12', orders=[order{id=1, ordertime='2019-12-12', total=3000, user=null}, order{id=2, ordertime='2019-12-12', total=4000, user=null}], roles=[Role{id=1, rolename='CTO', roleDesc='CTO', users=null}, Role{id=2, rolename='CEO', roleDesc='CEO', users=null}]}
User{id=2, username='tom', password='123', birthday='2019-12-12', orders=[order{id=3, ordertime='2019-12-12', total=5000, user=null}, order{id=4, ordertime='2019-12-13', total=5001, user=null}], roles=[Role{id=1, rolename='CTO', roleDesc='CTO', users=null}, Role{id=2, rolename='CEO', roleDesc='CEO', users=null}]}

注解动态SQL

• 迁移XMLSQL使用<script></script>标签

    @Update("<script>update user\n" +
            "        <set>\n" +
            "            <if test=\"username !=null\">username = #{username},</if>\n" +
            "            <if test=\"password !=null\">password = #{password},</if>\n" +
            "            <if test=\"birthday !=null\">birthday = #{birthday},</if>\n" +
            "        </set>\n" +
            "        where id =#{id}" +
            "</script>")
    int update(User user);

• 使用动态SQL注解 @InsertProvider, @UpdateProvider, @DeleteProvider, @SelectProvider

@SelectProvider(type = UserSqlBuilder.class, method = "buildGetUsersByName")
List<User> getUsersByName(
    @Param("name") String name, @Param("orderByColumn") String orderByColumn);

class UserSqlBuilder {

  // 如果不使用 @Param，就应该定义与 mapper 方法相同的参数
  public static String buildGetUsersByName(
      final String name, final String orderByColumn) {
    return new SQL(){{
      SELECT("*");
      FROM("users");
      WHERE("name like #{name} || '%'");
      ORDER_BY(orderByColumn);
    }}.toString();
  }

  // 如果使用 @Param，就可以只定义需要使用的参数
  public static String buildGetUsersByName(@Param("orderByColumn") final String orderByColumn) {
    return new SQL(){{
      SELECT("*");
      FROM("users");
      WHERE("name like #{name} || '%'");
      ORDER_BY(orderByColumn);
    }}.toString();
  }
}