MyBatis-03 MyBatis XML方式之select元素

概述
select用法
- 根据用户id查询用户信息
  - 1.UserMapper接口中添加接口方法
  - 2. UserMapper.xml中配置resultMap和select元素
- 查询全部的SysUser
  - 添加接口方法
  - 配置UserMapper.xml
- 单元测试
  - 基础测试类BaseMapperTest
- 全局属性mapUnderscoreToCamelCase
  - 1.增加UserMapper接口
  - 2.配置UserMapper.xml
  - 3.单元测试
- 多表关联查询
  - 需求一：根据用户id返回该用户所拥有的角色，返回的结果为角色集合，结果只有角色的信息即可，不用包含其他字段信息。
    - 1.UserMapper接口增加接口方法
    - 2. UserMapper.xml 配置语句
    - 3. 单元测试
  - 需求二：假设有个需求（仅仅为了说明用法）：以需求一为基础，不仅要包含sys_role的信息，同时还要包含当前用户的部分信息（不考虑嵌套的情况）
    - 1.SysRole添加 SysUser属性
    - 2.UserMapper.xml
    - 3.单元测试
小结

概述

撸完Mybatis-02Mybatis XML方式概述及配置步骤之后，准备工作准备完毕，就可以开始学习具体的用法了。我们先来看下select元素

select用法

权限系统中，几个常见的业务，需要查询出系统中的用户、角色、权限等数据，纯JDBC时，需要写查询语句，并且对结果集进行手工处理，将结果映射到对象的属性中。而如果使用Mybatis，只需要在XML中添加一个select元素，写一个SQL，做一些简单的配置，就可以将结果集映射到对象中。

根据用户id查询用户信息

1.UserMapper接口中添加接口方法

代码语言：javascript复制

package com.artisan.mybatis.xml.mapper;

import com.artisan.mybatis.xml.domain.SysUser;

public interface UserMapper {
    /**
     * 
     * 
     * @Title: selectSysUserById
     * 
     * @Description: 通过ID查询系统用户
     * 
     * @param id
     * @return
     * 
     * @return: SysUser
     */
    SysUser selectSysUserById(Long id);

}

因为ID是主键，所以只可能查询出一个SysUser ,所以selectSysUserById直接返回SysUser对象即可。

2. UserMapper.xml中配置resultMap和select元素

首先查看下数据库SysUser的字段

再来看下SysUser实体类中的属性

数据库字段和实体类中的属性名称不一致，两种方式

配置resultMap 映射 (selectSysUserById演示)
通过别名的方式（selectAll 演示）

配置之前，先来探讨一个问题: 前面创建接口和XML时提到过，接口和XML是通过将namespace设置为接口的全限定名来进行关联的，那么接口中的方法和XML又是怎么关联的呢？

Mybatis 是通过 xml中的select标签的ID和接口名称一致这种方式将接口方法和XML中定义的SQL语句关联起来的，如果接口方法中没有和XML 中的id属性相对应的值，启动程序便会报错。

映射XML和接口的命名需要符合如下规范：

当只是用XML而不适用接口的时候，namespace的值可以设置为任意不重复的名称
标签的id属性在任何时候都不能出现英文句号，并且同一个命名空间下不能出现重复的id
因为接口方法是可以重载的，所以接口中可以出现多个同名但参数不同的方法，但是xml中的id不能重复。

搞清了这些，我们来配置UserMapper.xml

代码语言：javascript复制

<mapper namespace="com.artisan.mybatis.xml.mapper.UserMapper">

    
    <resultMap id="userMap" 
               type="com.artisan.mybatis.xml.domain.SysUser">
        <id column="id" property="id" />
        <result property="userName" column="user_name" />
        <result property="userPassword" column="user_password" />
        <result property="userEmail" column="user_email" />
        <result property="userInfo" column="user_info" />
        <result property="headImg" column="head_img" jdbcType="BLOB" />
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP" />
    resultMap>

    
    <select id="selectSysUserById" parameterType="Long" resultMap="userMap">
        select
            a.id,
            a.user_name,
            a.user_password,
            a.user_email,
            a.user_info,
            a.head_img,
            a.create_time
        from
            sys_user a
        where id = #{id}
    select>

mapper>

解释下XML中的一些标签和属性的作用

:映射查询语句使用的标签id : 命名空间的唯一标示，代表这个语句resultMap:用于设置返回值的类型和映射关系#{id}: Mybatis SQL中使用预编译参数的一种方式，大括号中的id是传入的参数名。表示使用preparedstatement设置占位符号并将输入变量id传到sql。下面看下接口方法的返回值要如何定义接口中定义的返回值类型必须和XML中配置的ResultType类型一致，否则就会因为类型不一致而抛出异常。返回值类型是有XML中的resultType（或者resultMap中的type）决定的，不是由接口中所写的返回值类型决定的（这里我们建的是XML方式，先忽略注解的情况）查询全部的SysUser 添加接口方法 /** * * * @Title: selectAll * * @Description: 查询全部的SysUser * * @return * * @return: List */ List selectAll(); 配置UserMapper.xml <select id="selectAll" resultType="com.artisan.mybatis.xml.domain.SysUser"> select a.id id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime from sys_user a select> 我们看到这个接口中对应方法的返回类型为 List , 为什么不是SysUser呢？当返回值最多只有1个结果的时候（可以是0个），可以将接口返回值定义为SysUser，而不是List。当执行的SQL返回多个结果时，必须使用List 或者SysUser[] 作为返回值，如果使用SysUser，会抛出TooManyResultsException异常。观察一下UserMapper.xml中的selectSysUserById和selectAll的区别 selectSysUserById使用了resultMap来设置结果映射，而selectAll使用resultType直接指定了返回结果的类型。可以发现，如果使用resultType来设置返回结果的类型，需要在SQL中为所有列名和属性名不一致的列设置别名，通过设置别名使最终的查询结果和resultType指定对象的属性名保持一致，进而实现自动映射. 单元测试基础测试类BaseMapperTest 这个类用于被继承，主要功能是加载配置文件，提供获取SqlSession的方法在src/test/java中创建包 com.artisan.mybatis.xml.mapper package com.artisan.mybatis.xml.mapper; import java.io.IOException; import java.io.Reader; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.apache.log4j.Logger; import org.junit.BeforeClass; public class BaseMapperTest { private static Logger logger = Logger.getLogger(BaseMapperTest.class); private static SqlSessionFactory sessionFactory; @BeforeClass public static void init() { try { // 根据mybatis的配置文件创建sqlSessionFactory String config = "mybatis-config.xml"; Reader reader = Resources.getResourceAsReader(config); sessionFactory = new SqlSessionFactoryBuilder().build(reader); logger.info("sessionFactory bulit successfully"); reader.close(); logger.info("reader close successfully"); } catch (IOException e) { e.printStackTrace(); } } public static SqlSession getSqlSession() { return sessionFactory.openSession(); } } package com.artisan.mybatis.xml.mapper; import java.util.List; import org.apache.ibatis.session.SqlSession; import org.apache.log4j.Logger; import org.junit.Assert; import org.junit.Test; import com.artisan.mybatis.xml.domain.SysUser; /** * * * @ClassName: UserMapperTest * * @Description: UserMapperTest 单元测试类 * * @author: Mr.Yang * * @date: 2018年4月14日下午1:59:31 */ public class UserMapperTest extends BaseMapperTest { private static Logger logger = Logger.getLogger(UserMapperTest.class); @Test public void selectSysUserByIdTest() { logger.info("selectSysUserByIdTest"); // 获取SqlSession SqlSession sqlSession = getSqlSession(); try { // 获取UserMapper接口 UserMapper userMapper = sqlSession.getMapper(UserMapper.class); // 调用selectSysUserById方法，查询id=1的用户 SysUser sysUser = userMapper.selectSysUserById((long) 1); // sysUser不为空 Assert.assertNotNull(sysUser); // userName = "admin" Assert.assertEquals("admin", sysUser.getUserName()); logger.info(sysUser); } catch (Exception e) { e.printStackTrace(); } finally { sqlSession.close(); logger.info("sqlSession close successfully "); } } @Test public void selectAllTest() { logger.info("selectAllTest"); // 获取SqlSession SqlSession sqlSession = getSqlSession(); try { // 获取UserMapper接口 UserMapper userMapper = sqlSession.getMapper(UserMapper.class); // 调用selectAll,查询全部用户 List userList = userMapper.selectAll(); // 结果不为空 Assert.assertNotNull(userList); // 结果大于0 Assert.assertTrue(userList.size() > 0); logger.info("userList总数为:" userList.size()); for (SysUser sysUser : userList) { logger.info(sysUser); } } catch (Exception e) { e.printStackTrace(); } finally { sqlSession.close(); logger.info("sqlSession close successfully "); } } } 输出日志： 2018-04-15 14:11:47,623 INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully 2018-04-15 14:11:47,627 INFO [main] (BaseMapperTest.java:29) - reader close successfully 2018-04-15 14:11:47,630 INFO [main] (UserMapperTest.java:29) - selectSysUserByIdTest 2018-04-15 14:11:48,258 DEBUG [main] (BaseJdbcLogger.java:142) - ==> Preparing: select a.id, a.user_name, a.user_password, a.user_email, a.user_info, a.head_img, a.create_time from sys_user a where id = ? 2018-04-15 14:11:48,347 DEBUG [main] (BaseJdbcLogger.java:142) - ==> Parameters: 1(Long) 2018-04-15 14:11:48,379 TRACE [main] (BaseJdbcLogger.java:148) - <== Columns: id, user_name, user_password, user_email, user_info, head_img, create_time 2018-04-15 14:11:48,380 TRACE [main] (BaseJdbcLogger.java:148) - <== Row: 1, admin, 123456, admin@artisan.com, <>, <>, 2018-04-13 21:12:47.0 2018-04-15 14:11:48,385 DEBUG [main] (BaseJdbcLogger.java:142) - <== Total: 1 2018-04-15 14:11:48,386 INFO [main] (UserMapperTest.java:41) - SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@artisan.com, userInfo=管理员用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018] 2018-04-15 14:11:48,392 INFO [main] (UserMapperTest.java:46) - sqlSession close successfully 2018-04-15 14:11:48,393 INFO [main] (UserMapperTest.java:52) - selectAllTest 2018-04-15 14:11:48,402 DEBUG [main] (BaseJdbcLogger.java:142) - ==> Preparing: select a.id id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime from sys_user a 2018-04-15 14:11:48,403 DEBUG [main] (BaseJdbcLogger.java:142) - ==> Parameters: 2018-04-15 14:11:48,404 TRACE [main] (BaseJdbcLogger.java:148) - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime 2018-04-15 14:11:48,405 TRACE [main] (BaseJdbcLogger.java:148) - <== Row: 1, admin, 123456, admin@artisan.com, <>, <>, 2018-04-13 21:12:47.0 2018-04-15 14:11:48,407 TRACE [main] (BaseJdbcLogger.java:148) - <== Row: 1001, test, 123456, test@artisan.com, <>, <>, 2018-04-13 21:12:47.0 2018-04-15 14:11:48,412 DEBUG [main] (BaseJdbcLogger.java:142) - <== Total: 2 2018-04-15 14:11:48,412 INFO [main] (UserMapperTest.java:65) - userList总数为:2 2018-04-15 14:11:48,413 INFO [main] (UserMapperTest.java:67) - SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@artisan.com, userInfo=管理员用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018] 2018-04-15 14:11:48,413 INFO [main] (UserMapperTest.java:67) - SysUser [id=1001, userName=test, userPassword=123456, userEmail=test@artisan.com, userInfo=测试用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018] 2018-04-15 14:11:48,414 INFO [main] (UserMapperTest.java:73) - sqlSession close successfully 全局属性mapUnderscoreToCamelCase 在数据库中，由于大部分数据库设置不区分大小写，一次下画线方式的命名很常见，比如user_name, user_email . 在Java中，一般都是用驼峰式命名，比如userName,userEmail 因为数据库和Java中的这两种命名方式很常见，为此Mybatis提供了一个全局属性mapUnderscoreToCamelCase，通过配置这个属性为true可以自动将下画线方式命名的数据库列映射到java对象驼峰式命名属性中。这个属性默认为false，若想使用，需要在mybatis全局配置文件中settings节点下增加如下配置 <setting name="mapUnderscoreToCamelCase" value="true"/> 1.增加UserMapper接口 // 仅仅是为了测试mapUnderscoreToCamelCase 全局属性 SysUser selectUserByIdUseMapUnderscoreToCamelCase(Long id); 2.配置UserMapper.xml <select id="selectUserByIdUseMapUnderscoreToCamelCase" parameterType="Long" resultType="com.artisan.mybatis.xml.domain.SysUser"> select a.id, a.user_name, a.user_password, a.user_email, a.user_info, a.head_img, a.create_time from sys_user a where id = #{id} select> 注意使用的是resultType ，而不是resutlMap 3.单元测试 @Test public void selectUserByIdUseMapUnderscoreToCamelCaseTest() { logger.info("selectUserByIdUseMapUnderscoreToCamelCaseTest"); // 获取SqlSession SqlSession sqlSession = getSqlSession(); try { // 获取UserMapper接口 UserMapper userMapper = sqlSession.getMapper(UserMapper.class); // 调用selectSysUserById方法，查询id=1的用户 SysUser sysUser = userMapper.selectUserByIdUseMapUnderscoreToCamelCase((long) 1001); // sysUser不为空 Assert.assertNotNull(sysUser); // userName = "admin" Assert.assertEquals("test", sysUser.getUserName()); logger.info(sysUser); } catch (Exception e) { e.printStackTrace(); } finally { sqlSession.close(); logger.info("sqlSession close successfully "); } } 日志 2018-04-15 14:23:39,196 INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully 2018-04-15 14:23:39,200 INFO [main] (BaseMapperTest.java:29) - reader close successfully 2018-04-15 14:23:39,204 INFO [main] (UserMapperTest.java:79) - selectUserByIdUseMapUnderscoreToCamelCaseTest 2018-04-15 14:23:39,802 DEBUG [main] (BaseJdbcLogger.java:142) - ==> Preparing: select a.id, a.user_name, a.user_password, a.user_email, a.user_info, a.head_img, a.create_time from sys_user a where id = ? 2018-04-15 14:23:39,896 DEBUG [main] (BaseJdbcLogger.java:142) - ==> Parameters: 1001(Long) 2018-04-15 14:23:39,932 TRACE [main] (BaseJdbcLogger.java:148) - <== Columns: id, user_name, user_password, user_email, user_info, head_img, create_time 2018-04-15 14:23:39,933 TRACE [main] (BaseJdbcLogger.java:148) - <== Row: 1001, test, 123456, test@artisan.com, <>, <>, 2018-04-13 21:12:47.0 2018-04-15 14:23:39,936 DEBUG [main] (BaseJdbcLogger.java:142) - <== Total: 1 2018-04-15 14:23:39,939 INFO [main] (UserMapperTest.java:96) - sqlSession close successfully 多表关联查询需求一：根据用户id返回该用户所拥有的角色，返回的结果为角色集合，结果只有角色的信息即可，不用包含其他字段信息。根据我们的数据模型可知，涉及到3个表， sys_user 、sys_role 、sys_user_role 。 1.UserMapper接口增加接口方法 /** * * * @Title: selectRoleByUserId * * @Description: 根据用户ID查询用户角色 * * @param userId * @return * * @return: List */ List selectRoleListByUserId(Long userId); 2. UserMapper.xml 配置语句 <select id="selectRoleListByUserId" parameterType="Long" resultType="com.artisan.mybatis.xml.domain.SysRole"> SELECT c.id, c.role_name roleName, c.enabled, c.create_by createBy, c.create_time createTime FROM sys_user a JOIN sys_user_role b ON a.id = b.user_id JOIN sys_role c ON b.role_id = c.id WHERE a.id = #{userId} select> 3. 单元测试 @Test public void selectRoleListByUserIdTest() { logger.info("selectRoleListByUserIdTest"); // 获取SqlSession SqlSession sqlSession = getSqlSession(); try { // 获取UserMapper接口 UserMapper userMapper = sqlSession.getMapper(UserMapper.class); // 调用selectRoleListByUserId方法，查询用户id=1的角色 List roleList = userMapper.selectRoleListByUserId((long) 1); // roleList不为空 Assert.assertNotNull(roleList); // roleList > 0 Assert.assertTrue(roleList.size() > 0); logger.info(roleList); } catch (Exception e) { e.printStackTrace(); } finally { sqlSession.close(); logger.info("sqlSession close successfully "); } } 选中方法名，运行单元测试 2018-04-15 15:45:39,368 INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully 2018-04-15 15:45:39,373 INFO [main] (BaseMapperTest.java:29) - reader close successfully 2018-04-15 15:45:39,377 INFO [main] (UserMapperTest.java:103) - selectRoleListByUserIdTest 2018-04-15 15:45:40,086 DEBUG [main] (BaseJdbcLogger.java:142) - ==> Preparing: SELECT c.id, c.role_name roleName, c.enabled, c.create_by createBy, c.create_time createTime FROM sys_user a JOIN sys_user_role b ON a.id = b.user_id JOIN sys_role c ON b.role_id = c.id WHERE a.id = ? 2018-04-15 15:45:40,212 DEBUG [main] (BaseJdbcLogger.java:142) - ==> Parameters: 1(Long) 2018-04-15 15:45:40,262 TRACE [main] (BaseJdbcLogger.java:148) - <== Columns: id, roleName, enabled, createBy, createTime 2018-04-15 15:45:40,263 TRACE [main] (BaseJdbcLogger.java:148) - <== Row: 1, 管理员, 1, 1, 2018-04-13 21:12:46.0 2018-04-15 15:45:40,269 TRACE [main] (BaseJdbcLogger.java:148) - <== Row: 2, 普通用户, 1, 1, 2018-04-13 21:12:46.0 2018-04-15 15:45:40,270 DEBUG [main] (BaseJdbcLogger.java:142) - <== Total: 2 2018-04-15 15:45:40,271 INFO [main] (UserMapperTest.java:116) - [SysRole [id=1, roleName=管理员, enabled=1, createBy=1, createTime=Fri Apr 13 21:12:46 BOT 2018, user=null, privilegeList=null], SysRole [id=2, roleName=普通用户, enabled=1, createBy=1, createTime=Fri Apr 13 21:12:46 BOT 2018, user=null, privilegeList=null]] 2018-04-15 15:45:40,287 INFO [main] (UserMapperTest.java:121) - sqlSession close successfully 符合数据库中的记录。需求二：假设有个需求（仅仅为了说明用法）：以需求一为基础，不仅要包含sys_role的信息，同时还要包含当前用户的部分信息（不考虑嵌套的情况）比如增加查询列 user_name , user_email 这个时候该如何设置resultType呢？两种简单的方法第一种方法是在SysRole对象中直接添加userName,userEmail属性，这样仍然使用SysRole作为返回值第二种方法创建一个子类继承SysRole,将userName,userEmail作为子类的属性，将resultType的返回对象设置为子类。（这种方式适合在需要少量的额外字段时使用，如果需要其他表中大量列的时候就不合适了。）在不考虑嵌套XML配置的情况下，我们来演示下第一种方法 1.SysRole添加 SysUser属性 /** * 用户信息 */ private SysUser user; setter/getter 2.UserMapper.xml 增加 <select id="selectRoleListByUserIdMoreInfo" parameterType="Long" resultType="com.artisan.mybatis.xml.domain.SysRole"> SELECT c.id, c.role_name roleName, c.enabled, c.create_by createBy, c.create_time createTime, a.user_name as 'user.userName', a.user_email as 'user.userEmail' FROM sys_user a JOIN sys_user_role b ON a.id = b.user_id JOIN sys_role c ON b.role_id = c.id WHERE a.id = #{userId} select> 注意看查询列增加的两行 a.user_name as 'user.userName', a.user_email as 'user.userEmail' 这里设置别名的时候，使用的是“user.属性名”，user是SysRole中刚刚增加的属性，userName和userEmail是SysUser对象的属性，通过这种方式可以直接将值赋给user字段中的属性 3.单元测试 @Test public void selectRoleListByUserIdMoreInfoTest() { logger.info("selectRoleListByUserIdMoreInfoTest"); // 获取SqlSession SqlSession sqlSession = getSqlSession(); try { // 获取UserMapper接口 UserMapper userMapper = sqlSession.getMapper(UserMapper.class); // 调用selectRoleListByUserId方法，查询用户id=1的角色 List roleList = userMapper.selectRoleListByUserIdMoreInfo((long) 1); // roleList不为空 Assert.assertNotNull(roleList); // roleList > 0 Assert.assertTrue(roleList.size() > 0); logger.info(roleList); } catch (Exception e) { e.printStackTrace(); } finally { sqlSession.close(); logger.info("sqlSession close successfully "); } } 日志 2018-04-15 17:56:06,453 INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully 2018-04-15 17:56:06,457 INFO [main] (BaseMapperTest.java:29) - reader close successfully 2018-04-15 17:56:06,460 INFO [main] (UserMapperTest.java:127) - selectRoleListByUserIdMoreInfoTest 2018-04-15 17:56:06,987 DEBUG [main] (BaseJdbcLogger.java:142) - ==> Preparing: SELECT c.id, c.role_name roleName, c.enabled, c.create_by createBy, c.create_time createTime, a.user_name as 'user.userName', a.user_email as 'user.userEmail' FROM sys_user a JOIN sys_user_role b ON a.id = b.user_id JOIN sys_role c ON b.role_id = c.id WHERE a.id = ? 2018-04-15 17:56:07,085 DEBUG [main] (BaseJdbcLogger.java:142) - ==> Parameters: 1(Long) 2018-04-15 17:56:07,119 TRACE [main] (BaseJdbcLogger.java:148) - <== Columns: id, roleName, enabled, createBy, createTime, user.userName, user.userEmail 2018-04-15 17:56:07,119 TRACE [main] (BaseJdbcLogger.java:148) - <== Row: 1, 管理员, 1, 1, 2018-04-13 21:12:46.0, admin, admin@artisan.com 2018-04-15 17:56:07,124 TRACE [main] (BaseJdbcLogger.java:148) - <== Row: 2, 普通用户, 1, 1, 2018-04-13 21:12:46.0, admin, admin@artisan.com 2018-04-15 17:56:07,126 DEBUG [main] (BaseJdbcLogger.java:142) - <== Total: 2 2018-04-15 17:56:07,126 INFO [main] (UserMapperTest.java:140) - [SysRole [id=1, roleName=管理员, enabled=1, createBy=1, createTime=Fri Apr 13 21:12:46 BOT 2018, user=SysUser [id=null, userName=admin, userPassword=null, userEmail=admin@artisan.com, userInfo=null, headImg=null, createTime=null], privilegeList=null], SysRole [id=2, roleName=普通用户, enabled=1, createBy=1, createTime=Fri Apr 13 21:12:46 BOT 2018, user=SysUser [id=null, userName=admin, userPassword=null, userEmail=admin@artisan.com, userInfo=null, headImg=null, createTime=null], privilegeList=null]] 2018-04-15 17:56:07,133 INFO [main] (UserMapperTest.java:145) - sqlSession close successfully 为了更加直观，我们在Assert.assertNotNull(roleList); 加上断点，调测一下，如下图所示小结 Select元素的用法总结到此，接下来我们来开启insert元素的用法

java 日志服务 underscore 编程算法 javascript

0 人点赞