Mybatis的多种查询方式

一、一对一查询

从表实体要包含主表实体的对象引用
在这里建立Account和User的关系

package com.itheima.domain;

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

    public User getUser() {
        return user;
    }

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

    private 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 +
                '}';
    }
}

一对一关系需使用assocaition标签

 <!-- 定义封装account和user的resultMap -->
    <resultMap id="accountUserMap" type="account">
        <id property="id" column="aid"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <!-- 一对一的关系映射，配置封装user的内容 -->
        <association property="user" column="uid" javaType="user">
            <id property="id" column="id"></id>
            <result property="username" column="username"></result>
            <result property="address" column="address"></result>
            <result property="sex" column="sex"></result>
            <result property="birthday" column="birthday"></result>
        </association>

    </resultMap>
     <select id="findAllAccountUserOne2One" resultMap="accountUserMap">
        select u.*,a.id as aid,a.uid,a.money from account a,user u where u.id=a.uid;
    </select>

在测试类中打印getUser即可得到关联的User数据

    @Test
    //查找AccountUser
    public void findALlAccountUserOne2One() {
        List<Account> tests = accountDao.findAllAccountUserOne2One();
        for (Account test : tests) {
            System.out.println(test);
            System.out.println(test.getUser());
        }
    }

二、一对多查询操作

主表实体包含从表实体的集合引用
查询一个user下对应的所有account,在user实体类中添加account属性

package com.itheima.domain;

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

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

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

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

    private List<Account> 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;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

一对多关系需使用collection标签，properties对应实体类属性，ofType对应每一个元素的实体

 <resultMap id="userAccountMap" type="user">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="address" column="address"></result>
        <result property="sex" column="sex"></result>
        <result property="birthday" column="birthday"></result>
        <!-- 配置user对象中accounts集合的映射 -->
        <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="findUserAccounts" resultMap="userAccountMap">
        select * from user u left join account a on u.id=a.id
    </select>

 //查询用户对应的所有账户
    @Test
    public void findUserAccounts() {
        List<User> useraccounts = userDao.findUserAccounts();
        for (User useraccount : useraccounts) {
            System.out.println(useraccount);
            System.out.println(useraccount.getAccounts());
        }
    }

三、多对多查询

在用户和角色表往往都是多对多的关系
在Role实体类中，我们需要建立User的List集合，一个角色对应多个用户

package com.itheima.domain;

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

public class Role implements Serializable {
    private Integer roleId;
    private String roleName;
    private String roleDesc;
    private List<User> users;

    public List<User> getUsers() {
        return users;
    }

    public void setUsers(List<User> users) {
        this.users = users;
    }

    public Integer getRoleId() {
        return roleId;
    }

    public void setRoleId(Integer roleId) {
        this.roleId = roleId;
    }

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public String getRoleDesc() {
        return roleDesc;
    }

    public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }

    @Override
    public String toString() {
        return "Role{" +
                "roleId=" + roleId +
                ", roleName='" + roleName + '\'' +
                ", roleDesc='" + roleDesc + '\'' +
                ", users=" + users +
                '}';
    }
}

IRoleDao的映射配置文件修改如下

<?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.IRoleDao">
    <resultMap id="roleMap" type="role">
        <id property="roleId" column="rid"></id>
        <result property="roleName" column="role_name"></result>
        <result property="roleDesc" column="role_desc"></result>
        <collection property="users" ofType="user">
            <id property="id" column="id"></id>
            <result property="username" column="username"></result>
            <result property="address" column="address"></result>
            <result property="sex" column="sex"></result>
            <result property="birthday" column="birthday"></result>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="roleMap">
        select u.*,r.id as rid,r.role_name,role_desc from role r
        left join user_role ur on r.id=ur.rid
        left join user u on u.id = ur.uid
    </select>

</mapper>

测试类代码

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

同理一个用户对应多个角色，代码修改与一个角色对应多个用户一致