MyBatis的多表查询与嵌套查询

数据准备

--创建数据库和表
CREATE DATABASE `mybatis_db`;
USE `mybatis_db`;
CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(32) NOT NULL COMMENT '用户名称',
`birthday` datetime default NULL COMMENT '生日',
`sex` char(1) default NULL COMMENT '性别',
`address` varchar(256) default NULL COMMENT '地址',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- insert....
insert into `user`(`id`,`username`,`birthday`,`sex`,`address`) values (1,'子
慕','2020-11-11 00:00:00','男','北京海淀'),(2,'应颠','2020-12-12 00:00:00','男','北
京海淀');
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`ordertime` VARCHAR(255) DEFAULT NULL,
`total` DOUBLE DEFAULT NULL,
`uid` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('1', '2020-12-12', '3000', '1');
INSERT INTO `orders` VALUES ('2', '2020-12-12', '4000', '1');
INSERT INTO `orders` VALUES ('3', '2020-12-12', '5000', '2');
-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`rolename` VARCHAR(255) DEFAULT NULL,
`roleDesc` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sys_role
-- ----------------------------
INSERT INTO `sys_role` VALUES ('1', 'CTO', 'CTO');
INSERT INTO `sys_role` VALUES ('2', 'CEO', 'CEO');
-- ----------------------------
-- Table structure for sys_user_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` (
`userid` INT(11) NOT NULL,
`roleid` INT(11) NOT NULL,
PRIMARY KEY (`userid`,`roleid`),
KEY `roleid` (`roleid`),
CONSTRAINT `sys_user_role_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `sys_role`
(`id`),
CONSTRAINT `sys_user_role_ibfk_2` FOREIGN KEY (`roleid`) REFERENCES `user`
(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sys_user_role
-- ----------------------------
INSERT INTO `sys_user_role` VALUES ('1', '1');
INSERT INTO `sys_user_role` VALUES ('2', '1');
INSERT INTO `sys_user_role` VALUES ('1', '2');
INSERT INTO `sys_user_role` VALUES ('2', '2');

//创建订单实体类
public class Orders {

    private Integer id;
    private String ordertime;
    private Double total;
    private Integer uid;
    //表示当前订单属于哪个用户
    private User user;
    .
    .
    //get&set&toString
    .
    .
}

//创建用户实体类
public class User {

    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    //一个用户有多个订单
    private List<Orders> ordersList;
    //一个用户担任多个角色
    private List<User> roleList;

1.多表查询

1.1 一对一查询

• 一对一查询的需求：查询所有订单，与此同时查询出每个订单所属的用户

步骤一:在对应的OrderMapper接口中创建方法

//查询所有订单信息和订单所属的用户信息 1:1关系
public List<Orders> findAllOrderUser();

步骤二：配置OrderMapper.xml文件

<mapper namespace="com.zhoufengbin.mapper.OrderMapper">

    <!--配置映射信息-->
    <resultMap id="ordersMap" type="com.zhoufengbin.domain.Orders">
        <id property="id" column="id"/>
        <result property="ordertime" column="ordertime"/>
        <result property="total" column="total"/>
        <result property="uid" column="uid"/>
        <!--
            在一对一（多对一）使用association标签关联
            property="user" 封装实体的属性名
            javaType="user" 封装实体的属性类型
        -->
        <association property="user" javaType="com.zhoufengbin.domain.User">
            <id property="id" column="uid"/>
            <result property="username" column="username"/>
            <result property="birthday" column="birthday"/>
            <result property="sex" column="sex"/>
            <result property="address" column="address"/>
        </association>
    </resultMap>

    <!--多表查询之一对一查询：查询所有订单，与此同时查询出每个订单所属的用户
		由于订单表中没有用户表中的信息 ，需要进行关联查询，且还需要将查询到的所有字段封装到具体的实体类中，因此需要使用
		resultMap和association 属性配置映射关系
		-->
    <select id="findAllOrderUser" resultMap="ordersMap">
        SELECT * FROM orders o LEFT OUTER JOIN USER u ON o.`uid` = u.`id`;
    </select>
</mapper>

步骤三：进行单元测试

1.2 一对多查询

• 一对多查询的需求：查询所有用户，与此同时查询出该用户具有的订单

public List<User> findAllWithUserOrder();

步骤一:在对应的UserMapper接口中创建方法

//查询所有用户信息和用户拥有的订单信息 1：n 
public List<User> findAllWithUserOrder();

步骤二：配置UserMapper.xml文件

<resultMap id="userOrderMap" type="com.zhoufengbin.domain.User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="birthday" column="birthday"/>
        <result property="sex" column="sex"/>
        <result property="address" column="address"/>
        <!--
            一对多使用collection标签关联,当实体类中由集合时使用。
            property="orderList" 封装到集合的属性名
            ofType="order" 封装集合的泛型类型
        -->
        <collection property="ordersList" ofType="com.zhoufengbin.domain.Orders">
            <id property="id" column="oid"/>
            <result property="ordertime" column="ordertime"/>
            <result property="total" column="total"/>
            <result property="uid" column="uid"/>
        </collection>
    </resultMap>
	<!--一对多查询的需求：查询所有用户，与此同时查询出该用户具有的订单
	resultMap和collection 属性配置映射关系
	-->
    <select id="findAllWithUserOrder" resultMap="userOrderMap">
        SELECT *,o.id oid, ordertime, total, uid FROM USER u LEFT JOIN orders o ON u.`id` = o.`uid`;
    </select>

1.3 多对多查询

• 多对多查询的需求：查询所有用户同时查询出该用户的所有角色

步骤一:在对应的UserMapper接口中创建方法

//查询所有用户同时查询出该用户的所有角色 n:m
public List<User> findAllWithRole();

步骤一:在对应的UserMapper接口中创建方法

    <resultMap id="userRoleMap" type="com.zhoufengbin.domain.User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="birthday" column="birthday"/>
        <result property="sex" column="sex"/>
        <result property="address" column="address"/>
        <collection property="roleList" ofType="com.zhoufengbin.domain.Role">
            <id property="id" column="rid"/>
            <result property="rolename" column="rolename"/>
            <result property="roleDesc" column="roleDesc"/>
        </collection>
    </resultMap>
    
    <select id="findAllWithRole" resultMap="userRoleMap">
        SELECT u.*,r.`id` rid, r.`rolename`, r.`roleDesc` 
        FROM USER u LEFT OUTER JOIN sys_user_role ur ON u.`id`=ur.`userid`   
                    LEFT OUTER JOIN sys_role r ON r.`id`=ur.`roleid`
    </select>

2. 嵌套查询

• 嵌套查询就是将原来多表查询中的联合查询语句拆成单个表的查询，再使用MyBatis的语法嵌套在一
  起。

2.1 一对一嵌套查询

• 需求：查询一个订单，与此同时查询出该订单所属的用户

-- 1.查询说有订单信息
SELECT * FROM orders;
-- 2.根订单中的外键uid查询到用户信息，假设订单表中的uid = 1
SELECT * FROM USER WHERE id = 1
-- 3.使用MyBatis将上面两布嵌套起来

步骤一：在OrderMapper接口中创建方法

    /**
     * 查询每一个订单，与此同时查询出该订单所属的用户 1:1
     * 先查询出所有的订单信息
     * @return
     */
    public List<Orders> findAllOrders();

步骤二：在UserMapper接口中创建方法

    /**
     * 查询一个订单，与此同时查询出该订单所属的用户
     * 根据订单表中查询出的uid查询订单的用户信息
     * @param id
     * @return
     */
    public  User findOneUserById(Integer id);

步骤三：在OderMapper.xml中进行相应的配置

    <!--嵌套查询1:1：查询一个订单，与此同时查询出该订单所属的用户-->
    <resultMap id="orderUserMap" type="com.zhoufengbin.domain.Orders">
        <id property="id" column="id"/>
        <result property="ordertime" column="ordertime"/>
        <result property="total" column="total"/>
        <result property="uid" column="uid"/>
        <!--使用association属性引入其他映射文件中sql,根据username.id-->
        <!--
            property 的值为订单实体类中的User属性的属性名，应为该sql查询的结果需要封装到订单实体类中的user中。
            javaType user的全限定名。（表名user的类型为User类型）
            column 传入到引入sql语句中的参数
            select 值为引入sql的唯一标识符，表示需要引入该sql.
        -->
        <association property="user" javaType="com.zhoufengbin.domain.User" column="uid" select="com.zhoufengbin.mapper.UserOrderMapper.findOneUserById"/>
    </resultMap>
    <select id="findAllOrders" resultMap="orderUserMap" parameterType="int">
        select * from orders;
    </select>

步骤四：在UserMapper.xml中进行相应的配置,代引入的sql

    <!--根据订单表中查询出的uid查询订单的用户信息-->
    <select id="findOneUserById" parameterType="int" resultType="com.zhoufengbin.domain.User">
        select * from user where id = #{uid}
    </select>

2.2 一对多嵌套查询

• 需求：查询所有用户，与此同时查询出该用户具有的订单 1:n

步骤一：在UserMapper接口中创建查询方法

/**需求：查询所有用户，与此同时查询出该用户具有的订单
     * 1. 查询所有用户信息
     * @return
     */
    public List<User> findAllUser();

步骤二：在OrderMapper接口中创建查询方法

    /**
     * 需求：查询所有用户，与此同时查询出该用户具有的订单
     * 2. 根据用户表查询的id,查询订单信息
     * @param id
     * @return
     */
        public Orders findOneOrdersById(Integer id);
        

步骤三：在OrderMapper.xml配置相应的信息

    <!--查询所有用户，与此同时查询出该用户具有的订单 1:n-->
    <resultMap id="userOrderMap1" type="com.zhoufengbin.domain.User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="birthday" column="birthday"/>
        <result property="sex" column="sex"/>
        <result property="address" column="address"/>
        <collection property="ordersList" ofType="com.zhoufengbin.domain.Orders" column="id" select="com.zhoufengbin.mapper.OrderMapper.findOneOrdersById"/>
    </resultMap>
    <select id="findAllUser" resultMap="userOrderMap1">
        select * from USER
    </select>

步骤四：在OrderMapper.xml配置相应的信息

    <select id="findOneOrdersById" parameterType="int" resultType="com.zhoufengbin.domain.Orders">
        SELECT * FROM orders WHERE uid = #{id};
    </select>

2.3 多对多嵌套查询

• 需求：查询用户 同时查询出该用户的所有角色

-- 需求：查询用户 同时查询出该用户的所有角色
-- 查询用户信息
	SELECT * FROM USER;
-- 查询用户对应的角色信息
	SELECT r.id,r.`rolename` rolename,r.`roleDesc` roleDesc 
	FROM sys_role r LEFT OUTER JOIN sys_user_role ur ON r.id = ur.roleid
	                LEFT OUTER JOIN USER u ON u.id = ur.`userid`
	                WHERE u.`id` = 1
	                         
	SELECT r.id,r.`rolename` ,r.`roleDesc` 
	FROM sys_role r
	INNER JOIN sys_user_role ur ON r.`id` = ur.`roleid` 
	WHERE ur.`userid` = 1

步骤一：在UserMapper接口中创建方法

    /**需求：查询用户 同时查询出该用户的所有角色
     * 1.查询所用用户信息
     * @return
     */
    public List<User> findAllUser2();

步骤二：在RoleMapper接口中创建方法

    /**需求：查询用户 同时查询出该用户的所有角色
     * 2.根据用户id查询所有角色信息
     * @param id
     * @return
     */
    public List<Role> findAllRole(Integer id);

步骤二：在UserMapper.xml中配置相应的信息

    <!--嵌套查询多对多:查询所用用户信息-->
    <resultMap id="userRoleMap2" type="com.zhoufengbin.domain.User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="birthday" column="birthday"/>
        <result property="sex" column="sex"/>
        <result property="address" column="address"/>
        <collection property="roleList" ofType="com.zhoufengbin.domain.Role" column="id" select="com.zhoufengbin.mapper.RoleMapper.findAllRole"/>
    </resultMap>
    <select id="findAllUser2" resultMap="userRoleMap2" resultType="com.zhoufengbin.domain.User">
        select * from user
    </select>

步骤三：在RoleMapper.xml中配置相应的信息

//带引入的sql
    <select id="findAllRole"  parameterType="int" resultType="com.zhoufengbin.domain.Role">
        SELECT r.id,r.`rolename` ,r.`roleDesc` FROM sys_role r
                INNER JOIN sys_user_role ur ON r.`id` = ur.`roleid` WHERE ur.`userid` = #{userId}
    </select>