Mybatis关联查询<association> 和 <collection>

一、背景

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 

转载于:https://www.cnblogs.com/Laymen/p/6406793.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值