How to Fetch data from table with primary key of table getting saved in some other field of same table

Asked on December 07, 2017
I am using employee table and am storing super employee id (i.e, primary key of employee table). How to get super employee name.  how to achieve it using hibernet. My table structure is as follows.

enter image description here 

Replied on December 07, 2017
Please post your HQL which you have tried yet.

Replied on December 07, 2017
Hello Kamal ,

In employee model mentioned the secondary table as follows

@Table(name = "CORE_EMPLOYEE")
    @SecondaryTable(name="Employee_TYPE", pkJoinColumns={
        @PrimaryKeyJoinColumn(name="pk_empt_id", referencedColumnName="pk_emp_id") })

and for fetching the data wrote code as follows
public Iterable<Employee> getAllEmployees() {
//return employeeRepository.findAll();
Query query = em.createQuery("SELECT emp FROM Employee emp where emp.deleted=0");
return (Iterable<Employee>) query.getResultList();

Thanks and Regards

Anita Patil

Replied on December 07, 2017
Secondary table i am not using in this case (Sorry copied it by mistake)

Replied on December 08, 2017
For your table Employee, find a solution that may not be optimized.
HQL query:

select emp1.empName, 
(select empName From Employee as emp2 where emp2.empId = emp1.superiorEmpId) as boss 
FROM Employee as emp1

You can it as following:

public class Employee {
private int empId;
private String empName;
private String title;
private int superiorEmpId;
        //setter getter

public class HibernateExample {
public static void main(String[] args) {
Session session = HibernateUtil.getSessionFactory().openSession();
String hql = "select emp1.empName, (select empName From Employee as emp2 where emp2.empId = emp1.superiorEmpId) as boss FROM Employee as emp1";
List<?> list = session.createQuery(hql).list();
for(int i=0; i<list.size(); i++) {
Object[] row = (Object[]) list.get(i);
String emp = (String)row[0];
String boss = (String)row[1];
System.out.println(emp + " - " + boss);


Hibernate: select employee0_.emp_name as col_0_0_, (select employee1_.emp_name from employee employee1_ where employee1_.emp_id=employee0_.superior_emp_id) as col_1_0_ from employee employee0_

Peter - null
John - Peter
Amanda - Peter
Mary - John
Ralph - Amanda
Jeanne - Amanda

Write Answer

©2024 | Privacy Policy | Contact Us