Java框架_Mybatis_day06_多表关联之一对一、一对多查询

目录

十、Mybatis 多表关联查询之一对多关系

10.1 创建账户表，添加数据

10.2 查询所有账户

10.3 一对一查询，查询账号同时关联用户（方案一）

10.4 一对一查询，查询账号同时关联用户（方案二）

10.5  一对多查询，查询用户同时查询账号的集合

---

十、Mybatis 多表关联查询之一对多关系

10.1 创建账户表，添加数据

create table `account` (
  `id` int(11) not null comment '编号',
  `uid` int(11) default NULL comment '用户编号',
  `money` double default NULL comment '金额',
  primary key  (`id`),
  KEY `FK_Reference_8` (`uid`),
  constraint `FK_Reference_8` foreign key (`uid`) references `user` (`id`)
) engine=innodb default charset=utf8;

insert into `account`(`id`,`uid`,`money`) values (1,46,1000),(2,48,1000),(3,46,2000);

10.2 查询所有账户

这里的操作与以前的查询所有用户相同，相关代码可以查询 Java框架_Mybatis_day02 

10.3 一对一查询，查询账号同时关联用户（方案一）

方案一：将用户和账号一起封装成一个类，将这个类作为查询结果的返回值

AccountUser.java

package com.cpz.domain;

import java.util.Date;

public class AccountUser extends Account{
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    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;
    }

    @Override
    public String toString() {
        return "AccountUser{" +
                "account='" + super.toString() + '\'' +
                "，username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

Account.xml

<select id="findAccountUser" resultType="accountUser">
    select a.*,u.username,u.birthday,u.sex,u.address from account a,user u where a.uid = u.id
</select>

TestUser.java

@Test
public void findAccountUser(){
    List<AccountUser> list = accountDao.findAccountUser();
    for (AccountUser accountUser : list) {
        System.out.println(accountUser);
    }
}

AccountDao.java

List<AccountUser> findAccountUser();

10.4 一对一查询，查询账号同时关联用户（方案二）

方案二：

           使用resultMap，定义专门的resultMap用于映射一对一查询结果。

           通过面向对象的(一对一)关系可以得知，我们可以在Account类中加入一个User类的对象来代表这个账户是哪个用户的。

Account.java

package com.cpz.domain;

public class Account {
    private Integer id;
    private Integer uid;
    private Double money;

    private User user;

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", uid=" + uid +
                ", money=" + money +
                ", 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;
    }
}

AccountDao.java

List<Account> findAccountUser2();

AccountDao.xml

    <!-- association：多对一，一对一的映射（放的是对象类型）
             property=""：表示实体中的对象属性名称
             javaType=""：表示实体中的对象属性的类型（支持别名）
     -->
    <resultMap id="accountMap" type="account">
        <id column="aid" property="id"></id>
        <result column="uid" property="uid"></result>
        <result column="money" property="money"></result>
        <association property="user" javaType="user">
            <id column="id" property="id"></id>
            <result column="username" property="username"></result>
            <result column="birthday" property="birthday"></result>
            <result column="sex" property="sex"></result>
            <result column="address" property="address"></result>
        </association>
    </resultMap>

    <select id="findAccountUser2" resultMap="accountMap">
        select u.*,a.id aid,a.uid,a.money from account a,user u where a.uid = u.id
    </select>

测试类

    @Test
    public void findByCondition2(){
        List<Account> list = accountDao.findAccountUser2();
        for (Account account : list) {
            System.out.println(account);
        }
    }

10.5  一对多查询，查询用户同时查询账号的集合

       用户信息和他的账户信息为一对多关系，并且查询过程中如果用户没有账户信息，此时也要将用户信息查询出来，只不过没有信息的字段用null表示，我们想到了左外连接查询比较合适 。

User.java

package com.cpz.domain;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class User {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    List<Account> accounts = new ArrayList<Account>();
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                ", accounts=" + accounts +
                '}';
    }

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

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


    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 Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    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;
    }

}

UserDao.java

public List<User> findUserAccount();

UserDao.xml

    <!--
        resultMap：结果集的映射
            collection：一对多，多对多，集合的映射
                property="accounts"：集合的属性名称
                ofType=""：集合属性存储的数据类型
    -->    
    <resultMap id="userMap" type="user">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="birthday" property="birthday"></result>
        <result column="sex" property="sex"></result>
        <result column="address" property="address"></result>
        <collection property="accounts" ofType="account">
            <id column="aid" property="id"></id>
            <result column="uid" property="uid"></result>
            <result column="money" property="money"></result>
        </collection>
    </resultMap>

    <select id="findUserAccount" resultMap="userMap">
        select u.*,a.id aid,a.uid,a.money from user u left outer join account a on u.id = a.uid
    </select>

TestUser.java

    @Test
    public void findUserAccount(){
        List<User> list = userDao.findUserAccount();
        for (User user : list) {
            System.out.println(user);
        }
    }