Map an entity to a complex query involving multiple tables in a spring boot application using hibernate.

In a spring boot application, mapping database table to entity object is very easy using JPA / CRUD repository. If tables are dependent, still JPA repository provided easy solution. However, sometimes our sql query is so complex involving multiple independent tables that it is very difficult to express them using JPA Query Language. In such cases we adopt following approach.

1. Create a database view and map it to an entity.
2. Execute a native query and map the result to a entity/DTO.

The database view approach is better is if you want to implement write operations. However, if you just want to implement read operation, i suggest native query approach.

Native Query Mapping implementation

In order to map a database table or query result to an entity, we compulsorily require an id column.

In our example we will generate row number of resutset as id colum using function row_number() over() on postgresql database.

Suppose, we have four tables (table1,table3,table4) each having a column “date”. I want to get list of max date for each table in a single query. The required output should be like-

id,tablename,max
1,table1,2020–03–25
2,table2,2020–04–30
3,table3,2020–02–28
4,table4,2020–03–31

The sql query for postgresql database will be -

select row_number() over() as id, * from (
select ‘table1’ as tablename, max(date) from table1
union
select ‘table2’ as tablename, max(date) from table2 
union
select ‘table3’ as tablename, max(date) from table3
union
select ‘table4’ as tablename, max(date) from table4
order by tablename) t1;

There are two ways to execute native sql query.
1. Using Subselect in entity class.
2. Using @Query in repository class.

Method 1: Using Subselect in entity class.

//domain class
TableStatus.java

import java.time.LocalDate;
import javax.annotation.concurrent.Immutable;
import javax.persistence.Entity;
import javax.persistence.Id;
import org.hibernate.annotations.Subselect;
import org.hibernate.annotations.Synchronize;
@Entity
@Subselect( “select row_number() over() as id, * from ( select ‘table1’ as tablename, max(date) from table1 “+
“union select ‘table2’ as tablename, max(date) from table2 union select ‘table3’ as tablename, max(date) from table3”+
“union select ‘table4’ as tablename, max(date) from table4 order by tablename) t1”)
@Synchronize({“table1”, “table2”,”table3",”table4"})
@Immutable
public class TableStatus {
 
 @Id
 private Long id;
 private String tablename;
 private LocalDate max;
//constructor using field
 public DatasetStatus(Long id, String tablename,LocalDate max) {
 super();
 this.id = id;
 this.tablename = tablename;
 this.max = max;
 }
//constructor from superclass
 public DatasetStatus() {
 super();
 }
//getters and setters
 public long getId() {
 return id;
 }
 public void setId(long id) {
 this.id = id;
 }
 public String getTablename() {
 return tablename;
 }
 public void setTablename(String tablename) {
 this.tablename = tablename;
 }
 
 public LocalDate getMax() {
 return max;
 }
 public void setMax(LocalDate max) {
 this.max = max;
 }
}

Hibernate doesn’t know which database tables are used by the SQL statement configured in the @Subselect annotation. You can provide this information by annotating the entity with @Synchronize. That enables Hibernate to flush pending state transitions on tables table1, table2,table3 and table4 entities before selecting a TableStatus entity.

//controller class
TableStatusResource.java

import java.util.List;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping(“/api”)
public class TableStatusResource {
private final TableStatusRepository tableStatusRepository;
public TableStatusResource(TableStatusRepository tableStatusRepository) {
 this.tableStatusRepository = tableStatusRepository;
 }
 
 @GetMapping(“/table-status”)
 @Transactional(readOnly = true)
 public List<TableStatus> getAllTableStatus(){
 returntableStatusRepository.findAll(); 
 }
}

//repository class
TableStatusRepository.java

import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
@SuppressWarnings(“unused”)
@Repository
public interface TableStatusRepository extends JpaRepository<TableStatus,Long>{}

The api is ready at url: <servername:port>/api/table-status

The second method Using @Query in repository class will be demonstrated in the next article.