Variable number of Restriction.or() clauses

treilly_2000 · **Joined:** Mon Nov 20, 2006 5:28 pm **Posts:** 4

Suppose I have a simple table named "Book" with a column named "Title" VARCHAR(500).

[Books]
------------------------------------------------
|id | title |
------------------------------------------------
| 1 | "Gone with the Wind" |
| 2 | "The Stand" |
| 3 | "Standing at the edge" |
| 4 | "Standing at the edge of my sanity" |
------------------------------------------------|

So a search might be "standing edge" and should return books 3 and 4, or it could be "the" and return 1,2,3,4 or it could be "standing at the edge" and return 3 and 4.

I'm implementing a search page against this column where a user can enter multiple search terms.

I need to create a query against the book's title that returns all books with all given words in the title. The wrinkle is that there may be a variable number of search terms.

I'm new to Hibernate, and I can't come up with a more elegant solution than the following:

Code:

              int numTerms = tmpListTerms.size();
   String tmpSearchBy = "title";

              switch(numTerms)
              {
                 case 1: crit.add(Restrictions.like(tmpSearchBy, "" + tmpListTerms.get(0), MatchMode.ANYWHERE)); break;
                 case 2:   crit.add(Restrictions.or(Restrictions.like(tmpSearchBy, "" + tmpListTerms.get(0), MatchMode.ANYWHERE),
                             Restrictions.like(tmpSearchBy, "" + tmpListTerms.get(1), MatchMode.ANYWHERE))); break;
                 case 3: crit.add(Restrictions.or(
                                         Restrictions.or(Restrictions.like(tmpSearchBy, "" + tmpListTerms.get(0), MatchMode.ANYWHERE),
                                                      Restrictions.like(tmpSearchBy, "" + tmpListTerms.get(1), MatchMode.ANYWHERE)),
                                          Restrictions.like(tmpSearchBy, "" + tmpListTerms.get(2), MatchMode.ANYWHERE))); break;                                                      
                 case 4: crit.add(Restrictions.or(
                                         Restrictions.or(Restrictions.like(tmpSearchBy, "" + tmpListTerms.get(0), MatchMode.ANYWHERE),
                                         Restrictions.like(tmpSearchBy, "" + tmpListTerms.get(1), MatchMode.ANYWHERE)),
                                         Restrictions.like(tmpSearchBy, "" + tmpListTerms.get(2), MatchMode.ANYWHERE))); break;                  
    case etc....                         }

Specifically, I'm confused as to how to implement this logic that daisy chains "

Code:

Restriction.or()

" clauses for a variable number of search terms.

Is there a more elegant (and readable) solution?

I'm also confused about how to make the string comparison case insensitive.

Thanks for setting me straight,

-Tim

Hibernate version:
3.1

crouvrais · **Posted:** Mon Nov 20, 2006 5:54 pm

Hi,

if I where you I would use the native text search of your database.
Oracle, SQL Server, etc provide excellent indexing and search mecanisms for this.

a++ Cédric

treilly_2000 · **Joined:** Mon Nov 20, 2006 5:28 pm **Posts:** 4

Thanks for the reply Cedric. If at all possible, I would very much like to avoid changing the structure of the database. Major modifications like the one you suggest are things I'll investigate in version 2.0 of the app.

Obviously this is not the most performant query to write, but the application has a small number of records so performance of an inefficient query shouldn't suffer much.

crouvrais · **Posted:** Mon Nov 20, 2006 6:45 pm

I would create a collection of hibernate restrictions (crtierion) ...

Something like this (don't have a compiler at hand so just writing of the top of head)

ArrayList<Criterion> list = ...

Criterion criteria = null;

for (Restrictions restriction : list) {
criteria = Restrictions.or(criteria, restriction);
}

Criteria toExec = ...;
toExec.add(criteria);
return toExec.list();

Will generate many "or" statements but you'll probably get the appropriate result.

a++ Cédric

you'll have to initialize the criteria correctly on the first iteration, but you should get the idea here.

rajasaur · **Joined:** Tue Aug 23, 2005 8:52 am **Posts:** 181

Code:

String cond = "";
StringTokenizer st = new StringTokenizer(searchTerm, " ")
while (st.hasMoreTokens())
{
    cond += st.nextToken + "%";
}
...
crit.addRestriction(Restriction.like(tmpSearchBy, cond));

crouvrais · **Posted:** Tue Nov 21, 2006 8:43 am

Hey,

I'm sorry to say that I beleive that what you have written does not provide the desired behaviour.
It is an or that is requested between each like

a++ Cédric

rajasaur wrote:

Code:

String cond = "";
StringTokenizer st = new StringTokenizer(searchTerm, " ")
while (st.hasMoreTokens())
{
    cond += st.nextToken + "%";
}
...
crit.addRestriction(Restriction.like(tmpSearchBy, cond));

CWitherow · **Posted:** Tue Nov 21, 2006 9:34 am

Take a look at Restrictions.disjunction(). The JavaDoc is not very good for this method but it looks like it might help you.

Curtis ...

rajasaur · **Joined:** Tue Aug 23, 2005 8:52 am **Posts:** 181

crouvrais wrote:

Hey,

I'm sorry to say that I beleive that what you have written does not provide the desired behaviour.
It is an or that is requested between each like

a++ Cédric

Oops Sorry, Missed that, Sorry 4 the spam

treilly_2000 · **Joined:** Mon Nov 20, 2006 5:28 pm **Posts:** 4

Thanks very much for the help Cedric. I think you found a solution that'll work. I'll give it a try now and let you know how it goes.

treilly_2000 · **Joined:** Mon Nov 20, 2006 5:28 pm **Posts:** 4

Here's the solution that ended up working, pretty much exactly as Cedric outlined above.

Code:

// Remove 1st term entered
String tmpTerm = tmpListTerms.remove(0);
// Create criteria for 1st term
Criterion criteria = Restrictions.ilike(tmpSearchBy, tmpTerm, MatchMode.ANYWHERE);
              
// If additional terms, add them as additional criteria via an AND
for (String term : tmpListTerms) {
   criteria = Restrictions.and(criteria,Restrictions.ilike(tmpSearchBy, term, MatchMode.ANYWHERE)); 
} 

// Add to parent Criteria
crit.add(criteria);

Thanks very much for the help