@Subselect Anotasyonu

Örnek

Açıklaması şöyle

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

Native SQL ile şöyle yaparız

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;

Hibernate ile şöyle yaparız

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

@Repository
public interface TableStatusRepository extends JpaRepository<TableStatus,Long>{}

Açıklaması şöyle

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.