Mybatis多表查询（一对一<association>、一对多<collection>、多对多<collection>）

以user、account和role为例讲解，

①一个account对应一个user（一对一）

②一个user可有多个account（一对多）

③一个user可有多个role，一个role也可以有多个user（多对多）

一，数据库表如下

account表：

user表：

role表：

二、各实体类如下：

1. user

package com.zj.pojo;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

public class User implements Serializable {
    private Integer userId;
    private String userName;
    private Date userBirthday;
    private String userSex;
    private String userAddress;
    // 一个用户对应多个账户
    private List<Account> accounts;

    public List<Account> getAccounts() {
        return accounts;
    }

    public void setAccounts(List<Account> accounts) {
        this.accounts = accounts;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public Date getUserBirthday() {
        return userBirthday;
    }

    public void setUserBirthday(Date userBirthday) {
        this.userBirthday = userBirthday;
    }

    public String getUserSex() {
        return userSex;
    }

    public void setUserSex(String userSex) {
        this.userSex = userSex;
    }

    public String getUserAddress() {
        return userAddress;
    }

    public void setUserAddress(String userAddress) {
        this.userAddress = userAddress;
    }

    @Override
    public String toString() {
        return "User{" +
                "userId=" + userId +
                ", userName='" + userName + '\'' +
                ", userBirthday=" + userBirthday +
                ", userSex='" + userSex + '\'' +
                ", userAddress='" + userAddress + '\'' +
                '}';
    }
}

2. account

package com.zj.pojo;

import java.io.Serializable;

public class Account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;
    // 一个账户对应一个用户
    private User user;

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", uid=" + uid +
                ", money=" + money +
                '}';
    }
}

3. role

package com.zj.pojo;

import java.io.Serializable;

public class Account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;
    private User user;

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", uid=" + uid +
                ", money=" + money +
                '}';
    }
}

三、映射配置

1、一对一，以查找每个账户对应的用户为例：

<?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.zj.dao.IAccountDao">
    <resultMap id="accountUserMap" type="account">
        <id column="aid" property="id"/>
        <result column="uid" property="uid"/>
        <result column="money" property="money"/>
        <!--一对一的关系映射：配置封装user的内容
               property:对应的属性， javaType：属性对应的类型-->
        <association property="user" javaType="user">
            <id column="id" property="userId"/>
            <result column="username" property="userName"/>
            <result column="birthday" property="userBirthday"/>
            <result column="sex" property="userSex"/>
            <result column="address" property="userAddress"/>
        </association>
    </resultMap>

    <select id="findAll" resultMap="accountUserMap">
        select a.id aid,a.uid,a.money，u.*
        from user u,account a
        where u.id=a.uid
    </select>
  
</mapper>

2、一对多，以查找每个用户所拥有的全部账号为例：

<?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.zj.dao.IUserDao">

    <resultMap id="userAccountMap" type="user">
        <id column="id" property="userId"/>
        <result column="username" property="userName"/>
        <result column="birthday" property="userBirthday"/>
        <result column="sex" property="userSex"/>
        <result column="address" property="userAddress"/>
        <!--配置user中的accounts集合映射
            property指定属性 ofTyoe指定集合中的元素类型-->
        <collection property="accounts" ofType="account">
            <id column="aid" property="id"/>
            <result column="uid" property="uid"/>
            <result column="money" property="money"/>
        </collection>
    </resultMap>
  
    <select id="findAll" resultMap="userAccountMap">
        select u.*, a.id aid,a.uid,a.money
        from user u left join account a
        on u.id=a.uid
    </select>
    
</mapper>

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.zj.dao.IRoleDao">
    <resultMap id="roleUserMap" type="role">
        <id column="rid" property="id"/>
        <result column="role_name" property="roleName"/>
        <result column="role_desc" property="roleDesc"/>
        <collection property="users" ofType="user">
            <id column="id" property="userId"/>
            <result column="username" property="userName"/>
            <result column="birthday" property="userBirthday"/>
            <result column="sex" property="userSex"/>
            <result column="address" property="userAddress"/>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="roleUserMap">
        select r.id rid,r.role_name,r.role_desc,u.*
        from role r
        left join user_role ur on r.id=ur.rid
        left join user u on ur.uid=u.id
    </select>
</mapper>