Java 用于数据库视图（非表）的 JPA/SpringBoot 存储库

JPA/SpringBoot Repository for database view (not table)

提问by Paul Nelson Baker

I'm attempting to create a JPA entity for a view. From the database layer, a table and a view should be the same.

我正在尝试为视图创建 JPA 实体。从数据库层来说，表和视图应该是一样的。

However, problems begin to arise and they are two fold:

然而，问题开始出现，它们有两个方面：

1. When attempting to setup the correct annotations. A view does not have a primary key associated with it, yet without the proper @javax.persistence.Idannotated upon a field, you will get an org.hibernate.AnnotationException: No identifier specified for entitythrown at Runtime.

2. The Spring Boot JpaRepositoryinterface definition requires that the IDtype extends Serializable, which precludes utilizing java.lang.Voidas a work-around for the lack of an id on an view entity.

1. 尝试设置正确的注释时。视图没有与之关联的主键，但如果没有在@javax.persistence.Id字段上正确注释，您将org.hibernate.AnnotationException: No identifier specified for entity在运行时得到一个抛出。

2. Spring BootJpaRepository接口定义要求ID类型 extends Serializable，这排除了使用java.lang.Void作为视图实体上缺少 id 的解决方法。

What is the proper JPA/SpringBoot/Hibernate way to interact with a view that lacks a primary key?

与缺少主键的视图交互的正确 JPA/SpringBoot/Hibernate 方式是什么？

回答by davidxxx

About Entity ID mapping

关于实体 ID 映射

If you can change the view definition, you could use add the rownum as column.
It is generally specific to the DBMS. The idea is to make the row number in the table the id of the entity.
As alternative you could use the a generator of unique id. UUIDis a possibility. At last you could stick to native SQL while benefiting from JPA/Hibernate to map the native query results to a specific class representing the view data.

如果您可以更改视图定义，则可以使用添加 rownum 作为列。
它通常特定于 DBMS。这个想法是让表中的行号成为实体的 id。
作为替代方案，您可以使用唯一 ID 的生成器。UUID是一种可能性。最后，您可以坚持使用本机 SQL，同时受益于 JPA/Hibernate 将本机查询结果映射到表示视图数据的特定类。

About Spring Data Repository

关于 Spring Data Repository

If views don't fit naturally into the Spring Data Repository requirements, it probably means that using views instead of tables is not necessary suitable for.

如果视图不能自然地适应 Spring Data Repository 的要求，则可能意味着使用视图而不是表不一定适合。

Indeed Spring Data repositories classes such as CrudRepositoryor JpaRepositoryare designed to provide out of the box CRUD operations and some additional processing for a specific entity class.
The views in terms of DBMS are not included in as you select it but you don't update, insert or delete directly any row from the view.
Besides, what would be the added value to use such Repository beans for a view ? You will use almost nothing of the generated implementation provided by Spring.

事实上，Spring Data 存储库类（例如CrudRepository或 ）JpaRepository旨在为特定实体类提供开箱即用的 CRUD 操作和一些额外的处理。
DBMS 方面的视图在您选择时不包含在其中，但您不会直接从视图中更新、插入或删除任何行。
此外，将这样的 Repository bean 用于视图有什么附加价值？您将几乎不使用 Spring 提供的生成实现。

In your case if you define the view as an entity I think that using JpaTemplatewould make more sense.
It is just a layer on top of the JPA API.
From the documentation:

在您的情况下，如果您将视图定义为实体，我认为使用JpaTemplate会更有意义。
它只是 JPA API 之上的一层。
从文档：

JpaTemplate can be considered as direct alternative to working with the native JPA EntityManager API (through a shared EntityManager reference, as outlined above). The major advantage is its automatic conversion to DataAccessExceptions; the major disadvantage is that it introduces another thin layer on top of the native JPA API. Note that exception translation can also be achieved through AOP advice; check out PersistenceExceptionTranslationPostProcessor

JpaTemplate 可以被视为使用本机 JPA EntityManager API 的直接替代方案（通过共享 EntityManager 引用，如上所述）。主要优点是它自动转换为 DataAccessExceptions；主要缺点是它在本机 JPA API 之上引入了另一个薄层。注意异常翻译也可以通过AOP通知来实现；查看 PersistenceExceptionTranslationPostProcessor

回答by José Luis Condori Jara

I hope this helps you, the id you can assign it to a united value in your view.

我希望这对您有所帮助，您可以将 id 分配给您认为的统一值。

We map the view to a JPA object as:

我们将视图映射到 JPA 对象，如下所示：

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;

@Entity
@Table(name = "my_view")
public class MyView implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Column(name = "my_view_id")
private Long myViewId;
@NotNull
@Column(name = "my_view_name")
private String myViewName;
}

We then create a repository:

然后我们创建一个存储库：

import org.springframework.data.jpa.repository.JpaRepository;

public interface MyViewRepository extends JpaRepository<View, Long> {
}

回答by Robert Niestroj

I was exploring that topic too. I ended up using Spring Data JPA Interface-based Projectionswith native queries.

我也在探索这个话题。我最终将 Spring Data JPA Interface-based Projections与本机查询一起使用。

I created an interface, making sure the UPPERCASE part matches the DB Column names:

我创建了一个界面，确保大写部分与数据库列名称匹配：

public interface R11Dto {

   String getTITLE();

   Integer getAMOUNT();

   LocalDate getDATE_CREATED();
}

Then i created a repository, for an Entity (User) not related in any way to the view. In that repository i created a simple native query. vReport1_1 is my view.

然后我为与视图没有任何关系的实体（用户）创建了一个存储库。在该存储库中，我创建了一个简单的本机查询。vReport1_1 是我的观点。

public interface RaportRepository extends JpaRepository<User, Long> {

   @Query(nativeQuery = true, value = "SELECT * FROM vReport1_1 ORDER BY DATE_CREATED, AMOUNT")
   List<R11Dto> getR11();

}

回答by garfield

If your view doesn't have a candidate key you may add one through the creation query using something like the database UUID function and then use the UUID as the type for the ID the Entity.

如果您的视图没有候选键，您可以使用类似数据库 UUID 函数的方法通过创建查询添加一个，然后使用 UUID 作为实体 ID 的类型。

If you need to make your Entity read-only you may annotate the fields with

如果您需要使您的实体只读，您可以使用

 @Column(insertable = false, updatable = false)

Or annotate you entity class with org.hibernate.annotations.Immutable if your provider is Hibernate >= 5.2.

或者，如果您的提供者是 Hibernate >= 5.2，则使用 org.hibernate.annotations.Immutable 注释您的实体类。

回答by JMadushan

1. Create View with native SQL in the database,

1.在数据库中用原生SQL创建View，

create or replace view hunters_summary as 
select 
em.id as emp_id, hh.id as hh_id
from employee em 
inner join employee_type et on em.employee_type_id = et.id  
inner join head_hunter hh on hh.id = em.head_hunter_id;

2. Map that, View to an 'Immutable Entity'

2. 将其映射到“不可变实体”

package inc.manpower.domain;

import org.hibernate.annotations.Immutable;
import org.hibernate.annotations.Subselect;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;
import java.util.Date;

@Entity
@Immutable
@Table(name = "`hunters_summary`")
@Subselect("select uuid() as id, hs.* from hunters_summary hs")
public class HuntersSummary implements Serializable {

    @Id
    private String id;
    private Long empId;
    private String hhId;

    ...
}

3. Now create the Repository with your desired methods,

3. 现在用你想要的方法创建存储库，

package inc.manpower.repository;

import inc.manpower.domain.HuntersSummary;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.Date;
import java.util.List;

@Repository
@Transactional
public interface HuntersSummaryRepository extends PagingAndSortingRepository<HuntersSummary, String> {
    List<HuntersSummary> findByEmpRecruitedDateBetweenAndHhId(Date startDate, Date endDate, String hhId);
}