目录
1.多表联查(通过collection标签的ofType属性)
1)mapper
2)mapper.xml
3)测试代码
4)测试结果
2.分布查询(通过collection标签的select属性)
1)mapper
2)mapper.xml
3)测试代码
4)测试结果
附录
1.Classes实体类
2.student类
3.OneToManyMapper
4.OneToManyMapper.xml
5.OneToManyMapperTest.xml
6.sql
studentSql
classesSql
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;}@Overridepublic 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);}@Overridepublic 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 mapperPUBLIC "-//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 {@Autowiredprivate OneToManyMapper oneToManyMapper;/*** 通过collection关联查询*/@Testpublic 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分布查询*/@Testpublic 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 TransferSource Server : mybatisSource Server Type : MySQLSource Server Version : 80025Source Host : localhost:3306Source Schema : mybatisTarget Server Type : MySQLTarget Server Version : 80025File Encoding : 65001Date: 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
/*Navicat Premium Data TransferSource Server : mybatisSource Server Type : MySQLSource Server Version : 80025Source Host : localhost:3306Source Schema : mybatisTarget Server Type : MySQLTarget Server Version : 80025File Encoding : 65001Date: 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;