aspose file tools*
The moose likes Object Relational Mapping and the fly likes Hibernate Desing issue Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate Desing issue" Watch "Hibernate Desing issue" New topic
Author

Hibernate Desing issue

sharaf navas
Greenhorn

Joined: Aug 01, 2006
Posts: 25
Hi all i'm having problem in fetching data. where i've designed 3 tables
1)employee
-----------
empId,empName , empDOB

2)groups
-----------
gpId,gpName,gpDesc

3)emp_group
------------
id,empId,gpId

i'm having many-to-many relation here. n I'm assigning employee to some groups. So how do i write a HQL to get a employee who is not already in a group.

thanks in advance
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
Try a correlated subquery. Something like this maybe:

from Employee as employee
where not exists (
from EmpGroup as eg where eg.Employee = employee
)
sharaf navas
Greenhorn

Joined: Aug 01, 2006
Posts: 25
thanks scott
but i try to come up with SQL query to this scenario. but still having problem in transfering SQL to HQL any my SQL is here


thanks in advance
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518


Does this sql work the way you want? It's different that what you described above.

This will the group names of all groups that employee #1 is not in.

I read the post to say get employees not already in a group.

Do you really want to join to en_Group in the outer select? That will limit your results to just groups that have one or more employees. Seems like you should eliminate that join.

The example I gave above can be adapted. The entity for en_empGroup will be in the outer from clause instead of Employee. And you'll have an additional where condition in the subselect for employee id = 1.
sharaf navas
Greenhorn

Joined: Aug 01, 2006
Posts: 25
thanks
the mySQL whic have given is da correct one. but i need that to translated in to HQL. since the mySQL code gives me the correct result for me so please help me in it.
thanks in advance
Vilpesh Mistry
Ranch Hand

Joined: May 27, 2003
Posts: 60
Hi sharaf
Try exceuting this query, if need be change it & check

select empId,empName,empDOB from employee emp
where emp.empId not in (
select egp.empId
from emp_group egp left fetch join groups grp
where egp.gpId = grp.gpId
)

Please post corrections if this query is wrongly constructed
thanks


Thanks.
sharaf navas
Greenhorn

Joined: Aug 01, 2006
Posts: 25
thanks vilpesh
actually my code is
startOperation();
Query qry=session.createQuery("select distinct g.gpName from EmpGroupVO eg inner join GroupVO g g.gpId join eg.gpId where g.gpId not in (select empg.gpId from EmpGroupVO empg where emp_id = 1)");
Obj=qry.list();

and I'm getting error saying

Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: g near line 1, column 90 [select distinct g.gpName from com.tt.notification.vo.EmpGroupVO eg inner join GroupVO g g.gpId join eg.gpId where g.gpId not in (select empg.gpId from com.tt.notification.vo.EmpGroupVO empg where emp_id = 1)]


Caused by: line 1:90: unexpected token: g
at org.hibernate.hql.antlr.HqlBaseParser.fromJoin(HqlBaseParser.java:1765)
at org.hibernate.hql.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1420)
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:238)
... 16 more

i appritiate any one kindly help me regarding this.
thanks in advance
Vilpesh Mistry
Ranch Hand

Joined: May 27, 2003
Posts: 60
hi,
i feel their is syntax error in the inner join.

on is absent after the 2 tables joined using inner join,

Please try as follows :
select distinct g.gpName from EmpGroupVO eg inner join GroupVO g
on g.gpId = eg.gpId
where g.gpId.....

Thanks
[ September 25, 2006: Message edited by: Vilpesh Mistry ]
sharaf navas
Greenhorn

Joined: Aug 01, 2006
Posts: 25
hi there
i think on shouldn't come because by inserting on i had this error.
aused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 89 [select distinct g.gpName from com.tt.notification.vo.EmpGroupVO eg inner join GroupVO g on g.gpId = eg.gpId where g.gpId not in (select empg.gpId from com.tt.notification.vo.EmpGroupVO empg where emp_id = 1)]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:244)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:155)
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:133)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1583)
at com.tt.notification.dao.EmpGroupDAO.LoadGroup(EmpGroupDAO.java:121)
... 7 more
Caused by: line 1:89: unexpected token: on
at org.hibernate.hql.antlr.HqlBaseParser.fromJoin(HqlBaseParser.java:1765)
at org.hibernate.hql.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1420)
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:238)
Thats why didn't insert any ON there
thanks
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Hibernate Desing issue
 
Similar Threads
Oracle Forign Key Basic .
Please Help
Throwing and Catching Exceptions...
Oracle Cursor