Mybatis之综合查询(1对1，1对多，多对多)_mybatis综合搜索-优快云博客

本文链接：https://blog.youkuaiyun.com/a1265262132/article/details/80602060

本文介绍了一个基于MyBatis的综合查询项目案例，通过详细展示全局配置文件、数据库配置及各类映射文件，实现对班级、教师、学生及课程等多表之间的复杂关联查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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">

</typeAliases>

</dataSource>

</environment>

</environments>

</mappers>

</configuration>

数据库配置文件jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver

jdbc.url=jdbc:mysql://localhost:3306/sdut?useUnicode=true&characterEncoding=utf-8

jdbc.user=root

jdbc.password=ro

日志配置文件log4j.properties

### \u8BBE\u7F6E###

log4j.rootLogger = debug,stdout,D,E

### \u8F93\u51FA\u4FE1\u606F\u5230\u63A7\u5236\u62AC ###

log4j.appender.stdout = org.apache.log4j.ConsoleAppender

log4j.appender.stdout.Target = System.out

log4j.appender.stdout.layout = org.apache.log4j.PatternLayout

log4j.appender.stdout.layout.ConversionPattern = [%-5p] %d{yyyy-MM-dd HH:mm:ss,SSS} method:%l%n%m%n

### \u8F93\u51FADEBUG \u7EA7\u522B\u4EE5\u4E0A\u7684\u65E5\u5FD7\u5230=E://logs/error.log ###

log4j.appender.D = org.apache.log4j.DailyRollingFileAppender

log4j.appender.D.File = E://logs/log.log

log4j.appender.D.Append = true

log4j.appender.D.Threshold = DEBUG

log4j.appender.D.layout = org.apache.log4j.PatternLayout

log4j.appender.D.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss} [ %t:%r ] - [ %p ] %m%n

### \u8F93\u51FAERROR \u7EA7\u522B\u4EE5\u4E0A\u7684\u65E5\u5FD7\u5230=E://logs/error.log ###

log4j.appender.E = org.apache.log4j.DailyRollingFileAppender

log4j.appender.E.File =E://logs/error.log

log4j.appender.E.Append = true

log4j.appender.E.Threshold = ERROR

log4j.appender.E.layout = org.apache.log4j.PatternLayout

log4j.appender.E.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss} [ %t:%r ] - [ %p ] %m%n

创建表（注意：插入字符串单引号为中文字符，使用时需要修改成英文字符）

-- 创建班级表

create table clazz(

cid int primary key auto_increment,

cname varchar(20) not null,

cyear int not null

);

-- 班级表插入数据

insert into clazz values(null,’计科1601’,2016);

insert into clazz values(null,’计科1602’,2016);

insert into clazz values(null,’通信1501’,2015);

insert into clazz values(null,’通信1502’,2015);

-- 创建教师表

create table teacher(

tid int primary key auto_increment,

tname varchar(20) not null,

tyear int not null,

cid int not null

);

-- 教师表插入数据

insert into teacher values(null,’wang1’,2010,1);

insert into teacher values(null,’wang2’,2010,2);

insert into teacher values(null,’wang3’,2010,3);

-- 创建学生表

create table student(

sid int PRIMARY key auto_increment,

sname varchar(20) not null,

sage int not null,

cid int not null

);

-- 学生表插入数据

insert into student values(null,’张三’,20,1);

insert into student values(null,’李四’,21,1);

insert into student values(null,’王五’,22,2);

insert into student values(null,’王华’,23,2);

insert into student values(null,’中华’,24,3);

insert into student values(null,’李华’,25,4);

-- 创建课程表

create table course(

cid int PRIMARY key auto_increment,

cname VARCHAR(20) not null,

chour int not null,

ccredit int not null

);

-- 课程表插入数据

insert into course values(null,’语文’,48,3);

insert into course values(null,’数学’,96,6);

insert into course values(null,’英语’,64,4);

-- 创建成绩表

create table score(

scid int auto_increment,

sid int not null,

cid int not null,

score double,

PRIMARY key(scid,sid,cid)

);

-- 成绩表插入数据

insert into score values(null,1,1,90);

insert into score values(null,1,2,null);

insert into score values(null,1,3,89);

insert into score values(null,2,1,90);

insert into score values(null,2,2,null)

班级类Clazz.java

package cn.sdut.po;

import java.util.List;

public class Clazz {

int cid;

String cname;

int cyear;

Teacher teacher;//一个班级对应老师

List<Student> studentlist;//一个班级对应多个学生

public int getCid() {

return cid;

}

public void setCid(int cid) {

this.cid = cid;

}

public String getCname() {

return cname;

}

public void setCname(String cname) {

this.cname = cname;

}

public int getCyear() {

return cyear;

}

public void setCyear(int cyear) {

this.cyear = cyear;

}

public Teacher getTeacher() {

return teacher;

}

public void setTeacher(Teacher teacher) {

this.teacher = teacher;

}

public List<Student> getStudentlist() {

return studentlist;

}

public void setStudentlist(List<Student> studentlist) {

this.studentlist = studentlist;

}

@Override

public String toString() {

return "Clazz [cid=" + cid + ", cname=" + cname + ", cyear=" + cyear + ", teacher=" + teacher + ", studentlist="

+ studentlist + "]";

}

课程类Course.java

package cn.sdut.po;

import java.util.List;

public class Course {

int cid;

String cname;

int chour;

int ccredit;

List<Student> studentcourse;//一个课程对应多个学生

public int getCid() {

return cid;

}

public void setCid(int cid) {

this.cid = cid;

}

public String getCname() {

return cname;

}

public void setCname(String cname) {

this.cname = cname;

}

public int getChour() {

return chour;

}

public void setChour(int chour) {

this.chour = chour;

}

public int getCcredit() {

return ccredit;

}

public void setCcredit(int ccredit) {

this.ccredit = ccredit;

}

public List<Student> getStudentcourse() {

return studentcourse;

}

public void setStudentcourse(List<Student> studentcourse) {

this.studentcourse = studentcourse;

}

@Override

public String toString() {

return "Course [cid=" + cid + ", cname=" + cname + ", chour=" + chour + ", ccredit=" + ccredit

+ ", studentcourse=" + studentcourse + "]";

}

学生类Student.java

package cn.sdut.po;

import java.util.List;

public class Student {

int sid;

String Sname;

int sage;

Clazz clazzstudent;//一个学生对应一个班级

List<Course> course;//一个学生对应多个课程

public int getSid() {

return sid;

}

public void setSid(int sid) {

this.sid = sid;

}

public String getSname() {

return Sname;

}

public void setSname(String sname) {

Sname = sname;

}

public int getSage() {

return sage;

}

public void setSage(int sage) {

this.sage = sage;

}

public Clazz getClazzstudent() {

return clazzstudent;

}

public void setClazzstudent(Clazz clazzstudent) {

this.clazzstudent = clazzstudent;

}

public List<Course> getCourse() {

return course;

}

public void setCourse(List<Course> course) {

this.course = course;

}

@Override

public String toString() {

return "Student [sid=" + sid + ", Sname=" + Sname + ", sage=" + sage + ", clazzstudent=" + clazzstudent

+ ", course=" + course + "]";

}

老师类teacher.java

package cn.sdut.po;

public class Teacher {

int tid;

String tname;

int tyear;

public int getTid() {

return tid;

}

public void setTid(int tid) {

this.tid = tid;

}

public String getTname() {

return tname;

}

public void setTname(String tname) {

this.tname = tname;

}

public int getCyear() {

return tyear;

}

public void setCyear(int cyear) {

this.tyear = cyear;

}

@Override

public String toString() {

return "Teacher [tid=" + tid + ", tname=" + tname + ", cyear=" + tyear + "]";

}

班级表映射文件ClazzMapper.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">

</association>

</resultMap>

</collection>

</resultMap>

select * from

clazz C,teacher T

where C.cid=T.cid and C.cid=#{cid}

</select>

select * from

clazz C left join teacher T

on C.cid=T.cid

</select>

select *from

clazz C,student S

where C.cid=S.cid and C.cid=#{cid}

</select>

select *from

clazz C LEFT JOIN student S

on C.cid=S.cid

</select>

</mapper>

课程表映射文件CourseMapper.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">

</collection>

</resultMap>

select * from student S,course C,score SC where S.sid=SC.sid and

C.cid=SC.cid and C.cid=#{id}

</select>

select * from course C left join score SC on C.cid=SC.cid left join student S

on SC.sid=S.sid

</select>

</mapper>

学生表映射文件StudentMapper.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">

</association>

</resultMap>

</collection>

</resultMap>

select * from student S,clazz C where S.cid=C.cid and S.sid=#{id}

</select>

select * from student S left join clazz C on S.cid=C.cid

</select>

select * from student S,course C,score SC

where S.sid=SC.sid and C.cid=SC.cid and S.sid=#{id}

</select>

select * from student S left join score SC on S.sid=SC.sid

left join course C on SC.cid=C.cid

</select>

</mapper>

老师表映射文件teacherMapper.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">

select * from clazz C,teacher T

where C.cid=T.cid and T.tid=#{tid}

</select>

select * from clazz C left join teacher T

on C.cid=T.cid

</select>

</mapper>

主文件Main.java

package cn.sdut.view;

import java.io.IOException;

import java.io.Reader;

import java.util.ArrayList;

import java.util.List;

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 cn.sdut.po.Clazz;

import cn.sdut.po.Course;

import cn.sdut.po.Student;

import cn.sdut.po.Teacher;

public class Main {

public static void main(String[] args) throws IOException {

Reader reader = Resources.getResourceAsReader("mybatis-config.xml");

SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);

SqlSession session = factory.openSession();

* Teacher teacher=session.selectOne("selectById",1001);

* System.out.println(teacher);

/* 根据班级ID查询其基本信息和其班主任教师信息。 */

Clazz clazz = session.selectOne("selectClazzTeacherByCid", 1);

System.out.println(clazz);

/* 查询所有班级基本信息和其班主任教师信息。 */

List<Clazz> clazz0 = new ArrayList<Clazz>();

clazz0 = session.selectList("allselectClazzTeacherByCid");

System.out.println(clazz0);

/* 根据教师ID查询其信息和他所负责的班级。 */

Clazz clazz1 = session.selectOne("selectTeacherClazzByTid", 1);

System.out.println(clazz1);

/* 查询所有教师信息和他所负责的班级。 */

List<Clazz> clazz2 = new ArrayList<Clazz>();

clazz2 = session.selectList("allselectTeacherClazzByTid");

System.out.println(clazz2);

/* 根据班级ID查询其基本信息和它包含的所有学生信息 */

Clazz clazzstudent = session.selectOne("selectClazzStudentByCid", 1);

System.out.println(clazzstudent);

/* 查询所有班级基本信息和它包含的学生信息 */

List<Clazz> clazzstudent1 = new ArrayList<Clazz>();

clazzstudent1 = session.selectList("allselectClazzStudentByCid");

System.out.println(clazzstudent1);

/* 根据学生ID查询其信息和他所在的班级信息 */

Student student = session.selectOne("getStudentClazz", 1);

System.out.println(student);

/* 查询所有学生信息和他在负责的班级信息 */

List<Student> student1 = new ArrayList<>();

student1 = session.selectList("allgetStudentClazz");

System.out.println(student1);

/* 根据学生ID查询其基本信息和他所选择的课程信息。 */

Student studentcourse = session.selectOne("getStudentCourses", 1);

System.out.println(studentcourse);

/* 查询所有学生基本信息和每一位学生所选的课程信息 */

List<Student> studentcourselist = new ArrayList<>();

studentcourselist = session.selectList("allgetStudentCourses");

System.out.println(studentcourselist);

/* 根据课程ID查询其信息和所有选择了该门课程的学生信息。 */

Course course = session.selectOne("getCourseStudents", 1);

System.out.println(course);

/* 查询所有课程信息和选择了该门课程的学生信息。 */

List<Course> courselist = new ArrayList<>();

courselist = session.selectList("allgetCourseStudents");

System.out.println(studentcourselist);

}

测试结果

<== Total: 1

Clazz [cid=1, cname=计科1601, cyear=2016, teacher=Teacher [tid=1, tname=wang1, cyear=2010], studentlist=null]

<== Total: 4

[Clazz [cid=1, cname=计科1601, cyear=2016, teacher=Teacher [tid=1, tname=wang1, cyear=2010], studentlist=null], Clazz [cid=2, cname=计科1602, cyear=2016, teacher=Teacher [tid=2, tname=wang2, cyear=2010], studentlist=null], Clazz [cid=3, cname=通信1501, cyear=2015, teacher=Teacher [tid=3, tname=wang3, cyear=2010], studentlist=null], Clazz [cid=4, cname=通信1502, cyear=2015, teacher=null, studentlist=null]]

<== Total: 1

Clazz [cid=1, cname=计科1601, cyear=2016, teacher=Teacher [tid=1, tname=wang1, cyear=2010], studentlist=null]

<== Total: 4

<== Total: 2

Clazz [cid=1, cname=计科1601, cyear=2016, teacher=null, studentlist=[Student [sid=1, Sname=张三, sage=20, clazzstudent=null, course=null], Student [sid=2, Sname=李四, sage=21, clazzstudent=null, course=null]]]

<== Total: 6

[Clazz [cid=1, cname=计科1601, cyear=2016, teacher=null, studentlist=[Student [sid=1, Sname=张三, sage=20, clazzstudent=null, course=null], Student [sid=2, Sname=李四, sage=21, clazzstudent=null, course=null]]], Clazz [cid=2, cname=计科1602, cyear=2016, teacher=null, studentlist=[Student [sid=3, Sname=王五, sage=22, clazzstudent=null, course=null], Student [sid=4, Sname=王华, sage=23, clazzstudent=null, course=null]]], Clazz [cid=3, cname=通信1501, cyear=2015, teacher=null, studentlist=[Student [sid=5, Sname=中华, sage=24, clazzstudent=null, course=null]]], Clazz [cid=4, cname=通信1502, cyear=2015, teacher=null, studentlist=[Student [sid=6, Sname=李华, sage=25, clazzstudent=null, course=null]]]]

<== Total: 1

Student [sid=1, Sname=张三, sage=20, clazzstudent=Clazz [cid=1, cname=计科1601, cyear=0, teacher=null, studentlist=null], course=null]

<== Total: 6

[Student [sid=1, Sname=张三, sage=20, clazzstudent=Clazz [cid=1, cname=计科1601, cyear=0, teacher=null, studentlist=null], course=null], Student [sid=2, Sname=李四, sage=21, clazzstudent=Clazz [cid=1, cname=计科1601, cyear=0, teacher=null, studentlist=null], course=null], Student [sid=3, Sname=王五, sage=22, clazzstudent=Clazz [cid=2, cname=计科1602, cyear=0, teacher=null, studentlist=null], course=null], Student [sid=4, Sname=王华, sage=23, clazzstudent=Clazz [cid=2, cname=计科1602, cyear=0, teacher=null, studentlist=null], course=null], Student [sid=5, Sname=中华, sage=24, clazzstudent=Clazz [cid=3, cname=通信1501, cyear=0, teacher=null, studentlist=null], course=null], Student [sid=6, Sname=李华, sage=25, clazzstudent=Clazz [cid=4, cname=通信1502, cyear=0, teacher=null, studentlist=null], course=null]]

<== Total: 3

Student [sid=1, Sname=张三, sage=20, clazzstudent=null, course=[Course [cid=1, cname=语文, chour=48, ccredit=3, studentcourse=null]]]

<== Total: 9

[Student [sid=1, Sname=张三, sage=20, clazzstudent=null, course=[Course [cid=1, cname=语文, chour=48, ccredit=3, studentcourse=null]]], Student [sid=2, Sname=李四, sage=21, clazzstudent=null, course=[Course [cid=1, cname=语文, chour=48, ccredit=3, studentcourse=null]]], Student [sid=3, Sname=王五, sage=22, clazzstudent=null, course=[Course [cid=2, cname=null, chour=0, ccredit=0, studentcourse=null]]], Student [sid=4, Sname=王华, sage=23, clazzstudent=null, course=[Course [cid=2, cname=null, chour=0, ccredit=0, studentcourse=null]]], Student [sid=5, Sname=中华, sage=24, clazzstudent=null, course=[Course [cid=3, cname=null, chour=0, ccredit=0, studentcourse=null]]], Student [sid=6, Sname=李华, sage=25, clazzstudent=null, course=[Course [cid=4, cname=null, chour=0, ccredit=0, studentcourse=null]]]]

<== Total: 2

Course [cid=1, cname=语文, chour=48, ccredit=3, studentcourse=[Student [sid=1, Sname=张三, sage=20, clazzstudent=null, course=null], Student [sid=2, Sname=李四, sage=21, clazzstudent=null, course=null]]]

<== Total: 5