CODEWITHSUNDEEP
javahibernateoracle10g
user3413635
user3413635

Reputation: 1

Hibernate - Help required to write a query

I am new to hibernate and am trying to do a query using hibernate. The query is working fine when executing in DB directly. Could you please help me to write it in hibernate.

select A.SL_NO, A.DATETIME,A.IP,A.CATEGORY,A.SUBCATEGORY,A.VALUE FROM APP_MON_REF_TABLE A, 
(SELECT category, max(to_char(datetime,'YYYY-MM-DD HH24:MI:SS')) maxtime from APP_MON_REF_TABLE group by category) B 
where A.category = B.category and to_char(A.datetime,'YYYY-MM-DD HH24:MI:SS') = B.maxtime

The expected answer is as follows 

SL_NO        DATETIME         IP      CATEGORY  SUBCATEGORY VALUE

5      01/02/2014 12:12:30    12    TABLE SPACE   DATA01     65%
6      01/02/2014 12:12:30    23    TABLE SPACE   DATA01     65%

Thanks in advance. Maths

Upvotes: 0

Views: 415

Answers (3)

winklebort
winklebort

Reputation: 98

HQL doesn't work with inline views, i.e (from (select ... )). But, in your case you are just using that view to establish the max date based on category. So, I would first rewrite your sql query to something like this:

SELECT A.sl_no, 
       A.datetime, 
       A.ip, 
       A.category, 
       A.subcategory, 
       A.value 
FROM   app_mon_ref_table A
WHERE  A.datetime = (SELECT max(datetime) maxtime 
        FROM   app_mon_ref_table  b
        where B.category = A.category
        GROUP  BY category)

*note: you really don't need those to_char either, unless those are actually timestamp fields. dates (in oracle) will compare to the second resolution. I've removed those to_char cols from my answer, as that is a different issue, anyway.*

As for a rewrite in hql, you haven't posted the POJO code so it is difficult, but it would be very similar to the sql:

from AppMonRef A where A.datetime = (SELECT max(datetime) maxtime 
        FROM   AppMonRef  b
        where B.category = A.category
        GROUP  BY category)

You could add the projections if you wanted to limit to those fields. But this HQL query will give you back a managed AppMonRef object obeying any of the declarations you have about lazy loading on the properties.

Upvotes: 1

Kevin
Kevin

Reputation: 41

Try this:

    String sql =  "select A.SL_NO, A.DATETIME, A.IP, A.CATEGORY, A.SUBCATEGORY, A.VALUE FROM APP_MON_REF_TABLE A, " +
                  "(SELECT category, max(to_char(datetime,'YYYY-MM-DD HH24:MI:SS')) maxtime from APP_MON_REF_TABLE group by category) B " + 
                  "where A.category = B.category and to_char(A.datetime,'YYYY-MM-DD HH24:MI:SS') = B.maxtime";

    SQLQuery sqlQuery = getCurrentSession().createSQLQuery(sql);
    sqlQuery.addEntity("A", YourEntity.class);
    sqlQuery.addScalar("maxtime", StandardBasicTypes.CHARACTER_ARRAY);

    List<Object[]> results = sqlQuery.list();
    for (Object[] result : results) {
        YouEntity a= result[0];
        String maxTime = (String) result[1];
    }

Upvotes: 0

AhmedJava
AhmedJava

Reputation: 46

you could use String query = "select A.SL_NO, A.DATETIME,A.IP,A.CATEGORY,A.SUBCATEGORY,A.VALUE FROM APP_MON_REF_TABLE A, (SELECT category, max(to_char(datetime,'YYYY-MM-DD HH24:MI:SS')) maxtime from APP_MON_REF_TABLE group by category) B where A.category = B.category and to_char(A.datetime,'YYYY-MM-DD HH24:MI:SS') = B.maxtime" entityManager.createNativeQuery(query).getResultList() or you map your table in pojo and then use hql query.

Upvotes: 0

Related Questions