Mybatis一对一和多对多查询

案例说明

一对一：
学生表和学生详情表：一个学生和一个学生详情一一对应
学生表结构：

学生详情表结构

多对多：
学生表和课程表：一个学生可以报名多个课程，一个课程也可能有多个学生报名，学生表和课程表是多对多关系
为实现多对多关联，我们使用了一个中间表，中间表的两个字段分别外键关联学生ID和课程ID。

学生表结构同上
中间表结构

课程表结构

---

代码实现

导入jar包坐标

<packaging>jar</packaging>
    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

编写Student实体类

由于要封装详情信息和课程信息，所以还需要定义对应私有成员并设置get,set方法

package domain;

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

public class Student implements Serializable {
    private Integer id;
    private String name;
    private List<Course> courses;
    private Details details;

    public Details getDetails() {
        return details;
    }

    public void setDetails(Details details) {
        this.details = details;
    }

    public List<Course> getCourses() {
        return courses;
    }

    public void setCourses(List<Course> courses) {
        this.courses = courses;
    }

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

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

编写Details实体类

package domain;

import java.io.Serializable;

public class Details implements Serializable {
    private Integer d_id;
    private Integer s_id;
    private String sex;
    private Integer age;

    public Integer getD_id() {
        return d_id;
    }

    public void setD_id(Integer d_id) {
        this.d_id = d_id;
    }

    public Integer getS_id() {
        return s_id;
    }

    public void setS_id(Integer s_id) {
        this.s_id = s_id;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Details{" +
                "id=" + d_id +
                ", s_id=" + s_id +
                ", sex='" + sex + '\'' +
                ", age=" + age +
                '}';
    }
}

编写Course实体类

由于要封装课程对应的Student，所以也需要定义对应私有对象和get,set方法

package domain;

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

public class Course implements Serializable {
    private Integer c_id;
    private String c_name;
    private List<Student> students;

    public List<Student> getStudents() {
        return students;
    }

    public void setStudents(List<Student> students) {
        this.students = students;
    }

    public String getC_name() {
        return c_name;
    }

    public void setC_name(String c_name) {
        this.c_name = c_name;
    }

    public Integer getC_id() {
        return c_id;
    }

    public void setC_id(Integer c_id) {
        this.c_id = c_id;
    }

    @Override
    public String toString() {
        return "Course{" +
                "c_id=" + c_id +
                ", c_name=" + c_name +
                '}';
    }
}

编写IStudentDao接口

package dao;

import domain.Student;

import java.util.List;

public interface IStudentDao {

    List<Student> findAll();
}

编写ICourseDao接口

package dao;

import domain.Course;

import java.util.List;

public interface ICourseDao {

    List<Course> findAll();
}

编写 SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<!-- mybatis的主配置文件 -->
<configuration>
    <typeAliases>
        <package name="domain"></package>
    </typeAliases>
    <!-- 配置环境 -->
    <environments default="mysql">
        <!-- 配置mysql的环境-->
        <environment id="mysql">
            <!-- 配置事务的类型-->
            <transactionManager type="JDBC"></transactionManager>
            <!-- 配置数据源（连接池） -->
            <dataSource type="POOLED">
                <!-- 配置连接数据库的4个基本信息 -->
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/test?useUnicode=true&amp;serverTimezone=GMT&amp;useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="qwe123"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 指定映射配置文件的位置 -->
    <mappers>
        <package name="dao"></package>
    </mappers>
</configuration>

---

重头戏来了，敲黑板！！！

编写持久层接口的映射文件 IStudentDao.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="dao.IStudentDao">
    <resultMap id="studentMap" type="student">
        <id  property="id" column="id"></id>
        <result property="name" column="s_name"></result>

        <association property="details" column="s_id" javaType="details">
            <id property="d_id" column="de_id"></id>
            <result property="s_id" column="s_id"></result>
            <result property="sex" column="sex"></result>
            <result property="age" column="age"></result>
        </association>
        
        <collection property="courses" ofType="course">
            <id property="c_id" column="c_id"></id>
            <result property="c_name" column="c_name"></result>
        </collection>

    </resultMap>
    <!--配置查询所有-->
    <select id="findAll" resultMap="studentMap">
        select s.*,c.*,d.id as de_id,d.s_id,d.sex,d.age from student as s left outer join stu_and_cou as m on s.id=m.student_id left outer join course as c on course_id=c.c_id inner join details as d on d.s_id = s.id
    </select>
</mapper>

重点在SQL语句和映射配置。
<association> </association>配置一对一映射信息

<collection> </collection>配置多对多映射信息

编写持久层接口的映射文件 ICourseDao.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="dao.ICourseDao">

    <resultMap id="courseMap" type="course">
        <id property="c_id" column="c_id"></id>
        <result property="c_name" column="c_name"></result>
        <collection property="students" ofType="student">
            <id property="id" column="id"></id>
            <result property="name" column="s_name"></result>
        </collection>
    </resultMap>
    <!--配置查询所有-->
    <select id="findAll" resultMap="courseMap">
        select s.*,c.* from student as s left outer join stu_and_cou as m on s.id=m.student_id left outer join course as c on course_id=c.c_id
    </select>
</mapper>

最后编写测试文件类

import dao.ICourseDao;
import dao.IStudentDao;
import domain.Course;
import domain.Student;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.InputStream;
import java.util.List;

public class mybatis_test {
    private InputStream in;
    private SqlSession session;

    @Before
    public void init()throws Exception{
        //读取配置文件
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //创建SqlSessionFactory工厂
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        //使用工厂生产SqlSession对象
        session = factory.openSession();
    }
    @After
    public void destroy() throws Exception
    {
        //释放资源
        session.close();
        in.close();
    }
    @Test
    public void teststudent()
    {
        IStudentDao studentDao = session.getMapper(IStudentDao.class);
        List<Student> students = studentDao.findAll();
        for (Student student : students) {
            System.out.println("=============");
            System.out.println(student);
            System.out.println(student.getDetails());
            System.out.println(student.getCourses());
        }
    }

    @Test
    public void testcourse()
    {
        ICourseDao courseDao = session.getMapper(ICourseDao.class);
        List<Course> courses = courseDao.findAll();
        for (Course cours : courses) {
            System.out.println("===========");
            System.out.println(cours);
            System.out.println(cours.getStudents());
        }
    }
}

项目文件结构如下：

实现效果

当我们查询学生表时，将该学生对应的学生详情信息和报名的课程信息也封装出来。
当我们查询课程表时，将报名该课程的学生信息也查询出来。
测试teststudent()
查询学生的效果如下：

测试testcourse()
查询课程的结果如下：

至于一对多的实现同理使用<collection> </collection>即可配置，这里就不过多赘述了。

今天的分享就到这里了，希望大家能够有所收获，欢迎关注。