HQL string matching on complete words?

Question

How do I match complete words in a hibernate HQL query? For example, imagine in our database we have an entry "Sam Adams". Now, given this HQL fragment:

book.character like 'Sam'

I do not match on "Sam Adams".

However, if I change my query to:

book.character = 'Sam%'

then I also match on "Samantha". But, I don't want that to match on variants of the word 'Sam'; I only want to match on the complete word "Sam".

(And, by extension, it would be okay for the query 'Sam Adams' to match too, as this is two complete words.)

Solution 1

Since the HQL maps directly to the SQL LIKE functionality and the LIKE functionality does not support regular expressions, you will have to be creative and do something like:

book.character like 'Sam %' OR book.character like '% Sam' OR book.character like '% Sam %' OR book.character = 'Sam'

This might not be the exact syntax, but you get the point. And if you didn't realize, the performance on this bad boy is not going to be anything impressive.

Solution 2

This type of search is much better handled by a full text search engine like Lucene. Hibernate Search wraps Lucene so would be a good option in this case.