MyBatis学习总结[3]-多表查询

一对一

方法一：嵌套查询语句

1.现在实体类里添加对应表的实体类

package me.model;

import java.util.List;

public class User {
    private int id;
    private String name;
    private String age;
    //对应表的实体类
    private Car car;
    .
    .
    .
    getter,setter
}

2.在对应的Mapper.xml文件的 resultMap 标签下添加 association 标签

<?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="me.dao.UserDao">

  <resultMap type="User" id="User">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="age" column="age"/>
    <!-- 对应表，select:另一条语句的id，column:根据主表的哪个属性来执行select -->
    <association property="car" column="id" select="getCar" />
  </resultMap>

  <select id="selectById" parameterType="int" resultType="User">
    select * from users where id = #{id}
  </select>
  <!-- 一对一 -->
  <!-- 嵌套语句 -->
  <select id="selectUser"  resultMap="User">
    select * from users 
  </select>
  <select id="getCar" resultMap="Car">
    select * from car where u_id=#{id} 
  </select>

</mapper>

3.创建相关借口并调用，结果如下，

可见分成两条语句来执行。

方法二：嵌套结果

1.实体类

package me.model;

public class Car {
    private int id;
    private String name;
    private String money;
    private int age;
    private int uId; 
    <!-- 对应实体 --> 
    private User user;
    .
    .
    .
    getter,setter

}

2.Mapper

<?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="me.dao.UserDao">
  <resultMap type="Car" id="Car">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="money" column="money"/>
    <result property="age" column="age"/>
    <result property="uId" column="u_id"/>
    <!-- 嵌套结果 -->
    <association property="user" javaType="User">
        <id property="id" column="u_id"/>
        <result property="name" column="u_name"/>
        <result property="age" column="u_age"/>
    </association>
  </resultMap>
  <!-- 当两张表里有相同字段时，要为相同字段取别名，否则同名字段只有一个不为空 -->
  <select id="selectCar" resultMap="Car">
    select 
        c.*,
        u.id as u_id,
        u.name as u_name,
        u.age as u_age
        from car c,users u where u.id=c.u_id
  </select> 
</mapper>

3.结果

一对多

方法一：嵌套查询语句

1.实体类

package me.model;

import java.util.List;

public class User {
    private int id;
    private String name;
    private String age;
    //对应表的实体类
    private Car car;
    //对应多方的实体
    private List<Dog> dogs;
    .
    .
    .
    getter,setter
}

2.mapper

<?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="me.dao.UserDao">

  <resultMap type="User" id="User">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="age" column="age"/>
    <association property="car" column="id" select="getCar" />
    <!-- 与一对一类似 -->
    <collection property="dogs" column="id" select="selectDogsWithUid"/>
  <resultMap type="Dog" id="Dog">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="sex" column="sex"/>
        <result property="uId" column="u_id"/>
  </resultMap>
  <!-- 一对多 -->
  <!-- 嵌套语句 -->
  <select id="selectDogs"  resultMap="User">
    select * from users 
  </select>
  <select id="selectDogsWithUid"  resultMap="Dog">
    select * from dogs where u_id=#{id} 
  </select>
</mapper>

3.结果

方法二：嵌套结果

Mappeer

<?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="me.dao.UserDao">

  <resultMap type="User" id="User">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="age" column="age"/>
    <association property="car" column="id" select="getCar" />
    <!-- 与一对一类似 -->
    <collection property="dogs" ofType="Dog">
        <id property="id" column="d_id"/>
        <result property="name" column="d_name"/>
        <result property="age" column="d_age"/>
        <result property="sex" column="d_sex"/>
        <result property="uId" column="d_u_id"/>
    </collection> 
  </resultMap>
  <!-- 嵌套结果 -->
  <select id="selectUserWithDog" resultMap="User">
        select 
            u.*,
            d.id as d_id,
            d.name as d_name,
            d.age as d_age,
            d.sex as d_sex,
            d.u_id as d_u_id
            from users u,dogs d where u.id=d.u_id;
  </select>

</mapper>