MyBatis的关联查询实现，基于XML配置的实现，重点练习MyBatis的一对一，一对多的查询， mapper.xml文件配置， SqlSession的操作。

前言

完成课上的实验作业，想再熟悉一下以配置文件的方式来使用MyBatis，以往都是使用SpringBoot + MyBatis的开发方式。

这篇文章讲的是基于配置文件来使用MyBatis操作数据库，包括MyBatis的一对一、一对多的查询，并将结果封装成Java中的DTO，注意，这里也可以不用使用DTO，像”将A实体作为B实体的属性“，这样的操作也是可以的，看个人习惯啦。

在本篇文章中，我会在使用以上的两种方式来实现本案例，重点放在DTO模式上。

案例要求

场景描述：有一个数据库mybatis,里面有一张表student,包含5个字段：id,name,sex,classno,age(学号，姓名，性别，班号，年龄)，一张班级表class(cno, cname, number, teacher).学生对班级是一对一的关系， 班级对学生是一对多的关系，实现一对一的关联查询，一对多的关联查询。 实现以上的相关查询，并测试。

案例环境：IDEA + MySQL + MyBatis
知识点：使用association实现一对一查询功能，association、collection混合使用实现一对多查询功能

实现

• 创建一个原生的maven项目

  

• 添加相关的依赖

<!--mybatis-->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.2</version>
</dependency>

<!--mysql-connector-java-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.28</version>
</dependency>

<!--指定vfs，避免控制台输出mybatis的日志乱码-->
<dependency>
    <groupId>org.jboss</groupId>
    <artifactId>jboss-vfs</artifactId>
    <version>3.2.15.Final</version>
</dependency>

<!--测试-->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13</version>
    <scope>test</scope>
</dependency>

• 案例的架构

按照架构创建相应的包和文件夹即可

• 实体类

package com.openallzzz.entity;

import java.util.List;

public class Class { // 班级类

    private String cno;
    private String cname;
    private int number;
    private String teacher;
    // 这里多的一个属性就是用来演示不用 DTO 如何完成本案例
    private List<Student> studentList;

    @Override
    public String toString() {
        return "Class{" +
                "cno='" + cno + '\'' +
                ", cname='" + cname + '\'' +
                ", number=" + number +
                ", teacher='" + teacher + '\'' +
                ", studentList=" + studentList +
                '}';
    }

    public String getCno() {
        return cno;
    }

    public void setCno(String cno) {
        this.cno = cno;
    }

    public String getCname() {
        return cname;
    }

    public void setCname(String cname) {
        this.cname = cname;
    }

    public int getNumber() {
        return number;
    }

    public void setNumber(int number) {
        this.number = number;
    }

    public String getTeacher() {
        return teacher;
    }

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

    public List<Student> getStudentList() {
        return studentList;
    }

    public void setStudentList(List<Student> studentList) {
        this.studentList = studentList;
    }
}

package com.openallzzz.entity;

public class Student { // 学生类

    private String id;
    private String name;
    private String sex;
    private String classno;
    private int age;

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

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

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

    public String getSex() {
        return sex;
    }

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

    public String getClassno() {
        return classno;
    }

    public void setClassno(String classno) {
        this.classno = classno;
    }

    public int getAge() {
        return age;
    }

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

package com.openallzzz.entity.dto;

import com.openallzzz.entity.Class;
import com.openallzzz.entity.Student;

public class OneOne { // 一对一的 DTO
    private Student student;
    private com.openallzzz.entity.Class clazz;

    @Override
    public String toString() {
        return "OneOne{" +
                "student=" + student +
                ", clazz=" + clazz +
                '}';
    }

    public Student getStudent() {
        return student;
    }

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

    public Class getClazz() {
        return clazz;
    }

    public void setClazz(Class clazz) {
        this.clazz = clazz;
    }
}

package com.openallzzz.entity.dto;

import com.openallzzz.entity.Class;
import com.openallzzz.entity.Student;

import java.util.List;

public class OneMany { // 一对多的 DTO

    private Class clazz;
    private List<Student> studentList;

    @Override
    public String toString() {
        return "OneMany{" +
                "clazz=" + clazz +
                ", studentList=" + studentList +
                '}';
    }

    public Class getClazz() {
        return clazz;
    }

    public void setClazz(Class clazz) {
        this.clazz = clazz;
    }

    public List<Student> getStudentList() {
        return studentList;
    }

    public void setStudentList(List<Student> studentList) {
        this.studentList = studentList;
    }
}

• 数据库以及SQL文件

教室（class）表

学生（student）表

SQL文件，放在到名为mybatis的数据库下运行查询即可，> 没有mybatis数据库的，需要先创建名为mybatis的数据库

/*
 Navicat Premium Data Transfer

 Source Server         : 127.0.0.1-MySQL8.0
 Source Server Type    : MySQL
 Source Server Version : 80026 (8.0.26)
 Source Host           : localhost:3306
 Source Schema         : mybatis

 Target Server Type    : MySQL
 Target Server Version : 80026 (8.0.26)
 File Encoding         : 65001

 Date: 25/04/2023 17:27:42
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class`  (
  `cno` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `cname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `number` int NULL DEFAULT NULL,
  `teacher` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('03', '软件三班', 30, '周冲');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `sex` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `classno` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `age` int NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('420109070226', '张宇', '男', '03', 21);
INSERT INTO `student` VALUES ('420109070212', '小明', '男', '03', 22);
INSERT INTO `student` VALUES ('420109070223', '小美', '男', '03', 20);

SET FOREIGN_KEY_CHECKS = 1;

• 数据访问层

package com.openallzzz.dao;

import com.openallzzz.entity.Class;
import com.openallzzz.entity.Student;
import com.openallzzz.entity.dto.OneMany;
import com.openallzzz.entity.dto.OneOne;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface StudentMapper {
	// 一对一实现
    OneOne listOneToOne(String id);

    Class listOneToMany(String cno);

	// 一对多（DTO）实现
    OneMany listOneToManyByDto(String cno);
	
	// 一对多的（非DTO）实现
    List<Student> listStudentByClassno(String cno);
}

student-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.openallzzz.dao.StudentMapper">

    <select id="listStudentByClassno" resultType="student">
        select * from student where student.classno = #{cno}
    </select>

    <!--OneOne-->
    <select id="listOneToOne" parameterType="string" resultMap="OneOneMap">
        select * from student, class where student.classno = class.cno and student.id = #{id}
    </select>

    <resultMap id="OneOneMap" type="com.openallzzz.entity.dto.OneOne">
        <association property="student" javaType="student">
            <result property="id" column="id"/>
            <result property="name" column="name"/>
            <result property="sex" column="sex"/>
            <result property="classno" column="classno"/>
            <result property="age" column="age"/>
        </association>

        <association property="clazz" javaType="class">
            <result property="cno" column="cno"/>
            <result property="cname" column="cname"/>
            <result property="number" column="number"/>
            <result property="teacher" column="teacher"/>
        </association>
    </resultMap>

    <!--OneMany-->

    <!--方式一-->
    <select id="listOneToMany" parameterType="string" resultMap="OneManyMap">
        select * from class
        where class.cno = #{cno};
    </select>

    <resultMap id="OneManyMap" type="class">
        <result property="cno" column="cno"/>
        <result property="cname" column="cname"/>
        <result property="number" column="number"/>
        <result property="teacher" column="teacher"/>
        <collection property="studentList" column="{cno=cno}"
                    select="com.openallzzz.dao.StudentMapper.listStudentByClassno">
        </collection>
    </resultMap>

    <!--方式二-->
    <select id="listOneToManyByDto" resultMap="OneManyMapByDto">
        select * from class
        where class.cno = #{cno};
    </select>

    <resultMap id="OneManyMapByDto" type="oneMany">
        <association property="clazz" javaType="class">
            <result property="cno" column="cno"/>
            <result property="cname" column="cname"/>
            <result property="number" column="number"/>
            <result property="teacher" column="teacher"/>
        </association>

        <collection property="studentList" column="{cno=cno}"
                    select="com.openallzzz.dao.StudentMapper.listStudentByClassno">
        </collection>
    </resultMap>
</mapper>

mybatis的配置文件：mybatis-config.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>

    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <typeAliases>
        <!--将其第一个字母变为小写作为其别名-->
        <package name="com.openallzzz.entity"/>
    </typeAliases>

    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="zhangyu"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapper/student-mapper.xml"/>
    </mappers>

</configuration>

• 测试

在maven项目自带的模板的test包下创建testRelationFind测试类，由于本案例没有采用spring，我们不能自动注入StudentMapper，我们采用SqlSession获取Mapper的方式来初始化StudentMapper，在进行测试。

1. 初始化StudentMapper

private StudentMapper studentMapper;

{
    String resource = "mybatis-config.xml";
    InputStream inputStream = null;
    try {
        inputStream = Resources.getResourceAsStream(resource);
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    studentMapper = sqlSession.getMapper(StudentMapper.class);
}

2. 编写测试一对一查询功能的测试方法

@Test
public void testOneOne() {
     System.out.println(studentMapper.listOneToOne("420109070226"));
 }

3. 编写测试一对多查询功能的测试方法

@Test
public void testOneMany() {
	// DTO 实现
    System.out.println(studentMapper.listOneToManyByDto("03"));
    // 非 DTO 实现
    System.out.println(studentMapper.listOneToMany("03"));
}

4. 完整的测试代码

package com.openallzzz;

import com.openallzzz.dao.StudentMapper;
import com.openallzzz.entity.Class;
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.Test;

import java.io.IOException;
import java.io.InputStream;

public class testRelationFind {

    private StudentMapper studentMapper;

    {
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        studentMapper = sqlSession.getMapper(StudentMapper.class);
    }

    @Test
    public void testOneOne() {
        System.out.println(studentMapper.listOneToOne("420109070226"));
    }

    @Test
    public void testOneMany() {
//        System.out.println(studentMapper.listOneToManyByDto("03"));
        System.out.println(studentMapper.listOneToMany("03"));
    }
}

总结

都看到这里了，帮我点个喜欢吧🥰