目录
1.多表联查(通过collection标签的ofType属性)
2.分布查询(通过collection标签的select属性)
1.多表联查(通过collection标签的ofType属性)
1)mapper
/** * collectionBy ofType */ Classes queryClassesAndStudentBycollection(@Param("id") int id);
2)mapper.xml
<!--collection--> <resultMap id="collectionResultMap" type="org.xiji.enty.Classes"> <id property="cid" column="cid"/> <result property="className" column="className"/> <!--collection--> <collection property="students" ofType="org.xiji.enty.Student"> <id property="sid" column="sid"/> <result property="studentName" column="studentName"/> <result property="studentAge" column="studentAge"/> <result property="classId" column="cid"/> </collection> </resultMap> <select id="queryClassesAndStudentBycollection" resultMap="collectionResultMap" > select * from classes c right join student s on c.cid = s.classId where c.cid=#{id} </select>
解释:
- 主对象映射
- MyBatis 使用 resultMap 将查询结果映射到 Classes 对象上。
- 主键 cid 和属性 className 直接映射到对应的数据库列。
- 集合映射
- collection 标签用于映射 Classes 对象中的 students 集合。
- 每个 Student 对象的属性 sid, studentName, studentAge, classId 分别映射到对应的数据库列。
3)测试代码
/** * 通过collection关联查询 */ @Test public void queryClassesAndStudentByAssociation() { Classes classes = oneToManyMapper.queryClassesAndStudentBycollection(1); System.out.println(classes); List<Student> students = classes.getStudents(); for (Student student : students) { System.out.println(student.toString()); } }
4)测试结果
2.分布查询(通过collection标签的select属性)
1)mapper
/** * 分布查询 */ Classes queryClassesAndStudentByStep(@Param("id") int id); /** * 查询id */ List<Student> queryClassesAndStudentByStepTwo(int id);
2)mapper.xml
<!--通过分布查询--> <resultMap id="stepResultMap" type="org.xiji.enty.Classes"> <id property="cid" column="cid"/> <result property="className" column="className"/> <collection property="students" select="queryClassesAndStudentByStepTwo" column="cid" ofType="org.xiji.enty.Student" > <id property="sid" column="sid"></id> <result property="classId" column="classId"></result> <result property="studentName" column="studentName"></result> <result property="studentAge" column="studentAge"></result> </collection> </resultMap> <select id="queryClassesAndStudentByStep" resultMap="stepResultMap" > select * from classes where cid=#{id}; </select> <select id="queryClassesAndStudentByStepTwo" resultType="org.xiji.enty.Student"> select * from student where classId=#{id} </select>j
解释:
- 主对象映射
- MyBatis 使用 resultMap 将查询结果映射到 Classes 对象上。
- 主键 cid 和属性 className 直接映射到对应的数据库列。
- 分布查询
- collection 标签用于映射 Classes 对象中的 students 集合。
- 通过 select 属性指定另一个映射语句的 ID,用于执行分布查询。
- column 属性指定传递给分布查询的参数列名称,这里是 cid 列。
- 子对象映射
- 每个 Student 对象的属性 sid, classId, studentName, studentAge 分别映射到对应的数据库列。
3)测试代码
/** * 通过collection分布查询 */ @Test public void queryClassesAndStudentByStep() { Classes classes = oneToManyMapper.queryClassesAndStudentByStep(1); System.out.println(classes); List<Student> students = classes.getStudents(); for (Student student : students) { System.out.println(student); } }
4)测试结果
附录
1.Classes实体类
package org.xiji.enty;
import java.util.ArrayList;
import java.util.List;
import org.xiji. enty.Student;
/**
* 班级表
*/
public class Classes {
private int cid;
private String className;
List<Student> students;
public List<Student> getStudents() {
return students;
}
public Classes(int id, String className, List<Student> students) {
this.cid = id;
this.className = className;
this.students = students;
}
public Classes() {
students = new ArrayList<>();
}
public Classes(int id, String className) {
this.cid = id;
this.className = className;
}
public int getId() {
return cid;
}
public void setId(int id) {
this.cid = id;
}
public String getClassName() {
return className;
}
public void setClassName(String className) {
this.className = className;
}
public void setStudents(List<Student> students) {
this.students = students;
}
@Override
public String toString() {
return "Classes{" +
"id=" + cid +
", className='" + className + '\'' +
", students=" + students +
'}';
}
}
2.student类
package org.xiji.enty;
import org.xiji.enty.Classes;
/**
* 学生表
*/
public class Student {
private int sid;
private String studentName;
private int studentAge;
private int classId;
private Classes classes;
public Student(int id, String studentName, int studentAge, int classId, Classes classes) {
this.sid = id;
this.studentName = studentName;
this.studentAge = studentAge;
this.classId = classId;
this.classes = classes;
}
public Student(int id, String studentName, int studentAge, int classId) {
this.sid = id;
this.studentName = studentName;
this.studentAge = studentAge;
this.classId = classId;
}
public Student() {
}
public int getId() {
return sid;
}
public void setId(int id) {
this.sid = id;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
public int getStudentAge() {
return studentAge;
}
public void setStudentAge(int studentAge) {
this.studentAge = studentAge;
}
public int getClassId() {
return classId;
}
public void setClassId(int classId) {
this.classId = classId;
}
public Classes getClasses() {
return classes;
}
public void setClasses(Classes classes) {
this.classes = classes;
}
public void setClasses(int id,String className)
{
this.classes = new Classes(id,className);
}
@Override
public String toString() {
return "Student{" +
"id=" + sid +
", studentName='" + studentName + '\'' +
", studentAge=" + studentAge +
", classId=" + classId +
", classes=" + classes +
'}';
}
}
3.OneToManyMapper
package org.xiji.mapper; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import org.xiji.enty.Classes; import org.xiji.enty.Student; import java.util.List; @Mapper public interface OneToManyMapper { /** * association */ Classes queryClassesAndStudentByAssociation(@Param("id") int id); /** * 分布查询 */ Classes queryClassesAndStudentByStep(@Param("id") int id); /** * 查询id */ List<Student> queryClassesAndStudentByStepTwo(int id); }
4.OneToManyMapper.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="org.xiji.mapper.OneToManyMapper"> <!--collection--> <resultMap id="collectionResultMap" type="org.xiji.enty.Classes"> <id property="cid" column="cid"/> <result property="className" column="className"/> <!--collection--> <collection property="students" ofType="org.xiji.enty.Student"> <id property="sid" column="sid"/> <result property="studentName" column="studentName"/> <result property="studentAge" column="studentAge"/> <result property="classId" column="cid"/> </collection> </resultMap> <select id="queryClassesAndStudentByAssociation" resultMap="collectionResultMap" > select * from classes c right join student s on c.cid = s.classId where c.cid=#{id} </select> <!--通过分布查询--> <resultMap id="stepResultMap" type="org.xiji.enty.Classes"> <id property="cid" column="cid"/> <result property="className" column="className"/> <collection property="students" select="queryClassesAndStudentByStepTwo" column="cid" ofType="org.xiji.enty.Student" > <id property="sid" column="sid"></id> <result property="classId" column="classId"></result> <result property="studentName" column="studentName"></result> <result property="studentAge" column="studentAge"></result> </collection> </resultMap> <select id="queryClassesAndStudentByStep" resultMap="stepResultMap" > select * from classes where cid=#{id}; </select> <select id="queryClassesAndStudentByStepTwo" resultType="org.xiji.enty.Student"> select * from student where classId=#{id} </select> </mapper>
5.OneToManyMapperTest.xml
import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.test.context.junit.jupiter.SpringJUnitConfig; import org.xiji.enty.Classes; import org.xiji.enty.Student; import org.xiji.mapper.ManyToOneMapper; import org.xiji.mapper.OneToManyMapper; import java.util.List; @SpringJUnitConfig(locations = {"classpath:springConfig.xml"}) public class OneTwoManyMapperTest { @Autowired private OneToManyMapper oneToManyMapper; /** * 通过collection关联查询 */ @Test public void queryClassesAndStudentByAssociation() { Classes classes = oneToManyMapper.queryClassesAndStudentByAssociation(1); System.out.println(classes); List<Student> students = classes.getStudents(); for (Student student : students) { System.out.println(student.toString()); } } /** * 通过collection分布查询 */ @Test public void queryClassesAndStudentByStep() { Classes classes = oneToManyMapper.queryClassesAndStudentByStep(1); System.out.println(classes); List<Student> students = classes.getStudents(); for (Student student : students) { System.out.println(student); } } }
6.sql
studentSql
/* Navicat Premium Data Transfer Source Server : mybatis Source Server Type : MySQL Source Server Version : 80025 Source Host : localhost:3306 Source Schema : mybatis Target Server Type : MySQL Target Server Version : 80025 File Encoding : 65001 Date: 15/09/2024 23:50:47 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `sid` int NOT NULL AUTO_INCREMENT COMMENT '学生id', `studentName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '学生姓名', `studentAge` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '学生年龄', `classId` int NULL DEFAULT NULL COMMENT '班级id', PRIMARY KEY (`sid`) USING BTREE, INDEX `classId`(`classId` ASC) USING BTREE, CONSTRAINT `classId` FOREIGN KEY (`classId`) REFERENCES `classes` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES (1, '张三', '18', 1); INSERT INTO `student` VALUES (2, '李四', '20', 1); INSERT INTO `student` VALUES (3, '小久', '21', 1); INSERT INTO `student` VALUES (4, 'xiji', '22', 1); SET FOREIGN_KEY_CHECKS = 1;
classesSql
标签:classId,Classes,两种,int,id,classes,MyBatis,一对,public From: https://blog.csdn.net/2301_76862031/article/details/142290996/* Navicat Premium Data Transfer Source Server : mybatis Source Server Type : MySQL Source Server Version : 80025 Source Host : localhost:3306 Source Schema : mybatis Target Server Type : MySQL Target Server Version : 80025 File Encoding : 65001 Date: 15/09/2024 23:51:16 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for classes -- ---------------------------- DROP TABLE IF EXISTS `classes`; CREATE TABLE `classes` ( `cid` int NOT NULL AUTO_INCREMENT COMMENT '班级id', `className` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '班级名称', PRIMARY KEY (`cid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of classes -- ---------------------------- INSERT INTO `classes` VALUES (1, '一班'); INSERT INTO `classes` VALUES (2, '二班'); INSERT INTO `classes` VALUES (3, '三班'); INSERT INTO `classes` VALUES (5, '五班'); SET FOREIGN_KEY_CHECKS = 1;