一、背景
1.在系统中一个用户存在多个角色,那么如何在查询用户的信息时同时把他的角色信息查询出来啦?
2.用户pojo:
public class SysUser { private Long id; private String userName; private String password; private String nickName; private String salt; private List<SysRole> roleList;
3.数据库
二、实现
如果一个对象中的属性是另个对象,就会出现对象与对象的中的关系,及是一对一,多对多,还是一对多等等,从上面的需求中如果从user出发来分析,那么一个用户可以有多个对象,那么就存在了一对多的关系,可以直接使用 mybatis的中的collection标签;
如果是一对一的关系的话可以使用association标签,使用方式大相径庭。
<resultMap id="RoleBaseResultMap" type="com.layman.study.mybatis.pojo.SysRole"> <id column="id" property="id" jdbcType="BIGINT"/> <result column="role_name" property="roleName" jdbcType="VARCHAR"/> <result column="role_code" property="roleCode" jdbcType="VARCHAR"/> <result column="status" property="status" jdbcType="INTEGER"/> <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/> </resultMap> <resultMap id="UserBaseResultMap" type="com.layman.study.mybatis.pojo.SysUser"> <id column="id" property="id" jdbcType="BIGINT"/> <result column="user_name" property="userName" jdbcType="VARCHAR"/> <result column="password" property="password" jdbcType="VARCHAR"/> <result column="nick_name" property="nickName" jdbcType="VARCHAR"/> <result column="salt" property="salt" jdbcType="VARCHAR"/> <collection column="id" property="roleList" javaType="list" select="selectSysRoleByUserId"/> </resultMap> <select id="selectSysUserAll" resultMap="UserBaseResultMap"> SELECT * FROM sys_user </select> <select id="selectSysUserByPrimaryKey" parameterType="int" resultMap="UserBaseResultMap"> SELECT * FROM sys_user WHERE id = #{id} </select> <select id="selectSysRoleByUserId" parameterType="int" resultMap="RoleBaseResultMap"> select * from sys_role sr join sys_user_role sur on sr.id = sur.role_id WHERE sur.user_id = #{user_id}; </select>
在上面的场景下解释collection标签:
<collection column="id" property="roleList" javaType="list" select="selectSysRoleByUserId"/>
1.column:是指定需要使用那个字段的值去查询关联的角色信息,这里是使用id,就是用户id,对应查询接口selectSysUserAll中的语句SELECT * FROM sys_user
2.property:这个属性的值就是我们user对象中的roleList字段属性,用来封装查询出来的角色信息
3.javaType:指定property指定的属性在java中的类型
4.select:指定使用按个查询语句去查询角色信息
三、测试
@Test public void test2() throws Exception { List<SysUser> userList = sysUserService.selectSysUserAll(); LOGGER.info(objectMapper.writeValueAsString(userList)); }
结果:
[
{
"id": 1,
"userName": "Layman",
"password": "737b0be0e65ddbc20664b377a003c7bd",
"nickName": "聖殿罗刹",
"salt": "SWLwSE",
"roleList": [
{
"id": 1,
"roleName": "超级管理员",
"roleCode": "ADMIN",
"status": 1,
"createTime": 1479889105000
},
{
"id": 2,
"roleName": "普通管理员",
"roleCode": "GENERAL_MANAGER",
"status": 1,
"createTime": 1479889154000
}
]
},
{
"id": 2,
"userName": "leo",
"password": "412a104c131e929751242998542159ab",
"nickName": "爱婷",
"salt": "MUffQ/TBU+V98iBHD0XPwg==",
"roleList": [
{
"id": 2,
"roleName": "普通管理员",
"roleCode": "GENERAL_MANAGER",
"status": 1,
"createTime": 1479889154000
}
]
}
]
引出(N+1的问题):我们通过DEBUG方式查看打出的日志发现,mybatis其实发出了多条查询语句去查询,查询出一个用户然后拿着这个用户的id去查询对应的角色信息
17-02-16 16:55:19,477 [main] DEBUG transaction.SpringManagedTransaction - JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@62ee45c0] will not be managed by Spring 2017-02-16 16:55:19,495 [main] DEBUG SysUserDao.selectSysUserAll - ==> Preparing: SELECT * FROM sys_user 2017-02-16 16:55:19,569 [main] DEBUG SysUserDao.selectSysUserAll - ==> Parameters: 2017-02-16 16:55:19,595 [main] DEBUG SysUserDao.selectSysRoleByUserId - ====> Preparing: select * from sys_role sr join sys_user_role sur on sr.id = sur.role_id WHERE sur.user_id = ?; 2017-02-16 16:55:19,596 [main] DEBUG SysUserDao.selectSysRoleByUserId - ====> Parameters: 1(Integer) 2017-02-16 16:55:19,599 [main] DEBUG SysUserDao.selectSysRoleByUserId - <==== Total: 2 2017-02-16 16:55:19,606 [main] DEBUG SysUserDao.selectSysRoleByUserId - ====> Preparing: select * from sys_role sr join sys_user_role sur on sr.id = sur.role_id WHERE sur.user_id = ?; 2017-02-16 16:55:19,606 [main] DEBUG SysUserDao.selectSysRoleByUserId - ====> Parameters: 2(Integer) 2017-02-16 16:55:19,608 [main] DEBUG SysUserDao.selectSysRoleByUserId - <==== Total: 1 2017-02-16 16:55:19,609 [main] DEBUG SysUserDao.selectSysUserAll - <== Total: 2
所以如果是以这种方式查询的话,数据一旦多了就会出现极大的性能问题
四、改进
<resultMap id="UserBaseResultMap2" type="com.layman.study.mybatis.pojo.SysUser"> <id column="id" property="id" jdbcType="BIGINT"/> <result column="user_name" property="userName" jdbcType="VARCHAR"/> <result column="password" property="password" jdbcType="VARCHAR"/> <result column="nick_name" property="nickName" jdbcType="VARCHAR"/> <result column="salt" property="salt" jdbcType="VARCHAR"/> <collection property="roleList" javaType="list" ofType="com.layman.study.mybatis.pojo.SysRole"> <id column="role_id" property="id"/> <result column="role_name" property="roleName" jdbcType="VARCHAR"/> <result column="role_code" property="roleCode" jdbcType="VARCHAR"/> <result column="status" property="status" jdbcType="INTEGER"/> <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/> </collection> </resultMap> <select id="selectSysUserAll2" resultMap="UserBaseResultMap2"> select u.*,r.id role_id,r.role_name,r.role_code,r.status,r.create_time from sys_user u join sys_user_role ur on u.id = ur.user_id join sys_role r on ur.role_id = r.id; </select>
改进的方式为,直接使用一条语句直接关联查询出想要的信息:
只是在配置resultmap的时候稍有改变,但是查询的结果是一样的:
<resultMap id="UserBaseResultMap2" type="com.layman.study.mybatis.pojo.SysUser"> <id column="id" property="id" jdbcType="BIGINT"/> <result column="user_name" property="userName" jdbcType="VARCHAR"/> <result column="password" property="password" jdbcType="VARCHAR"/> <result column="nick_name" property="nickName" jdbcType="VARCHAR"/> <result column="salt" property="salt" jdbcType="VARCHAR"/> <collection property="roleList" javaType="list" ofType="com.layman.study.mybatis.pojo.SysRole"> <id column="role_id" property="id"/> <result column="role_name" property="roleName" jdbcType="VARCHAR"/> <result column="role_code" property="roleCode" jdbcType="VARCHAR"/> <result column="status" property="status" jdbcType="INTEGER"/> <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/> </collection> </resultMap>
查询日志:
2017-02-16 17:28:21,370 [main] DEBUG transaction.SpringManagedTransaction - JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@3e74aff4] will not be managed by Spring 2017-02-16 17:28:21,381 [main] DEBUG SysUserDao.selectSysUserAll2 - ==> Preparing: select u.*,r.id role_id,r.role_name,r.role_code,r.status,r.create_time from sys_user u join sys_user_role ur on u.id = ur.user_id join sys_role r on ur.role_id = r.id; 2017-02-16 17:28:21,462 [main] DEBUG SysUserDao.selectSysUserAll2 - ==> Parameters: 2017-02-16 17:28:21,492 [main] DEBUG SysUserDao.selectSysUserAll2 - <== Total: 3