HQL to return the index of an object in an ordered query result?

Question

Can I use HQL to get the index of an ordered query result?

The only way I know to do this is to get back all of the results from the database, then iterate through all of the results.

For example, given:

<hibernate-mapping>
<class name="Dog" table="DOG">

 <id name="id" type="int" column="DOG_id">
  <meta attribute="scope-set">protected</meta>
  <generator class="native"/>
 </id>

 <property name="age" type="int" />

 <property name="name" type="string" />
</class>    
</hibernate-mapping>

then these types of queries:

//determine where dog with id 43 is in the current search results for dogs ordered by age
Query dogQuery = sess.createQuery("select d.id from Dog as d order by d.age");
List<Dog> dogList = dogQuery.list();
int idx = dogList.indexOf( 43 );

or, a more refined search through the dog list:

Query dogQuery = sess.createQuery("select d.id from Dog as d where (d.name='fluffy' OR d.age >= 3) order by d.age");
List<Dog> dogList = dogQuery.list();
int idx = dogList.indexOf( 43 );

The drawback here is that we are loading every dog.id into memory.

Scenario where I would need this:

• displaying a specific query result (of thousands) as a dot on a line. The line, and the dot, gets updated every minute or so. This visualization gives "real time" updates on the rank of a search query

Answer 1

In a java List, you can do a call to indexOf(Object).

See http://java.sun.com/j2se/1.4.2/docs/api/java/util/List.html for the API.

Answer 2

Loading the whole list of results into memory and then using application logic just to filter what you need is the WRONG way to interact with a database.

Use the SQL WHERE and HAVING clauses to get what you need from the DB in the first place.

Answer 3

starting with this HQL (valid SQL too, in this case):

select dog.id as dogId from canines dog order by dog.codename asc

It can be slightly modified and inserted into a valid SQL query (only for Oracle with the rownum column):

select rn from 
( select dogid dogId, rownum rn from canines dog order by dogname asc)
where dogid=206

Unfortunately, this is not valid HQL...

To use my initial HQL query to get the same information as that SQL query, I plan to:

• have hibernate generate my HQL query into the SQL it would use to make the list of all results (hopefully not running the query first)
• insert this hibernate-generated SQL into a new SQL query using sessionObj.createSQLQuery. This new query will look like the working sql above.

Is this a crazy solution?

--

notes

http://stackoverflow.com/questions/534217/sql-to-return-the-rownum-of-a-specific-row-using-oracle-db

Answer 4

use the List.indexOf() If the list is extra large then make the list "extra-lazy".

But why do you need the index? Maybe you really don't (and thus you will not need to solve this issue)

---

Revised answer:

You are doing it completely wrong. All you need is :

select count(*) from dog d, dog d1 where d.age <= d1.age and d1.id = :entityId and d1.id <> d.id

---

Why this works: This query finds every dog that is the same age or younger than the dog in question (d1) but is not d1. (but does not actual return all those dogs - since we don't care about them).

The count tells you the number of dogs "before" d1. Thus you can tell where d1 appears in the complete list of dogs. Since you don't need to know the position of all the dogs in the list, you don't need to retrieve all the dogs.

Answer 5

You should be able to use something like Hibernates Criteria object to return just a small subsection of your query.

My guess is you're trying to implement pagination.

Criteria criteria=session.createCriteria(Item.class);
criteria.addOrder(Order.asc("name"));
criteria.setFirstResult(100);
criteria.setMaxResults(50);
List pageResults=criteria.list();

The above code was lifted from: http://forum.springframework.org/archive/index.php/t-9658.html

This will also work for a catalog that is always updated since you should be making this query on every page request.