Hello,

I am having a little problem. I want to get a Pojo returned from hibernate through the Query when doing a postgresql fulltext search with TSearch2.

The SQL search query looks like this:

"select * from image as i WHERE (i.idxfti @@ 'car'::tsquery)"

but Hibernate chokes on the Syntax with the @@. Is there any way to grant this kind of syntax with a normal Query or am I forced to use the SQLQuery which does (as far as I know) not return a complete Object as result?

Is there any way to get a Pojo returned from SQLQuery?

Hibernate version:
3

Full stack trace of any exception that occurs:
org.hibernate.QueryException: unexpected char: '@' [from de.bildmaschine.database.Image as i WHERE (i.idxfti @@ '?'::tsquery)]
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:109)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:75)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:54)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:134)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:113)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1602)
at de.bildmaschine.service.helper.ImageDBHelper.getSearchQuery(ImageDBHelper.java:566)
at de.bildmaschine.service.helper.ImageDBHelper.searchImages(ImageDBHelper.java:598)
at de.bildmaschine.service.impl.ImageServiceImpl.xmlImageSearch(ImageServiceImpl.java:65)
at de.bildmaschine.service.impl.ImageServiceImpl.executeService(ImageServiceImpl.java:828)
at de.bildmaschine.service.impl.BaseServiceImpl.processXml(BaseServiceImpl.java:989)
at de.bildmaschine.xml.BMXMLFactory.process(BMXMLFactory.java:72)
at de.bildmaschine.servlets.MsxmlServlet.processMessage(MsxmlServlet.java:228)
at de.bildmaschine.servlets.MsxmlServlet.doPost(MsxmlServlet.java:282)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:831)
at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:652)
at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1203)
at java.lang.Thread.run(Thread.java:595)
Caused by: line 1:56: unexpected char: '@'
at org.hibernate.hql.antlr.HqlBaseLexer.nextToken(HqlBaseLexer.java:278)
at antlr.TokenBuffer.fill(TokenBuffer.java:69)
at antlr.TokenBuffer.LA(TokenBuffer.java:80)
at antlr.LLkParser.LA(LLkParser.java:52)
at org.hibernate.hql.ast.HqlParser.handleDotIdent(HqlParser.java:312)
at org.hibernate.hql.antlr.HqlBaseParser.identPrimary(HqlBaseParser.java:3906)
at org.hibernate.hql.antlr.HqlBaseParser.primaryExpression(HqlBaseParser.java:861)
at org.hibernate.hql.antlr.HqlBaseParser.atom(HqlBaseParser.java:3422)
at org.hibernate.hql.antlr.HqlBaseParser.unaryExpression(HqlBaseParser.java:3200)
at org.hibernate.hql.antlr.HqlBaseParser.multiplyExpression(HqlBaseParser.java:3082)
at org.hibernate.hql.antlr.HqlBaseParser.additiveExpression(HqlBaseParser.java:2802)
at org.hibernate.hql.antlr.HqlBaseParser.concatenation(HqlBaseParser.java:570)
at org.hibernate.hql.antlr.HqlBaseParser.relationalExpression(HqlBaseParser.java:2586)
at org.hibernate.hql.antlr.HqlBaseParser.equalityExpression(HqlBaseParser.java:2449)
at org.hibernate.hql.antlr.HqlBaseParser.negatedExpression(HqlBaseParser.java:2413)
at org.hibernate.hql.antlr.HqlBaseParser.logicalAndExpression(HqlBaseParser.java:2331)
at org.hibernate.hql.antlr.HqlBaseParser.logicalOrExpression(HqlBaseParser.java:2296)
at org.hibernate.hql.antlr.HqlBaseParser.expression(HqlBaseParser.java:2082)
at org.hibernate.hql.antlr.HqlBaseParser.expressionOrVector(HqlBaseParser.java:4165)
at org.hibernate.hql.antlr.HqlBaseParser.primaryExpression(HqlBaseParser.java:944)
at org.hibernate.hql.antlr.HqlBaseParser.atom(HqlBaseParser.java:3422)
at org.hibernate.hql.antlr.HqlBaseParser.unaryExpression(HqlBaseParser.java:3200)
at org.hibernate.hql.antlr.HqlBaseParser.multiplyExpression(HqlBaseParser.java:3082)
at org.hibernate.hql.antlr.HqlBaseParser.additiveExpression(HqlBaseParser.java:2802)
at org.hibernate.hql.antlr.HqlBaseParser.concatenation(HqlBaseParser.java:570)
at org.hibernate.hql.antlr.HqlBaseParser.relationalExpression(HqlBaseParser.java:2586)
at org.hibernate.hql.antlr.HqlBaseParser.equalityExpression(HqlBaseParser.java:2449)
at org.hibernate.hql.antlr.HqlBaseParser.negatedExpression(HqlBaseParser.java:2413)
at org.hibernate.hql.antlr.HqlBaseParser.logicalAndExpression(HqlBaseParser.java:2331)
at org.hibernate.hql.antlr.HqlBaseParser.logicalOrExpression(HqlBaseParser.java:2296)
at org.hibernate.hql.antlr.HqlBaseParser.expression(HqlBaseParser.java:2082)
at org.hibernate.hql.antlr.HqlBaseParser.logicalExpression(HqlBaseParser.java:1858)
at org.hibernate.hql.antlr.HqlBaseParser.whereClause(HqlBaseParser.java:454)
at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:708)
at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:296)
at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:159)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:238)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:155)
... 29 more


Name and version of the database you are using:
Postgresql 8.1.3

The generated SQL (show_sql=true):
from de.bildmaschine.database.Image as i WHERE (idxfti @@ '?'::tsquery)

There is: simply use SQL support in the Hibernate
http://www.hibernate.org/hib_docs/v3/re ... rysql.html