Spring Data JPA Repository for Database View – 数据库视图的 Spring Data JPA 资源库

1. Overview

1.概述

A database view is a table-like structure in a relational database system in which the data source is from one or more tables joined together.

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

While Spring Data repositories are commonly used for database tables, they can be effectively applied to database views as well. In this tutorial, we’ll explore adopting Spring Data repositories for database views.

Spring Data 资源库通常用于数据库表，但也可有效地应用于数据库视图。在本教程中，我们将探讨如何在数据库视图中采用 Spring 数据存储库。

2. Database Table Setup

2.数据库表设置

In this tutorial, we’ll adopt the H2 database system for data definition and demonstrate the database view concept using two example tables — SHOP and SHOP_TRANSACTION.

在本教程中，我们将采用 H2 数据库系统进行数据定义，并使用 SHOP 和 SHOP_TRANSACTION 这两个示例表演示数据库视图概念。

The SHOP table stores the shop information:

SHOP 表存储商店信息：

CREATE TABLE SHOP
(
    shop_id             int             AUTO_INCREMENT,
    shop_location       varchar(100)    NOT NULL UNIQUE,
    PRIMARY KEY(shop_id)
);

And the SHOP_TRANSACTION table stores transaction records associated with shops and references to the SHOP table via the 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)
);

In the Entity-Relationship (ER) model, we can illustrate it as a one-to-many relationship where one shop can have multiple transactions. Still, each transaction is associated with one shop only. We can represent this visually using an ER diagram:

在实体-关系（ER）模型中，我们可以将其说明为一对多的关系，即一个商店可以有多个事务。但是，每笔交易只与一家商店相关联。我们可以使用 ER 图来直观地表示这种关系：

3. Database View

3.数据库视图

A database view provides a virtual table that gathers data from the result of a predefined query. There are advantages to using a database view instead of using a join query:

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

• Simplicity – Views encapsulate complex joins, eliminating the need to rewrite the same join query repeatedly
• Security – Views may only include a subset of data from the base tables, reducing the risk of exposing sensitive information from base tables
• Maintainability – Updating view definitions when the base table structure changes prevents the need to modify queries referencing the altered base table in our application

3.1. Standard View and Materialized View

3.1.标准视图和物化视图

There are two common types of database views, and they serve different purposes:

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

• Standard Views – These are generated by executing a predefined SQL query when queried. They do not store data themselves. All data is stored in the underlying base tables.
• Materialized Views – These are similar to standard views, which are also generated from a predefined SQL query. In contrast, they copy the query result to a physical table in the database. Subsequent queries retrieve data from this table rather than generating it dynamically.

The following comparison table highlights the varying characteristics of standard and materialized views, aiding in selecting the appropriate view type based on specific requirements:

下表重点介绍了标准视图和物化视图的不同特性，有助于根据具体要求选择合适的视图类型：

3.2. Standard View Example

3.2.标准视图示例

In our example, we would like to define a view that concludes the total sales amount of shops for each calendar month. The materialized view proves suitable since past sales amounts from previous months remain unchanged. Real-time data is unnecessary for calculating total sales unless the current month’s data is required.

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

However, the H2 database does not support materialized views. We’ll create a standard view instead:

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

Upon querying the view, we should obtain data like the following:

在查询视图时，我们应该获得如下数据：

4. Entity Bean Definition

4.实体 Bean 定义

We can now define the entity bean for our database view SHOP_SALE_VIEW. Indeed, the definition is almost the same as defining an entity bean for a normal database table.

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

In JPA, an entity bean has a requirement that it must have the primary key. There are two strategies that we can consider to define a primary key in a database view.

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

4.1. Physical Primary Key

4.1.物理主键

In most scenarios, we can pick one or multiple columns in the view to identify the uniqueness of a row in the database view. In our scenario, the shop ID, year, and month can uniquely identify each row in the view.

在大多数应用场景中，我们可以选择视图中的一列或多列来标识数据库视图中某一行的唯一性。在我们的应用场景中，商店 ID、年和月可以唯一标识视图中的每一行。

Hence, we can derive the composite primary key by columns shop_id, transaction_year, and transaction_month. In JPA, we have to first define a separate class to represent the composite primary key:

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

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

Subsequently, we embed this composite ID class into the entity class with @EmbeddedId and define the column mappings by annotating the composite ID by @AttributeOverrides:

随后，我们通过 @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
}

4.2. Virtual Primary Key

4.2.虚拟主键

In certain scenarios, defining a physical primary key is not feasible due to the absence of column combinations that can ensure the uniqueness of each row within the database view. We can generate a virtual primary key to emulate row uniqueness as a workaround.

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

In our database view definition, we have an additional column id that utilized ROW_NUMBER() OVER () to generate row numbers as identifiers. This is the entity class definition when we adopt a virtual primary key strategy:

在我们的数据库视图定义中，我们有一个额外的列 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
}

It’s crucial to note that these identifiers are specific to the current result set. The row numbers assigned to each row could be different upon re-query. As a result, the same row number in subsequent queries may represent different rows in the database view.

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

5. View Repository

5.查看存储库

Depending on the database, systems such as Oracle may support updatable views that allow data updates on them under some conditions. However, database views are mostly read-only.

根据数据库的不同，Oracle 等系统可能支持可更新视图，允许在某些条件下对视图进行数据更新。不过，数据库视图大多是只读的。

For read-only database views, it’s unnecessary to expose data modifying methods such as save() or delete() in our repositories. Attempting to call these methods will throw an exception since the database system doesn’t support such operations:

对于只读数据库视图，没有必要在我们的存储库中公开数据修改方法，如 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 (?,?,?,?,?,?)]

In such rationale, we’ll exclude these methods and expose only data retrieval methods when defining our Spring Data JPA Repository.

在这种情况下，我们将排除这些方法，在定义 Spring Data JPA 资源库时只公开数据检索方法。

5.1. Physical Primary Key

5.1.物理主键

For views with a physical primary key, we can define a new base repository interface that only exposes data retrieval methods:

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

@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);
}

The @NoRepositoryBean annotation indicates this interface is a base repository interface and instructs Spring Data JPA not to create an instance of this interface at runtime. In this repository interface, we include all data retrieval methods from ListCrudRepository and exclude all data-changing methods.

@NoRepositoryBean 注解表明此接口是一个基础存储库接口，并指示 Spring Data JPA 不要在运行时创建此接口的实例。在此版本库接口中，我们包含了 ListCrudRepository 中的所有数据检索方法，并排除了所有数据更改方法。

For our entity bean with composite ID, we extend ViewRepository and define an additional method for querying the shop sale for the shopId:

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

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

We’ve defined the query method as findByIdShopId() instead of findByShopId() because it derives from the property id.shopId in the ShopSale entity class.

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

5.2. Virtual Primary Key

5.2.虚拟主键

Our approach has a slight difference when we’re dealing with the repository design for database views with a virtual primary key since the virtual primary key is an artificial one that cannot truly identify the uniqueness of data rows.

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

Due to this nature, we’ll define another base repository interface that excludes the query methods by primary key as well. It’s because we’re using a virtual primary key, and it makes no sense for us to retrieve data using a fake primary key:

基于这种性质，我们将定义另一个基础存储库接口，该接口也排除了按主键查询的方法。这是因为我们使用的是虚拟主键，使用假主键检索数据毫无意义：

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

    List<T> findAll();
}

Subsequently, let’s define our repository by extending it to ViewNoIdRepository:

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

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

Since the ShopSale entity class defines the shopId directly this time, we can use findByShopId() in our repository.

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

6. Conclusion

6.结论

This article has provided an introduction to database views, offering a brief comparison between standard views and materialized views.

本文介绍了数据库视图，并简要比较了标准视图和物化视图。

Furthermore, we’ve described applying different primary key strategies on database views depending on the nature of the data. Finally, we explored the definition of an entity bean and base Repository interfaces based on the key strategies we had chosen.

此外，我们还介绍了根据数据的性质在数据库视图中应用不同的主键策略。最后，我们根据所选的主键策略，探讨了实体 bean 和基础 Repository 接口的定义。

As usual, the examples discussed are available over on GitHub.

与往常一样，所讨论的示例可在 GitHub 上获取。