-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 4 posts ] 
  Previous topic | Next topic 
Author Message
chawax
 Post subject: How to use a subquery in FROM clause
PostPosted: Fri Oct 17, 2008 5:37 am 
Beginner
Beginner

Joined: Mon Jan 10, 2005 7:14 am
Posts: 32
Hi,

I have the following SQL query with a subquery in FROM clause :

Code:
select emp.roll_number, emp.first_name
from employee emp, (
    select elt.external_id
    from element_value_by_date evd
    inner join data d on d.id = evd.data_fk
    inner join element elt on elt.id = evd.element_fk
    where d.code = 'HJOU_PRECYCLE'
    and evd.string_value = 420
) myElement
where emp.id = myElement.external_id


I try to write this query in HQL / JP-QL but I can't find how to do this. I tried this :

Code:
select emp
from Employee emp, (
    select distinct ev.element.externalId 
    from ElementValue ev
    where ev.data.code = :dataCode 
    and ev.stringValue = :value) elt
where emp.id = elt.externalId


But Hibernate can't parse the query. It throws the following stack trace :

Code:
Caused by: java.lang.NoSuchMethodError: org.hibernate.hql.antlr.HqlBaseParser.recover(Lantlr/RecognitionException;Lantlr/collections/impl/BitSet;)V
   at org.hibernate.hql.antlr.HqlBaseParser.fromRange(HqlBaseParser.java:1590)
   at org.hibernate.hql.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1428)
   at org.hibernate.hql.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1130)
   at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:702)
   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:248)
   at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
   at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
   at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
   at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
   at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
   at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
   at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:92)
   at org.apache.openejb.persistence.JtaEntityManager.createQuery(JtaEntityManager.java:174)
   at org.jboss.seam.persistence.EntityManagerProxy.createQuery(EntityManagerProxy.java:81)
   at t4.core.commons.employee.internal.EmployeeDaoImpl.handleLoadFromElements(EmployeeDaoImpl.java:36)
   at t4.core.commons.employee.internal.EmployeeDaoBase.loadFromElements(EmployeeDaoBase.java:443)
   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:585)
   at org.apache.openejb.core.interceptor.ReflectionInvocationContext$Invocation.invoke(ReflectionInvocationContext.java:158)
   at org.apache.openejb.core.interceptor.ReflectionInvocationContext.proceed(ReflectionInvocationContext.java:141)
   at org.jboss.seam.intercept.EJBInvocationContext.proceed(EJBInvocationContext.java:44)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56)
   at org.jboss.seam.transaction.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:28)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
   at org.jboss.seam.core.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:44)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
   at org.jboss.seam.persistence.EntityManagerProxyInterceptor.aroundInvoke(EntityManagerProxyInterceptor.java:29)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
   at org.jboss.seam.persistence.HibernateSessionProxyInterceptor.aroundInvoke(HibernateSessionProxyInterceptor.java:31)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
   at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:107)
   at org.jboss.seam.intercept.SessionBeanInterceptor.aroundInvoke(SessionBeanInterceptor.java:50)
   at sun.reflect.GeneratedMethodAccessor35.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:585)
   at org.apache.openejb.core.interceptor.ReflectionInvocationContext$Invocation.invoke(ReflectionInvocationContext.java:158)
   at org.apache.openejb.core.interceptor.ReflectionInvocationContext.proceed(ReflectionInvocationContext.java:141)
   at org.apache.openejb.core.interceptor.InterceptorStack.invoke(InterceptorStack.java:67)
   at org.apache.openejb.core.stateless.StatelessContainer._invoke(StatelessContainer.java:210)
   at org.apache.openejb.core.stateless.StatelessContainer._invoke(StatelessContainer.java:188)
   at org.apache.openejb.core.stateless.StatelessContainer.invoke(StatelessContainer.java:165)
   at org.apache.openejb.core.ivm.EjbObjectProxyHandler.businessMethod(EjbObjectProxyHandler.java:217)
   at org.apache.openejb.core.ivm.EjbObjectProxyHandler._invoke(EjbObjectProxyHandler.java:77)
   at org.apache.openejb.core.ivm.BaseEjbProxyHandler.invoke(BaseEjbProxyHandler.java:321)
   at org.apache.openejb.util.proxy.Jdk13InvocationHandler.invoke(Jdk13InvocationHandler.java:49)
   at $Proxy100.loadFromElements(Unknown Source)
   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:585)
   at org.jboss.seam.util.Reflections.invoke(Reflections.java:22)
   at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:31)
   at org.jboss.seam.intercept.ClientSideInterceptor$1.proceed(ClientSideInterceptor.java:76)
   at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56)
   at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:107)
   at org.jboss.seam.intercept.ClientSideInterceptor.invoke(ClientSideInterceptor.java:54)
   at org.javassist.tmp.java.lang.Object_$$_javassist_1.loadFromElements(Object_$$_javassist_1.java)
   at t4.core.commons.employee.internal.EmployeeInternalServiceBean.handleLoadEmployeesFromElementSelection(EmployeeInternalServiceBean.java:256)
   at t4.core.commons.employee.internal.EmployeeInternalServiceBase.loadEmployeesFromElementSelection(EmployeeInternalServiceBase.java:574)
   ... 79 more



I could use a IN clause instead, but performance with MySQL is really ugly when subquery returns a lot of records (but no performance problem with the query above). I don't want neither to use a JOIN between employee and element table because I just get the HQL for subquery from another application, I don't write it. And of course I don't want to write in in SQL !

Are subqueries in FROM clause supported ? If yes, how should I do this ?
Top
 Profile  
 
Madan_Prabhu
 Post subject:
PostPosted: Fri Oct 17, 2008 6:32 am 
Regular
Regular

Joined: Wed Oct 15, 2008 6:59 am
Posts: 103
Location: Chennai
You may use 'IN' in ur query... like that
select ... from employee e where e.id in (select external_id.....)

_________________
If u feel it will help you, don't forget to rate me....

Top
 Profile  
 
chawax
 Post subject:
PostPosted: Fri Oct 17, 2008 10:38 am 
Beginner
Beginner

Joined: Mon Jan 10, 2005 7:14 am
Posts: 32
As I said, I don't want to use IN clause because performance are very bad in MySQL with big amount of datas when using IN clause. The table used in my subquery has more than 5 millions records. When using IN clause it takes about 2 minutes to execute, using the subquery in FROM clause, just a few seconds ...
Top
 Profile  
 
Madan_Prabhu
 Post subject:
PostPosted: Sat Oct 18, 2008 12:54 am 
Regular
Regular

Joined: Wed Oct 15, 2008 6:59 am
Posts: 103
Location: Chennai
Ciao
Then u may use join, just create the associate mapping for these tables on id fields. Actually use of sub query results in the double execution. So i would like to prefer u to join. In our project we also use join. when i use subquery to retrieve some data my eclipse closes itself. because of the memory used for these retrieved objects.
So go ahead with join.

_________________
If u feel it will help you, don't forget to rate me....

Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 4 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.