Hi,
I am using hibernate to query a database of orders. The result set returned is generally in the thousands. We have created indices on the database for the searches.

The problem is that while using the crtieria class, expressions or restrictions get added in a certain order. If a certain criteria is not applicable, we would have to skip it (say for example if admin is doing a search, we do not need to set the customerId, but if customer is searching then only that customer's orders are to be returned, hence id needs to be set).
If a certain criteria is not added, the chain of the indices is broken and from what I understand, that composite index will not kick in for the query.

For examle, if search is perfomed in the following order...

customerId, dateCreated, status, amount

then we create a coposite index in that order. Now say if status is not defined (get orders of all status), then our query would be based on customerId, dateCreated and amount, which brreaks teh index chain.

My question is, what is the best approach to handle such an issue. Please keep in mind that our query criteria contain many more properties than the 4 mentioned above.

Thanks,
-Riz.