CODEWITHSUNDEEP
hibernatejpaspring-dataspring-data-jpahql
ASLAM TP
ASLAM TP

Reputation: 170

How to form an HQL query for one to many relationship and grouping one to many

I need to generate hql query. My table structure is as given below
A -----one to many------> B

and my oracle db query 

select a.CONF_NUMBER,to_char(max(b.RECEIVE_DATE), 'YYYY-MM-DD hh24:mi:ss')
from A a, B b
where b.RECEIVE_DATE >= to_date('2012-06-07 13:20:28','YYYY-MM-DD hh24:mi:ss') 
group by a.CONF_NUMBER

Here I am confused with the fact that how the max(b.RECEIVE_DATE) can be applied Confused about how the subquery works in HQL

Upvotes: 0

Views: 424

Answers (1)

Cepr0
Cepr0

Reputation: 30289

Try something like this:

DTO: 

public interface NumAndDate {
    String getNumber();
    Date getDate();
}

Repository:

public interface ARepository extends JpaRepository<A, Long> {

     @Query("select a.confNumber as number, max(b.receiveDate) as date from A a join a.b b where b.receiveDate >= ?1 group by a.confNumber")
     List<NumAndDate> getDataByDate(Date date);
}

See more info in JPQL Language Reference

Upvotes: 1

Related Questions