Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Hibernate Desing issue

 
sharaf navas
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 518
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 518
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 62
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
sharaf navas
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 62
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic