HQL for finding starting index of objects in a predefined order?

Question

Using HQL, I would like to search for the starting index of sequenced objects.

For example, if I have the following persisted classes:

<class name="Word">

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

 <many-to-one name="sentence" class="Sentence"/>

 <property name="word" type="string"/>

 <property name="num" type="integer"/>

</class>

<class name="Sentence">

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

 <set name="words" lazy="true">
  <one-to-many class="Word"/>
 </set>

</class>

and I persist the following Words into a Sentence object:

WORD   NUM
---------- 
the    0 
cow    1 
jumped 2 
over   3 
the    4 
moon   5 
but    6 
the    7 
cow    8 
forgot 9 
her    10  
bell   11

I would like to search for "the cow", and get back 0 and 7. A search for "the cow jumped" would only return 0.

My current approach is an iterative searches -- do a query for the index of the first word, then use that index to see if the following words' indexes are what I want them to be. Is this a good approach or is there a way to do it all in one query?

Answer 1

there is nothing wrong with this approach. iterative queries are sometimes unavoidable.

there are databases that return the row number and you could maybe use that to contruct a complicated query. i don't think that hibernate has support for this. (i know of oracle, postgre >= 8.4) most likely the porformance will be worse than doing two queries.

just make sure that you won't run into endless queries or the number of queries grows linear with the size of input or size of existing data.