Getting the object with the highest member value in hql?

Question

I'm still a little new to hql and I have a question about the aggregation and performance functions for the query I'm writing.

Let's say this class is displayed in sleep mode (getters / seters / constructors / etc. Omitted for brevity):

public class Foo { private int i; private String s; private float f; } 

I want to execute an hql query to get an instance of Foo with the highest value of i and the specified values ​​of s and f. 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 N + 1 select the problem (although as long as I just grab the first result from the list, N will hopefully be 1, but still). I suppose there is a way to do this with uniqueResult (), but I don’t quite understand how aggregation functions will work in this case. The only examples I could find in "max ()" are either in the where clause or in the return value.

How do I write this query to do this in one select and grab the Foo instance with the highest i?

Thanks for the help!

Answer 1

If I understood correctly, this method will have the problem of choosing N + 1

I don’t see how your query can lead to the choice of n + 1. There is no reason for Hibernate to iterate over the results of the first query and execute subsequent queries here.

I suppose there is some way to do this with uniqueResult (), but I don’t quite understand how the aggregation functions will work in this case.

If you want to get one result, it would be wise not to retrieve the entire table. Firstly, the request will be more efficient, and secondly, you are not going to waste memory on nothing (and perhaps even make the session exploded). What if your table contains a million records?

How do I write this query to do this in one select and grab the Foo instance with the highest i?

Once just mentioned, retrieve only the record you need, use setMaxResults(1) to limit the number of results (this will create the correct 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;