Hibernate version: 3.1.3

I want to use this MySQL SQL statement passing in the search string:

select product_id, categoryid, offerid, productid, productname, productdesc from product where status = 'L' and match(productname) against('+ ":searchStr" ' in boolean mode)


If I leave the + sign out of the statement Hibernate works but I need the + to give me the exact results I require.

I've tried a Named Query, and then createSQLQuery and tried to build the statement I want but I either get "invalid token" (for match/against) or "cannot find named parameter" ... gotta be the + sign creating this problem!

How can I do this please?

Here's what I have tried (unsuccessfully):
...................................................................
<sql-query name="com.xxx.xxxx.models.productSearchB">
<return class="com.xxx.xxxxx.models.Product"/>
<![CDATA[
select product_id, categoryid, offerid, productid, productname, productdesc
from product
where status = 'L'
and match(productname) against('+ :searchStr ' in boolean mode)
]]>
</sql-query>
...................................................................
public List getProductList(String search, int pageno)
throws HibernateException
{

String queryString;
List products;

queryString = "select * from com.xxx.xxxx.models.Product "
+ "where status = 'L' "
+ "and match(productname) against('+ :searchStr ' in boolean mode) ";

products = session.createSQLQuery(queryString)
.setString("searchStr", search)
.setFirstResult((pageno-1)*30)
.setMaxResults(30)
.list();

return products;

}

Historically searching in the MySQL forums I found this link suggesting "Add your own SQLFunction to a custom subclass of MySQLDialect".
This was back in mid-2005 ... so is this the only solution currently?

http://forums.mysql.com/read.php?39,411 ... #msg-41195

Any suggestions appreciated.

(As yet, I haven't tried this.)

Cheers,

Mark D

BUMP!

Right, I finally got it working.

1) Extended MySQLDialect to register new function match_against

2) Created MatchAgainstFunction class (extends SQLFunction) with render to return MATCH AGAINST syntax
e.g. MATCH(fieldname) AGAINST (\'+ \" :searchStr \"\' IN BOOLEAN MODE)

3) Used extended MySQLDialect class (in hibernate.cfg.xml) instead.


Loads of viewers, No suggestions. but we got there.