1

I have some experience in SQL, but my knowledge in HQL is 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

Koushik Paul
  • 685
  • 6
  • 10

0 Answers0