多表查询(多对一、一对多)
一、复杂查询环境搭建
1.导入lombok
2.新建实体类Teacher,Student
3.建立Mapper接口
4.建立Mapper.xml文件
5.在核心配置文件中绑定注册我们的Mapper接口或者文件 【方式很多,随心选】
6.测试查询是否能够成功
搭建环境
◆ 多个学生,对应一个老师
◆ 对于学生这边而言,关联,多个学生,关联一个老师【多对一】
◆ 对于老师而言,集合,一个老师,有很多学生【一对多】
1.数据库创建teacher表和student表
CREATE TABLE `teacher` (
`id`INT (10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY( `id`),
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `teacher` (`id`, `name`) VALUES ('1', '清风老师');
CREATE TABLE `student` (
`id`INT (10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
`tid` INT(10) DEFAULT NULL,
PRIMARY KEY( `id`),
KEY `fktid` (`tid`),
CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher`(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');
)
2.新建一个项目
项目下载:
链接:https://pan.baidu.com/s/1erHaT_avxIKlhRGMb-qlfA
提取码:b3qb
表结构:
工具类
package com.cy.utils;
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;
//SqlSessionFactory --> sqlSession
public class MybatisUtils {
static SqlSessionFactory sqlSessionFactory = null;
static {
try {
//使用Mybatis第一步 :获取sqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession();
}
}
Student 类
package com.cy.pojo;
public class Student {
private int id;
private String name;
//学生需要关联一个老师!
private Teacher teacher;
}
Teacher 类
package com.cy.pojo;
import lombok.Data;
@Data
public class Teacher {
private int id;
private String name;
}
StudentMapper 接口
package com.cy.dao;
public interface StudentMapper {
}
TeacherMapper 接口
package com.cy.dao;
import com.cy.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
public interface TeacherMapper {
@Select("select * from teacher where id = #{tid}")
Teacher getTeacher(@Param("tid") int id);
}
配置数据库连接
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?userSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
username=root
password=root
配置环境
<?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核心配置文件-->
<configuration>
<!--引入外部配置文件-->
<properties resource="application.properties"/>
<!--标准的日志工厂实现-->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<!--给实体类起别名-->
<typeAliases>
<package name="com.cy.pojo"/>
</typeAliases>
<!--配置环境-->
<environments default="mysql">
<!--配置mysql环境-->
<environment id="mysql">
<!--配置事务的类型-->
<transactionManager type="JDBC"/>
<!--配置数据源(连接池)-->
<dataSource type="POOLED">
<!--配置连接数据库的四个基本信息-->
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper class="com.cy.dao.TeacherMapper"/>
<mapper class="com.cy.dao.StudentMapper"/>
</mappers>
</configuration>
测试类:
package com.cy.dao;
import com.cy.pojo.Teacher;
import com.cy.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class MyTest {
public static void main(String[] args) {
SqlSession sqlSession= MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher(1);
System.out.println(teacher);
sqlSession.close();
}
}
二、多对一
1.查询所有的学生信息,以及对应的老师的信息!
分析sql查询语句
select s.id,s.name,t.name from student s,teacher t where s.tid =t.id;
修改接口
package com.cy.dao;
import com.cy.pojo.Student;
import java.util.List;
public interface StudentMapper {
//查询所有的学生信息,以及对应的老师的信息!
public List<Student> getStudent();
}
配置StudentMapper.xml
<mapper namespace="com.cy.dao.StudentMapper">
<select id="getStudent" resultType="Student">
select * from student;
</select>
</mapper>
测试:
@Test
public void testStudent(){
SqlSession sqlSession=MybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = mapper.getStudent();
for (Student student:studentList){
System.out.println(student);
}
sqlSession.close();
}
运行结果:
2. 按照查询嵌套处理
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--
思路:
1. 查询所有的学生信息
2. 根据查询出来的学生的tid寻找特定的老师 (子查询)
-->
<mapper namespace="com.cy.dao.StudentMapper">
<!--先查学生-->
<select id="getStudent" resultMap="StudentTeacher">
select * from student;
</select>
<!--单独处理学生中老师的字段-->
<resultMap id="StudentTeacher" type="Stuent">
<result property="id" column="id"/>
<result property="name" column="name"/>
<!--复杂的属性,我们需要单独出来
对象:association /关联(association)
集合:collection
-->
<collection property="teacher" column="tid" javaType="teacher" select="getTeacher"/>
</resultMap>
<!--在查老师-->
<select id="getTeacher" resultType="Teacher">
select * from teacher where id=#{id}
</select>
</mapper>
property:映射到列结果的字段或属性
column:数据库中的列名或者列的别名
javaType:一个java类的完全限定名,或一个类型别名。
jdbcType:JDBC类型
resultMap:结果映射的 ID,可以将嵌套的结果集映射到一个合适的对象树中。
测试:
3.按照结果嵌套处理
<!--按照结果集查询-->
<select id="getStudent2" resultMap="StudentTeacher2">
select s.id sid,s.name sname,t.id tid,t.name tname
from student s,teacher t
where s.tid =t.id
</select>
<resultMap id="StudentTeacher2" type="Student">
<result property="id" column="sid"/>
<result property="id" column="tid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
</association>
</resultMap>
第一种可以理解为:查出老师和学生,通过子查询 查老师
第二种可以理解为: 先查出学生的所有值,对于具体的tname,再去teacher里面查
回顾Mysql多对一查询方式:
◆ 子查询 (按照查询嵌套)
◆ 联表查询 (按照结果嵌套)
三、一对多
一个老师多个学生;
对于老师而言,就是一对多的关系;
1.环境搭建,和之前一样
新建一个项目或者把刚刚写的内容清理和开始的模板项目一样
这里就省略环境搭建了,和上面一样,把写的查询删除就可以了
修改实体类
package com.cy.pojo;
import lombok.Data;
import java.util.List;
@Data
public class Teacher {
private int id;
private String name;
//一个老师拥有多个学生
private List<Student> students;
}
package com.cy.pojo;
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private int teacher;
}
2. 按照结果嵌套处理
接口
public interface TeacherMapper {
Teacher getTeacher(@Param("tid") int id);
}
<!--按结果嵌套查询-->
<select id="getTeacher" resultMap="TeacherStudent">
select s.id sid,s.name sname,t.name tname,t.id tid
from student s ,teacher t
where s.tid = t.id and t.id =#{tid}
</select>
<resultMap id="TeacherStudent" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<!--复杂的属性,我们需要单独处理 对象:association 集合:collection
javaType=""指定属性的类型!
集合中的泛型信息,我们使用ofType获取
-->
<collection property="students" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
测试:
@Test
public void test(){
SqlSession sqlSession= MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher(1);
System.out.println(teacher);
sqlSession.close();
}
3.按照查询嵌套处理
<!--=================================================-->
<select id="getTeacher2" resultMap="TeacherStudent2">
select * from teacher where id =#{tid}
</select>
<resultMap id="TeacherStudent2" type="Teacher">
<collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/>
</resultMap>
<select id="getStudentByTeacherId" resultType="Student">
select * from student where tid = #{tid}
</select>
小结
1.关联 - association 【多对一】
2.集合 - collection 【一对多】
3.javaType & ofType
◆ JavaType用来指定实体类中的类型
◆ ofType用来指定映射到List或者集合中的pojo类型,泛型中的约束类型
注意点:
◆ 保证SQL的可读性,尽量保证通俗易懂
◆ 注意一对多和多对一,属性名和字段的问题
◆ 如果问题不好排查错误,可以使用日志,建议使用Log4j
面试高频
◆ Mysql引擎
◆ InnoDB底层原理
◆ 索引
◆ 索引优化