CODEWITHSUNDEEP
sql-serverhibernatehqlfull-text-search
Torsten
Torsten

Reputation: 71

Hibernate + MSSQL + Fulltext Search via Contains

My goal is to use the MSSQL Fulltext Function with HQL. For what case I wrote a specific SQLFunction mapping the my "fulltext" function to the contains function.

However, the problem is that in HQL (it seems) I have to explicity use a return type, which the MSSQL Contains functions does not use or accepts.

This is how it works in MSSQL:

select distinct id from content c where CONTAINS(c.content, 'p')

This is my idea of using it in HQL:

select id from Content c  where fulltext(c.content, 'p')

This does not work, since HQL needs a return type. For example this will parse in HQL:

select id from Content c  where fulltext(c.content, 'p') = true

And it will generate as SQL:

select distinct id from content c where CONTAINS(c.content, 'p') = 1

which will not work in MS SQL.

My ideas are so far but what does not seem possible in this setup:

  1. Make hibernate parse functions with no return value (Hibernate does not support this in my version used)
  2. Trying to mix HQL and SQL (also does not seem to work)

Anyone got another idea or help?

My Hibernate Version used is 3.2.6ga and MSSQL Server 2008.

Upvotes: 3

Views: 3612

Answers (2)

Torsten
Torsten

Reputation: 71

I found a way what works for me.

Instead of generating 

CONTAINS(a,b)

it will generate 

CONTAINS(a,b) AND 1

And in conjunction with the HQL Query

fulltext(a,b) = true

It will result in:

CONTAINS(a,b) AND 1 = 1

And it works.

Upvotes: 4

NimChimpsky
NimChimpsky

Reputation: 47290

Native Query might help

Upvotes: 1

Related Questions