JPA/SpringBoot Repository for database view (not table)

Question

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

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.Id annotated upon a field, you will get an org.hibernate.AnnotationException: No identifier specified for entity thrown at Runtime.

2. The Spring Boot JpaRepository interface definition requires that the ID type extends Serializable, which precludes utilizing java.lang.Void as a work-around for the lack of an id on an view entity.

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

Answer 1

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

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.

public interface RaportRepository extends JpaRepository<User, Long> {

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

}

Answer 2

1. Create View with native SQL in the database,

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'

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,

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

Answer 3

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

We map the view to a JPA object as:

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> {
}

Answer 4

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.

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.

Answer 5

About Entity ID mapping

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. UUID is 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.

About 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.

Indeed Spring Data repositories classes such as CrudRepository or JpaRepository are 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.

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

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