Mybatis多表查询

头像
短腿臭柯基
    阅读 12 分钟

    1、表关系说明

    本案例一共采用三个实体表与一个关系表：用户表、账户表、角色表、用户角色关系表，其中表之间的关系如下：

    • 一个账户只能属于一个用户（当然也可以多个账户属于一个用户，但是单从某个账户的角度来看，一个账户只能属于一个用户）
    • 一个用户可以拥有多个账户
    • 一个用户可以拥有多个角色，一个角色可以被多个用户拥有

    他们之间的关系为：

    • 账户与用户属于一对一关系
    • 用户与账户属于一对多关系
    • 用户与角色属于多对多关系

    2、需求说明

    • 一对一关系：查询账户信息，并且关联查询出账户所属的用户信息
    • 一对多关系：查询用户信息，并且关联查询出该用户下的所有账户信息
    • 多对多关系：查询用户信息，并且关联查询出该用户下的所有角色信息；查询角色信息，并且关联查询出该角色分配到的用户信息

    3、数据准备

    -- 创建数据库
CREATE DATABASE mybatis;

-- 使用数据库
USE mybatis;

-- 删除用户表
DROP TABLE IF EXISTS USER;

-- 创建用户表
CREATE TABLE `user` (
  `id` INT(8) 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 INTO `user`(`id`,`username`,`birthday`,`sex`,`address`) VALUES(41,'张三','2019-03-27 18:47:08','男','北京');
INSERT INTO `user`(`id`,`username`,`birthday`,`sex`,`address`) VALUES(42,'李四','2019-03-27 18:47:08','男','上海');
INSERT INTO `user`(`id`,`username`,`birthday`,`sex`,`address`) VALUES(43,'王五','2019-03-27 18:47:08','男','广州');

-- 删除账户表
DROP TABLE IF EXISTS `account`;

-- 创建账户表
CREATE TABLE `account` (
    `id` INT(8) NOT NULL COMMENT '编号',
    `uid` INT(8) DEFAULT NULL COMMENT '用户编号',
    `money` DOUBLE DEFAULT NULL COMMENT '金额',
    PRIMARY KEY  (`id`),
    KEY `FK_REFERENCE_ACCOUNT_UID` (`uid`),
    CONSTRAINT `FK_REFERENCE_ACCOUNT_UID` FOREIGN KEY(`uid`) REFERENCES `user`(`id`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

-- 插入账户表数据
INSERT INTO `account`(`ID`,`UID`,`MONEY`) VALUES(1,41,1000);
INSERT INTO `account`(`ID`,`UID`,`MONEY`) VALUES(2,42,1000);
INSERT INTO `account`(`ID`,`UID`,`MONEY`) VALUES(3,43,1000);

-- 删除角色表
DROP TABLE IF EXISTS `role`;

-- 创建角色表
CREATE TABLE `role` (
    `id` INT(11) NOT NULL COMMENT '编号',
    `role_name` VARCHAR(32) DEFAULT NULL COMMENT '角色名称',
    `role_desc` VARCHAR(64) DEFAULT NULL COMMENT '角色描述',
    PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

-- 插入角色表数据
INSERT  INTO `role`(`id`,`role_name`,`role_desc`) VALUES(1,'院长','管理整个学院');
INSERT  INTO `role`(`id`,`role_name`,`role_desc`) VALUES(2,'总裁','管理整个公司');
INSERT  INTO `role`(`id`,`role_name`,`role_desc`) VALUES(3,'校长','管理整个学校');

-- 删除用户角色关系表
DROP TABLE IF EXISTS `user_role`;

-- 创建用户角色关系表
CREATE TABLE `user_role` (
    `uid` INT(11) NOT NULL COMMENT '用户编号',
    `rid` INT(11) NOT NULL COMMENT '角色编号',
    PRIMARY KEY  (`uid`,`rid`),
    KEY `FK_REFERENCE_USER_ROLE_RID` (`rid`),
    KEY `FK_REFERENCE_USER_ROLE_UID` (`uid`),
    CONSTRAINT `FK_REFERENCE_USER_ROLE_RID` FOREIGN KEY(`rid`) REFERENCES `role`(`id`),
    CONSTRAINT `FK_REFERENCE_USER_ROLE_UID` FOREIGN KEY(`uid`) REFERENCES `user`(`id`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

-- 插入用户角色关系表数据
INSERT  INTO `user_role`(`uid`,`rid`) VALUES(41,1);
INSERT  INTO `user_role`(`uid`,`rid`) VALUES(42,2);
INSERT  INTO `user_role`(`uid`,`rid`) VALUES(43,3);

    4、一对一查询

    4.1 创建pojo与dao

    4.1.1 创建pojo

    4.1.1.1 创建User类
    public class User implements Serializable {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
}
    4.1.1.2 创建Account类
    public class Account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;
    // 一对一关系中，从表实体需要包含一个主表实体对象的引用
    private User user;
}

    4.1.2 创建dao

    public interface AccountMapper {
    /**
     * 查询所有账户，并且返回账户所属的用户信息
     * @return
     */
    List<Account> findAll();
}

    4.2 创建Mybatis配置文件

    4.2.1 数据库配置文件

    jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/mybatis
jdbc.username = root
jdbc.password = root

    4.2.2 主配置文件

    <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="datasource.properties"></properties>
    
    <typeAliases>
        <package name="com.itcast.pojo"/>
    </typeAliases>
    
    <environments default="development">
        <environment id="development">
            <transactionManager type="jdbc"></transactionManager>
            <dataSource type="pooled">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <package name="com.itcast.dao"/>
    </mappers>
</configuration>

    4.2.3 映射配置文件

    <?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.itcast.dao.AccountMapper">
    <resultMap id="accountMap" type="com.itcast.pojo.Account">
        <id property="id" column="aid"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <association property="user" javaType="com.itcast.pojo.User">
            <id property="id" column="id"></id>
            <result property="username" column="username"></result>
            <result property="birthday" column="birthday"></result>
            <result property="sex" column="sex"></result>
            <result property="address" column="address"></result>
        </association>
    </resultMap>

    <select id="findAll" resultMap="accountMap">
        SELECT a.`id` AS aid, a.uid, a.money, u.*
        FROM user u, account a
        WHERE a.`uid` = u.`id`
    </select>
</mapper>

    4.3 创建测试类

    /**
 * 测试查询所有方法
 */
@Test
public void testFindAll() {
    List<Account> accountList = this.accountMapper.findAll();
    for (Account account : accountList) {
        System.out.println(account);
    }
}

    4.4 测试结果

    Account{id=1, uid=41, money=1000.0, user=User{id=41, username='张三', birthday=Wed Mar 27 18:47:08 CST 2019, sex='男', address='北京'}}
Account{id=2, uid=42, money=1000.0, user=User{id=42, username='李四', birthday=Wed Mar 27 18:47:08 CST 2019, sex='男', address='上海'}}
Account{id=3, uid=43, money=1000.0, user=User{id=43, username='王五', birthday=Wed Mar 27 18:47:08 CST 2019, sex='男', address='广州'}}

    5、一对多查询

    5.1 创建pojo与dao

    5.1.1 创建pojo

    5.1.1.1 创建User类
    public class User implements Serializable {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    // 一对多关系中，主表实体中需要包含从表实体的集合引用
    private List<Account> accounts;
}
    5.1.1.2 创建Account类
    public class Account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;
}

    5.1.2 创建dao

    public interface UserMapper {
    /**
     * 查询所用户，并且获取该用户下所有的账户信息
     * @return
     */
    List<User> findAll();
}

    5.2 创建Mybatis配置文件

    5.2.1 数据库配置文件

    jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/mybatis
jdbc.username = root
jdbc.password = root

    5.2.2 主配置文件

    <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="datasource.properties"></properties>
    
    <typeAliases>
        <package name="com.itcast.pojo"/>
    </typeAliases>
    
    <environments default="development">
        <environment id="development">
            <transactionManager type="jdbc"></transactionManager>
            <dataSource type="pooled">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <package name="com.itcast.dao"/>
    </mappers>
</configuration>

    5.2.3 映射配置文件

    <?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.itcast.dao.UserMapper">
    <resultMap id="userMap" type="com.itcast.pojo.User">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="birthday" column="birthday"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>
        <collection property="accounts" javaType="java.util.List" ofType="com.itcast.pojo.Account">
            <id property="id" column="aid"></id>
            <result property="uid" column="uid"></result>
            <result property="money" column="money"></result>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="userMap">
        SELECT u.*, a.`ID` AS aid, a.`UID`, a.`MONEY`
        FROM USER u LEFT OUTER JOIN account a
        ON u.`id` = a.`UID`;
    </select>
</mapper>

    5.3 创建测试类

    /**
 * 测试查询所有方法
 */
@Test
public void testFindAll() {
    List<User> userList = this.userMapper.findAll();
    for (User user : userList) {
        System.out.println(user);
    }
}

    5.4 测试结果

    User{id=41, username='张三', birthday=Wed Mar 27 18:47:08 CST 2019, sex='男', address='北京', accounts=[Account{id=1, uid=41, money=1000.0}]}
User{id=42, username='李四', birthday=Wed Mar 27 18:47:08 CST 2019, sex='男', address='上海', accounts=[Account{id=2, uid=42, money=1000.0}]}
User{id=43, username='王五', birthday=Wed Mar 27 18:47:08 CST 2019, sex='男', address='广州', accounts=[Account{id=3, uid=43, money=1000.0}]}

    6、多对多查询

    6.1 创建pojo与dao

    6.1.1 创建pojo

    6.1.1.1 创建User类
    public class User implements Serializable {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    // 多对多关系中，一个用户可以拥有多个角色
    private List<Role> roleList;
}
    6.1.1.2 创建Role类
    public class Role implements Serializable {
    private Integer id;
    private String roleName;
    private String roleDesc;
    // 多对多关系中，一个角色可以赋予多个用户
    private List<User> userList;
}

    6.1.2 创建dao

    public interface UserMapper {
    /**
     * 查询所用户，并且关联查询出用户所用户的角色信息
     * @return
     */
    List<User> findAll();
}

    6.2 创建Mybatis配置文件

    6.2.1 数据库配置文件

    jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/mybatis
jdbc.username = root
jdbc.password = root

    6.2.2 主配置文件

    <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="datasource.properties"></properties>
    
    <typeAliases>
        <package name="com.itcast.pojo"/>
    </typeAliases>
    
    <environments default="development">
        <environment id="development">
            <transactionManager type="jdbc"></transactionManager>
            <dataSource type="pooled">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <package name="com.itcast.dao"/>
    </mappers>
</configuration>

    6.2.3 映射配置文件

    <?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.itcast.dao.UserMapper">
    <resultMap id="userMap" type="com.itcast.pojo.User">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="birthday" column="birthday"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>
        <collection property="roleList" javaType="java.util.List" ofType="com.itcast.pojo.Role">
            <id property="id" 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 ur.`RID` = r.`ID`
    </select>
</mapper>

    6.3 创建测试类

    /**
 * 测试查询所有方法
 */
@Test
public void testFindAll() {
    List<User> userList = this.userMapper.findAll();
    for (User user : userList) {
        System.out.println(user);
    }
}

    6.4 测试结果

    User{id=41, username='张三', birthday=Wed Mar 27 18:47:08 CST 2019, sex='男', address='北京', roleList=[Role{id=1, roleName='院长', roleDesc='管理整个学院'}]}
User{id=42, username='李四', birthday=Wed Mar 27 18:47:08 CST 2019, sex='男', address='上海', roleList=[Role{id=2, roleName='总裁', roleDesc='管理整个公司'}]}
User{id=43, username='王五', birthday=Wed Mar 27 18:47:08 CST 2019, sex='男', address='广州', roleList=[Role{id=3, roleName='校长', roleDesc='管理整个学校'}]}
    mybatis
    短腿臭柯基
    9 声望2 粉丝
    « 上一篇
    MyBatis动态SQL
    下一篇 »
    MyBatis延迟加载

    引用和评论

    推荐阅读
    头像
    MySQL索引详解

    短腿臭柯基阅读 1.5k

    头像
    深入理解 MyBatis 延迟加载机制与实现原理

    异常君阅读 384

    0 条评论
    得票最新