mybatis多表联查(一对一、一对多/多对一、多对多)

在开发过程中单表查询往往不能满足需求分析的很多功能，对于比较复杂业务来说，关联的表有几个，甚至是几十个并且表与表之间的关联相当复杂。为了能够实现复杂的功能业务，就必须进行多表查询，在mybatis中提供了多表查询映射标签，可以实现表之间的一对一、一对多、多对多关系的映射。
注意：大批代码正在向你靠近

注意：测试代码块放最后运行出错可以看看

在示例演练之前需要的准备：首先创建一个maven项目

在pom.xml中配置文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>com.etime</groupId><artifactId>day11</artifactId><version>1.0-SNAPSHOT</version><properties><maven.compiler.source>8</maven.compiler.source><maven.compiler.target>8</maven.compiler.target></properties><dependencies><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.16</version></dependency><dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.5.1</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.11</version><scope>runtime</scope></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version><scope>test</scope></dependency></dependencies>
</project>

创建配置jdbc.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/db_418?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
jdbc.username=root
jdbc.password=h123456

创建配置文件config.xml连接数据库和数据流

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration><properties resource="jdbc.properties"></properties><typeAliases><package name="com.etime.pojo"/></typeAliases><!--    配置 mybatis的环境--><environments default="development"><!--        配置环境--><environment id="development"><!--            配置事物类型--><transactionManager type="JDBC"></transactionManager><!--            配置连接数据库的信息：用的是数据源[连接池]--><dataSource type="POOLED"><property name="driver" value="${jdbc.driver}"/><!--                jdbc:mysql://localhost:3306/db_school?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC--><!--                和javaWeb servlet三层架构中的区别这里是只需要设置时区就可以了jdbc:mysql://localhost:3306/db_school?serverTimezone=UTC--><property name="url" value="${jdbc.url}"/><property name="username" value="${jdbc.username}"/><property name="password" value="${jdbc.password}"/></dataSource></environment></environments><!--    注册StudentDao接口映射文件位置--><mappers><package name="com.etime.mapper"/></mappers>
</configuration>

创建工具类SqlSessionUtil.java为读取配置文件做准备

package com.etime.util;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 java.io.IOException;
import java.io.InputStream;public class SqlSessionUtil {private static  SqlSession sqlSession =null;static {//加载配置文件InputStream inputStream = null;try {inputStream = Resources.getResourceAsStream("config.xml");} catch (IOException e) {e.printStackTrace();}//用于读取配置文件内容，生成SqlSessionFactorySqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);//获取SqlSession对象//参数为true，则底层调用setAutoCommit方法时，sqlSession = sqlSessionFactory.openSession(true);}public SqlSession getSqlSession(){return sqlSession;}
}

1、一对一多表联查

创建数据库：

CREATE DATABASE db_418;
USE db_418;
CREATE TABLE wife(wid INT PRIMARY KEY AUTO_INCREMENT,wname VARCHAR(10) NOT NULL
);
CREATE TABLE husband(hid INT PRIMARY KEY AUTO_INCREMENT,hname VARCHAR(10) NOT NULL,wid INT,FOREIGN KEY(wid) REFERENCES wife(wid)
);

创建接口类HusbandMapper.java

package com.etime.mapper;import com.etime.pojo.Husband;import java.util.List;public interface HusbandMapper {//获取所有丈夫对象List<Husband> getAllHusband();
}

创建实体类Husband.java

package com.etime.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;@AllArgsConstructor
@NoArgsConstructor
@Data
public class Husband {private int hid;private String hname;private int wid;private Wife wife;
}

创建Wife.java实体类

package com.etime.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;@AllArgsConstructor
@NoArgsConstructor
@Data
public class Wife {private int wid;private String wname;
}

创建工具类SqlSessionUtil.java

package com.etime.util;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 java.io.IOException;
import java.io.InputStream;public class SqlSessionUtil {private static  SqlSession sqlSession =null;static {//加载配置文件InputStream inputStream = null;try {inputStream = Resources.getResourceAsStream("config.xml");} catch (IOException e) {e.printStackTrace();}//用于读取配置文件内容，生成SqlSessionFactorySqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);//获取SqlSession对象//参数为true，则底层调用setAutoCommit方法时，sqlSession = sqlSessionFactory.openSession(true);}public SqlSession getSqlSession(){return sqlSession;}
}

创建HusbandMapper.xml为配置sql映射

<?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="com.etime.mapper.HusbandMapper"><select id="getAllHusband" resultMap="husbandAndWife">select * from husband h,wife w where w.wid = h.wid;</select><resultMap id="husbandAndWife" type="Husband"><id property="hid" column="hid"></id><result property="hname" column="hname"></result><result property="wid" column="wid"></result><association property="wife" javaType="Wife"><id property="wid" column="wid"></id><result property="wname" column="wname"></result></association></resultMap></mapper>

创建测试类MybatisTest.java

查询所有丈夫和妻子的关系数据

package com.etime.test;import com.etime.mapper.HusbandMapper;
import com.etime.pojo.Husband;
import com.etime.util.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import java.util.List;public class MyBatisTest {SqlSessionUtil sqlSessionUtil = new SqlSessionUtil();@Testpublic void t01(){SqlSession sqlSession = sqlSessionUtil.getSqlSession();HusbandMapper husbandMapper = sqlSession.getMapper(HusbandMapper.class);List<Husband> list = husbandMapper.getAllHusband();list.forEach(System.out::println);sqlSession.close();}
}

运行结果：

2、一对多/多对一

创建数据库表classes、student：

方式一：

CREATE TABLE classes(cid INT PRIMARY KEY AUTO_INCREMENT,cname VARCHAR(10) NOT NULL
);
CREATE TABLE student(sid INT PRIMARY KEY AUTO_INCREMENT,sname VARCHAR(10) NOT NULL,cid INT,FOREIGN KEY(cid) REFERENCES classes(cid)
);

创建实体类Student.java

package com.etime.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;@AllArgsConstructor
@NoArgsConstructor
@Data
public class Student {private int sid;private String sname;private int cid;private Classes classes;
}

创建实体类Classes.java

package com.etime.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;@NoArgsConstructor
@AllArgsConstructor
@Data
public class Classes {private int cid;private String cname;
}

创建StudentMapper.java

package com.etime.mapper;import com.etime.pojo.Student;import java.util.List;public interface StudentMapper {List<Student> getAllStudent();
}

创建StudentMapper.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="com.etime.mapper.StudentMapper"><select id="getAllStudent" resultMap="studentAndClasses">select * from student s,classes c where s.cid = c.cid;</select><resultMap id="studentAndClasses" type="Student"><id property="sid" column="sid"></id><result property="sname" column="sname"></result><result property="cid" column="cid"></result><association property="classes" javaType="Classes"><id property="cid" column="cid"></id><result property="cname" column="cname"></result></association></resultMap>
</mapper>

测试：

@Testpublic void t02(){SqlSession sqlSession = sqlSessionUtil.getSqlSession();StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);List<Student> list = studentMapper.getAllStudent();list.forEach(System.out::println);sqlSession.close();}

方式二

一个班级的学生有：

修改Student.java

package com.etime.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;@AllArgsConstructor
@NoArgsConstructor
@Data
public class Student {private int sid;private String sname;private int cid;
}

修改Classes.java

创建属性为classes对象的数组stuList

package com.etime.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import java.util.List;@NoArgsConstructor
@AllArgsConstructor
@Data
public class Classes {private int cid;private String cname;private List<Student> stuList;
}

创建ClassesMapper.java接口类

package com.etime.mapper;import com.etime.pojo.Classes;import java.util.List;public interface ClassesMapper {List<Classes> getAllClasses();
}

创建ClassesMapper.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="com.etime.mapper.ClassesMapper"><select id="getAllClasses" resultMap="classesAndStudent">select * from classes c,student s where s.cid = c.cid;</select><resultMap id="classesAndStudent" type="Classes"><id property="cid" column="cid"></id><result property="cname" column="cname"></result><collection property="stuList" ofType="Student"><id property="sid" column="sid"></id><result property="sname" column="sname"></result><result property="cid" column="cid"></result></collection></resultMap>
</mapper>

添加测试：

@Testpublic void t03(){SqlSession sqlSession = sqlSessionUtil.getSqlSession();ClassesMapper classesMapper = sqlSession.getMapper(ClassesMapper.class);List<Classes> list = classesMapper.getAllClasses();list.forEach(System.out::println);sqlSession.close();}

运行结果：

3、多对多

创建学生课程数据库表：

CREATE TABLE course(courseid INT PRIMARY KEY AUTO_INCREMENT,coursename VARCHAR(10) NOT NULL
);CREATE TABLE studentcourse(scid INT PRIMARY KEY AUTO_INCREMENT,sid INT,courseid INT,FOREIGN KEY(sid) REFERENCES student(sid),FOREIGN KEY(courseid) REFERENCES course(courseid)
);

创建课程实体类Course.java

package com.etime.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;@AllArgsConstructor
@Data
@NoArgsConstructor
public class Course {private int courseid;private String coursename;
}

更改学生类实体，为学生类实体创建新属性List< Course> courseList 装该学生所学的所有课程

package com.etime.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import java.util.List;@AllArgsConstructor
@NoArgsConstructor
@Data
public class Student {private int sid;private String sname;private int cid;private List<Course> courseList;
}

在StudentMapper.xml中配置sql的查询语句

<select id="getAllStudentAndCourse" resultMap="studentAndCourse">select * from student s,course c,studentcourse sc where s.sid = sc.sid and c.courseid = sc.courseid;</select><resultMap id="studentAndCourse" type="Student"><id property="sid" column="sid"></id><result property="sname" column="sname"></result><result property="cid" column="cid"></result><collection property="courseList" ofType="Course"><id property="courseid" column="courseid"></id><result property="coursename" column="coursename"></result></collection></resultMap>

测试：

@Testpublic void t04(){SqlSession sqlSession = sqlSessionUtil.getSqlSession();StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);List<Student> list = studentMapper.getAllStudentAndCourse();list.forEach(System.out::println);sqlSession.close();}

运行结果：

方式二、

在文件StudentMapper.java文件添加方法查询所有关系表信息和主表信息

List<Student> getAllStudentAndCourse1();

在Student.java中定义该主体是学生，后面的课程会以对象的方式存储到数组中，并包括中间表的信息

package com.etime.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import java.util.List;@AllArgsConstructor
@NoArgsConstructor
@Data
public class Student {private int sid;private String sname;private int cid;//private List<Course> courseList;private List<StudentCourse> studentCourseList;
}

创建中间表的信息StudentCourse.java实体类

package com.etime.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;@NoArgsConstructor
@AllArgsConstructor
@Data
public class StudentCourse {private int scid;private int sid;private int courseid;private Course course;
}

修改课程表实体类Course.java

package com.etime.pojo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;@AllArgsConstructor
@Data
@NoArgsConstructor
public class Course {private int courseid;private String coursename;
}

通过StudentMapper.xml映射文件的方式进行数据sql的映射和配置，查询学生对应课程的多对多的信息

<select id="getAllStudentAndCourse1" resultMap="studentAndCourse1">select  * from student s,course c,studentcourse sc where s.sid = sc.sid and c.courseid = sc.courseid;</select><resultMap id="studentAndCourse1" type="Student"><id property="sid" column="sid"></id><result property="sname" column="sname"></result><result property="cid" column="cid"></result><collection property="studentCourseList" ofType="StudentCourse"><id property="scid" column="scid"></id><result property="sid" column="sid"></result><result property="courseid" column="courseid"></result><association property="course" javaType="Course"><id property="courseid" column="courseid"></id><result property="coursename" column="coursename"></result></association></collection></resultMap>

测试：

@Testpublic void t05(){SqlSession sqlSession = sqlSessionUtil.getSqlSession();StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);List<Student> list = studentMapper.getAllStudentAndCourse1();list.forEach(System.out::println);sqlSession.close();}

运行结果：

MyBatisTest.java中最后测试文件

package com.etime.test;import com.etime.mapper.ClassesMapper;
import com.etime.mapper.HusbandMapper;
import com.etime.mapper.StudentMapper;
import com.etime.pojo.Classes;
import com.etime.pojo.Husband;
import com.etime.pojo.Student;
import com.etime.util.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import java.util.List;public class MyBatisTest {SqlSessionUtil sqlSessionUtil = new SqlSessionUtil();@Testpublic void t01(){SqlSession sqlSession = sqlSessionUtil.getSqlSession();HusbandMapper husbandMapper = sqlSession.getMapper(HusbandMapper.class);List<Husband> list = husbandMapper.getAllHusband();list.forEach(System.out::println);sqlSession.close();}@Testpublic void t02(){SqlSession sqlSession = sqlSessionUtil.getSqlSession();StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);List<Student> list = studentMapper.getAllStudent();list.forEach(System.out::println);sqlSession.close();}@Testpublic void t03(){SqlSession sqlSession = sqlSessionUtil.getSqlSession();ClassesMapper classesMapper = sqlSession.getMapper(ClassesMapper.class);List<Classes> list = classesMapper.getAllClasses();list.forEach(System.out::println);sqlSession.close();}@Testpublic void t04(){SqlSession sqlSession = sqlSessionUtil.getSqlSession();StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);List<Student> list = studentMapper.getAllStudentAndCourse();list.forEach(System.out::println);sqlSession.close();}@Testpublic void t05(){SqlSession sqlSession = sqlSessionUtil.getSqlSession();StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);List<Student> list = studentMapper.getAllStudentAndCourse1();list.forEach(System.out::println);sqlSession.close();}
}

路由器知识