注解开发

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

注解和XML标签对应关系

注解	作用对象	XML标签	说明
@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 注解，这两个注解将被此注解覆盖

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();}
}