Relevance with Hibernate

To create a search engine using the SQL Server relevance function:

You are unable to access the system tables in hibernate, but you can use a function in hibernate and have the function access the system tables. Here is what the function could look like:

CREATE FUNCTION dbo.f_getrelevance (@li uniqueidentifier,@relevancestring varchar (80))
RETURNS integer
AS
BEGIN
DECLARE @rank integer
SELECT @rank=KEY_TBL.RANK
FROM dbo.LibraryItemVersions AS FT_TBL
inner join dbo.libraryitems li on li_liguid=liv_liguid
INNER JOIN FREETEXTTABLE(dbo.LibraryItemVersions, Liv_Content,
@relevancestring)
AS KEY_TBL
ON FT_TBL.LIV_verGUID = KEY_TBL.[KEY]
where li_liguid=@li
RETURN ( @rank )
END

You pass in the relevance string and the id associated with the LibraryItem. The LIV_Content is the fulltext field and the LibraryItemVersions is the table.

You will then use a hibernate query and write the query something like this:

SELECT li.LI_title as title,dbo.f_getrelevance(li_liguid,relevanceKeyWord) as Rank
FROM LibraryItemVersions AS liv ON li.LI_liGUID = liv.LIV_liGUID

You are passing in the guid into the relevance function (dbo.f_getrelevance) and the relevance function will return the rank. The relevanceKeyWord is the text that the user has inputted in for this search. You have to remember that the query will crash if the string only contains noise words so you have to check to make sure that the relevanceKeyWord contains more than noise words. A noise word is a word such as and, or, the etc. There is a list of noise words in your SQL server directory. Make sure you SQL Server has already been set up for indexing.