MyBatis笔记（三）

多表查询

本次案例采用用户、账户和角色的模型来分析MyBatis多表关系。
User（一）对Account（多），关系如下：

User（多）对Role（多），关系如下：

1.一对一查询

需求：
关联查询一个账户及其用户信息。
定义专门的resultMap用于映射一对一查询结果。
Account.java

@Data
public class Account {
    private Integer id;
    private Integer userId;
    private Double money;
    private User user;
}

AccountDao.java

public interface AccountDao {
    List<Account> findAll();
}

AccountDao.xml

<mapper namespace="com.itheima.dao.AccountDao">
    <resultMap id="accountMap" type="account">
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="money" property="money"/>
        <association property="user" javaType="User">
            <id column="id" property="id"/>
            <result column="username" property="username"/>
            <result column="sex" property="sex"/>
            <result column="birthday" property="birthday"/>
            <result column="address" property="address"/>
        </association>
    </resultMap>
    <select id="findAll" resultMap="accountMap">
        select a.*, u.* from account a, user u where a.user_id = u.id;
    </select>
</mapper>

AccountTests.java

@Test
public void testFindAll() {
    List<Account> accounts = accountDao.findAll();
    for (Account account : accounts) {
        System.out.println(account);
        System.out.println(account.getUser());
    }
}

2.一对多查询

需求：查询所有用户并查出其所有账户
User.java添加私有属性

private List<Account> accounts;

UserDao.java不变，使用其findAll方法
UserDao.xml中添加resultMap并修改findAll

<resultMap id="userMap" type="User">
    <id column="id" property="id"/>
    <result column="username" property="username"/>
    <result column="address" property="address"/>
    <result column="sex" property="sex"/>
    <result column="birthday" property="birthday"/>
    <collection property="accounts" ofType="Account">
        <id column="aid" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="money" property="money"/>
    </collection>
</resultMap>

<select id="findAll" resultMap="userMap">
    select u.*, a.id as aid, a.user_id, a.money from user u left outer join account a on u.id = a.user_id;
</select>

<collection>定义了用户关联的账户信息，表示关联查询结果集。
property=""：关联查询的结果集存储在User对象的哪个属性；
ofType=""：指定关联查询的结果集中的对象类型即List中的对象类型，此处可以使用别名，也可以使用全限定名。
这里要给a.id取个别名aid，避免与user的id重复。
UserTest.java修改testSelectAll()方法

    @Test
    public void testSelectAll() {
        List<User> users = userDao.findAll();
        for (User user : users) {
            System.out.println("======用户======");
            System.out.println(user);
            System.out.println(user.getAccounts());
        }
    }

3.多对多查询

需求：查询所有的角色并显示对应的用户信息。
实体类Role.java

@Data
public class Role {
    private Integer roleId;
    private String roleName;
    private String roleDesc;
    private List<User> users;
}

持久层接口RoleDao.java

public interface RoleDao {
    List<Role> findAll();
}

映射文件RoleDao.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.itheima.dao.RoleDao">
    <resultMap id="roleMap" type="role">
        <id property="roleId" column="rid"/>
        <result property="roleName" column="role_name"/>
        <result property="roleDesc" column="role_desc"/>
        <collection property="users" ofType="user">
            <id column="id" property="id"/>
            <result column="username" property="username"/>
            <result column="address" property="address"/>
            <result column="sex" property="sex"/>
            <result column="birthday" property="birthday"/>
        </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.role_id
        left outer join user u on u.id = ur.user_id
    </select>
</mapper>

测试类RoleTests.java

    @Test
    public void testFindAll() {
        List<Role> roles = roleDao.findAll();
        for(Role role : roles) {
            System.out.println(role);
        }
    }