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 configuration
PUBLIC "-//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();
}
//用于读取配置文件内容，生成SqlSessionFactory
SqlSessionFactoryBuilder 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();
}
//用于读取配置文件内容，生成SqlSessionFactory
SqlSessionFactoryBuilder 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 mapper
PUBLIC "-//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();
@Test
public 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 mapper
PUBLIC "-//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>
测试：
@Test
public 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 mapper
PUBLIC "-//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>
添加测试：
@Test
public 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>
测试：
@Test
public 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>
测试：
@Test
public 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();
@Test
public void t01(){
SqlSession sqlSession = sqlSessionUtil.getSqlSession();
HusbandMapper husbandMapper = sqlSession.getMapper(HusbandMapper.class);
List<Husband> list = husbandMapper.getAllHusband();
list.forEach(System.out::println);
sqlSession.close();
}
@Test
public void t02(){
SqlSession sqlSession = sqlSessionUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> list = studentMapper.getAllStudent();
list.forEach(System.out::println);
sqlSession.close();
}
@Test
public void t03(){
SqlSession sqlSession = sqlSessionUtil.getSqlSession();
ClassesMapper classesMapper = sqlSession.getMapper(ClassesMapper.class);
List<Classes> list = classesMapper.getAllClasses();
list.forEach(System.out::println);
sqlSession.close();
}
@Test
public void t04(){
SqlSession sqlSession = sqlSessionUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> list = studentMapper.getAllStudentAndCourse();
list.forEach(System.out::println);
sqlSession.close();
}
@Test
public void t05(){
SqlSession sqlSession = sqlSessionUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> list = studentMapper.getAllStudentAndCourse1();
list.forEach(System.out::println);
sqlSession.close();
}
}
860
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
