how to do Self join in hibernate

Abel · **Joined:** Tue Feb 17, 2009 4:54 am **Posts:** 2

how to execute sql equivalent in hibernate :

select e.*,d.ename from emp e, emp d where e.empno = d.empno;

String sql = "SELECT E.*,E.ename FROM emp E, emp D WHERE E.mgr=D.empno";

SQLQuery query = session.createSQLQuery(sql);
List results = query.list();

I'm able to get the result for results List type

but I dont know how to extract that from that results.

please give me some code snippet.

Thanks & Regards,
Abel

parmendratyagi · **Posted:** Tue Feb 17, 2009 6:18 am

I don't have any code now. But i used such query some time back in my code.

Each element of list will be the array of object. so when you iterate list, element return by

Code:

list.getNext()

will be type of

Code:

Object[]

littypreethkr · **Posted:** Tue Feb 17, 2009 8:18 am

Code:

 for (Object obj : results) {
   Object[] arr = (Object[]) obj;
   Employee e = (Employee) arr[0];
   String mgrName = (String) arr[1];
}

Abel · **Joined:** Tue Feb 17, 2009 4:54 am **Posts:** 2

while executing the above code
I'm getting exception

java.lang.ArrayIndexOutOfBoundsException: 0

while executing
System.out.println(results);

output :
[[Ljava.lang.Object;@11978b, [Ljava.lang.Object;@f42ad0, [Ljava.lang.Object;@26dbec, [Ljava.lang.Object;@1309e87, [Ljava.lang.Object;@f7c31d, [Ljava.lang.Object;@2acc65, [Ljava.lang.Object;@1d10a5c, [Ljava.lang.Object;@ff2413, [Ljava.lang.Object;@9980d5, [Ljava.lang.Object;@1d95492, [Ljava.lang.Object;@13f7281, [Ljava.lang.Object;@76ab2f, [Ljava.lang.Object;@e0cc23, [Ljava.lang.Object;@7a1576, [Ljava.lang.Object;@1171b26, [Ljava.lang.Object;@1f78040, [Ljava.lang.Object;@50988, [Ljava.lang.Object;@b4e29b, [Ljava.lang.Object;@18941f7, [Ljava.lang.Object;@49d67c, [Ljava.lang.Object;@1a06e38, [Ljava.lang.Object;@1a42792, [Ljava.lang.Object;@2200d5, [Ljava.lang.Object;@64ab4d, [Ljava.lang.Object;@12a55aa, [Ljava.lang.Object;@5e13ad]

so I' getting the results but Unable to parse them.

littypreethkr · **Posted:** Tue Feb 17, 2009 8:54 am

Sorry I didnt notice that you are running native sql. I gave it for HQL.

Also I think that you meant:
String sql = "SELECT E.*,D.ename FROM emp E, emp D WHERE E.mgr=D.empno";

Employee record and corresponding manager name.

So now if the emp table has 5 columns your result list will be a list of object arrays having 6 elements, the last element being a String of manager name.