内容导读
一对一关联查询
一对多关联查询
多对多关联查询
单元测试
项目准备
创建基于Spring boot的项目,导入mysql驱动包,mybatis持久化框架的包等,用于测试查询结果。
pom.xml文件部分参考:
<!--依赖或库版本设置-->
<properties>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<spring-boot.version>2.3.7.RELEASE</spring-boot.version>
</properties>
<!--导入相关依赖-->
<dependencies>
<!--实体类简写小工具-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--mybatis依赖-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<!--mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--单元测试依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<dependencyManagement>
<!--Spring boot依赖-->
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>${spring-boot.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
一、 一对一关联查询
学生表stu->学籍卡表card
1、学生实体类:Stu
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Stu implements Serializable{
private int id;//学号
private String name;//姓名
private String gender;//性别
private long mobile;//手机
//关联另一个实体类对象,通过mybatis关联操作直接拿到表中的记录,生成对象
private Card card;
}
2、学籍卡实体类:Card
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Card implements Serializable{
private int id;//学号
private String grade_name;//年级名
}
3、接口:StuMapper
@Mapper
public interface StuMapper{
Stu selectStu();
}
4、映射文件:StuMapper.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.mysoft.mapper.StuMapper">
<!--自定义结果集,实现关联查询-->
<resultMap id="mystu" type="com.mysoft.entity.Stu">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="gender" column="gender"/>
<result property="mobile" column="mobile"/>
<!--拆分card对象的属性与另一个表中的查询到的字段对应-->
<result property="card.id" column="cid"/>
<result property="card.grade_name" column="grade_name"/>
</resultMap>
<!--两表关联查询生成一个对象,注意将学籍表中查到的id取个别名,以便与学生表的id区分开-->
<select id="selectStu" resultMap="mystu">
select s.id,s.name,s.gender,s.mobile,c.id cid ,c.grade_name from stu s inner join card c on s.id=c.id where s.id=#{id};
</select>
</mapper>
二、 一对多关联查询
年级表grade->学生表stu
1、年级类:Grade
@Builder
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Grade implements Serializable{
private int id;
private String grade_name;
private String address;
//查询年级同时查询此年级所对应所有学生信息
List<Student> students;
}
2、学生类:Student
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
//实体类序列化,其对象将转成字节流
public class Student implements Serializable {
private int id;//学号
private String name;//姓名
private String gender;//性别
private long mobile;//手机
//年级id
private int grade_id;
//查年级对象
private Grade grade;
}
3、接口GradeMapper
@Mapper
public interface StuMapper{
Grade selectGrade(int id);
List<Grade> selectGrades();
}
4、映射文件GradeMapper.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.mysoft.mapper.GradeMapper">
<!--自定义结果集,在一个对象关联另一个集合对象-->
<resultMap id="grade" type="com.mysoft.entity.Grade">
<!--property:类中属性名,column:表中的字段名-->
<id property="id" column="id"/>
<result property="grade_name" column="grade_name" />
<result property="address" column="address" />
<!--关联集合对象,property:类中的属性名,ofType:集合中的泛型-->
<collection property="students" ofType="Student">
<id property="id" column="sid"/>
<result property="name" column="name"/>
<result property="gender" column="gender"/>
<result property="mobile" column="mobile"/>
</collection>
</resultMap>
<sql id="ids">
g.id,g.grade_name,g.address,s.id sid,s.name,s.gender,s.mobile
</sql>
<!--查询年级表,并同时查询某个年级对象的所有学生信息-->
<select id="selectGrade" resultMap="grade">
select <include refid="ids"/> from grade g inner join stu s on g.id=s.grade_id where g.id=#{id};
</select>
<!--查询所有年级的所有学生信息-->
<select id="selectGrades" resultMap="grade">
select <include refid="ids"/> from grade g inner join stu s on g.id=s.grade_id;
</select>
</mapper>
三、多对多关联查询
stu学生表、course_stu课程学生关系表、course课程表三表关联查询;
查询一个或所有学生信息,同时查询学生所选所有课程。
1、实体类MyStudent
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
//实体类序列化,其对象将转成字节流
public class MyStudent implements Serializable {
private int id;//学号
private String name;//姓名
private String gender;//性别
private long mobile;//手机
//查当前学生所选的所有课程
List<MyCourse> courses;
}
2、实体类MyCourse
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class MyCourse {
private int id;
private String course_name;
private int credit;
//查当前课程的所有学生
List<MyStudent> students;
}
3、接口StudentMapper
@Mapper
public interface StudentMapper{
Grade selectGrade(int id);
List<Grade> selectGrades();
}
4、映射文件MyStudentMapper.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.mysoft.mapper.MyStudentMapper">
<!--自定义结果集查所有学生,对应的所有课程-->
<resultMap id="mys" type="com.mysoft.entity.MyStudent">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="gender" column="gender"/>
<result property="mobile" column="mobile"/>
<!--关联查所有课程-->
<collection property="courses" ofType="com.mysoft.entity.MyCourse">
<id property="id" column="cid"/>
<result property="course_name" column="course_name"/>
<result property="credit" column="credit"/>
</collection>
</resultMap>
<!--字段组合-->
<sql id="ids">
s.id,s.name,s.gender,c.id cid,c.course_name,c.credit
</sql>
<!--查询所有学生的所有课程-->
<select id="selectStudents" resultMap="mys">
select <include refid="ids"/> from stu s inner join stu_course sc on s.id=sc.stu_id inner join course c on sc.course_id=c.id;
</select>
</mapper>
四、单元测试
测试一对一、一对多、多对多关联查询
@SpringBootTest
public class MyTest{
//1.一对一两个表关联查询,结果写入到一个stu对象中
@Test
public void go1() {
SqlSession ss = MyTools.getSqlSession();
//生成操作对象
StuMapper sm = ss.getMapper(StuMapper.class);
//获取一个学生信息
System.out.println(sm.selectStu(1002));
//取出stu中的其它关联的对象
Stu stu = sm.selectStu(1002);
Card card = stu.getCard();
//输出card对象
System.out.println(card);
}//go1
//2.1 一对多两个表的关联查询
@Test
public void go2(){
SqlSession ss = MyTools.getSqlSession();
GradeMapper gm = ss.getMapper(GradeMapper.class);
Grade grade = gm.selectGrade(8001);
System.out.println(grade);
//还可以取出此对象中的集合对象
List<Student> stus = grade.getStudents();
System.out.println("------------------");
System.out.println(stus);
}
//2.2 一对多关联查询所有年级中的所有学生信息
@Test
public void go3(){
SqlSession ss = MyTools.getSqlSession();
GradeMapper gm = ss.getMapper(GradeMapper.class);
//查所有年级
System.out.println(gm.selectGrades());
}
//3.多对多关联查询,查所有学生所选的所有课程
@Test
public void go4(){
SqlSession ss = MyTools.getSqlSession();
MyStudentMapper msm = ss.getMapper(MyStudentMapper.class);
System.out.println(msm.selectStudents());
}
}
更多内容请关注本站!