Hibernate Community • View topic - Max results in where clause for Query By Criteria in Java

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Max results in where clause for Query By Criteria in Java

Page 1 of 1

[ 4 posts ]

Previous topic | Next topic

Author

Message

Goodz13

Post subject: Max results in where clause for Query By Criteria in Java

Posted: Thu May 24, 2007 2:09 pm

Newbie

Joined: Thu May 24, 2007 1:48 pm
Posts: 3

Hello,

I've been working with hibernate for a bit and was wondering how to transform the following query to Hibernate Query By Criteria in Java?

Code:

SELECT table1.* FROM table1 

INNER JOIN table2 ON 

   table1.field1 = table2.tbl1Field1 

GROUP BY 

   table1.field1

ORDER BY 

   MAX(table2.numField);

I've tried this:

Code:

Criteria myCrit = session.createCriteria(Table1.class);
             
myCrit.createAlias("table2", "tb2");
               
ProjectionList projList = Projections.projectionList();
               
projList.add(Projections.groupProperty("field1"));
               
projList.add(Projections.max("tb2.numField), "maxNumField");
               
myCrit.setProjection(projList);
             
myCrit.addOrder(Order.asc("maxNumField"));
             
// get the results
List lst = myCrit.list();

for (int i = 0; i < lst.size(); i ++)   {
   Table1 tb1 = (Table1) lst.get(i);

}

... But I get a cast exception because the values returned in the List are in an Object array and not in a Table1 object. The values in the Object array are a String value representing "field1" and an Integer representing "maxNumField"

Top

Goodz13

Post subject:

Posted: Thu May 24, 2007 3:20 pm

Newbie

Joined: Thu May 24, 2007 1:48 pm
Posts: 3

I figured out why it doesn't work. Basically my query was incorrecct. I can only select either the field that I am grouping by and/or the field that I am "MAXing". Hence the reason that the code returns a list of arrays of objects.

I don't like this design, but since the field that I am grouping by is a primary key, I am using that to make severial calls against the database instead of just one.

example

Code:

// ... code from pre. post

// get the results
List lst = myCrit.list();

for (int i = 0; i < lst.size(); i ++)   {
    Object obj[] = (Object[])lst.get(i);

     // getHibernateTemplate().get(Table1.class, field1)
    Table1 tb1 = (Table1) getHibernateTemplate().get(Table1.class, obj[0]);

}

Top

amila733

Post subject:

Posted: Fri May 25, 2007 2:16 am

Senior

Joined: Thu May 17, 2007 2:31 am
Posts: 194
Location: Sri Lanka

Hi

I don't understand need of Order by Max(aFiled)

Can you send your table structure

Amila

Top

Goodz13

Post subject:

Posted: Fri May 25, 2007 8:23 am

Newbie

Joined: Thu May 24, 2007 1:48 pm
Posts: 3

Basically we have a one to many relationship between two tables. We need to grab the records from the master table in the order of the maximum value of a date field in the child table grouped by the primary key of the master table and process the records in that order.

The solution that I posted earlier works but there are a lot of database calls. I would prefer one call.

The query that ends up being produced by hiberbate looks something like this:

Code:

SELECT 
    table1.field1 as fld1, max(table2.dateField) as mxFld
FROM 
    table1 INNER JOIN table2 ON table1.field1 = table2.tbl1Field1
GROUP BY
    fld1
ORDER BY
    mxFld;

Then I loop through the results and take the value from field1 and query the database to produce the pojo model of table1.

Top

Page 1 of 1

[ 4 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum