JPA/SpringBoot Repository for database view (not table)

We Are Going To Discuss About JPA/SpringBoot Repository for database view (not table). So lets Start this Java Article.

JPA/SpringBoot Repository for database view (not table)

  1. JPA/SpringBoot Repository for database view (not table)

    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 {

  2. JPA/SpringBoot Repository for database view (not table)

    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 {

Solution 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();

}

Original Author Robert Niestroj Of This Content

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

Original Author Janitha Madushan Of This Content

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

Original Author José Luis Condori Jara Of This Content

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

Original Author garfield Of This Content

Conclusion

So This is all About This Tutorial. Hope This Tutorial Helped You. Thank You.

Also Read,

Siddharth

I am an Information Technology Engineer. I have Completed my MCA And I have 4 Year Plus Experience, I am a web developer with knowledge of multiple back-end platforms Like PHP, Node.js, Python and frontend JavaScript frameworks Like Angular, React, and Vue.

Leave a Comment