Mybatis的多表关联查询（多对多）

刺骨的言语ヽ痛彻心扉 2022-10-29 10:28 2阅读 0赞

### Mybatis的多表关联查询（多对多） ###

*  项目目录结构
 *  实现 Role 到 User 多对多
 *   *  业务要求
     *  用户与角色的关系模型
     *  编写角色实体类
     *  编写 Role 持久层接口
     *  实现的 SQL 语句
     *  编写映射文件
     *  测试代码
 *  实现 User 到 Role 的多对多
 *   *  业务要求
     *  编写用户实体类
     *  编写 User持久层接口
     *  实现的 SQL 语句
     *  编写映射文件
     *  测试代码

mybatis中的多表查询：
    
    	示例：用户和角色
    		一个用户可以有多个角色
    		一个角色可以赋予多个用户
    	步骤：
    		1、建立两张表：用户表，角色表
    			让用户表和角色表具有多对多的关系。需要使用中间表，中间表中包含各自的主键，在中间表中是外键。
    		2、建立两个实体类：用户实体类和角色实体类
    			让用户和角色的实体类能体现出来多对多的关系
    			各自包含对方一个集合引用
    		3、建立两个配置文件
    			用户的配置文件
    			角色的配置文件
    		4、实现配置：
    			当我们查询用户时，可以同时得到用户所包含的角色信息
    			当我们查询角色时，可以同时得到角色的所赋予的用户信息

# 项目目录结构 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70]

# 实现 Role 到 User 多对多 #

> **多对多关系其实我们看成是双向的一对多关系。**

## 业务要求 ##

> **需求：  
> 当我们查询角色时，可以同时得到角色的所赋予的用户信息。  
> 分析：  
> 查询角色我们需要用到Role表，但角色分配的用户的信息我们并不能直接找到用户信息，而是要通过中间表(USER\_ROLE 表)才能关联到用户信息。**

## 用户与角色的关系模型 ##

> **用户与角色的多对多关系模型如下：**  
> ![在这里插入图片描述][20210212234550797.png]

**角色表：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70 1]  
**用户表：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70 2]  
**用户角色中间表：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70 3]

## 编写角色实体类 ##

**Role：**

package com.keafmd.domain;
    
    import java.io.Serializable;
    import java.util.List;
    
    /** * Keafmd * * @ClassName: Role * @Description: 角色实体类 * @author: 牛哄哄的柯南 * @date: 2021-02-12 16:45 */
    public class Role implements Serializable { 
    
        private Integer roleId;
        private String roleName;
        private String roleDesc;
    
        //多对多的关系映射
        private List<User> users;
    
        public List<User> getUsers() { 
            return users;
        }
    
        public void setUsers(List<User> users) { 
            this.users = users;
        }
    
        public Integer getRoleId() { 
            return roleId;
        }
    
        public void setRoleId(Integer roleId) { 
            this.roleId = roleId;
        }
    
        public String getRoleName() { 
            return roleName;
        }
    
        public void setRoleName(String roleName) { 
            this.roleName = roleName;
        }
    
        public String getRoleDesc() { 
            return roleDesc;
        }
    
        public void setRoleDesc(String roleDesc) { 
            this.roleDesc = roleDesc;
        }
    
        @Override
        public String toString() { 
            return "Role{" +
                    "roleId=" + roleId +
                    ", roleName='" + roleName + '\'' +
                    ", roleDesc='" + roleDesc + '\'' +
                    '}';
        }
    }

## 编写 Role 持久层接口 ##

**IRoleDao：**

package com.keafmd.dao;
    import com.keafmd.domain.Role;
    import java.util.List;
    
    /** * Keafmd * * @ClassName: IRoleDao * @Description: * @author: 牛哄哄的柯南 * @date: 2021-02-12 19:01 */
    
    public interface IRoleDao { 
    
        /** * 查询所有角色 * @return */
        List<Role> findAll();
    
    }

## 实现的 SQL 语句 ##

select u.*,r.id as rid ,r.role_name ,r.role_desc from role r  
    left outer join user_role ur on r.id = ur.rid  
    left outer join user u on u.id=ur.uid

**执行结果：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70 4]

> **注意：sql语句换行的时候最好在每行的末尾或开头添加空格，这样可以防止合并成一行时发生错误。**

## 编写映射文件 ##

**IRoleDao.xml：**

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.keafmd.dao.IRoleDao">
    
        
        <resultMap id="roleMap" type="role">
            <id property="roleId" column="rid"></id>
            <result property="roleName" column="role_name"></result>
            <result property="roleDesc" column="role_desc"></result>
            <collection property="users" ofType="user">
                <id column="id" property="id"></id>
                <result column="username" property="username"></result>
                <result column="address" property="address"></result>
                <result column="sex" property="sex"></result>
                <result column="birthday" property="birthday"></result>
            </collection>
        </resultMap>
    
        
        <select id="findAll" resultMap="roleMap">
            select u.*,r.id as rid ,r.role_name ,r.role_desc from role r
            left outer join user_role ur on r.id = ur.rid
            left outer join user u on u.id=ur.uid
        </select>
    
    </mapper>

## 测试代码 ##

**RoleTest：**

package com.keafmd.test;
    
    import com.keafmd.dao.IRoleDao;
    import com.keafmd.dao.IUserDao;
    import com.keafmd.domain.Role;
    import com.keafmd.domain.User;
    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.junit.After;
    import org.junit.Before;
    import org.junit.Test;
    
    import java.io.InputStream;
    import java.util.List;
    
    /** * Keafmd * * @ClassName: MybatisTest * @Description: 测试类，测试crud操作 * @author: 牛哄哄的柯南 * @date: 2021-02-08 15:24 */
    public class RoleTest { 
    
        private InputStream in;
        private SqlSession sqlsession;
        private IRoleDao roleDao;
    
        @Before // 用于在测试方法执行前执行
        public void init()throws Exception{ 
            //1.读取配置文件,生成字节输入流
            in = Resources.getResourceAsStream("SqlMapConfig.xml");
            //2.创建SqlSessionFactory工厂
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            SqlSessionFactory factory = builder.build(in);
            //3.使用工厂生产SqlSession对象
            sqlsession = factory.openSession(); //里面写个true，下面每次就不用了写 sqlsession.commit(); 了
            //4.使用SqlSession创建Dao接口的代理对象
            roleDao = sqlsession.getMapper(IRoleDao.class);
        }
    
        @After // 用于在测试方法执行后执行
        public void destory() throws Exception{ 
            //提交事务
            sqlsession.commit();
            //6.释放资源
            sqlsession.close();
            in.close();
        }
    
    
    
        /** * 查询所有 * @throws Exception */
        @Test
        public void testFindAll() { 
            List<Role> roles = roleDao.findAll();
            for (Role role : roles) { 
                System.out.println("--------每个角色的信息---------");
                System.out.println(role);
                System.out.println(role.getUsers());
               
            }
        }
    
    }

**运行结果：**

2021-02-13 00:05:47,481 349    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Opening JDBC Connection
    2021-02-13 00:05:47,784 652    [           main] DEBUG source.pooled.PooledDataSource  - Created connection 1027007693.
    2021-02-13 00:05:47,784 652    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@3d36e4cd]
    2021-02-13 00:05:47,791 659    [           main] DEBUG om.keafmd.dao.IRoleDao.findAll  - ==>  Preparing: select u.*,r.id as rid ,r.role_name ,r.role_desc from role r left outer join user_role ur on r.id = ur.rid left outer join user u on u.id=ur.uid
    2021-02-13 00:05:47,842 710    [           main] DEBUG om.keafmd.dao.IRoleDao.findAll  - ==> Parameters: 
    2021-02-13 00:05:47,869 737    [           main] DEBUG om.keafmd.dao.IRoleDao.findAll  - <==      Total: 4
    --------每个角色的信息---------
    Role{ roleId=1, roleName='院长', roleDesc='管理整个学院'}
    [User{ id=41, username='老王', sex='男', address='北京', birthday=Tue Feb 27 17:47:08 CST 2018}, User{ id=45, username='新一', sex='男', address='北京', birthday=Sun Mar 04 12:04:06 CST 2018}]
    --------每个角色的信息---------
    Role{ roleId=2, roleName='总裁', roleDesc='管理整个公司'}
    [User{ id=41, username='老王', sex='男', address='北京', birthday=Tue Feb 27 17:47:08 CST 2018}]
    --------每个角色的信息---------
    Role{ roleId=3, roleName='校长', roleDesc='管理整个学校'}
    []
    2021-02-13 00:05:47,871 739    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@3d36e4cd]
    2021-02-13 00:05:47,872 740    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@3d36e4cd]
    2021-02-13 00:05:47,872 740    [           main] DEBUG source.pooled.PooledDataSource  - Returned connection 1027007693 to pool.
    
    Process finished with exit code 0

# 实现 User 到 Role 的多对多 #

## 业务要求 ##

> **需求：  
> 当我们查询用户时，可以同时得到用户所包含的角色信息。**  
> **分析：**  
> **相比上面的实现 Role 到 User 多对多，主要变化就是sql语句的变化。**

## 编写用户实体类 ##

**User：**

package com.keafmd.domain;
    
    import java.io.Serializable;
    import java.util.Date;
    import java.util.List;
    
    /** * Keafmd * * @ClassName: User * @Description: * @author: 牛哄哄的柯南 * @date: 2021-02-08 15:16 */
    
    public class User implements Serializable { 
        private Integer id;
        private String username;
        private String sex;
        private String address;
        private Date birthday;
    
        //多对多的关系映射，一个用户可以具备多个角色
        private List<Role> roles;
    
        public List<Role> getRoles() { 
            return roles;
        }
    
        public void setRoles(List<Role> roles) { 
            this.roles = roles;
        }
    
        public Integer getId() { 
            return id;
        }
    
        public void setId(Integer id) { 
            this.id = id;
        }
    
        public String getUsername() { 
            return username;
        }
    
        public void setUsername(String username) { 
            this.username = username;
        }
    
        public String getSex() { 
            return sex;
        }
    
        public void setSex(String sex) { 
            this.sex = sex;
        }
    
        public String getAddress() { 
            return address;
        }
    
        public void setAddress(String address) { 
            this.address = address;
        }
    
        public Date getBirthday() { 
            return birthday;
        }
    
        public void setBirthday(Date birthday) { 
            this.birthday = birthday;
        }
    
        @Override
        public String toString() { 
            return "User{" +
                    "id=" + id +
                    ", username='" + username + '\'' +
                    ", sex='" + sex + '\'' +
                    ", address='" + address + '\'' +
                    ", birthday=" + birthday +
                    '}';
        }
    }

## 编写 User持久层接口 ##

**IUserDao：**

package com.keafmd.dao;
    
    import com.keafmd.domain.User;
    
    import java.util.List;
    
    /** * Keafmd * * @ClassName: IUserDao * @Description: 用户的持久层接口 * @author: 牛哄哄的柯南 * @date: 2021-02-06 19:29 */
    
    public interface IUserDao { 
        /** * 查询所有用户，同时获取到用户下所有账户的信息 * @return */
        List<User> findAll();
    
    }

## 实现的 SQL 语句 ##

select u.*,r.id as rid ,r.role_name ,r.role_desc from user u 
       left outer join user_role ur on u.id = ur.uid 
       left outer join role r on r.id=ur.rid

**执行结果：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70 5]

## 编写映射文件 ##

**IUserDao.xml：**

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.keafmd.dao.IUserDao">
    
        
        <resultMap id="userMap" type="user">
            <id property="id" column="id"></id>
            <result property="username" column="username"></result>
            <result property="address" column="address"></result>
            <result property="sex" column="sex"></result>
            <result property="birthday" column="birthday"></result>
            
            <collection property="roles" ofType="role">
                <id property="roleId" column="rid"></id>
                <result property="roleName" column="role_name"></result>
                <result property="roleDesc" column="role_desc"></result>
            </collection>
    
        </resultMap>
    
        
        <select id="findAll" resultMap="userMap">
            select u.*,r.id as rid ,r.role_name ,r.role_desc from user u
            left outer join user_role ur on u.id = ur.uid
            left outer join role r on r.id=ur.rid
    
        </select>
    
    </mapper>

## 测试代码 ##

**UserTest ：**

package com.keafmd.test;
    
    import com.keafmd.dao.IUserDao;
    import com.keafmd.domain.User;
    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.junit.After;
    import org.junit.Before;
    import org.junit.Test;
    
    import java.io.InputStream;
    import java.util.List;
    
    /** * Keafmd * * @ClassName: MybatisTest * @Description: 测试类，测试crud操作 * @author: 牛哄哄的柯南 * @date: 2021-02-08 15:24 */
    public class UserTest { 
    
        private InputStream in;
        private SqlSession sqlsession;
        private IUserDao userDao;
    
        @Before // 用于在测试方法执行前执行
        public void init()throws Exception{ 
            //1.读取配置文件,生成字节输入流
            in = Resources.getResourceAsStream("SqlMapConfig.xml");
            //2.创建SqlSessionFactory工厂
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            SqlSessionFactory factory = builder.build(in);
            //3.使用工厂生产SqlSession对象
            sqlsession = factory.openSession(); //里面写个true，下面每次就不用了写 sqlsession.commit(); 了
            //4.使用SqlSession创建Dao接口的代理对象
            userDao = sqlsession.getMapper(IUserDao.class);
        }
    
        @After // 用于在测试方法执行后执行
        public void destory() throws Exception{ 
            //提交事务
            sqlsession.commit();
            //6.释放资源
            sqlsession.close();
            in.close();
        }
    
    
    
        /** * 查询所有 * @throws Exception */
        @Test
        public void testFindAll() { 
            List<User> users = userDao.findAll();
            for (User user : users) { 
                System.out.println("--------每个用户的信息---------");
                System.out.println(user);
                System.out.println(user.getRoles());
            }
        }
    
    }

**运行结果：**

2021-02-13 00:17:32,971 422    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Opening JDBC Connection
    2021-02-13 00:17:33,275 726    [           main] DEBUG source.pooled.PooledDataSource  - Created connection 1027007693.
    2021-02-13 00:17:33,275 726    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@3d36e4cd]
    2021-02-13 00:17:33,280 731    [           main] DEBUG om.keafmd.dao.IUserDao.findAll  - ==>  Preparing: select u.*,r.id as rid ,r.role_name ,r.role_desc from user u left outer join user_role ur on u.id = ur.uid left outer join role r on r.id=ur.rid
    2021-02-13 00:17:33,319 770    [           main] DEBUG om.keafmd.dao.IUserDao.findAll  - ==> Parameters: 
    2021-02-13 00:17:33,343 794    [           main] DEBUG om.keafmd.dao.IUserDao.findAll  - <==      Total: 10
    --------每个用户的信息---------
    User{ id=41, username='老王', sex='男', address='北京', birthday=Tue Feb 27 17:47:08 CST 2018}
    [Role{ roleId=1, roleName='院长', roleDesc='管理整个学院'}, Role{ roleId=2, roleName='总裁', roleDesc='管理整个公司'}]
    --------每个用户的信息---------
    User{ id=42, username='update', sex='男', address='XXXXXXX', birthday=Mon Feb 08 19:37:31 CST 2021}
    []
    --------每个用户的信息---------
    User{ id=43, username='小二王', sex='女', address='北京', birthday=Sun Mar 04 11:34:34 CST 2018}
    []
    --------每个用户的信息---------
    User{ id=45, username='新一', sex='男', address='北京', birthday=Sun Mar 04 12:04:06 CST 2018}
    [Role{ roleId=1, roleName='院长', roleDesc='管理整个学院'}]
    --------每个用户的信息---------
    User{ id=50, username='Keafmd', sex='男', address='XXXXXXX', birthday=Mon Feb 08 15:44:01 CST 2021}
    []
    --------每个用户的信息---------
    User{ id=51, username='update DAO', sex='男', address='XXXXXXX', birthday=Tue Feb 09 11:31:38 CST 2021}
    []
    --------每个用户的信息---------
    User{ id=52, username='Keafmd DAO', sex='男', address='XXXXXXX', birthday=Tue Feb 09 11:29:41 CST 2021}
    []
    --------每个用户的信息---------
    User{ id=53, username='Keafmd laset insertid 1', sex='男', address='XXXXXXX', birthday=Fri Feb 12 20:53:46 CST 2021}
    []
    --------每个用户的信息---------
    User{ id=54, username='Keafmd laset insertid 2 auto', sex='男', address='XXXXXXX', birthday=Fri Feb 12 21:02:12 CST 2021}
    []
    2021-02-13 00:17:33,345 796    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@3d36e4cd]
    2021-02-13 00:17:33,346 797    [           main] DEBUG ansaction.jdbc.JdbcTransaction  - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@3d36e4cd]
    2021-02-13 00:17:33,346 797    [           main] DEBUG source.pooled.PooledDataSource  - Returned connection 1027007693 to pool.
    
    Process finished with exit code 0

> **以上就是Mybatis的多表关联查询（多对多）的全部内容。**

**看完如果对你有帮助，感谢点赞支持！  
如果你是电脑端的话，看到右下角的 “一键三连” 了吗，没错点它\[哈哈\]**  
![在这里插入图片描述][20210115230629703.gif_pic_center]

**加油！**

**共同努力！**

**Keafmd**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70]: /images/20221024/4be2eda2a9204f0b8dc9582addac5216.png
[20210212234550797.png]: /images/20221024/4666cb35f9d7476bbbfe2de639d8e932.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70 1]: /images/20221024/4d09069d488b4c97a9c28132ea8744dc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70 2]: /images/20221024/cfefe6864fed4fb389f0c43f94ab0f9a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70 3]: /images/20221024/3d3b3c933c824b68aa544fc661d69c08.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70 4]: /images/20221024/65ef5d10ee2347dbab2c7627a301628c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70 5]: /images/20221024/8480f07001114d3f915174bf98c99ccc.png
[20210115230629703.gif_pic_center]: /images/20221024/8a7a7d1c94e148a9892eba9067120f29.png