What is the best to create a complex, dynamic query?

mystic · **Joined:** Tue May 12, 2009 6:08 am **Posts:** 92

Let's say I have a search query. I don't know what parameters I want to search on until run time - it could be many things like properties, or complex collections, and things like that. Also, the query's order by will also be dynamic. The last requirement is to allow for paging.

What is the "defacto" way to do this in Hibernate these days? Of course I can build a query with a bunch of "if" statements, but has hibernate evolved to make this query a lot more trivial than it used to be many, many years ago?

Thanks for pointing me in the right direction. I'm looking for a robust solution that will work for complex examples.

Cameron McKenzie · **Posted:** Wed Sep 16, 2009 9:02 am

I'd use the Criteria API. It's great at doing exactly these types of things, and it's very flexible and expandable.

Here's a little tutorial on using the Hibernate Criteria API:

http://jpa.ezhibernate.com/Javacode/learn.jsp?tutorial=09howtousethecriteriaapi

mystic · **Joined:** Tue May 12, 2009 6:08 am **Posts:** 92

Thanks for the link! This is great explanation of these features.

Three last questions.

1) Does the example also work with the children? For example, if object A has a list of B, what is the correct way to create a query condition on B?

2) What is the best way to deal with primitive types rather than objects? I noticed one of my member was "int x", but when used within an example object, Hibernate assumes this is is querying the value "0". Are objects mandatory then? (My guess is that yes they are). I don't like using objects because JUnit needs qualifiers such as (Integer) in the assertEquals() statements :(

3) Does this mean that my objects cannot have default values and that they must all be null? Sometimes I like to initialize strings to values or enter numbers like 0 instead of null. However, this would be bad if I used a brand new object as the criteria for querying. Do I create a factory method for a criteria object to null everything out so I can still keep my common default values? Is that the common idiom?

mystic · **Joined:** Tue May 12, 2009 6:08 am **Posts:** 92

Yeah, this is kind of clunky. I just realized testing some business logic that the integer inside the domain object MUST be initialized to zero.

The following does not work once the integer value is 0.

Code:

A a = new A();

List<A> as = aDao.findAllByCriteria( a, "name", 0, 10 );
assertEquals( 9, as.size() );

It must be changed to this:

Code:

A a = A.criteria();

List<A> as = aDao.findAllByCriteria( a, "name", 0, 10 );
assertEquals( 9, as.size() );

And criteria() is something like this within the A class:

Code:

...

Integer integerValue = 0;

...

public static A criteria() {
   A a = new A();
   a.setIntegerValue( null );

   return a;
}

Isn't this kind of bloated? Like to make this much code to make it work? :( I guess it's not too bad, but this is a definitely a "hidden" disadvantage, unless I'm missing something.

To make testing a bit simpler, I've had to create a mismatched pair, which is kind of annoying as well:

Code:

public int getIntegerValue() {
   return integerValue;
}

public void setIntegerValue( Integer integerValue ) {
   this. integerValue = integerValue;
}

Am I doing something wrong?