MyBatis多表查询
多表模型分类
一对一：在任意一方建立外键，关联对方的主键。
一对多：在多的一方建立外键，关联一的一方的主键。
多对多：借助中间表，中间表至少两个字段，分别关联两张表的主键。
数据准备
sql
CREATE DATABASE db2;USE db2;-- 创建person表CREATE TABLE person(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),age INT);INSERT INTO person VALUES (NULL,'张三',23);INSERT INTO person VALUES (NULL,'李四',24);INSERT INTO person VALUES (NULL,'王五',25);-- 创建card表CREATE TABLE card(id INT PRIMARY KEY AUTO_INCREMENT,number VARCHAR(30),pid INT,CONSTRAINT cp_fk FOREIGN KEY (pid) REFERENCES person(id));INSERT INTO card VALUES (NULL,'12345',1);INSERT INTO card VALUES (NULL,'23456',2);INSERT INTO card VALUES (NULL,'34567',3);-- 创建班级表CREATE TABLE classes(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20));INSERT INTO classes VALUES (NULL,'一班');INSERT INTO classes VALUES (NULL,'二班');-- 创建学生表CREATE TABLE student(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(30),age INT,cid INT,CONSTRAINT cs_fk FOREIGN KEY (cid) REFERENCES classes(id));INSERT INTO student VALUES (NULL,'张三',23,1);INSERT INTO student VALUES (NULL,'李四',24,1);INSERT INTO student VALUES (NULL,'王五',25,2);INSERT INTO student VALUES (NULL,'赵六',26,2);-- 创建课程表CREATE TABLE course(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20));INSERT INTO course VALUES (NULL,'语文');INSERT INTO course VALUES (NULL,'数学');-- 学生表与课程表的中间表CREATE TABLE stu_cr(id INT PRIMARY KEY AUTO_INCREMENT,sid INT,cid INT,CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id),CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id));INSERT INTO stu_cr VALUES (NULL,1,1);INSERT INTO stu_cr VALUES (NULL,1,2);INSERT INTO stu_cr VALUES (NULL,2,1);INSERT INTO stu_cr VALUES (NULL,2,2);
表之间的关系
多表操作之XML
目录结构
MyBatis核心配置文件以及jdbc,log4j配置文件放在src目录下
MyBatis核心配置文件
<?xml version="1.0" encoding="UTF-8" ?><!--MyBatis的DTD约束--><!DOCTYPE configuration PUBLIC "-////DTD Config 3.0//EN" "/dtd/mybatis-3-config.dtd"><!--configuration 核心根标签--><configuration><!--引入数据库连接的配置文件--><properties resource="jdbc.properties"/><!--配置LOG4J--><settings><setting name="logImpl" value="log4j"/></settings><!--起别名--><typeAliases><package name="com.fs.bean"/></typeAliases><!--environments配置数据库环境，环境可以有多个。default属性指定使用的是哪个--><environments default="mysql"><!--environment配置数据库环境 id属性唯一标识--><environment id="mysql"><!-- transactionManager事务管理。 type属性，采用JDBC默认的事务--><transactionManager type="JDBC"></transactionManager><!-- dataSource数据源信息 type属性 连接池--><dataSource type="POOLED"><!-- property获取数据库连接的配置信息 --><property name="driver" value="${driver}" /><property name="url" value="${url}" /><property name="username" value="${username}" /><property name="password" value="${password}" /></dataSource></environment></environments><!-- mappers引入映射配置文件 --><mappers><mapper resource="com/fs/one_to_one/OneToOneMapper.xml"/><mapper resource="com/fs/one_to_many/OneToManyMapper.xml"/><mapper resource="com/fs/many_to_many/ManyToManyMapper.xml"/><!--若为注解,就配置注解扫描的包 --><!--配置映射关系--><mappers><package name="com.fs"/></mappers></mappers></configuration>
jdbc配置文件
driver=com.mysql.jdbc.Driverurl=jdbc:mysql://192.168.93.132:3306/db2username=rootpassword=root
log4j配置文件
# Global logging configuration# ERROR WARN INFO DEBUGlog4j.rootLogger=DEBUG, stdout# Console output...log4j.appender.stdout=org.apache.log4j.ConsoleAppenderlog4j.appender.stdout.layout=org.apache.log4j.PatternLayoutlog4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
bean实体类
一对一的多表查询XML
OneToOneMapper.xml
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapperPUBLIC "-////DTD Mapper 3.0//EN""/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.fs.table01.OneToOneMapper"><!--配置字段和实体对象属性的映射关系resultMap:配置映射关系,方法的返回值,id:起个名字type:那个实体对象的别名--><resultMap id="oneToOne" type="card"><!--id:配置主键标签column:查询结果的字段名property:实体类中对应的属性名--><id column="cid" property="id" /><!-- result:其余字段配置标签--><result column="number" property="number" /><!-- association：配置被包含对象的映射关系property：被包含对象的变量名javaType：被包含对象的数据类型--><association property="p" javaType="person"><id column="pid" property="id" /><result column="name" property="name" /><result column="age" property="age" /></association></resultMap><!--resultMap:多表查询属性封装--><select id="selectAll" resultMap="oneToOne">SELECT c.id cid,number,pid,NAME,age FROM card c,person p WHERE c.pid=p.id</select></mapper>
映射接口
OneToOneMapper
package com.fs.table01;import com.fs.bean.Card;import java.util.List;public interface OneToOneMapper {//查询全部public abstract List<Card> selectAll();}
测试类
package com.fs.table01;import com.fs.bean.Card;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 org.junit.Test;import java.io.InputStream;import java.util.List;public class Test01 {@Testpublic void selectAll() throws Exception{//1.加载核心配置文件InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");//2.获取SqlSession工厂对象SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);//3.通过工厂对象获取SqlSession对象SqlSession sqlSession = sqlSessionFactory.openSession(true);//4.获取OneToOneMapper接口的实现类对象OneToOneMapper mapper = sqlSession.getMapper(OneToOneMapper.class);//5.调用实现类的方法，接收结果List<Card> list = mapper.selectAll();//6.处理结果for (Card c : list) {System.out.println(c);}//7.释放资源sqlSession.close();is.close();}}
一对一XML图解
一对多的表查询XML
OneToManyMapper.xml
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapperPUBLIC "-////DTD Mapper 3.0//EN""/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.fs.table02.OneToManyMapper"><resultMap id="oneToMany" type="classes"><id column="cid" property="id"/><result column="cname" property="name"/><!--collection：配置被包含的集合对象映射关系property：被包含对象的变量名ofType：被包含对象的实际数据类型--><collection property="students" ofType="student"><id column="sid" property="id"/><result column="sname" property="name"/><result column="sage" property="age"/></collection></resultMap><select id="selectAll" resultMap="oneToMany">SELECT c.id cid,c.name cname,s.id sid,s.name sname,s.age sage FROM classes c,student s WHERE c.id=s.cid</select></mapper>
OneToManyMapper
package com.fs.table02;import com.fs.bean.Classes;import java.util.List;public interface OneToManyMapper {//查询全部public abstract List<Classes> selectAll();}
测试类
package com.fs.table02;import com.fs.bean.Classes;import com.fs.bean.Student;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 org.junit.Test;import java.io.InputStream;import java.util.List;public class Test01 {@Testpublic void selectAll() throws Exception{//1.加载核心配置文件InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");//2.获取SqlSession工厂对象SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);//3.通过工厂对象获取SqlSession对象SqlSession sqlSession = sqlSessionFactory.openSession(true);//4.获取OneToManyMapper接口的实现类对象OneToManyMapper mapper = sqlSession.getMapper(OneToManyMapper.class);//5.调用实现类的方法，接收结果List<Classes> classes = mapper.selectAll();//6.处理结果for (Classes cls : classes) {System.out.println(cls.getId() + "," + cls.getName());List<Student> students = cls.getStudents();for (Student student : students) {System.out.println("\t" + student);}}//7.释放资源sqlSession.close();is.close();}}
一对多XML图解
多对多的多表查询XML
ManyToManyMapper.xml
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapperPUBLIC "-////DTD Mapper 3.0//EN""/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.fs.table03.ManyToManyMapper"><!--配置字段和实体对象属性的映射关系resultMap:配置映射关系,方法的返回值,id:起个名字type:那个实体对象的别名--><resultMap id="manyToMany" type="student"><!--id:配置主键标签column:查询结果的字段名property:实体类中对应的属性名--><id column="sid" property="id"/><result column="sname" property="name"/><result column="sage" property="age"/><!--collection：配置被包含的集合对象映射关系property：被包含对象的变量名ofType：被包含对象的实际数据类型--><collection property="courses" ofType="course"><id column="cid" property="id"/><result column="cname" property="name"/></collection></resultMap><resultMap id="selectCourse" type="course"><id column="cid" property="id"/><result column="cname" property="name"/><collection property="students" ofType="student"><id column="sid" property="id"/><result column="sname" property="name"/><result column="sage" property="age"/></collection></resultMap><select id="selectAll" resultMap="manyToMany">SELECT sc.sid,s.name sname,s.age sage,sc.cid,c.name cname FROM student s,course c,stu_cr sc WHEREsc.sid=s.id AND sc.cid=c.id</select><select id="selectAllCourse" resultMap="selectCourse">SELECT sc.sid,s.name sname,s.age sage,sc.cid,c.name cname FROM student s,course c,stu_cr sc WHEREsc.sid=s.id AND sc.cid=c.id</select></mapper>
ManyToManyMapper
package com.fs.table03;import com.fs.bean.Course;import com.fs.bean.Student;import java.util.List;public interface ManyToManyMapper {//查询全部public abstract List<Student> selectAll();List<Course> selectAllCourse();}
** 测试类 **
package com.fs.table03;import com.fs.bean.Course;import com.fs.bean.Student;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 org.junit.Test;import java.io.InputStream;import java.util.List;public class Test01 {@Testpublic void selectAll() throws Exception{//1.加载核心配置文件InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");//2.获取SqlSession工厂对象SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);//3.通过工厂对象获取SqlSession对象SqlSession sqlSession = sqlSessionFactory.openSession(true);//4.获取ManyToManyMapper接口的实现类对象ManyToManyMapper mapper = sqlSession.getMapper(ManyToManyMapper.class);//5.调用实现类的方法，接收结果List<Student> students = mapper.selectAll();//6.处理结果for (Student student : students) {System.out.println(student.getId() + "," + student.getName() + "," + student.getAge());List<Course> courses = student.getCourses();for (Course cours : courses) {System.out.println("\t" + cours);}}//7.释放资源sqlSession.close();is.close();}@Testpublic void selectAllCourse() throws Exception{//1.加载核心配置文件InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");//2.获取SqlSession工厂对象SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);//3.通过工厂对象获取SqlSession对象SqlSession sqlSession = sqlSessionFactory.openSession(true);//4.获取ManyToManyMapper接口的实现类对象ManyToManyMapper mapper = sqlSession.getMapper(ManyToManyMapper.class);List<Course> courses = mapper.selectAllCourse();//5.调用实现类的方法，接收结果for (Course cours : courses) {System.out.println("课程编号:"+cours.getId()+","+"课程名字:"+cours.getName());List<Student> students = cours.getStudents();for (Student student : students) {System.out.println(student);}}//7.释放资源sqlSession.close();is.close();}}
多对多的图解
多表操作之注解
项目结构
由于项目结构差不多,我这里就直接图解,相当于配置文件删除了,多了接口,让MyBatis动态代理生成实现类,来完成操作
一对一的多表查询注解图解
一对多的多表查询注解图解
多对多的表查询注解图解
多表查询小结:
多表操作-XML
多表模型分类：一对一、一对多、多对多。
<resultMap>：配置字段和对象属性的映射关系标签。
id 属性：唯一标识
type 属性：实体对象类型
<id>：配置主键映射关系标签。
<result>：配置非主键映射关系标签。
column 属性：表中字段名称
property 属性： 实体对象变量名称
<association>：配置被包含对象的映射关系标签。
property 属性：被包含对象的变量名
javaType 属性：被包含对象的数据类型
<collection>：配置被包含集合对象的映射关系标签。
property 属性：被包含集合对象的变量名
ofType 属性：集合中保存的对象数据类型
注解多表操作
@Results：封装映射关系的父注解。
Result[] value()：定义了 Result 数组
@Result：封装映射关系的子注解。
column 属性：查询出的表中字段名称
property 属性：实体对象中的属性名称
javaType 属性：被包含对象的数据类型
one 属性：一对一查询固定属性
many 属性：一对多查询固定属性
@One：一对一查询的注解。
select 属性：指定调用某个接口中的方法
@Many：一对多查询的注解。
select 属性：指定调用某个接口中的方法
