MyBatis-07MyBatis注解方式之@Select

2021-08-17 10:57:21 浏览数 (1)
  • 概述
  • @Select注解
    • 通过数据库字段别名方式映射到实体类属性
      • 1. RoleMapper接口增加接口方法，同时标注注解
      • 2.单元测试
    • 通过mapUnderscoreToCamelCase完成数据库字段到实体类属性的映射
      • 1.MyBatis全局文件settings节点增加如下配置
      • 2. RoleMapper接口增加接口方法，同时标注注解
      • 3.单元测试
    • 通过@Results / @ReusltMap注解完成数据库字段到实体类属性的映射
      • 1. RoleMapper接口增加接口方法，同时标注注解
      • 2.单元测试
    • @Results的复用
  • 小结

概述

MyBatis注解方式是将SQL直接注解写在接口上 。 这种方式的优点是对于需求比较简单的系统，效率较高。 缺点是：当SQL有变化时都需要重新编译代码。

一般情况下不建议使用注解的方式。这里仅仅演示下简单的用法，不深究。

@Select注解

使用注解的方式同样需要考虑表字段和Java属性字段映射的问题。我们现在看下通过注解如何完成这项工作

我们以通过Id查询所属的角色为例

通过数据库字段别名方式映射到实体类属性

1. RoleMapper接口增加接口方法，同时标注注解

代码语言：javascript复制
/**
     * 
     * 
     * @Title: selectSysRoleById
     * 
     * @Description: 采用数据库字段别名和实体类属性同名的方式映射
     * 
     * @param roleId
     * @return
     * 
     * @return: SysRole
     */

    @Select({ "SELECT "
                  " a.id, "
                  " a.role_name roleName, "
                  " a.enabled, "
                  " a.create_by createBy, "
                  " a.create_time createTime "
              " FROM "
                  " sys_role a "
              " WHERE "
              " a.id = #{roleId}" })
    SysRole selectSysRoleById(Long roleId);

2.单元测试

代码语言：javascript复制
@Test
    public void selectSysRoleByIdTest() {

        logger.info("selectSysRoleByIdTest");
        try {
            // 获取SqlSession
            sqlSession = getSqlSession();
            // 获取接口
            RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
            // 调用接口方法
            SysRole sysRole = roleMapper.selectSysRoleById((long) 1);
            // 期待不为空
            Assert.assertNotNull(sysRole);
            // 期望为 roleName="管理员"
            Assert.assertEquals("管理员", sysRole.getRoleName());

            logger.info("sysRole Info:"   sysRole);
        } finally {
            sqlSession.close();
        }
    }

日志

代码语言：javascript复制
2018-04-17 21:37:36,996  INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully
2018-04-17 21:37:37,001  INFO [main] (BaseMapperTest.java:29) - reader close successfully
2018-04-17 21:37:37,006  INFO [main] (RoleMapperTest.java:19) - selectSysRoleByIdTest
2018-04-17 21:37:37,528 DEBUG [main] (BaseJdbcLogger.java:142) - ==>  Preparing: SELECT a.id, a.role_name roleName, a.enabled, a.create_by createBy, a.create_time createTime FROM sys_role a WHERE a.id = ? 
2018-04-17 21:37:37,607 DEBUG [main] (BaseJdbcLogger.java:142) - ==> Parameters: 1(Long)
2018-04-17 21:37:37,627 TRACE [main] (BaseJdbcLogger.java:148) - <==    Columns: id, roleName, enabled, createBy, createTime
2018-04-17 21:37:37,627 TRACE [main] (BaseJdbcLogger.java:148) - <==        Row: 1, 管理员, 1, 1, 2018-04-13 21:12:46.0
2018-04-17 21:37:37,629 DEBUG [main] (BaseJdbcLogger.java:142) - <==      Total: 1
2018-04-17 21:37:37,632  INFO [main] (RoleMapperTest.java:32) - sysRole Info:SysRole [id=1, roleName=管理员, enabled=1, createBy=1, createTime=Fri Apr 13 21:12:46 BOT 2018, user=null, privilegeList=null]

通过mapUnderscoreToCamelCase完成数据库字段到实体类属性的映射

1.MyBatis全局文件settings节点增加如下配置

代码语言：javascript复制
<setting name="mapUnderscoreToCamelCase" value="true"/>

2. RoleMapper接口增加接口方法，同时标注注解

代码语言：javascript复制
/**
     * 
     * 
     * @Title: selectSysRoleById2
     * 
     * @Description: 采用 MyBatis全局配置文件设置 mapUnderscoreToCamelCase 映射
     *               通过配置这个属性为true可以自动将下画线方式命名的数据库列映射到java对象驼峰式命名属性中
     * 
     * @param roleId
     * @return
     * 
     * @return: SysRole
     */
    @Select({ "SELECT "
              " a.id, "
              " a.role_name , "
              " a.enabled, "
              " a.create_by , "
              " a.create_time "
          " FROM "
              " sys_role a "
          " WHERE "
          " a.id = #{roleId}" })
    SysRole selectSysRoleById2(Long roleId);

3.单元测试

代码语言：javascript复制
/**
     * 
     * 
     * @Title: selectSysRoleByIdTest2
     * 
     * @Description: 测试的时候需要将  放开
     * 
     * 
     * @return: void
     */
    @Test
    public void selectSysRoleByIdTest2() {
        logger.info("selectSysRoleByIdTest2");
        try {
            // 获取SqlSession
            sqlSession = getSqlSession();
            // 获取接口
            RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
            // 调用接口方法
            SysRole sysRole = roleMapper.selectSysRoleById2((long) 2);
            // 期待不为空
            Assert.assertNotNull(sysRole);
            // 期望为 roleName="普通用户"
            Assert.assertEquals("普通用户", sysRole.getRoleName());

            logger.info("sysRole Info:"   sysRole);
        } finally {
            sqlSession.close();
        }
    }
代码语言：javascript复制
2018-04-17 21:38:57,518  INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully
2018-04-17 21:38:57,523  INFO [main] (BaseMapperTest.java:29) - reader close successfully
2018-04-17 21:38:57,526  INFO [main] (RoleMapperTest.java:51) - selectSysRoleByIdTest2
2018-04-17 21:38:58,057 DEBUG [main] (BaseJdbcLogger.java:142) - ==>  Preparing: SELECT a.id, a.role_name , a.enabled, a.create_by , a.create_time FROM sys_role a WHERE a.id = ? 
2018-04-17 21:38:58,139 DEBUG [main] (BaseJdbcLogger.java:142) - ==> Parameters: 2(Long)
2018-04-17 21:38:58,168 TRACE [main] (BaseJdbcLogger.java:148) - <==    Columns: id, role_name, enabled, create_by, create_time
2018-04-17 21:38:58,169 TRACE [main] (BaseJdbcLogger.java:148) - <==        Row: 2, 普通用户, 1, 1, 2018-04-13 21:12:46.0
2018-04-17 21:38:58,172 DEBUG [main] (BaseJdbcLogger.java:142) - <==      Total: 1
2018-04-17 21:38:58,178  INFO [main] (RoleMapperTest.java:64) - sysRole Info:SysRole [id=2, roleName=普通用户, enabled=1, createBy=1, createTime=Fri Apr 13 21:12:46 BOT 2018, user=null, privilegeList=null]

通过@Results / @ReusltMap注解完成数据库字段到实体类属性的映射

XML中的resultMap元素有一个对应的JavaBean注解@Results,使用这个注解来实现属性映射。

1. RoleMapper接口增加接口方法，同时标注注解

代码语言：javascript复制
/**
     * 
     * 
     * @Title: selectSysRoleById3
     * 
     * @Description: 通过ResultMap的方式实现数据库列到java对象的映射
     * 
     * @param roleId
     * @return
     * 
     * @return: SysRole
     */

    @Results({ 
        @Result(property = "id", column = "id", id = true),
        @Result(property = "roleName", column = "role_name"),      @Result(property = "enabled", column = "enabled"),
        @Result(property = "createBy", column = "create_by"),  @Result(property = "createTime", column = "create_time")
    })
    @Select({ "SELECT "
              " a.id, "
              " a.role_name , "
              " a.enabled, "
              " a.create_by , "
              " a.create_time "
          " FROM "
              " sys_role a "
          " WHERE "
          " a.id = #{roleId}" })
    SysRole selectSysRoleById3(Long roleId);

这里的@Result注解对应着xml文件中的元素,而参数中协商id = true 时就对应 元素

2.单元测试

代码语言：javascript复制
@Test
    public void selectSysRoleByIdTest3() {
        logger.info("selectSysRoleByIdTest3");
        try {
            // 获取SqlSession
            sqlSession = getSqlSession();
            // 获取接口
            RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
            // 调用接口方法
            SysRole sysRole = roleMapper.selectSysRoleById3((long) 2);
            // 期待不为空
            Assert.assertNotNull(sysRole);
            // 期望为 roleName="普通用户"
            Assert.assertEquals("普通用户", sysRole.getRoleName());

            logger.info("sysRole Info:"   sysRole);
        } finally {
            sqlSession.close();
        }
    }
代码语言：javascript复制
2018-04-17 21:40:09,799  INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully
2018-04-17 21:40:09,803  INFO [main] (BaseMapperTest.java:29) - reader close successfully
2018-04-17 21:40:09,806  INFO [main] (RoleMapperTest.java:72) - selectSysRoleByIdTest3
2018-04-17 21:40:10,303 DEBUG [main] (BaseJdbcLogger.java:142) - ==>  Preparing: SELECT a.id, a.role_name , a.enabled, a.create_by , a.create_time FROM sys_role a WHERE a.id = ? 
2018-04-17 21:40:10,384 DEBUG [main] (BaseJdbcLogger.java:142) - ==> Parameters: 2(Long)
2018-04-17 21:40:10,412 TRACE [main] (BaseJdbcLogger.java:148) - <==    Columns: id, role_name, enabled, create_by, create_time
2018-04-17 21:40:10,413 TRACE [main] (BaseJdbcLogger.java:148) - <==        Row: 2, 普通用户, 1, 1, 2018-04-13 21:12:46.0
2018-04-17 21:40:10,416 DEBUG [main] (BaseJdbcLogger.java:142) - <==      Total: 1
2018-04-17 21:40:10,421  INFO [main] (RoleMapperTest.java:85) - sysRole Info:SysRole [id=2, roleName=普通用户, enabled=1, createBy=1, createTime=Fri Apr 13 21:12:46 BOT 2018, user=null, privilegeList=null]

@Results的复用

MyBatis 3.3.0及以前版本中，注解定义的@Results不能共用，使用起来很不方便，需要再每个方法上都写一遍。

从MyBatis3.3.1版本开始，@Results注解增加了一个id属性，设置了i属性后，就可以通过id属性引用同一个@Reuslts配置了。

调整Mybatis版本

代码语言：javascript复制
<mybatis.version>3.4.1mybatis.version>
代码语言：javascript复制
@Results(id = "roleResultMap", value = {
  @Result(property = "id", column = "id", id = true), 
  @Result(property = "roleName", column = "role_name"),
  @Result(property = "enabled", column = "enabled"), 
  @Result(property = "createTime", column = "create_time") })

如何引用呢？ 通过@ResultMap注解一弄

代码语言：javascript复制
    /**
     * 
     * 
     * @Title: selectSysRoleById4
     * 
     * @Description: 通过改方法演示在mybatis3.3.1版本及其之后的版本 通过@ResultMap应用带有id的@Results
     * 
     * @param roleId
     * @return
     * 
     * @return: SysRole
     */

    @ResultMap("roleResultMap")
    @Select({
 "SELECT  a.id, a.role_name,  a.enabled, a.create_by ,  a.create_time  FROM  sys_role a " })
    List selectAllSysRole();

单元测试

代码语言：javascript复制
@Test
    public void selectAllSysTest() {
        logger.info("selectAllSysTest");
        try {
            // 获取SqlSession
            sqlSession = getSqlSession();
            // 获取接口
            RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
            // 调用接口方法
            List sysRoleList = roleMapper.selectAllSysRole();
            // 期待不为空
            Assert.assertNotNull(sysRoleList);
            // 期望为sysRoleList > 0
            Assert.assertTrue(sysRoleList.size() > 0);

            for (SysRole sysRole2 : sysRoleList) {
                logger.info("sysRole Info:"   sysRole2);
            }

        } finally {
            sqlSession.close();
        }
    }

日志

代码语言：javascript复制
2018-04-17 21:57:03,655  INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully
2018-04-17 21:57:03,659  INFO [main] (BaseMapperTest.java:29) - reader close successfully
2018-04-17 21:57:03,662  INFO [main] (RoleMapperTest.java:95) - selectAllSysTest
2018-04-17 21:57:04,159 DEBUG [main] (BaseJdbcLogger.java:145) - ==>  Preparing: SELECT a.id, a.role_name, a.enabled, a.create_by , a.create_time FROM sys_role a 
2018-04-17 21:57:04,249 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 
2018-04-17 21:57:04,295 TRACE [main] (BaseJdbcLogger.java:151) - <==    Columns: id, role_name, enabled, create_by, create_time
2018-04-17 21:57:04,296 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1, 管理员, 1, 1, 2018-04-13 21:12:46.0
2018-04-17 21:57:04,299 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 2, 普通用户, 1, 1, 2018-04-13 21:12:46.0
2018-04-17 21:57:04,300 DEBUG [main] (BaseJdbcLogger.java:145) - <==      Total: 2
2018-04-17 21:57:04,307  INFO [main] (RoleMapperTest.java:109) - sysRole Info:SysRole [id=1, roleName=管理员, enabled=1, createBy=1, createTime=Fri Apr 13 21:12:46 BOT 2018, user=null, privilegeList=null]
2018-04-17 21:57:04,307  INFO [main] (RoleMapperTest.java:109) - sysRole Info:SysRole [id=2, roleName=普通用户, enabled=1, createBy=1, createTime=Fri Apr 13 21:12:46 BOT 2018, user=null, privilegeList=null]

小结

@Select基本的用法我们就说到这里， 接下来看@insert注解

单元测试 编程算法 mybatis 数据库 sql

0 人点赞