[Java知识库] Mybatis学习（三）—— Mybatis一对多与多对一

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> Java知识库 -> Mybatis学习（三）—— Mybatis一对多与多对一 -> 正文阅读

[Java知识库]Mybatis学习（三）—— Mybatis一对多与多对一

在进行sql查询的时候，如果是联表查询，那么简单的resultType就不能满足我们的需要了。

MyBatis多对一查询

场景

多个学生被一个老师教，就是多对一，我们在查询学生信息的时候需要同时查到这个学生的老师的信息。

sql文件

数据库名为mybatis，其中包含两个表student和teacher

student表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `sid` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `tid` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '张三', '1');
INSERT INTO `student` VALUES ('2', '李四', '2');
INSERT INTO `student` VALUES ('3', '王五', '2');
INSERT INTO `student` VALUES ('4', '赵六', '3');

SET FOREIGN_KEY_CHECKS = 1;

在这里插入图片描述

teacher表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `tid` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '大老师');
INSERT INTO `teacher` VALUES ('2', '二老师');
INSERT INTO `teacher` VALUES ('3', '三老师');

SET FOREIGN_KEY_CHECKS = 1;

在这里插入图片描述

实现多对一查询项目

创建Maven项目

创建一个普通的maven项目，在pom.xml中导入依赖，加上防止资源过滤问题的配置。

<!--导入依赖-->
<dependencies>
    <!--mysqlq驱动-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.12</version>
    </dependency>
    <!--mybatis-->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.4</version>
    </dependency>

    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13</version>
        <scope>test</scope>
    </dependency>
</dependencies>

<build>
    <resources>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>false</filtering>
        </resource>
        <resource>
            <directory>src/main/resources</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>false</filtering>
        </resource>
    </resources>
</build>

项目结构

在这里插入图片描述

实体类

先创建两个实体类，Student和Teacher，实体类Student中包含了一个属性Teacher，我们的任务就是让这个teacher能够被查询出来并且封装到Student中。

Student.java

package com.zhang.pojo;

/**
 * @title: Student
 * @Author 张宜强
 * @Date: 2021/2/26 15:44
 * @Effect:
 */
public class Student {
    private String sid;
    private String name;
    private Teacher teacher;

    public Student() {
    }

    public Student(String sid, String name, Teacher teacher) {
        this.sid = sid;
        this.name = name;
        this.teacher = teacher;
    }

    public String getSid() {
        return sid;
    }

    public void setSid(String sid) {
        this.sid = sid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }

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

Teacher.java

package com.zhang.pojo;

/**
 * @title: Teacher
 * @Author 张宜强
 * @Date: 2021/2/26 15:44
 * @Effect:
 */
public class Teacher {
    private String tid;
    private String name;

    public Teacher() {
    }

    public Teacher(String tid, String name) {
        this.tid = tid;
        this.name = name;
    }

    public String getTid() {
        return tid;
    }

    public void setTid(String tid) {
        this.tid = tid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

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

mapper接口

由于我们现在要实现的是多对一的查询，所以只要编写Student的接口就好。

接口里面有两个方法，根据id查询学生信息和查询所有学生的信息

package com.zhang.mapper;

import com.zhang.pojo.Student;

import java.util.List;

/**
 * @title: StudentMapper
 * @Author 张宜强
 * @Date: 2021/2/26 15:58
 * @Effect:
 */
public interface StudentMapper {

    public List<Student> findAllStudent();

    public Student findStudentById(String id);
}

工具类

GetSqlSession.java工具类，用于获取sqlSession。

package com.zhang.util;

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 java.io.IOException;
import java.io.InputStream;

/**
 * @title: GetSqlSession
 * @Author 张宜强
 * @Date: 2021/1/13 21:18
 * @Effect:
 */
public class GetSqlSession {

    static InputStream inputStream = null;
    static {
        try {
            String resource = "mybatis_config.xml";
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    static SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    static public SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }

}

Mybatis文件配置

db.properties

配置mysql的基本信息

driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&characterEncoding=UTF-8&useSSL=false
username = root
password = 123456

mybatis_config.xml

Mybatis的环境配置，配置mapper。

<?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">

<configuration>
    <!-- 环境配置 -->

    <properties resource="db.properties">
    </properties>

    <typeAliases>
        <package name="com.zhang.pojo"/>
    </typeAliases>


    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <!-- 数据库连接相关配置 ,这里动态获取config.properties文件中的内容-->
            <dataSource type="POOLED">
                <property name="driver" value="${driver}" />
                <property name="url" value= "${url}" />
                <property name="username" value="${username}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>
    <!-- mapping文件路径配置 -->
    <mappers>
        <mapper resource="mapper/StudentMapper.xml"/>
    </mappers>

</configuration>

StudentMapper.xml

之前写的都是基本的配置和固定的写法，如何从数据库中查询出数据并且封装到实体类中是StudentMapper.xml的任务，这也是多对一查询的核心所在。

第一种写法

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zhang.mapper.StudentMapper">

    <select id="findStudentById" resultMap="StudentTeacher">
        SELECT
            s.sid,
            s.NAME sname,
            s.tid,
            t.NAME tname
        FROM
            student s,
            teacher t
        WHERE
            s.sid = #{id} 
        and
            s.tid = t.tid
    </select>

    <resultMap id="StudentTeacher" type="Student">
        <result property="sid" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="tid" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>

    <select id="findAllStudent" resultMap="StudentTeacher">
        SELECT
            s.sid,
            s.NAME sname,
            s.tid,
            t.NAME tname
        FROM
            student s,
            teacher t
        WHERE
            s.tid = t.tid
    </select>

</mapper>

这一种写法是比较好理解的写法，首先，我们要从数据库中查询一个学生的基本信息，我们就按照那个方式写就好了，其中sid是要从接口中与select id 同名的方法传过来。

SELECT
	s.sid,
	s.NAME sname,
	s.tid,
	t.NAME tname
FROM
	student s,
	teacher t
WHERE
	s.tid = t.tid
and
	s.sid = #{id}

那么这个查询的结果肯定是要用resultMap来接收了，我们创建一个StudentTeacher来做结果集映射。

首先是Student的两个基本属性，sid，name对应实体类属性，sid，sname对应数据库查询的结果，我们给他们一一对应。

<result property="sid" column="sid"/>
<result property="name" column="sname"/>

然后，实体类Student中的属性是一个类Teacher，这里我们使用association标签来标志这个属性是一个类，然后再将属性和数据库中查到的字段对应。

这样resultMap就写好了。我们的根据id查询学生也写好了。

<resultMap id="StudentTeacher" type="Student">
    <result property="sid" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="Teacher">
        <result property="tid" column="tid"/>
        <result property="name" column="tname"/>
    </association>
</resultMap>

那根据id查询写好了，查询所有学生的语句也是一样的，不过是不用查询sid了而已。

<select id="findAllStudent" resultMap="StudentTeacher">
    SELECT
        s.sid,
        s.NAME sname,
        s.tid,
        t.NAME tname
    FROM
        student s,
        teacher t
    WHERE
        s.tid = t.tid
</select>

根据上面的内容整合一下StudentMapper.xml

StudentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zhang.mapper.StudentMapper">

    <select id="findStudentById" resultMap="StudentTeacher">
        SELECT
            s.sid,
            s.NAME sname,
            s.tid,
            t.NAME tname
        FROM
            student s,
            teacher t
        WHERE
            s.sid = #{id} and
            s.tid = t.tid
    </select>

    <resultMap id="StudentTeacher" type="Student">
        <result property="sid" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="tid" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>

    <select id="findAllStudent" resultMap="StudentTeacher">
        SELECT
            s.sid,
            s.NAME sname,
            s.tid,
            t.NAME tname
        FROM
            student s,
            teacher t
        WHERE
            s.tid = t.tid
    </select>

</mapper>

测试第一种方法

创建测试类MyatisTest.java

import com.zhang.mapper.StudentMapper;
import com.zhang.mapper.TeacherMapper;
import com.zhang.pojo.Student;
import com.zhang.pojo.Teacher;
import com.zhang.util.GetSqlSession;
import junit.framework.TestCase;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

/**
 * @title: MybatisTest
 * @Author 张宜强
 * @Date: 2021/2/26 16:07
 * @Effect:
 */

public class MybatisTest {

    @Test
    public void Test1(){
        SqlSession sqlSession = GetSqlSession.getSqlSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

        Student studentById = studentMapper.findStudentById("1");
        System.out.println(studentById);

        List<Student> allStudent = studentMapper.findAllStudent();
        for (Student student : allStudent) {
            System.out.println(student);
        }
        sqlSession.close();
    }

}

查询结果：
在这里插入图片描述

第二种方法

我们先根据id查出学生的所有信息，光有这还不够，我们还是用resultMap来做结果集映射。

<select id="findStudentById" resultMap="StudentTeacher">
    select * from student where sid = #{id}
</select>

首先是Student的基本属性，我们先给配置上

<id property="sid" column="sid"/>
<result property="name" column="name"/>

然后依旧是Teacher对象，看下面这句配置:property代表Student中的属性，javaType指定类名，那么这个对象的属性从何而来，后面的select="findTeacherById"指定查询的id，column代表传入参数。

<association property="teacher" column="tid" javaType="Teacher" select="findTeacherById"/>

上面指定了查询的id，但是我们现在还没有这个查询，所以我们再写一个查询teacher的sql。这个#{id}即为上面的tid，其实这里写的并不规范，应该写成tid = #{tid}，这里是因为传入的只有一个参数，所以这个地方的变量不论怎么写都可以被成功注入。

<select id="findTeacherById" resultType="Teacher">
    select * from teacher where tid = #{id}
</select>

最后整理StudentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zhang.mapper.StudentMapper">

    <select id="findStudentById" resultMap="StudentTeacher">
        select * from student where sid = #{id}
    </select>

    <resultMap id="StudentTeacher" type="Student">
        <id property="sid" column="sid"/>
        <result property="name" column="name"/>
        <association property="teacher" column="tid" javaType="Teacher" select="findTeacherById"/>

    </resultMap>

    <select id="findTeacherById" resultType="Teacher">
        select * from teacher where tid = #{id}
    </select>

    <select id="findAllStudent" resultMap="StudentTeacher">
        select * from student
    </select>

</mapper>

测试第二种方法

测试之后可以发现输出了查询的结果
在这里插入图片描述

MyBatis一对多查询

场景

上面说过了多个学生对于一个老师为多对一，那么反过来一个老师对于对于多个学生就是一对多。

实体类

这里我们需要修改一下实体类，老师的属性下有一个List，是改老师下的所有学生

package com.zhang.pojo;

/**
 * @title: Student
 * @Author 张宜强
 * @Date: 2021/2/26 15:44
 * @Effect:
 */
public class Student {
    private String sid;
    private String name;
    private String tid;

    public Student() {
    }

    public Student(String sid, String name, String tid) {
        this.sid = sid;
        this.name = name;
        this.tid = tid;
    }

    public String getSid() {
        return sid;
    }

    public void setSid(String sid) {
        this.sid = sid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getTid() {
        return tid;
    }

    public void setTid(String tid) {
        this.tid = tid;
    }

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

Teacher.java

package com.zhang.pojo;

import java.util.List;

/**
 * @title: Teacher
 * @Author 张宜强
 * @Date: 2021/2/26 15:44
 * @Effect:
 */
public class Teacher {
    private String tid;
    private String name;
    private List<Student> student;

    public Teacher() {
    }

    public Teacher(String tid, String name, List<Student> student) {
        this.tid = tid;
        this.name = name;
        this.student = student;
    }

    public String getTid() {
        return tid;
    }

    public void setTid(String tid) {
        this.tid = tid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Student> getStudent() {
        return student;
    }

    public void setStudent(List<Student> student) {
        this.student = student;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "tid='" + tid + '\'' +
                ", name='" + name + '\'' +
                ", student=" + student +
                '}';
    }
}

mapper接口

TeacherMapper.java

package com.zhang.mapper;

import com.zhang.pojo.Teacher;

/**
 * @title: TeacherMapper
 * @Author 张宜强
 * @Date: 2021/2/26 15:58
 * @Effect:
 */
public interface TeacherMapper {

    public Teacher findTeacherById(String id);

}

mybatis配置

在mybatis中配置一下TeacherMapper.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">

<configuration>
    <!-- 环境配置 -->

    <properties resource="db.properties">
    </properties>

    <typeAliases>
        <package name="com.zhang.pojo"/>
    </typeAliases>


    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <!-- 数据库连接相关配置 ,这里动态获取config.properties文件中的内容-->
            <dataSource type="POOLED">
                <property name="driver" value="${driver}" />
                <property name="url" value= "${url}" />
                <property name="username" value="${username}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>
    <!-- mapping文件路径配置 -->
    <mappers>
        <mapper resource="mapper/TeacherMapper.xml" />
    </mappers>

</configuration>

TeacherMapper.xml

核心还是TeacherMapper.xml，依旧是两种方法。

第一种方法

第一种方法先把sql写好，然后resultMap来做结果集映射

SELECT
    s.sid,
    s.NAME sname,
    s.tid,
    t.NAME tname
FROM
    student s,
    teacher t
WHERE
    t.tid = #{id}
AND
    s.tid = t.tid

先是Teacher类的两个基本属性

<result property="tid" column="tid"/>
<result property="name" column="tname"/>

然后是List，这里我们使用collection标签，ofType代表List集合中的存储的类别

<collection property="student" ofType="Student">
    <result property="sid" column="sid"/>
    <result property="name" column="sname"/>
</collection>

整理TeacherMapper.xml，其实一对多和多对一是一样的，只不过一个使用collection标签，一个使用association 标签。

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

<mapper namespace="com.zhang.mapper.TeacherMapper">
    <select id="findTeacherById" resultMap="TeacherStudent">
        SELECT
            s.sid,
            s.NAME sname,
            s.tid,
            t.NAME tname
        FROM
            student s,
            teacher t
        WHERE
            t.tid = #{id}
        AND
            s.tid = t.tid
    </select>
    
    <resultMap id="TeacherStudent" type="Teacher">
        <result property="tid" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="student" ofType="Student">
            <result property="sid" column="sid"/>
            <result property="name" column="sname"/>
        </collection>
    </resultMap>
</mapper>

测试第一种方法

import com.zhang.mapper.TeacherMapper;
import com.zhang.pojo.Teacher;
import com.zhang.util.GetSqlSession;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

/**
 * @title: MybatisTest
 * @Author 张宜强
 * @Date: 2021/2/26 18:44
 * @Effect:
 */
public class MybatisTest {

    @Test
    public void Test1() {
        SqlSession sqlSession = GetSqlSession.getSqlSession();
        TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);

        Teacher teacherById = teacherMapper.findTeacherById("2");
        System.out.println(teacherById);
        sqlSession.close();
    }
}

运行结果：

在这里插入图片描述

第二种方法

这种方法和多对一的第二种方法类似，只不过使用了collection标签

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

<mapper namespace="com.zhang.mapper.TeacherMapper">

    <select id="findTeacherById" resultMap="TeacherStudent">
        select * from teacher where tid = #{id}
    </select>

    <resultMap id="TeacherStudent" type="Teacher">
        <result property="tid" column="tid"/>
        <result property="name" column="name"/>
        <collection property="student" column="tid" javaType="ArrayList" ofType="Student" select="findStudentByTeacherId">
            <result property="sid" column="sid"/>
            <result property="name" column="name"/>
            <result property="sid" column="sid"/>
        </collection>
    </resultMap>

    <select id="findStudentByTeacherId" resultType="Student">
        select * from student where tid = #{id}
    </select>

</mapper>

第二种方法测试

import com.zhang.mapper.TeacherMapper;
import com.zhang.pojo.Teacher;
import com.zhang.util.GetSqlSession;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

/**
 * @title: MybatisTest
 * @Author 张宜强
 * @Date: 2021/2/26 18:44
 * @Effect:
 */
public class MybatisTest {

    @Test
    public void Test1() {
        SqlSession sqlSession = GetSqlSession.getSqlSession();
        TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);

        Teacher teacherById = teacherMapper.findTeacherById("1");
        System.out.println(teacherById);
        sqlSession.close();
    }
}