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