上文已经讲解了基于XML配置的SQL映射器,在XML配置的基础上MyBatis提供了简单的Java注解,使得我们可以不配置XML格式的Mapper文件,也能方便的编写简单的数据库操作代码。
Mybatis之接口注解
映射语句
@Insert
可以使用@Insert注解来定义一个INSERT映射语句:
![](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif?x-oss-process=image/resize,w_1400/format,webp)
![](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif?x-oss-process=image/resize,w_1400/format,webp)
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);
[自动生成主键]
在上一章中我们讨论过主键列值可以自动生成。我们可以使用@Options注解的userGeneratedKeys 和keyProperty属性让数据库生成AUTO_INCREMENT列的值
![](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif?x-oss-process=image/resize,w_1400/format,webp)
![](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif?x-oss-process=image/resize,w_1400/format,webp)
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);
有些数据库如Oracle并不支持 AUTO_INCREMENT 列,其使用序列(SEQUENCE),或者其他查询(uuid)来生成主键值,如下所示:
![](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif?x-oss-process=image/resize,w_1400/format,webp)
![](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif?x-oss-process=image/resize,w_1400/format,webp)
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);
@Update
可以使用@Update注解来定义一个UPDATE映射语句:
![](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif?x-oss-process=image/resize,w_1400/format,webp)
![](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif?x-oss-process=image/resize,w_1400/format,webp)
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);
@Delete
可以使用@Delete 注解来定义一个DELETE映射语句:
![](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif?x-oss-process=image/resize,w_1400/format,webp)
![](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif?x-oss-process=image/resize,w_1400/format,webp)
1 @Delete("delete from sys_user WHERE id=#{id}") 2 int deleteUser(String id);
@Select
可以使用@ Select注解来定义一个SELECT映射语句:
![](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif?x-oss-process=image/resize,w_1400/format,webp)
![](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif?x-oss-process=image/resize,w_1400/format,webp)
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);
结果映射
我们可以将查询结果通过别名或者是@Results注解与JavaBean属性映射起来:
![](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif?x-oss-process=image/resize,w_1400/format,webp)
![](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif?x-oss-process=image/resize,w_1400/format,webp)
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);
一对一映射
MyBatis提供了@One注解来使用嵌套select语句(Nested-Select)加载一对一关联查询数据。MyBatis3.2.2版本,并没有对应的注解支持嵌套结果ResultMap。
![](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif?x-oss-process=image/resize,w_1400/format,webp)
![](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif?x-oss-process=image/resize,w_1400/format,webp)
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);
一对多映射
MyBatis提供了@Many注解来使用嵌套Select语句加载一对多关联查询。
![](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif?x-oss-process=image/resize,w_1400/format,webp)
![](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif?x-oss-process=image/resize,w_1400/format,webp)
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);
动态SQL
有时候我们需要根据输入条件动态地构建SQL语句。MyBatis提供了各种注解如@InsertProvider,@UpdateProvider,@DeleteProvider和@SelectProvider,来帮助构建动态SQL语句,然后让MyBatis执行这些SQL语句。
@SelectProvider
可以使用org.apache.ibatis.jdbc.SQL工具类来构建SQL语句:
![](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif?x-oss-process=image/resize,w_1400/format,webp)
![](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif?x-oss-process=image/resize,w_1400/format,webp)
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 }
可以使用@SelectProvider注解来创建一个SELECT映射语句:
![](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif?x-oss-process=image/resize,w_1400/format,webp)
![](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif?x-oss-process=image/resize,w_1400/format,webp)
1 @SelectProvider(type=sysUserDynaSqlProvider.class, method="findUserByIdSql") 2 sysUser findUserByIdSql(@Param("id") String id);
SQL工具类会处理以合适的空格前缀和后缀来构造SQL语句。
动态SQL provider方法可以接收以下其中一种参数:
- 无参数
- 和映射器Mapper接口的方法同类型的参数
- java.util.Map
例如 映射器Mapper接口方法只有一个参数,那么可以定义SQLProvider方法,它接受一个与Mapper接口方法相同类型的参数:
![](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif?x-oss-process=image/resize,w_1400/format,webp)
![](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif?x-oss-process=image/resize,w_1400/format,webp)
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 }
如果映射器Mapper接口有多个输入参数,我们可以使用参数类型为java.util.Map的方法作为SQLprovider方法。MyBatis提供的多值映射参数,使用的是#{param}语法。
![](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif?x-oss-process=image/resize,w_1400/format,webp)
![](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif?x-oss-process=image/resize,w_1400/format,webp)
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);
SQL工具类也提供了其他的方法来表示JOINS,ORDER_BY,GROUP_BY等等。
例如 使用LEFT_OUTER_JOIN的例子:
![](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif?x-oss-process=image/resize,w_1400/format,webp)
![](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif?x-oss-process=image/resize,w_1400/format,webp)
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);
@InsertProvider
可以使用@InsertProvider注解创建动态的INSERT语句:
![](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif?x-oss-process=image/resize,w_1400/format,webp)
![](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif?x-oss-process=image/resize,w_1400/format,webp)
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);
@UpdateProvider
可以通过@UpdateProvider注解创建动态的UPDATE语句:
![](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif?x-oss-process=image/resize,w_1400/format,webp)
![](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif?x-oss-process=image/resize,w_1400/format,webp)
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);
@DeleteProvider
可以使用@DeleteProvider注解创建动态的DELETE语句:
![](https://yqfile.alicdn.com/img_1c53668bcee393edac0d7b3b3daff1ae.gif?x-oss-process=image/resize,w_1400/format,webp)
![](https://yqfile.alicdn.com/img_405b18b4b6584ae338e0f6ecaf736533.gif?x-oss-process=image/resize,w_1400/format,webp)
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);
GITHUB
github : https://github.com/nbfujx/learn-java-demo/tree/master/Goku.MybatisDemo.Annotation