目录
一、多表联查
1.准备阶段
2.多对一查询
1、join联查
2.嵌套查询(子查询)
一、多表联查
1.准备阶段
数据库表2个
![](https://img-blog.csdnimg.cn/39cd932f19f14c528fcf0e66d3f64fc6.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LS-5pav5rGAX2FiYw==,size_20,color_FFFFFF,t_70,g_se,x_16)
CREATE TABLE `emp` (
`emp_id` int NOT NULL AUTO_INCREMENT,
`emp_name` varchar(20) DEFAULT NULL,
`emp_age` int DEFAULT NULL,
`part_id` int DEFAULT NULL,
PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('1', '张三', '22', '2');
INSERT INTO `emp` VALUES ('2', '李四', '32', '1');
INSERT INTO `emp` VALUES ('3', '王五', '21', '2');
INSERT INTO `emp` VALUES ('4', '二夫人', '24', '3');
INSERT INTO `emp` VALUES ('5', '李夫人', '32', '1');
CREATE TABLE `part` (
`part_id` int NOT NULL AUTO_INCREMENT,
`part_name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`part_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ----------------------------
-- Records of part
-- ----------------------------
INSERT INTO `part` VALUES ('1', '财务部');
INSERT INTO `part` VALUES ('2', '人力部');
INSERT INTO `part` VALUES ('3', '后勤部');
建maven项目,导入相关依赖等 我前面一片文章详细介绍过 此处就不再冗述
此处以查询员工号为5的员工的所有信息为例
2.多对一查询
1、join联查
创建相关的实体类
![](https://img-blog.csdnimg.cn/5a328904c4734a17ac94d7c448000104.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LS-5pav5rGAX2FiYw==,size_20,color_FFFFFF,t_70,g_se,x_16)
package com.exy.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @program: Mybatis08
* @description:
* @author: jdy
* @create: 2021-12-08 19:03
**/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp {
private int empid;
private String empname;
private Integer empage;
private int partid;
private Part part;
}
package com.exy.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @program: Mybatis08
* @description:
* @author: jdy
* @create: 2021-12-08 19:03
**/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Part {
private int partid;
private String partname;
}
创建dao接口并添加以id查询的方法
![](https://img-blog.csdnimg.cn/baf39400127d43319368ad51aab29125.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LS-5pav5rGAX2FiYw==,size_20,color_FFFFFF,t_70,g_se,x_16)
List selectById(int id);
写出EmpMapper.xml查询相关的代码
![](https://img-blog.csdnimg.cn/32562072b0504db985296c67d72bb375.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LS-5pav5rGAX2FiYw==,size_20,color_FFFFFF,t_70,g_se,x_16)
select * from emp e join part p on e.part_id=p.part_id where e.emp_id=5
添加测试类进行测试
![](https://img-blog.csdnimg.cn/ef866861ad4f425ea061f957138d01c9.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LS-5pav5rGAX2FiYw==,size_20,color_FFFFFF,t_70,g_se,x_16)
import com.exy.dao.EmpDao;
import com.exy.entity.Emp;
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.Before;
import org.junit.Test;
import java.io.Reader;
import java.util.List;
/**
* @program: Mybatis08
* @description:
* @author: jdy
* @create: 2021-12-08 19:15
**/
public class ETest {
private SqlSession session;
@Before
public void before() throws Exception{
Reader reader = Resources.getResourceAsReader("mybatis.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
session=sessionFactory.openSession();
}
@Test
public void test01(){
EmpDao empDao = session.getMapper(EmpDao.class);
List emps = empDao.selectById(5);
System.out.println(emps);
}
}
结果如图
![](https://img-blog.csdnimg.cn/8c8a3e05e93e4826a2a7f6f0c0866e3d.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LS-5pav5rGAX2FiYw==,size_20,color_FFFFFF,t_70,g_se,x_16)
2.嵌套查询(子查询)
嵌套查询也就是先查出一个表中的相关数据 再用得出的数据查询下一个表 从而的出数据
![](https://img-blog.csdnimg.cn/64c1d86b3be74c9f8fbc6fd5cc309137.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LS-5pav5rGAX2FiYw==,size_20,color_FFFFFF,t_70,g_se,x_16)
分析得出 我们想查出员工为5的部门的话需要 本表的部门号 再用部门号查询另一个表
先根据part查询部门
![](https://img-blog.csdnimg.cn/b2f026c174a441099d6e3b52cf51ddf8.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LS-5pav5rGAX2FiYw==,size_20,color_FFFFFF,t_70,g_se,x_16)
select * from part where part_id=#{partid}
![](https://img-blog.csdnimg.cn/9dfd8ebfaa9f420da5da2ccd9e653f11.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LS-5pav5rGAX2FiYw==,size_20,color_FFFFFF,t_70,g_se,x_16)
zdEmpMapper中添加代码
![](https://img-blog.csdnimg.cn/4ffc502337d5424695c6e498b8870063.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LS-5pav5rGAX2FiYw==,size_20,color_FFFFFF,t_70,g_se,x_16)
注意我图中标记的 是引用上一个的查询的结果中的列
添加测试代码
![](https://img-blog.csdnimg.cn/acf803e63f114138ba3e3e4def6dea1b.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LS-5pav5rGAX2FiYw==,size_20,color_FFFFFF,t_70,g_se,x_16)
@Test
public void test03(){
PartDao partDao = session.getMapper(PartDao.class);
List emps = partDao.selectById(1);
System.out.println(emps);
}
运行结果如图所示
![](https://img-blog.csdnimg.cn/96c0c28364d949e7b1222720034a2c08.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LS-5pav5rGAX2FiYw==,size_20,color_FFFFFF,t_70,g_se,x_16)
发出了两条sql
注:
![](https://img-blog.csdnimg.cn/8b2ec6b7e6e740638bd0b225c3731676.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6LS-5pav5rGAX2FiYw==,size_20,color_FFFFFF,t_70,g_se,x_16)
|