SpringBoot Data JPA 多表关联查询
一、数据库架构设计
1.1 数据表结构
- 传感器基础信息表
sensor_info - 传感器种类表
sensor_type - 传感器（所属面）位置信息表
sensor_aspect
架构如下：
2.2 建立数据库表：
CREATE DATABASE /*!32312 IF NOT EXISTS*/`dstructure` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `dstructure`;
/*Table structure for table `rocketmq_conf` */
DROP TABLE IF EXISTS `rocketmq_conf`;
CREATE TABLE `rocketmq_conf` (
`id` int NOT NULL AUTO_INCREMENT,
`nameserver_addr` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQUE_ADDR` (`nameserver_addr`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `rocketmq_conf` */
insert into `rocketmq_conf`(`id`,`nameserver_addr`) values (1,'47.106.243.139:9876');
/*Table structure for table `sensor_aspect` */
DROP TABLE IF EXISTS `sensor_aspect`;
CREATE TABLE `sensor_aspect` (
`id` int NOT NULL AUTO_INCREMENT,
`aspect_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`aspect_desc` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQUE` (`aspect_type`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `sensor_aspect` */
insert into `sensor_aspect`(`id`,`aspect_type`,`aspect_desc`) values (1,'top','上'),(2,'bottom','下'),(3,'left','左'),(4,'right','右'),(5,'front','前'),(6,'behind','后'),(7,'deck','第一层甲板'),(8,'airtight_doors','气密门');
/*Table structure for table `sensor_info` */
DROP TABLE IF EXISTS `sensor_info`;
CREATE TABLE `sensor_info` (
`id` int NOT NULL AUTO_INCREMENT,
`sensor_id` varchar(10) NOT NULL,
`sensor_site_id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`sensor_type_id` int NOT NULL,
`max` decimal(4,0) NOT NULL,
`min` decimal(4,0) NOT NULL,
`sensor_aspect_id` int NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQUE_sensor_id` (`sensor_id`),
KEY `FR_ASPECT_ID` (`sensor_aspect_id`),
KEY `fr_site_id` (`sensor_site_id`),
KEY `FK3qvkpeexq73liv8vdwl9mc39g` (`sensor_type_id`),
CONSTRAINT `FK3qvkpeexq73liv8vdwl9mc39g` FOREIGN KEY (`sensor_type_id`) REFERENCES `sensor_type` (`id`),
CONSTRAINT `FR_ASPECT_ID` FOREIGN KEY (`sensor_aspect_id`) REFERENCES `sensor_aspect` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `sensor_info` */
insert into `sensor_info`(`id`,`sensor_id`,`sensor_site_id`,`sensor_type_id`,`max`,`min`,`sensor_aspect_id`) values (1,'WD001','NO.1',1,'22','11',1),(2,'WD002','2',1,'23','434',2),(3,'WD003','3',1,'23','23',2),(4,'YB001','3',2,'23','23',4),(5,'YB002','2',2,'34','45',5),(6,'YB003','2',2,'45','45',4);
/*Table structure for table `sensor_point` */
DROP TABLE IF EXISTS `sensor_point`;
CREATE TABLE `sensor_point` (
`id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`x` float NOT NULL,
`y` float NOT NULL,
`z` float NOT NULL,
`point_sensor_id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `sensor_point` */
insert into `sensor_point`(`id`,`x`,`y`,`z`,`point_sensor_id`) values ('1',1,1,1,'WD001');
/*Table structure for table `sensor_type` */
DROP TABLE IF EXISTS `sensor_type`;
CREATE TABLE `sensor_type` (
`id` int NOT NULL AUTO_INCREMENT,
`sensor_type` varchar(20) NOT NULL,
`desc` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQUE_TYPE` (`sensor_type`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `sensor_type` */
insert into `sensor_type`(`id`,`sensor_type`,`desc`) values (1,'WD_DATA','温度传感器'),(2,'YB_DATA','应变传感器'),(3,'YL_DATA','压力传感器'),(4,'CJ_DATA','冲击传感器'),(5,'WY_DATA','位移传感器'),(6,'ZD_DATA','震动传感器');
/*Table structure for table `topic` */
DROP TABLE IF EXISTS `topic`;
CREATE TABLE `topic` (
`topic_id` int NOT NULL AUTO_INCREMENT,
`topic_type` varchar(10) NOT NULL,
`desc` varchar(30) DEFAULT NULL,
PRIMARY KEY (`topic_id`),
UNIQUE KEY `UNIQUE_TOPIC_TYPE` (`topic_type`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `topic` */
insert into `topic`(`topic_id`,`topic_type`,`desc`) values (1,'WD_DATA','温度数据'),(2,'YB_DATA','应变数据'),(3,'YL_DATA','压力数据'),(4,'WY_DATA','位移数据'),(5,'CJ_DATA','冲击数据'),(6,'ZD_DATA','震动数据'),(7,'Status','传感器状态信息'),(8,'Dispose','传感器配置信息');
二、SpringBoot整合JPA多表查询
2.1 环境配置
- pom依赖
<!--Springboot data jpa-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--数据库连接驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
2.application.yml配置
如果是 application.properties，修改成application.yml，然后添加一下配置。
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/dstructure?useUnicode=true&characterEncoding=UTF-8&userSSL=false&serverTimezone=GMT%2B8
username: root
password: 123456
jpa:
hibernate:
ddl-auto: update
show-sql: true
2.2 建立数据库映射模型
- 常用的注解
@Entity
作用：指定当前类是实体类。
@Table
作用：指定实体类和表之间的对应关系。
属性：
name：指定数据库表的名称
@Id
作用：指定当前字段是主键。
@GeneratedValue
作用：指定主键的生成方式。。
属性：
strategy ：指定主键生成策略。
@Column
作用：指定实体类属性和数据库表之间的对应关系
属性：
name：指定数据库表的列名称。
unique：是否唯一
nullable：是否可以为空
inserttable：是否可以插入
updateable：是否可以更新
columnDefinition: 定义建表时创建此列的DDL
secondaryTable: 从表名。如果此列不建在主表上（默认建在主表），该属性定义该列所在从表的名字搭建开发环境[重点]
在建立实体类时，第一步只需要对数据库基本属性映射就行，不需要考虑外键映射
切记，toString方法里不要打印了外键，只打印基本属性就行。否则可能会导致后续的实体关联中，由于相互嵌套造成程序堆栈溢出
- 建立
InfoModel类
@Setter
@Getter
@Entity
@Table(name = "sensor_info")
public class InfoModel {
@Id//声明当前私有属性为主键
@GeneratedValue(strategy=GenerationType.IDENTITY) //配置主键的生成策略
private int id;
@Column(name = "max")
private float max;
@Column(name = "min")
private float min;
@Override
public String toString() {
return "InfoModel{" +
"id=" + id +
", max=" + max +
", min=" + min +
'}';
}
}
- 建立
TypeModel类
@Setter
@Getter
@Entity
@Table(name = "sensor_type")
public class TypeModel {
@Id//声明当前私有属性为主键
@GeneratedValue(strategy=GenerationType.IDENTITY) //配置主键的生成策略
private int id;
@Column(name = "sensor_type")
private String sensorType;
@Column(name = "desc")
private String desc;
@Override
public String toString() {
return "TypeModel{" +
"id=" + id +
", sensorType='" + sensorType + '\'' +
", desc='" + desc + '\'' +
'}';
}
}
- 建立
AspectModel
@Setter
@Getter
@Entity
@Table(name = "sensor_aspect")
public class AspectModel {
@Id//声明当前私有属性为主键
@GeneratedValue(strategy=GenerationType.IDENTITY) //配置主键的生成策略
private int id;
@Column(name = "aspect_type")
private String aspectType;
@Column(name = "desc")
private String desc;
@Override
public String toString() {
return "AspectModel{" +
"id=" + id +
", aspectType='" + aspectType + '\'' +
", desc='" + desc + '\'' +
'}';
}
}
2.2 编写DAO层接口
InfoDao
/*
* 第一个参数：接口对应的 实体对象
* 第二个参数：实体的主键数据包装类
* */
public interface InfoDao extends JpaRepository<InfoModel,Integer> {
}
TypeDao
public interface TypeDao extends JpaRepository<TypeModel,Integer> {
}
AspectDao
public interface AspectDao extends JpaRepository<AspectModel,Integer> {
}
2.3 在测试类中，测试数据库映射是否正确。
@SpringBootTest
class DatadumpApplicationTests {
@Autowired
private InfoDao infoDao;
@Test
void baseTest() {
List<InfoModel> infos = infoDao.findAll();
for (InfoModel info : infos) {
System.out.println(info);
}
}
}
发现控制台打印正常，说明实体类与数据库映射成功：
InfoModel{id=1, max=22.0, min=11.0}
InfoModel{id=2, max=23.0, min=434.0}
InfoModel{id=3, max=23.0, min=23.0}
InfoModel{id=4, max=23.0, min=23.0}
InfoModel{id=5, max=34.0, min=45.0}
InfoModel{id=6, max=45.0, min=45.0}
2.3 在JPA框架中表关系的分析步骤
-
第一步：首先确定两张表之间的关系。
如果关系确定错了，后面做的所有操作就都不可能正确。
-
第二步：在数据库中实现两张表的关系
-
第三步：在实体类中描述出两个实体的关系
-
第四步：配置出实体类和数据库表的关系映射（重点）
在进行多表关联查询时候，（本文章是一对多）第一步，要分清谁是“多”的一方，谁是“一”的一方。
比如 学生与班级，一个学生只属于一个班级，一个班级有很多学生。因此学生是多，班级是一。
而在本文的数据表中，一个传感器只有一个类型type，因此sensor_info和sensor_type是一对多的关系，sensor_info是一，而sensor_type是多。
同理sensor_info和sensor_aspect是一对多的关系，sensor_info是一，而sensor_aspect是多。
2.4 映射的注解说明
@OneToMany:
作用：建立一对多的关系映射
属性：
targetEntityClass：指定多的多方的类的字节码
mappedBy：指定从表实体类中引用主表对象的名称。
cascade：指定要使用的级联操作
fetch：指定是否采用延迟加载
orphanRemoval：是否使用孤儿删除
@ManyToOne
作用：建立多对一的关系
属性：
targetEntityClass：指定一的一方实体类字节码
cascade：指定要使用的级联操作
fetch：指定是否采用延迟加载
optional：关联是否可选。如果设置为false，则必须始终存在非空关系。
@JoinColumn
作用：用于定义主键字段和外键字段的对应关系。
属性：
name：指定外键字段的名称
referencedColumnName：指定引用主表的主键字段名称
unique：是否唯一。默认值不唯一
nullable：是否允许为空。默认值允许。
insertable：是否允许插入。默认值允许。
updatable：是否允许更新。默认值允许。
columnDefinition：列的定义信息。
2.5 多表关联查询
- 为
多的一方的实体类添加外键，即在InfoModel类中添加以下代码：
/*
* sensor_info 和 sensor_type 表关联
* sensor_info 是多方
*
* name是指的是多方（从表）的外键（建立在从表）
* referencedColumnName 指的是引用一方(主表)的主键字段名称
*
*
* */
@ManyToOne(targetEntity = TypeModel.class)
@JoinColumn(name = "sensor_type_id",referencedColumnName = "id")
private TypeModel type;
@ManyToOne(targetEntity = AspectModel.class)
@JoinColumn(name = "sensor_aspect_id",referencedColumnName = "id")
private AspectModel aspect;
- 为
一方添加外键关联
TypeModel 类
/*
* mappedBy 是参照 多方的映射关系。只需要 填上对方的相应的外键的私有属性
* 一对多时候需要加上，fetch = FetchType.EAGER， 设定其在映射的时候立刻加载数据库，避免懒加载造成的 会话关闭 而无法加载数据
* 这里的type 就是 InfoModel中的私有属性type
* */
@OneToMany(mappedBy = "type",targetEntity = InfoModel.class,fetch = FetchType.EAGER)
private Set<InfoModel> infos=new HashSet<InfoModel>();
AspectModel 类
/*
* mappedBy 是参照 多方的映射关系。只需要 填上对方的相应的外键的私有属性
* 一对多时候需要加上，fetch = FetchType.EAGER， 设定其在映射的时候立刻加载数据库，避免懒加载造成的 会话关闭 而无法加载数据
* 这里的type 就是 InfoModel中的私有属性 aspect
* */
@OneToMany(mappedBy = "aspect",targetEntity = InfoModel.class,fetch = FetchType.EAGER)
private Set<InfoModel> infos=new HashSet<InfoModel>();
2.6 测试多表查询
@SpringBootTest
class DatadumpApplicationTests {
@Autowired
private InfoDao infoDao;
@Autowired
private TypeDao typeDao;
@Autowired
private AspectDao aspectDao;
@Test
void multiTableQueryTest() {
List<InfoModel> infos = infoDao.findAll();
for (InfoModel info : infos) {
System.out.println(info+"----传感器种类为："+info.getType()+"----传感器的面信息为："+info.getAspect());
}
System.out.println("==================");
List<TypeModel> types = typeDao.findAll();
for (TypeModel type : types) {
System.out.println(type+"---该类型包含以下传感器："+type.getInfos());
}
}
}
发现控制台进行多表查询：
InfoModel{id=1, max=22.0, min=11.0}----传感器种类为：TypeModel{id=1, sensorType='WD_DATA', desc='温度传感器'}----传感器的面信息为：AspectModel{id=1, aspectType='top', desc='上'}
InfoModel{id=2, max=23.0, min=434.0}----传感器种类为：TypeModel{id=1, sensorType='WD_DATA', desc='温度传感器'}----传感器的面信息为：AspectModel{id=2, aspectType='bottom', desc='下'}
InfoModel{id=3, max=23.0, min=23.0}----传感器种类为：TypeModel{id=1, sensorType='WD_DATA', desc='温度传感器'}----传感器的面信息为：AspectModel{id=2, aspectType='bottom', desc='下'}
InfoModel{id=4, max=23.0, min=23.0}----传感器种类为：TypeModel{id=2, sensorType='YB_DATA', desc='应变传感器'}----传感器的面信息为：AspectModel{id=4, aspectType='right', desc='右'}
InfoModel{id=5, max=34.0, min=45.0}----传感器种类为：TypeModel{id=2, sensorType='YB_DATA', desc='应变传感器'}----传感器的面信息为：AspectModel{id=5, aspectType='front', desc='前'}
InfoModel{id=6, max=45.0, min=45.0}----传感器种类为：TypeModel{id=2, sensorType='YB_DATA', desc='应变传感器'}----传感器的面信息为：AspectModel{id=4, aspectType='right', desc='右'}
==================
TypeModel{id=1, sensorType='WD_DATA', desc='温度传感器'}---该类型包含以下传感器：[InfoModel{id=1, max=22.0, min=11.0}, InfoModel{id=2, max=23.0, min=434.0}, InfoModel{id=3, max=23.0, min=23.0}]
TypeModel{id=2, sensorType='YB_DATA', desc='应变传感器'}---该类型包含以下传感器：[InfoModel{id=5, max=34.0, min=45.0}, InfoModel{id=4, max=23.0, min=23.0}, InfoModel{id=6, max=45.0, min=45.0}]
TypeModel{id=3, sensorType='YL_DATA', desc='压力传感器'}---该类型包含以下传感器：[]
TypeModel{id=4, sensorType='CJ_DATA', desc='冲击传感器'}---该类型包含以下传感器：[]
TypeModel{id=5, sensorType='WY_DATA', desc='位移传感器'}---该类型包含以下传感器：[]
TypeModel{id=6, sensorType='ZD_DATA', desc='震动传感器'}---该类型包含以下传感器：[]