• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Hibernate is not recognizing SQL's 'FREETEXT' keyword

 
Nina Anderson
Ranch Hand
Posts: 148
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm trying to execute a search query with hibernate and the query is failing because hibernate does not recognize MS SQL Server's keyword "FREETEXT".

Select S.serviceId from ServiceCbo S where FREETEXT(S.serviceName, '" + userInput + "')

I've been struggling use this for DAYS!! and there are not any information online regarding this keyword.
 
Shailesh Kini
Ranch Hand
Posts: 153
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Nina,

I assume you have already set the Dialect for your database in hibernate config. Are you using Native Query interface to execute your query?

Can you please provide us with the code and the error message?

Also, which jdbc driver are you using?
[ August 16, 2007: Message edited by: Shailesh Kini ]
 
Nourbii Khuako
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There is simple way to select data using database specific features like freetext function , we can use native sql query instend of HQL.
Let assume that we have 1 table testTbl that has 4 columns
rc1, rc2, sc1,sc2. We want to make query that filter data on columns sc1,sc2 and return columns rc1,rc2
and that query should use freetext SQLServer function.

Then we need to make 2 steps procedure:

1- write the query and execute it:

String nativeSql = "select t1.rc1 as firstProp, t1.rc2 as secondProp from testTbl t1 where t1.sc1 = aram1 and freetext (t1.sc2, aram2)";
SQLQuery query = session.createSQLQuery(nativeSql);
query.setParameter("param1", firstParamValue);
query.setParameter("param2", secondParamValue);

2. write simple object that will hold query data and has default ctor and getter/setter methods to access its properties.

public class ResultObject {

//note that in sql query we explicitly give result columns aliases : firstProp and secondProp
//so in this class we need to name our properties accordingly.

private String secondProp;
private String firstProp;

public ResultObject() { }

public String getSecondProp() { return secondProp; }
public void setSecondProp(String secondProp) { this.secondProp = secondProp; }
public String getFirstProp() { return firstProp; }
public void setFirstProp(String firstProp) { this.firstProp = firstProp; }

here we also need to implement 2 methods from Object correctly:
boolean equals(Object o) { ... } and
int hashCode() { ... }
}

after that the data can be fetched like this:
query.setResultTransformer(Transformers.aliasToBean(ResultObject.class));
List data = query.list();

this way of using native sql does not require that ResultObject has to be managed entities(we dont need to write mapping file for it).

Also we can extract sql in mapping file like this:

<sql-query name="filterByText">
select
t1.rc1 as firstProp,
t1.rc2 as secondProp
from
testTbl t1
where
t1.sc1 = aram1 and freetext (t1.sc2, aram2)
</sql-query>

then use it like this:

Query query = session.getNamedQuery("filterByText");
query.setParameter("param1", firstParamValue);
query.setParameter("param2", secondParamValue);
query.setResultTransformer(Transformers.aliasToBean(ResultObject.class));
List data = query.list();

so there is no need to use stored procedure to call freetext function of the SQLServer,
but we can also use stored procedure, for example if we create stored procedure spFindText that that has our native sql query then
we can use it in a simular way:

String nativeSql = "{ call spFindText ( aram1, aram2) }";
SQLQuery query = session.createSQLQuery(nativeSql);
query.setParameter("param1", firstParamValue);
query.setParameter("param2", secondParamValue);
query.setResultTransformer(Transformers.aliasToBean(ResultObject.class));
List data = query.list();

There is one moment to note here. In our ResultObject we use all properties as String, but some columns in ResultSet
may be integer or other types, in this case we can convert all columns to varchar in query like this:

select
cast(t1.rc1 as varchar (10)) as firstProp,
cast(t1.rc2 as varchar (10)) as secondProp
...
or
use in ResultObject appropriate property types for example for integer column use Integer wrapper.
(all this information can be found in 16.1.5. Returning non-managed entities of the hibernate reference).
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic