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混合使用实现一对多查询功能
实现
<!--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;
}
}
教室（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&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，在进行测试。
- 初始化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);
}
- 编写测试一对一查询功能的测试方法
@Test
public void testOneOne() {
System.out.println(studentMapper.listOneToOne("420109070226"));
}
- 编写测试一对多查询功能的测试方法
@Test
public void testOneMany() {
// DTO 实现
System.out.println(studentMapper.listOneToManyByDto("03"));
// 非 DTO 实现
System.out.println(studentMapper.listOneToMany("03"));
}
- 完整的测试代码
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"));
}
}
总结
都看到这里了，帮我点个喜欢吧🥰
