MyBatis学习存档（5）——联表查询

2023-07-17 11:51:40

之前的数据库操作都是基于一张表进行操作的，若一次查询涉及到多张表，那该如何进行操作呢？

首先明确联表查询的几个关系，大体可以分为一对一和一对多这两种情况，接下来对这两种情况进行分析：

一、建立表、添加数据、反向生成

建立teacher、classes、student三张表，并添加几条数据用于后续操作

MyBatis学习存档（5）——联表查询

MyBatis学习存档（5）——联表查询MyBatis学习存档（5）——联表查询MyBatis学习存档（5）——联表查询

进行反向生成

二、一对一联表查询

一个班级（classes）只有一个班主任（teacher），即一对一的关系

仅仅通过反向生成的文件是无法进行联表查询的，因此需要进行一些改动

package com.pojo;

public class Teacher {

    private Integer id;

    private String name;

    public Integer getId() {

        return id;

    }

    public void setId(Integer id) {

        this.id = id;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name == null ? null : name.trim();

    }

    @Override

    public String toString() {

        return "Teacher [id=" + id + ", name=" + name + "]";

    }

}

Teacher类

在Classes类中添加一个teacher的属性，删除多余的teacher_id：

package com.pojo;

public class Classes {

    private Integer classId;

    private String className;

    private Teacher teacher;    //一对一对应的teacher

    public Integer getClassId() {

        return classId;

    }

    public void setClassId(Integer classId) {

        this.classId = classId;

    }

    public String getClassName() {

        return className;

    }

    public void setClassName(String className) {

        this.className = className;

    }

    public Teacher getTeacher() {

        return teacher;

    }

    public void setTeacher(Teacher teacher) {

        this.teacher = teacher;

    }

    @Override

    public String toString() {

        return "Classes [classId=" + classId + ", className=" + className

                + ", teacher=" + teacher + "]";

    }

}

Classes类

修改接口及mapper映射文件：此处有2种方法进行查询

在resultMap节点下添加一个association节点，其配置与resultMap类似，该节点可以用于处理“一对一“的类型关系

package com.mapper;

import com.pojo.Teacher;

public interface TeacherMapper {

    Teacher selectTeacherById(Integer id);

}

TeacherMapper.java

<?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.mapper.TeacherMapper" >

  <resultMap id="BaseResultMap" type="com.pojo.Teacher" >

    <id column="id" property="id" jdbcType="INTEGER" />

    <result column="name" property="name" jdbcType="VARCHAR" />

  </resultMap>

  <sql id="Base_Column_List" >

    id, name

  </sql>

  <select id="selectTeacherById" parameterType="java.lang.Integer" resultMap="BaseResultMap">

      select

      <include refid="Base_Column_List"/>

      from teacher where id=#{id}

  </select>

</mapper>

TeacherMapper.xml

package com.mapper;

import com.pojo.Classes;

public interface ClassesMapper {

    //方法一

    Classes selectByClassId1(Integer classId);

    //方法二

    Classes selectByClassId2(Integer classId);

}

ClassesMapper.java

<?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.mapper.ClassesMapper" >

  <sql id="Base_Column_List">

      class_id,class_name,teacher_id

  </sql>

  <sql id="classesAndTeacher">

      classes.class_id,classes.class_name,teacher.id,teacher.name

  </sql>

  <!--

      方法一：直接嵌套查询

  -->

  <resultMap id="ClassesMap1" type="com.pojo.Classes" >

    <id column="class_id" property="classId" jdbcType="INTEGER" />

    <result column="class_name" property="className" jdbcType="VARCHAR" />

    <association property="teacher" javaType="com.pojo.Teacher">

        <id column="id" property="id" jdbcType="INTEGER"/>

        <result column="name" property="name" jdbcType="VARCHAR"/>

    </association>

  </resultMap>

  <select id="selectByClassId1" resultMap="ClassesMap1" parameterType="java.lang.Integer" >

    select

    <include refid="classesAndTeacher"/>

    from classes LEFT JOIN teacher ON classes.teacher_id=teacher.id where classes.class_id=#{classId}

  </select>

  <!--

      方法二：进行2次查询（可使用懒加载）

  -->

  <resultMap id="ClassesMap2" type="com.pojo.Classes" >

    <id column="class_id" property="classId" jdbcType="INTEGER" />

    <result column="class_name" property="className" jdbcType="VARCHAR" />

    <association property="teacher" javaType="com.pojo.Teacher" column="teacher_id" select="com.mapper.TeacherMapper.selectTeacherById"/>

    <!--

        若需设置懒加载，需在mybatis-config.xml中添加下列节点（开启懒加载，关闭急加载）

        <settings>

            开启懒加载

            <setting name="lazyLoadingEnabled" value="true"/>

            关闭急加载

              <setting name="aggressiveLazyLoading" value="false"/>

          </settings>

        并在association节点中添加fetchType属性并设置为lazy，如下

    -->

    <!-- <association property="teacher" javaType="com.pojo.Teacher" column="teacher_id" select="com.mapper.TeacherMapper.selectTeacherById" fetchType="lazy"/> -->

  </resultMap>

  <select id="selectByClassId2" resultMap="ClassesMap2" parameterType="java.lang.Integer">

      select

      <include refid="Base_Column_List"/>

      from classes where class_id=#{id}

  </select>

</mapper>

ClassesMapper.xml

编写测试类

package com.test;

import java.io.InputStream;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.mapper.ClassesMapper;

import com.pojo.Classes;

public class TestHasOne {

    public static void main(String[] args) throws Exception {

        //mybatis的核心配置文件

        String resource = "mybatis-config.xml";

        //使用MyBatis提供的Resources类加载mybatis的配置文件

        InputStream  is = Resources.getResourceAsStream(resource);

        //构建SqlSession的工厂

        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);

        //开启SqlSession

        SqlSession session = factory.openSession();

        //通过映射接口执行操作

        ClassesMapper mapper = session.getMapper(ClassesMapper.class);

        Classes clazz1 = mapper.selectByClassId1(1);

        System.out.println("方法一：" + clazz1);

        Classes clazz2 = mapper.selectByClassId2(1);

        System.out.println("方法二：" + clazz2);

        session.close();

    }

}

TestHasOne

输出结果如下：

MyBatis学习存档（5）——联表查询

可从日志输出中看出，方法一仅执行了1条查询语句，方法二执行了2条查询语句

三、一对多联表查询

一个班级（classes）中有许多学生（student），即一对多的关系

修改反向生成的文件

删除Student类中多余的cId属性

package com.pojo;

public class Student {

    private Integer id;

    private String name;

    public Integer getId() {

        return id;

    }

    public void setId(Integer id) {

        this.id = id;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name == null ? null : name.trim();

    }

    @Override

    public String toString() {

        return "Student [id=" + id + ", name=" + name + "]";

    }

}

Student

在Classes类中添加一个List<Student>类型的students集合

package com.pojo;

import java.util.List;

public class Classes {

    private Integer classId;

    private String className;

    private Teacher teacher;    //一对一对应的teacher

    private List<Student> students;    //一对多对应的students

    public Integer getClassId() {

        return classId;

    }

    public void setClassId(Integer classId) {

        this.classId = classId;

    }

    public String getClassName() {

        return className;

    }

    public void setClassName(String className) {

        this.className = className;

    }

    public Teacher getTeacher() {

        return teacher;

    }

    public void setTeacher(Teacher teacher) {

        this.teacher = teacher;

    }

    public List<Student> getStudents() {

        return students;

    }

    public void setStudents(List<Student> students) {

        this.students = students;

    }

    @Override

    public String toString() {

        return "Classes [classId=" + classId + ", className=" + className

                + ", teacher=" + teacher + ", students=" + students + "]";

    }

}

Classes

修改接口及mapper映射文件：

在resultMap节点下添加一个association节点，其配置与resultMap类似，该节点可以用于处理“一对多“的类型关系

package com.mapper;

import com.pojo.Student;

public interface StudentMapper {

    Student selectStudentById(Integer cId);

}

StudentMapper.java

<?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.mapper.StudentMapper" >

  <resultMap id="BaseResultMap" type="com.pojo.Student" >

    <id column="id" property="id" jdbcType="INTEGER" />

    <result column="name" property="name" jdbcType="VARCHAR" />

  </resultMap>

  <sql id="Base_Column_List" >

    id, name

  </sql>

  <select id="selectStudentById" resultMap="BaseResultMap" parameterType="java.lang.Integer">

      select

      <include refid="Base_Column_List"/>

      from student where c_id=#{cId}

  </select>

</mapper>

StudentMapper.xml

package com.mapper;

import com.pojo.Classes;

public interface ClassesMapper {

    //方法一

    Classes selectByClassId1(Integer classId);

    //方法二

    Classes selectByClassId2(Integer classId);

    //一对多查询

    Classes selectByClassId3(Integer classId);

}

ClassesMapper.java

<?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.mapper.ClassesMapper" >

  <sql id="Base_Column_List">

      class_id,class_name,teacher_id

  </sql>

  <sql id="classesAndTeacher">

      classes.class_id,classes.class_name,teacher.id,teacher.name

  </sql>

  <sql id="classesAndStudent">

      classes.class_id,classes.class_name,student.id,student.name

  </sql>

  <!--

      方法一：直接嵌套查询

  -->

  <resultMap id="ClassesMap1" type="com.pojo.Classes" >

    <id column="class_id" property="classId" jdbcType="INTEGER" />

    <result column="class_name" property="className" jdbcType="VARCHAR" />

    <association property="teacher" javaType="com.pojo.Teacher">

        <id column="id" property="id" jdbcType="INTEGER"/>

        <result column="name" property="name" jdbcType="VARCHAR"/>

    </association>

  </resultMap>

  <select id="selectByClassId1" resultMap="ClassesMap1" parameterType="java.lang.Integer" >

    select

    <include refid="classesAndTeacher"/>

    from classes LEFT JOIN teacher ON classes.teacher_id=teacher.id where classes.class_id=#{classId}

  </select>

  <!--

      方法二：进行2次查询（可使用懒加载）

  -->

  <resultMap id="ClassesMap2" type="com.pojo.Classes" >

    <id column="class_id" property="classId" jdbcType="INTEGER" />

    <result column="class_name" property="className" jdbcType="VARCHAR" />

    <association property="teacher" javaType="com.pojo.Teacher" column="teacher_id" select="com.mapper.TeacherMapper.selectTeacherById"/>

    <!--

        若需设置懒加载，需在mybatis-config.xml中添加下列节点（开启懒加载，关闭急加载）

        <settings>

            开启懒加载

            <setting name="lazyLoadingEnabled" value="true"/>

            关闭急加载

              <setting name="aggressiveLazyLoading" value="false"/>

          </settings>

        并在association节点中添加fetchType属性并设置为lazy，如下

    -->

    <!-- <association property="teacher" javaType="com.pojo.Teacher" column="teacher_id" select="com.mapper.TeacherMapper.selectTeacherById" fetchType="lazy"/> -->

  </resultMap>

  <select id="selectByClassId2" resultMap="ClassesMap2" parameterType="java.lang.Integer">

      select

      <include refid="Base_Column_List"/>

      from classes where class_id=#{id}

  </select>

  <!-- 一对多查询 -->

  <resultMap id="ClassesMap3" type="com.pojo.Classes" >

    <id column="class_id" property="classId" jdbcType="INTEGER" />

    <result column="class_name" property="className" jdbcType="VARCHAR" />

    <association property="teacher" javaType="com.pojo.Teacher" column="teacher_id" select="com.mapper.TeacherMapper.selectTeacherById"/>

    <collection property="students" column="class_id" ofType="com.pojo.Student" select="com.mapper.StudentMapper.selectStudentById"/>

  </resultMap>

  <select id="selectByClassId3" resultMap="ClassesMap3" parameterType="java.lang.Integer">

      select

      <include refid="Base_Column_List"/>

      from classes where class_id=#{id}

  </select>

</mapper>

ClassesMapper.xml

编写测试类

package com.test;

import java.io.InputStream;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.mapper.ClassesMapper;

import com.pojo.Classes;

public class TestHasMany {

    public static void main(String[] args) throws Exception{

        //mybatis的核心配置文件

        String resource = "mybatis-config.xml";

        //使用MyBatis提供的Resources类加载mybatis的配置文件

        InputStream  is = Resources.getResourceAsStream(resource);

        //构建SqlSession的工厂

        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);

        //开启SqlSession

        SqlSession session = factory.openSession();

        //通过映射接口执行操作

        ClassesMapper mapper = session.getMapper(ClassesMapper.class);

        Classes clazz = mapper.selectByClassId3(1);

        System.out.println(clazz);

    }

}

TestHasMany

输出结果如下

MyBatis学习存档（5）——联表查询

Classes [classId=1, className=一年1班, teacher=Teacher [id=2, name=教师B], students=[Student [id=1, name=张三], Student [id=2, name=李四], Student [id=3, name=王五]]]

上一篇：辗转相除法求最大公约数和最小公倍数【gcd】


下一篇：mybatis学习 十四 resultMap标签 一对一(联合查询)