HQL Hibernate INNER JOIN SUBQuery

I have some experience in SQL, but my knowledge in HQL is very limited

Here is my SQL query

select * from
(select * from readings join
(select max(signal) as signal,sender as mac ,receiver as rc from readings where created_datetime between ‘2018-02-05 10:25:52’ AND ‘2018-04-05 10:25:52’ group by readings.sender,readings.receiver)a
on
a.signal=readings.signal and
a.mac=readings.sender and
a.rc=readings.receiver ) c
group by c.sender,c.receiver;
Note:The outer most query is to get didtinct results only.

And the DTO

public class Readings implements java.io.Serializable {
private Integer id;
private String sender;
private String major;
private String minor;
private int signal;
private BigDecimal temperature;
private String batteryLife;
private String receiver;
private Date createdDatetime;
}

Criteria cr = session.createCriteria(com.xyz.hibernate.dto.Readings.class)
.setProjection(Projections.projectionList()
.add(Projections.max(“signal”))
.add(Projections.groupProperty(“sender”))
.add(Projections.groupProperty(“receiver”)));
cr.add(Restrictions.between(“createdDatetime”, from, to));

String query="select max(r.signal) as signal,r.sender as mac ,r.receiver as rc from com.xyz.hibernate.dto.Readings r where r.createdDatetime  between :from AND :to group by r.sender,r.receiver";
Query createQuery = session.createQuery(query);     
createQuery.setParameter("from", from);
createQuery.setParameter("to", to);

So far I was able to complete only the inner most query in above two ways, but I am kind of stuck there.

This one works >>select max(r.signal) as signal,r.sender as mac ,r.receiver as rc from com.xyz.hibernate.dto.Readings r where r.createdDatetime between :from AND :to group by r.sender,r.receiver

But this one doesnt >>select first.signal from (select max(r.signal) as signal,r.sender as mac ,r.receiver as rc from com.xyz.hibernate.dto.Readings r where r.createdDatetime between :from AND :to group by r.sender,r.receiver)first

If possible I would like to go with the first approach, like pure HQL, but I really doubt if thats possible.

Any suggestion will be much helpful

Just run the SQL query. Why do you want to translate it to HQL?

HQL does not support derive tables anyway.

Because I want to play with the object of the DTO itself, the other types like criteria, native… gives me the output as an object of a different class, like the Object class itself, and if I am using hibernate then I would prefer to use as HQL so that I can take the most advantage of it( if possible) , else I would go for jdbc , I want to avoid using the getters and the setters s much as possible.

Criteria cr = session.createCriteria(com.leadics.as.locator.hb.dto.XYZ.class)
.setProjection(Projections.projectionList()
.add(Projections.max("signal"))
.add(Projections.groupProperty("sender"))
.add(Projections.groupProperty("receiver")));
cr.add(Restrictions.between("createdDatetime", from, to));

List<XYZ>  br=()cr.list();

I want to use like above…
but this one fails here( List br=cr.list():wink:

I can only use List br=cr.list();
which is not helpful for me.

Okay.

Could you please tell me , what other options I have, where I will receive the output as an object of the DTO…

For derived table queries, you only option is native SQL. For other simpler queries, you can use JPQL or Criteria API.