Hi all:

Is there any support for MySQL "FULLTEXT" queries in Hibernate? Searching this forum I see this question was asked back in September, but there were no answers, so perhaps the answer is "no"?....

If that's the case - how do people handle querying large-text databases in Hibernate? Or is this not done?


Thanks

- bill

I think this is the type of performance bottleneck that people usually hand code in JDBC.

It SHOULD be that the HQL parser will just pass through the MySQL full text search syntax. So try it.

But recently there was a report that it does not, in fact, let you do this. let me know.

Well, I may be doing something wrong, but it doesn't seem to be working for me. Here's what I did:



This produces:
net.sf.hibernate.QueryException: Incorrect query syntax [FROM items WHERE MATCH (title) AGAINST (:query)]

So, passing the MySQL FULLTEXT query through doesnt seem to work.

I also downloaded the latest 2.1b4, and tried using createSQLQuery, ie:



This produces:

net.sf.hibernate.QueryException: Named parameter does not appear in Query: query [SELECT {items.*} FROM items WHERE MATCH (title) AGAINST (:query)]

Is this a parsing bug? I'm not sure about the rules re Hibernate parsing out named parameters - for example, this works fine:



While this does not: (note the addition of the MySQL '%' wildcard to the ':query' parameter:



Above produces:

java.lang.IllegalArgumentException: Parameter query does not exist as a named parameter in [SELECT {items.*} FROM items WHERE title LIKE :query%]


So, there you go. It seems not to be working, but it could just be my relative newbiness.

EH??? This is completely broken HQL!

But this:



might be what you are looking for.



And also a broken SQLQuery, I think. Try:






Of course not! Append the % to the parameter value, not the name of the parameter!.

Well, thanks for the replies... I'll try these changes and see what happens. I'll not post another question re this......


- bill

Using createSQLQuery() for creating native full text queries makes it less attractive to use Hibernate, since we need to write the dialects for each used datase (in our case, Oracle, MySQL and PostgreSQL). Why not use the plain old JDBC instead, if we loose the database-independency anyway?

It should be possible to write a simple full text query with HQL. Hibernate would then compile the HQL expression into native SQL dialect of the used database. The Hibernate would return an error message for unsupported full text query operators (if the underlying database would not support the used operator (if the HQL cannot be successfully compiled into current SQL dialect)). Most databases support basic operators (and, or, not). These could be the initially supported HQL full text search operators.