HQL Query with where clause

Question

Hi guys;

I am trying to query hibernate for given scenario:

My Data Model

Class Communication:

variables : subject, sentTo, creator.

Now lets say there are some notes exchanged between usera and userb. I want to find all those notes by a hibernate query. I have written below my query string.

select note from Communication n where n.subject=:subject and ((n.sentTo=:sentto and n.creator=:creator) or (n.sentTo:creator and n.creator=:sentto))

But hibernate is not able to parse this query. Please suggest what to do?

Exception the parser throws

2010-02-26 15:34:37,169 DEBUG [ErrorCounter] line 1:114: unexpected token: :
line 1:114: unexpected token: :
 at org.hibernate.hql.antlr.HqlBaseParser.atom(HqlBaseParser.java:3520)
 at org.hibernate.hql.antlr.HqlBaseParser.unaryExpression(HqlBaseParser.java:3216)
 at org.hibernate.hql.antlr.HqlBaseParser.multiplyExpression(HqlBaseParser.java:3098)
 at org.hibernate.hql.antlr.HqlBaseParser.additiveExpression(HqlBaseParser.java:2818)
 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:4181)
 at org.hibernate.hql.antlr.HqlBaseParser.primaryExpression(HqlBaseParser.java:944)
 at org.hibernate.hql.antlr.HqlBaseParser.atom(HqlBaseParser.java:3438)
 at org.hibernate.hql.antlr.HqlBaseParser.unaryExpression(HqlBaseParser.java:3216)
 at org.hibernate.hql.antlr.HqlBaseParser.multiplyExpression(HqlBaseParser.java:3098)
 at org.hibernate.hql.antlr.HqlBaseParser.additiveExpression(HqlBaseParser.java:2818)
 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:2306)
 at org.hibernate.hql.antlr.HqlBaseParser.expression(HqlBaseParser.java:2082)
 at org.hibernate.hql.antlr.HqlBaseParser.expressionOrVector(HqlBaseParser.java:4181)
 at org.hibernate.hql.antlr.HqlBaseParser.primaryExpression(HqlBaseParser.java:944)
 at org.hibernate.hql.antlr.HqlBaseParser.atom(HqlBaseParser.java:3438)
 at org.hibernate.hql.antlr.HqlBaseParser.unaryExpression(HqlBaseParser.java:3216)
 at org.hibernate.hql.antlr.HqlBaseParser.multiplyExpression(HqlBaseParser.java:3098)
 at org.hibernate.hql.antlr.HqlBaseParser.additiveExpression(HqlBaseParser.java:2818)
 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:2341)
 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:271)
 at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:180)
 at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:134)
 at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
 at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
 at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94)
 at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:436)
 at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:384)
 at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1327)
 at org.hibernate.cfg.AnnotationConfiguration.buildSessionFactory(AnnotationConfiguration.java:867)
 at org.springframework.orm.hibernate3.LocalSessionFactoryBean.newSessionFactory(LocalSessionFactoryBean.java:871)
 at org.springframework.orm.hibernate3.LocalSessionFactoryBean.afterPropertiesSet(LocalSessionFactoryBean.java:797)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1460)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1398)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:512)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:450)
 at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:290)
 at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
 at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:287)
 at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:189)
 at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:322)
 at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:106)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1305)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1067)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:511)
 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:450)
 at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:290)
 at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
 at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:287)
 at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:189)
 at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:557)
 at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:842)
 at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:416)
 at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:261)
 at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:192)
 at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:47)
 at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3972)
 at org.apache.catalina.core.StandardContext.start(StandardContext.java:4467)
 at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
 at org.apache.catalina.core.StandardHost.start(StandardHost.java:722)
 at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
 at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
 at org.apache.catalina.core.StandardService.start(StandardService.java:516)
 at org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
 at org.apache.catalina.startup.Catalina.start(Catalina.java:593)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:597)
 at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:289)
 at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:414)

Answer 1

I don't know if there is a mistake but your alias n doesn't seems to be defined in your query.

Can you give us the stacktrace?

Answer 2

Try using:

select note from Communication n where n.subject=:subject and ((n.sentTo=:sentto and n.creator=:creator) or (n.sentTo:creator and n.creator=:sentto))

Alias for Communication is missing and you are trying to access it through its alias 'n' (that's not defined)

Thanks

Answer 3

Hibernate is falling over here:

(n.sentTo:creator and n.creator=:sentto)
---------^

I suspect this should be

(n.sentTo = :creator and n.creator=:sentto)
---------^^^