Getting the object with the highest member value in hql?

Question

I'm still a little new to hql, and I had a question about aggregation functions and efficiency for a query I'm writing.

Let's say I have this class mapped in hibernate (getters/setters/constructors/etc. omitted for brevity):

public class Foo
{
    private int i;

    private String s;

    private float f;
}

I want to do a hql query to get the Foo instance with the highest i value and specified s & f values. My current solution is this:

List<Foo> fooList = (List<Foo>)session.createQuery(
    "from Foo as foo where foo.s = :str and foo.f = :val order by foo.i desc").
    setParameter("str", <stringgoeshere>).setParameter("val", <floatgoeshere>).
    list();
return fooList.get(0);

If I understand correctly, this method will have the N+1 selects problem (although as long as I'm only grabbing the first result from the list, N will hopefully be 1, but still). I assume that there's some way of doing this with uniqueResult(), but I don't quite understand how the aggregation functions would work in this case. The only examples I've been able to find of the "max()" are either in the where clause or the returned value.

How should I write this query to do it in a single select and grab the Foo instance with the highest i?

Thanks for the help!

Answer 1

If I understand correctly, this method will have the N+1 selects problem

I don't see how your query could result in n+1 selects. There is no reason for Hibernate to iterate over the results of the first query and to perform subsequent queries here.

I assume that there's some way of doing this with uniqueResult(), but I don't quite understand how the aggregation functions would work in this case.

If you want to retrieve a single result, it would be indeed smart to not retrieve the entire table. First, the query would be more efficient and second, you're not going to waste memory for nothing (and possibly even make the Session explode). What if your table contains a million of records?

How should I write this query to do it in a single select and grab the Foo instance with the highest i?

As just mentioned, retrieve only the wanted record, use setMaxResults(1) to limit the number of results (this will generate the proper SQL order depending on your database):

Foo foo = (Foo) session.createQuery(
    "from Foo as foo where foo.s = :str and foo.f = :val order by foo.i desc")
    .setParameter("str", <stringgoeshere>).setParameter("val", <floatgoeshere>)
    .setMaxResults(1)
    .uniqueResult();
return foo;