Mybatis关联查询

• 实体间的关系（拥有 has、属于 belong）

  • OneToOne：一对一关系（account ←→ user）

  • OneToMany：一对多关系（user ←→ account）

  • ManyToMany：多对多关系（user ←→ role）

• 什么是关联查询

  当访问关系的一方时，如果需要查看与之关联的另一方数据，则必须使用表链接查询，将查询到的另一方数据，保存在本方的属性中

• 关联查询的语法

  指定“一方”关系时（对象），使用< association javaType="" >

  指定“多方”关系时（集合），使用< collection ofType="" >

• 1.一对一查询

  需求：查询账户信息，关联查询用户信息。

  分析：因为一个账户信息只能供某个用户使用，所以从查询账户信息出发关联查询用户信息为一对一查询。

• 1.1 pojo

• public class Account implements Serializable {
  
      private Integer id;
      private Integer uid;
      private Double money;
      //加入User类的对象作为Account类的一个属性
      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 +
                  ", user=" + user +
                  '}';
      }
  }

  1.2 mapper

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

  1.3mapper.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.by.dao.AccountDao">
      <!-- 结果映射 -->
      <resultMap type="account" id="findAllResultMap">
          <id column="aid" property="id"/>
          <result column="uid" property="uid"/>
          <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="findAllResultMap">
          select u.*,a.id as aid,a.uid,a.money from account a,user u where a.uid =u.id
      </select>
  </mapper>

  1.4 测试

•  @Test
      public void testOneToOne() {
          AccountDao accountDao = sqlSession.getMapper(AccountDao.class);
          List<Account> accountList = accountDao.findAll();
          for (Account ac : accountList) {
              System.out.println(ac);
          }
      }

  2.一对多查询

• 需求：查询所有用户信息及用户关联的账户信息。

  分析：用户信息和他的账户信息为一对多关系，并且查询过程中如果用户没有账户信息，此时也要将用户信息查询出来，此时左外连接查询比较合适

  2.1 pojo

• package com.by.pojo;
  
  import java.io.Serializable;
  import java.util.Date;
  import java.util.List;
  
  public class User implements Serializable {
      private Integer id;
      private String username;
      private String password;
      private Date birthday;
      private String sex;
      private String address;
      //加入List<Account>存储用户所拥有的账户
      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;
      }
  
      public String getPassword() {
          return password;
      }
  
      public void setPassword(String password) {
          this.password = password;
      }
  
      public List<Account> getAccounts() {
          return accounts;
      }
  
      public void setAccounts(List<Account> accounts) {
          this.accounts = accounts;
      }
  
      @Override
      public String toString() {
          return "User{" +
                  "id=" + id +
                  ", username='" + username + '\'' +
                  ", password='" + password + '\'' +
                  ", birthday=" + birthday +
                  ", sex='" + sex + '\'' +
                  ", address='" + address + '\'' +
                  ", accounts=" + accounts +
                  '}';
      }
  }

  2.2 mapper

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

  2.3 mapper.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.by.dao.UserDao">
      <resultMap type="user" id="findAllResultMap">
          <id column="id" property="id"></id>
          <result column="username" property="username"/>
          <result column="address" property="address"/>
          <result column="sex" property="sex"/>
          <result column="birthday" property="birthday"/>
          <!-- collection 是用于建立一对多中集合属性的对应关系
          ofType 用于指定集合元素的数据类型
          -->
          <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="findAllResultMap">
        select u.*,a.id as aid ,a.uid,a.money 
        from user u left join account a on u.id =a.uid
      </select>
  </mapper>

  2.4测试

•   @Test
      public void testOneToMany() {
          UserDao userDao = sqlSession.getMapper(UserDao.class);
          List<User> userList = userDao.findAll();
          for(User user : userList){
              System.out.println(user);
          }
      }

  3.多对多查询

• 需求：查询角色及角色赋予的用户信息。

  分析：一个用户可以拥有多个角色，一个角色也可以赋予多个用户，用户和角色为双向的一对多关系，多对多关系其实我们看成是双向的一对多关系。

  

  user(uid, username)：王贺、万通

  user_role(uid, rid)

  role(rid, )：校长、老师、学生

• 3.1 pojo

• public class Role {
      private Integer id;
      private String roleName;
      private String roleDesc;
      //加入List<User> users存储角色赋予的用户信息
      private List<User> users;
  
      public Integer getId() {
          return id;
      }
  
      public void setId(Integer id) {
          this.id = id;
      }
  
      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;
      }
  
      public List<User> getUsers() {
          return users;
      }
  
      public void setUsers(List<User> users) {
          this.users = users;
      }
  
      @Override
      public String toString() {
          return "Role{" +
                  "id=" + id +
                  ", roleName='" + roleName + '\'' +
                  ", roleDesc='" + roleDesc + '\'' +
                  ", users=" + users +
                  '}';
      }
  }

  3.2 mapper

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

  3.3 mapper.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.by.dao.RoleDao">
      <!--定义 role 表的 ResultMap-->
      <resultMap id="findAllResultMap" type="Role">
          <id property="id" column="rid"></id>
          <result property="roleName" column="role_name"></result>
          <result property="roleDesc" column="role_desc"></result>
          <collection property="users" ofType="user">
              <id column="id" property="id"></id>
              <result column="username" property="username"></result>
              <result column="address" property="address"></result>
              <result column="sex" property="sex"></result>
              <result column="birthday" property="birthday"></result>
          </collection>
      </resultMap>
      <!--查询所有-->
      <select id="findAll" resultMap="findAllResultMap">
          select r.id as 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 u.id = ur.uid
      </select>
  </mapper>

  3.4 测试

•  @Test
      public void testManyToMany() {
          RoleDao roleDao = sqlSession.getMapper(RoleDao.class);
          List<Role> roleList = roleDao.findAll();
          for(Role role : roleList){
              System.out.println(role);
          }
      }