Hibernate version: 3.0.4

I'm trying to implement a keyword search on my Product object, to return a list of products matching a user's keyword search.

Users can enter a list of keywords in freeform text, and I need to retrieve products that have any of the keywords in their name OR description.

I started off trying to use Restriction.in("name", keywords), but this will only match exactly to the name.

e.g. if the user types "Nike", it should match the name="Nike Trainers", but doesn't.

I figure I need to use LIKE somewhere, but I have no idea how to do that at the same time as using the IN restriction.

And of course, it needs to search for these keywords in BOTH the name AND description, and match them if EITHER is present.

This is really confusing me I'm afraid. Can anyone help, or even point me in the right direction?

Thanks,

Dave.

1. It is possible to construct HQL on the fly based on provided input, something like (approximate pseudo code):
StringBuffer sb = new StringBuffer( “from Product p where ” );
StringTokenizer st = new StringTokenizer(userInput);
While( st.hasMoreTokens()){
String keyword = st.nextToken();
sb.append( “p.name like ‘%”).append( escape( keyword) ).append(”%’”);
sb.append( “ or p.description like ‘%”).append( escape( keyword) ).append(”%’”);
}

This way is not very efficient and requires case independence at DB level to catch ‘Nike’ and ‘nike’.


2. Consider using search engines like:
DBSight http://search.dbsight.com/
or Compass
http://compass.sourceforge.net/

see also discussion: http://www.theserverside.com/news/threa ... d_id=35679

Thanks for the reply kgignatyev,

I considered doing the dynamic building of HQL, as I've done this before using standard JDBC, but I was hoping there was a better way to do this with Hibernate.

Compass looks quite good, although it means adding another framework plus all the configuration that goes with it. For now, I was looking for a Hibernate-only solution. I think I'll look into Compass though, once this project is finished.

Dave.