Spring Data JPA如何用于数据库视图？

数据库视图是关系数据库系统中的一种类似表的结构，其中数据源来自连接在一起的一个或多个表。

虽然 Spring Data存储库通常用于数据库表，但它们也可以有效地应用于数据库视图。

在本教程中，我们将探索采用 Spring Data 存储库作为数据库视图。

在本教程中，我们将采用 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）模型中，我们可以将其描述为一对多关系，其中一个商店可以有多个交易。尽管如此，每笔交易仅与一家商店相关。

数据库视图
数据库视图提供了一个虚拟表，该表从预定义查询的结果中收集数据。使用数据库视图而不是连接查询有以下优点：

  • 简单性——视图封装了复杂的连接，无需重复重写相同的连接查询
  • 安全性 – 视图可能仅包含基表中的数据子集，从而降低了暴露基表中敏感信息的风险
  • 可维护性 – 当基表结构更改时更新视图定义，无需修改引用应用程序中已更改基表的查询


有两种常见类型的数据库视图，它们有不同的用途：

  • 标准视图 – 这些是通过在查询时执行预定义的 SQL 查询而生成的。它们本身不存储数据。所有数据都存储在底层基表中。
  • 物化视图 – 这些与标准视图类似，也是从预定义的 SQL 查询生成的。相反，它们将查询结果复制到数据库中的物理表中。后续查询将从该表中检索数据，而不是动态生成数据。

标准视图示例
在我们的示例中，我们希望定义一个视图来总结每个日历月商店的总销售额。事实证明，物化视图是合适的，因为前几个月的销售额保持不变。除非需要当月的数据，否则计算总销售额时不需要实时数据。

但是，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;

实体Bean定义
现在，我们可以为数据库视图SHOP_SALE_VIEW定义实体 bean 。实际上，该定义与为普通数据库表定义实体 bean 几乎相同。

在 JPA 中，实体 bean 要求它必须具有主键。我们可以考虑两种策略来在数据库视图中定义主键。

物理主键
在大多数情况下，我们可以在视图中选取一列或多列来标识数据库视图中一行的唯一性。在我们的场景中，商店 ID、年份和月份可以唯一标识视图中的每一行。

因此，我们可以通过列shop_id、transaction_year和transaction_month导出复合主键。在JPA中，我们首先要定义一个单独的类来表示复合主键：

public class ShopSaleCompositeId {
    private int shopId;
    private int year;
    private int month;
    // constructors, getters, setters
}

随后，我们使用@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;
    // constructor, getters and setters
}

虚拟主键
在某些场景下，由于缺乏可以确保数据库视图中每一行的唯一性的列组合，定义物理主键是不可行的。作为一种解决方法，我们可以生成一个虚拟主键来模拟行的唯一性。

在我们的数据库视图定义中，我们有一个附加的列id，它利用ROW_NUMBER() OVER ()生成行号作为标识符。这是我们采用虚拟主键策略时的实体类定义：

@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;
    // constructors, getters and setters
}

需要注意的是，这些标识符特定于当前结果集。重新查询时分配给每行的行号可能不同。因此，后续查询中的相同行号可能代表数据库视图中的不同行。

Spring查看存储库
根据数据库的不同，Oracle 等系统可能支持可更新视图，允许在某些条件下更新它们上的数据。然而，数据库视图大多是只读的。

对于只读数据库视图，没有必要在我们的存储库中公开数据修改方法，例如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 存储库时将排除这些方法并仅公开数据检索方法。

 物理主键
对于具有物理主键的视图，我们可以定义一个新的基本存储库接口，仅公开数据检索方法：

@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注释指示此接口是基本存储库接口，并指示 Spring Data JPA 不要在运行时创建此接口的实例。在此存储库接口中，我们包含ListCrudRepository中的所有数据检索方法，并排除所有数据更改方法。

对于具有复合 ID 的实体 bean，我们扩展ViewRepository并定义一个附加方法来查询shopId的商店销售情况：

public interface ShopSaleRepository extends ViewRepository<ShopSale, ShopSaleCompositeId> {
    List<ShopSale> findByIdShopId(Integer shopId);
}

我们将查询方法定义为findByIdShopId() 而不是findByShopId()  ，因为它派生自ShopSale实体类中的 属性id.shopId。

虚拟主键
当我们处理具有虚拟主键的数据库视图的存储库设计时，我们的方法略有不同，因为虚拟主键是人造的，无法真正识别数据行的唯一性。

由于这种性质，我们将定义另一个基本存储库接口，该接口也排除通过主键的查询方法。这是因为我们使用的是虚拟主键，并且使用假主键检索数据是没有意义的：

public interface ViewNoIdRepository<T, K> extends Repository<T, K> {
    long count();
    List<T> findAll();
}

随后，我们将其扩展为ViewNoIdRepository来定义我们的存储库：

public interface ShopSaleRepository extends ViewNoIdRepository<ShopSale, Long> {
    List<ShopSale> findByShopId(Integer shopId);
}

由于ShopSale实体类这次直接定义了shopId ，因此我们可以在存储库中使用findByShopId() 。