HQL Hibernate INNER JOIN SUBQuery

Hibernate ORM
1

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

2

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

HQL does not support derive tables anyway.

3

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.

4

Okay.

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

5

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