Hi there,
I'm a new Hibernate user and I'm trying to find a way to perform free text searching using Hibernate. I'm using mySQL as my underlying database, which has excellent free text search features- unfortunately after reading the Hibernate documentation I have been able to find anything that would allow me to utilize this feature with HQL.

Its possible that I missed something. I was looking for something along the lines of Oracle's CONTAINS keyword, or mySQL's MATCH(foo) AGAINST(bar) syntax, but didnt find anything. Heres a link to the relevant section of the mySQL manual if you're not too sure what I'm on about:

http://www.mysql.com/doc/en/Fulltext_Search.html

Is there a way of performing this kind of search through Hibernate?

Thanks for your time

Jon

Use Lucene: http://jakarta.apache.org/lucene/

Thank you for the link otis.

I am actually tied to using Hibernate and after reading the Lucine FAQ, it appears that its a stand-alone application rather than something I could plug into Hibernate- is this correct?

Jon

you can use createSQLQuery()

Thanks.

OK. So, after reading up on the createSQLQuery() Session class method I now know I can do my Free Text Search using native SQL just by passing the native SQL query as an argument.

I still have a small problem though. Some of the queries I'm going to be writing will have to use a mixture of SQL for the free text search and HQL at the same time (for additional constraints on the results).

Is there any way of using SQL and HQL together? Or could I set additional constraints on the Query object once I've got it back from the createSQLQuery() method?

Jon

You can use arbitrary SQL functions in the WHERE clause of an HQL query.

Thank you I'll give that a shot.

Jon

If you really want advanced free text searching (wo killing you DB) I strongly recommend you Lucene as a standalone product.

Hi,
OK. Going on what has been said in this thread so far I am under the impression that the following HQL/SQL query would work...

from Group
where match(name, description) against ('Some Text')

To clarify: Group is an class which maps onto a table which has text columns called "name" and "description". The "match" and "against" keywords are mySQL specific (In Oracle you would use "contains"). So basically everything after the word "where" is specific to mySQL.

Does Hibernate allow Database-specific SQL after the where clause in a query?

Jon

Just try it.

Reference Documentation, 10.8:

"Scalar SQL functions supported by the underlying database may be used."

Of course, it would need to be:

It didnt work. Running the following query:

from Group g
where match(g.name, g.description) against('foreign')

Caused an exception to be thrown. The error message was of the form:

Caused by: net.sf.hibernate.QueryException: Incorrect query syntax [from Group g where match(g.name, g.description) against('foreign')]

I read something in the manual about table aliases being inside braces {}, but didnt really understand it. Could this be something to do with it?

Logging into the test database and running the following SQL query:

select * from group where match(name, description) against('foreign');

Produced the expected results.

Jon

OK, so it perhaps doesn't work. Looks like the query parser doesn't like two "function calls" right beside each other.

So, do like I said the first time, and use createSQLQuery().

OK. I'll need to talk to my boss about this, he's not about at the moment.

I think the likelihood is that I will be asked to alter Hibernate to support these Text Searches without using SQL at all.

I can see it coming.

I've had a breif look at your CVS repository, but are there any classes you recommend I pay special attension to in light of this possibility.

Thank you for your help so far.

Jon

We're going to want to be able to do searches that combine freetext searches with constraints on other columns that are already mapped in hbm.xml files. Using createSQLQuery() would mean having to wire in table column names, creating what would be unnecessary duplication if we could mix and match the HQL and SQL we need. We'd have to do this for all our tables that have fulltext indexes.

(Additionally, though I think it's unlikely, if we wanted to search over multiple types of Domain Object then even those objects that don't have fulltext indexes would have to be searched with a regular SQL query so they can be joined with those that have and have the search results paged back by Hibernate.)

Wouldn't it be better to modify the query parser to enable it to deal with juxtaposed function calls?

Presumably you're saying that in the query:



that and are parsed as separate function calls?