Mybatis基于接口注解配置SQL映射器(一)

2022-03-22 05:09:21

上文已经讲解了基于XML配置的SQL映射器,在XML配置的基础上MyBatis提供了简单的Java注解，使得我们可以不配置XML格式的Mapper文件，也能方便的编写简单的数据库操作代码。

Mybatis之接口注解

映射语句

@Insert

可以使用@Insert注解来定义一个INSERT映射语句：

 1  @Insert(" insert into sys_user (id, username, password, " +
 2             "      name, sex, status, org_id, " +
 3             "      email, idcard, is_admin, " +
 4             "      sort, mobile, stationid" +
 5             "      )" +
 6             "    values (#{id}, #{username}, #{password}," +
 7             "      #{name}, #{sex}, #{status}, #{orgId}, " +
 8             "      #{email}, #{idcard}, #{isAdmin}, " +
 9             "      #{sort}, #{mobile}, #{stationid}" +
10             "      )")
11     int insertUser(sysUser sysuser);

View Code

[自动生成主键]

在上一章中我们讨论过主键列值可以自动生成。我们可以使用@Options注解的userGeneratedKeys 和keyProperty属性让数据库生成AUTO_INCREMENT列的值

 1    @Insert(" insert into sys_user ( username, password, " +
 2             "      name, sex, status, org_id, " +
 3             "      email, idcard, is_admin, " +
 4             "      sort, mobile, stationid" +
 5             "      )" +
 6             "    values ( #{username}, #{password}," +
 7             "      #{name}, #{sex}, #{status}, #{orgId}, " +
 8             "      #{email}, #{idcard}, #{isAdmin}, " +
 9             "      #{sort}, #{mobile}, #{stationid}" +
10             "      )")
11     @Options(useGeneratedKeys = true, keyProperty = "id")
12     int insertUser2(sysUser sysuser);

View Code

有些数据库如Oracle并不支持 AUTO_INCREMENT 列，其使用序列（SEQUENCE），或者其他查询（uuid）来生成主键值，如下所示：

 1 @Insert(" insert into sys_user ( username, password, " +
 2             "      name, sex, status, org_id, " +
 3             "      email, idcard, is_admin, " +
 4             "      sort, mobile, stationid" +
 5             "      )" +
 6             "    values ( #{username}, #{password}," +
 7             "      #{name}, #{sex}, #{status}, #{orgId}, " +
 8             "      #{email}, #{idcard}, #{isAdmin}, " +
 9             "      #{sort}, #{mobile}, #{stationid}" +
10             "      )")
11     @SelectKey(statement=" SELECT replace(uuid(),'-','')  AS id",
12             keyProperty="id", resultType=String.class, before=true)
13     int insertUser3(sysUser sysuser);

View Code

@Update

可以使用@Update注解来定义一个UPDATE映射语句：

 1 @Update("update sys_user" +
 2             "    set username = #{username}," +
 3             "      password = #{password}," +
 4             "      name = #{name}," +
 5             "      sex = #{sex}," +
 6             "      status = #{status}," +
 7             "      org_id = #{orgId}," +
 8             "      email = #{email}," +
 9             "      idcard = #{idcard}," +
10             "      is_admin = #{isAdmin}," +
11             "      sort = #{sort}," +
12             "      mobile = #{mobile}," +
13             "      stationid = #{stationid}" +
14             "    where id = #{id}")
15     int updateUser(sysUser sysuser);

View Code

@Delete

可以使用@Delete 注解来定义一个DELETE映射语句：

1   @Delete("delete from sys_user WHERE id=#{id}")
2     int deleteUser(String id);

View Code

@Select

可以使用@ Select注解来定义一个SELECT映射语句：

1     @Select("SELECT * FROM sys_user WHERE username = #{username}")
2     sysUser findByUsername(@Param("username") String username);
3 
4     @Select("SELECT * FROM sys_user WHERE id = #{id}")
5     sysUser findById(@Param("id") String id);

View Code

结果映射

我们可以将查询结果通过别名或者是@Results注解与JavaBean属性映射起来：

1 @Select("SELECT * FROM sys_user WHERE username = #{username}")
2     @Results(
3             {
4                     @Result(id = true, column = "id", property = "id"),
5                     @Result(column = "password", property = "password"),
6                     @Result(column = "name", property = "name"),
7                     @Result(column = "sex", property = "sex")
8             })
9     sysUser findByUsername(@Param("username") String username);

View Code

一对一映射

MyBatis提供了@One注解来使用嵌套select语句（Nested-Select）加载一对一关联查询数据。MyBatis3.2.2版本，并没有对应的注解支持嵌套结果ResultMap。

 1  @Select("SELECT * FROM sys_user WHERE id = #{id}")
 2     @Results(
 3      {
 4         @Result(id = true,column = "id", property = "id"),
 5         @Result(column = "password", property = "password"),
 6         @Result(column = "name", property = "name"),
 7         @Result(column = "sex", property = "sex"),
 8         @Result(property = "sysuserinfo", column = "id",
 9            one = @One(select = "com.goku.mybatis.mapper.sysUserMapper.findsysUserInfo"))
10      })
11     sysUser findsysUser(@Param("id") String id);
12 
13     @Select("select * from sys_user_info where id=#{id}")
14     sysUserInfo findsysUserInfo(String id);

View Code

一对多映射

MyBatis提供了@Many注解来使用嵌套Select语句加载一对多关联查询。

 1  @Select("SELECT * FROM sys_org WHERE id = #{id}")
 2     @Results(
 3     {
 4             @Result(id = true,column = "id", property = "id"),
 5             @Result(column = "version", property = "version"),
 6             @Result(column = "level", property = "level"),
 7             @Result(column = "type", property = "type"),
 8             @Result(property = "sysuser", column = "id",
 9                     many = @Many(select = "com.goku.mybatis.mapper.sysUserMapper.findByOrgId"))
10     })
11     sysOrg findsysOrg(@Param("id") String id);
12 
13     @Select("SELECT * FROM sys_user WHERE org_id = #{id}")
14     sysUser findByOrgId(String id);

View Code

动态SQL

有时候我们需要根据输入条件动态地构建SQL语句。MyBatis提供了各种注解如@InsertProvider,@UpdateProvider,@DeleteProvider和@SelectProvider，来帮助构建动态SQL语句，然后让MyBatis执行这些SQL语句。

@SelectProvider

可以使用org.apache.ibatis.jdbc.SQL工具类来构建SQL语句：

 1 public String findUserByIdSql()
 2     {
 3         return new SQL()
 4         {
 5             {
 6                 SELECT("*");
 7                 FROM("sys_user");
 8                 WHERE("id=#{id}");
 9             }
10         } .toString();
11     }

View Code

可以使用@SelectProvider注解来创建一个SELECT映射语句：

1    @SelectProvider(type=sysUserDynaSqlProvider.class, method="findUserByIdSql")
2     sysUser findUserByIdSql(@Param("id") String id);

View Code

SQL工具类会处理以合适的空格前缀和后缀来构造SQL语句。

动态SQL provider方法可以接收以下其中一种参数：

无参数
和映射器Mapper接口的方法同类型的参数
java.util.Map

例如映射器Mapper接口方法只有一个参数，那么可以定义SQLProvider方法，它接受一个与Mapper接口方法相同类型的参数：

 1  public String findUserByIdSql(final String id)
 2     {
 3         return new SQL()
 4         {
 5             {
 6                 SELECT("*");
 7                 FROM("sys_user");
 8                 WHERE("id='"+id+"'");
 9             }
10         } .toString();
11     }

View Code

如果映射器Mapper接口有多个输入参数，我们可以使用参数类型为java.util.Map的方法作为SQLprovider方法。MyBatis提供的多值映射参数，使用的是#{param}语法。

 1 public String findUserByIdSql2(Map<String, Object> map)
 2     {
 3         return new SQL()
 4         {
 5             {
 6                 SELECT("*");
 7                 FROM("sys_user");
 8                 WHERE("id=#{param1} and username=#{param2}");
 9             }
10         } .toString();
11     }
12 
13     @SelectProvider(type=sysUserDynaSqlProvider.class, method="findUserByIdSql2")
14     sysUser findUserByIdSql2(String id, String name);

View Code

SQL工具类也提供了其他的方法来表示JOINS，ORDER_BY，GROUP_BY等等。

例如使用LEFT_OUTER_JOIN的例子：

 1  public String findUserinfoByIdSql()
 2     {
 3         return new SQL()
 4         {
 5             {
 6                 SELECT("u.id, username, password, name, sex, status, org_id, email, idcard, is_admin, sort," +
 7                         "        mobile,address, post_code, height, weight, birthday, blood, culture, finish_school_date," +
 8                         "        folk, government, homepage, householder, marriage, msn, nativity_address, qq, speciality," +
 9                         "        description, version");
10                 FROM("sys_user u");
11                 LEFT_OUTER_JOIN("sys_user_info t on u.id=t.id");
12                 WHERE("u.id=#{id}");
13             }
14         } .toString();
15     }
16 
17     @SelectProvider(type=sysUserDynaSqlProvider.class, method="findUserinfoByIdSql")
18     List<Map<String, String>> findUserinfoByIdSql(@Param("id") String id);

View Code

@InsertProvider

可以使用@InsertProvider注解创建动态的INSERT语句:

 1     public String insertUser(final sysUser sys_user)
 2     {
 3         return new SQL()
 4         {
 5             {
 6                 INSERT_INTO("sys_user");
 7                 if (sys_user.getId()!=null)
 8                 {
 9                     VALUES("id", "#{id}");
10                 }
11                 if (sys_user.getName() != null)
12                 {
13                     VALUES("name", "#{name}");
14                 }
15             }
16         } .toString();
17     }
18 
19  @InsertProvider(type = sysUserDynaSqlProvider.class, method = "insertUser")
20     int insertUser4(sysUser sysuser);

View Code

@UpdateProvider

可以通过@UpdateProvider注解创建动态的UPDATE语句：

 1     public String updateUser(final sysUser sys_user)
 2     {
 3         return new SQL()
 4         {
 5             {
 6                 UPDATE("sys_user");
 7                 if (sys_user.getName() != null)
 8                 {
 9                     SET("name = #{name}");
10                 }
11                 WHERE("id = #{id}");
12             }
13         } .toString();
14     }
15 
16     @InsertProvider(type = sysUserDynaSqlProvider.class, method = "updateUser")
17     int updateUser2(sysUser sysuser);

View Code

@DeleteProvider

可以使用@DeleteProvider注解创建动态的DELETE语句：

 1     public String deleteUser()
 2     {
 3         return new SQL()
 4         {
 5             {
 6                 DELETE_FROM("sys_user");
 7                 WHERE("id = #{id}");
 8             }
 9         } .toString();
10     }
11 
12     @DeleteProvider(type = sysUserDynaSqlProvider.class, method = "deleteUser")
13     int deleteUser2(@Param("id") String id);

View Code

GITHUB

github : https://github.com/nbfujx/learn-java-demo/tree/master/Goku.MybatisDemo.Annotation

码农公寓

Mybatis之接口注解

映射语句

@Insert

[自动生成主键]

@Update

@Delete

@Select

结果映射

一对一映射

一对多映射

动态SQL

@SelectProvider

@InsertProvider

@UpdateProvider

@DeleteProvider

GITHUB

相关文章