Spring Data JPA Repository 和数据库视图
1、概览
数据库视图（Database View）是关系型数据库系统中的一种类似表的结构，其中的数据源来自一个或多个连接在一起的表。
Spring Data Repository 通常用于数据库表，但也可以有效地应用于数据库视图。本文将带你了解如何在 Spring Data JPA 中使用 Repository 从数据库视图检索数据。
2、数据库表设置
本文使用 H2 数据库系统进行数据定义，并使用 SHOP 和 SHOP_TRANSACTION 这两个示例表演示数据库视图概念。
SHOP 表存储商店信息：
CREATE TABLE SHOP
(
shop_id int AUTO_INCREMENT,
shop_location varchar(100) NOT NULL UNIQUE,
PRIMARY KEY(shop_id)
);
SHOP_TRANSACTION 表存储与商店相关的交易记录，并通过 shop_id 对 SHOP 表进行引用：
CREATE TABLE SHOP_TRANSACTION
(
transaction_id bigint AUTO_INCREMENT,
transaction_date date NOT NULL,
shop_id int NOT NULL,
amount decimal(8,2) NOT NULL,
PRIMARY KEY(transaction_id),
FOREIGN KEY(shop_id) REFERENCES SHOP(shop_id)
);
在实体-关系（ER）模型中，可以将其说明为 “一对多” 的关系，即一个商店可以有多笔交易。但是，每笔交易只与一家商店相关联。可以用 ER 图直观地表示这一点：
3、数据库视图
数据库视图提供一个虚拟表，从预定义查询的结果中收集数据。使用数据库视图比使用连接查询更有优势：
- 简洁性 - 视图封装了复杂的连接，无需重复重写相同的连接查询。
- 安全性 - 视图可能只包括基础表中的一个数据子集，从而降低了暴露基础表中敏感信息的风险。
- 可维护性 - 在基表结构发生变化时更新视图定义，从而避免了在应用中修改引用已更改基表的查询的需要。
3.1、标准视图和物化视图
有两种常见的数据库视图，它们的用途各不相同：
- 标准视图 - 这些视图在被查询时通过执行预定义的 SQL 查询生成。它们本身不存储数据。所有数据都存储在底层基础表中。
- 物化视图 - 物化视图与标准视图类似，也是由预定义的 SQL 查询生成的。相比之下，它们会将查询结果复制到数据库中的一个物理表中。后续查询将从该表中检索数据，而不是动态生成数据。
下表重点介绍了标准视图和物化视图的不同特性，有助于根据具体要求选择合适的视图类型：
| 标准视图 | 物化视图 | |
|---|---|---|
| 数据源 | 通过预定义查询从基础表动态生成 | 包含预定义查询数据的物理表 |
| 性能 | 由于动态查询生成，速度较慢 | 从物理表检索数据，因此速度更快 |
| 数据实时性 | 始终返回最新数据 | 可能会过时，需要定期刷新 |
| 用例 | 适用于实时数据 | 适用于计算开销较大的查询，在数据的实时性不是关键的情况下 |
3.2、标准视图示例
在本例中，我们想定义一个视图来总结每个日历月的店铺销售总额。事实证明，物化视图是合适的，因为前几个月的销售额保持不变。除非需要当前月份的数据，否则计算总销售额不需要实时数据。
但是，H2 数据库不支持物化视图。我们需要创建一个标准视图：
CREATE VIEW SHOP_SALE_VIEW AS
SELECT ROW_NUMBER() OVER () AS id, shop_id, shop_location, transaction_year, transaction_month, SUM(amount) AS total_amount
FROM (
SELECT
shop.shop_id, shop.shop_location, trans.amount,
YEAR(transaction_date) AS transaction_year, MONTH(transaction_date) AS transaction_month
FROM SHOP shop, SHOP_TRANSACTION trans
WHERE shop.shop_id = trans.shop_id
) SHOP_MONTH_TRANSACTION
GROUP BY shop_id, transaction_year, transaction_month;
在查询视图时，我们可以获得如下数据：
| id | shop_id | shop_location | transaction_year | transaction_month | amount |
|---|---|---|---|---|---|
| 1 | 1 | Ealing | 2024 | 1 | 10.78 |
| 2 | 1 | Ealing | 2024 | 2 | 13.58 |
| 3 | 1 | Ealing | 2024 | 3 | 14.48 |
| 4 | 2 | Richmond | 2024 | 1 | 17.98 |
| 5 | 2 | Richmond | 2024 | 2 | 8.49 |
| 6 | 2 | Richmond | 2024 | 3 | 13.78 |
4、实体 Bean 定义
现在，可以为数据库视图 SHOP_SALE_VIEW 定义实体 Bean。事实上，该定义与为普通数据库表定义实体 Bean 几乎相同。
在 JPA 中，实体 Bean 要求必须有主键。在数据库视图中定义主键时，可以考虑两种策略。
4.1、物理主键
在大多数情况下，可以选择视图中的一列或多列来标识数据库视图中某一行的唯一性。在我们的方案中，商店 ID、年和月可以唯一标识视图中的每一行。
因此，可以通过 shop_id、transaction_year 和 transaction_month 这三列来派生复合主键。在 JPA 中，必须首先定义一个单独的类来表示复合主键：
public class ShopSaleCompositeId {
private int shopId;
private int year;
private int month;
// 构造函数、Get、Set
}
随后，用 @EmbeddedId 将这个复合 ID 类嵌入到实体类中，并用 @AttributeOverrides 对复合 ID 进行注解，从而定义列映射：
@Entity
@Table(name = "SHOP_SALE_VIEW")
public class ShopSale {
@EmbeddedId
@AttributeOverrides({
@AttributeOverride( name = "shopId", column = @Column(name = "shop_id")),
@AttributeOverride( name = "year", column = @Column(name = "transaction_year")),
@AttributeOverride( name = "month", column = @Column(name = "transaction_month"))
})
private ShopSaleCompositeId id;
@Column(name = "shop_location", length = 100)
private String shopLocation;
@Column(name = "total_amount")
private BigDecimal totalAmount;
// 构造函数、Getter、Setter
}
4.2、虚拟主键
在某些情况下，由于缺乏能确保数据库视图中每一行唯一性的列组合，定义物理主键并不可行。作为一种变通方法，可以生成一个 虚拟主键 来模拟行的唯一性。
在我们的数据库视图定义中，有一个额外的列 id，利用 ROW_NUMBER() OVER () 来生成作为 id 的行号。采用虚拟主键策略时的实体类定义如下：
@Entity
@Table(name = "SHOP_SALE_VIEW")
public class ShopSale {
@Id
@Column(name = "id")
private Long id;
@Column(name = "shop_id")
private int shopId;
@Column(name = "shop_location", length = 100)
private String shopLocation;
@Column(name = "transaction_year")
private int year;
@Column(name = "transaction_month")
private int month;
@Column(name = "total_amount")
private BigDecimal totalAmount;
// 构造函数、Getter、Setter
}
需要注意的是，这些 id 是当前结果集的特定 id。重新查询时，分配给每一行的行号可能会不同。因此，后续查询中的相同行号可能代表数据库视图中的不同行。
5、视图 Repository
根据数据库的不同，Oracle 等系统可能支持可更新视图，允许在某些条件下对视图进行数据更新。不过，数据库视图大多是只读的。
对于只读数据库视图，我们没有必要在 Repository 中公开数据修改方法，如 save() 或 delete()。由于数据库系统不支持此类操作，因此尝试调用这些方法时会出现异常：
org.springframework.orm.jpa.JpaSystemException: could not execute statement [Feature not supported: "TableView.addRow"; SQL statement:
insert into shop_sale_view (transaction_month,shop_id,shop_location,total_amount,transaction_year,id) values (?,?,?,?,?,?) [50100-224]] [insert into shop_sale_view (transaction_month,shop_id,shop_location,total_amount,transaction_year,id) values (?,?,?,?,?,?)]
在这种情况下，需要排除这些方法，在定义 Spring Data JPA Repository 时只公开数据检索方法。
5.1、物理主键
对于有物理主键的视图，可以定义一个新的 Base Repository 接口，该接口只公开数据检索方法：
@NoRepositoryBean
public interface ViewRepository<T, K> extends Repository<T, K> {
long count();
boolean existsById(K id);
List<T> findAll();
List<T> findAllById(Iterable<K> ids);
Optional<T> findById(K id);
}
@NoRepositoryBean 注解表明此接口是一个 Base Repository 接口，并指示 Spring Data JPA 不要在运行时创建此接口的实例。在这个 Repository 接口中，包含了 ListCrudRepository 的所有数据检索方法，但排除了所有数据更改方法。
对于具有复合 ID 的实体 Bean，我们继承了 ViewRepository，并定义了一个额外的方法，用于查询商店销售的 shopId：
public interface ShopSaleRepository extends ViewRepository<ShopSale, ShopSaleCompositeId> {
List<ShopSale> findByIdShopId(Integer shopId);
}
将查询方法定义为 findByIdShopId() 而不是 findByShopId() ，因为它源自 ShopSale 实体类中的 id.shopId 属性。
5.2、虚拟主键
当处理带有虚拟主键的数据库视图的 Repository 设计时，方法略有不同，因为虚拟主键是人为的，无法真正识别数据行的唯一性。
基于这种性质，需要定义另一个 Base Repository 接口，该接口也排除了按主键查询的方法。这是因为我们使用的是虚拟主键，使用假主键检索数据毫无意义：
public interface ViewNoIdRepository<T, K> extends Repository<T, K> {
long count();
List<T> findAll();
}
随后，定义我们的 Repository，继承自 ViewNoIdRepository：
public interface ShopSaleRepository extends ViewNoIdRepository<ShopSale, Long> {
List<ShopSale> findByShopId(Integer shopId);
}
由于 ShopSale 实体类直接定义了 shopId，因此我们可以在 Repository 中使用 findByShopId() 方法。
6、总结
本文介绍了数据库视图，并简要比较了标准视图和物化视图的区别和各自的优缺点，还介绍了根据数据的性质在数据库视图中应用不同的主键策略。最后，介绍了实体 Bean 和 Base Repository 接口的定义。
Ref：https://www.baeldung.com/spring-data-jpa-repository-view
