Home  >  Forum  >  Hibernate
Post New Query

Fetching data from 2 tables based on the id criteria (primary id of one table is saved in other tables column)

Asked on December 07, 2017

I want to fetch employee details from employee table and employee type name from employee_type table in one go (fetch in one api call),

 Am having 2 tables one is employee and one is employee_type table while inserting employees details  am inserting primary key of employee_type table along with the other employee details in employee table (employee table having one field called empt_type_id). How to achieve it using hibernate. Can any one help me out.

Replied on December 07, 2017
What is table structure and what HQL have you tried yet?

Replied on December 07, 2017
I want the hql query for my following sql query

select * from core_employee emp, employee_type empt where emp.deleted=0 and emp.emp_type_id=empt.pk_empt_id;

Replied on December 08, 2017
Suppose entity for core_employee is CoreEmployee  and entity for employee_type is EmployeeType, your HQL could be as following

FROM CoreEmployee as emp, EmployeeType as empt WHERE where emp.deleted=0 and emp.empTypeId=empt.EmptId;

Replied on December 10, 2017
@Kamal thank you it got worked.

Replied on December 10, 2017
I am getting json response in array of array format how do i get in single array.

My json responce is as follows,

[[{"pk_emp_id":5,"tenant_id":"Zone1","location_id":1,"emp_number":"sk44","prefix":"","first_name":"qqqqq","middle_name":"www","last_name":"eeee","display_name":"qqqq","full_name":"qqq qqqq","email":"[email protected]","gender":"Female","emp_type_id":2,"date_of_hire":191000,"date_of_birth":null,"manager_id":7,"phone_number":"9877654","position":"SE","responsibility":"","notes":"","contracted":"0","street":"vidyanagar","state":"Karnataka","city":"hubli","zip_code":"9898","dob":-19800000,"ssn":"","deleted":0},{"pk_empt_id":2,"empt_tenant_id":"2","empt_name":"temporary","deleted":0}]]

and service implantation is as follows,

public List<Employee> getAllEmployees() {
//return employeeRepository.findAll();
Query query = em.createQuery("FROM Employee as emp, EmployeeType as empt WHERE  emp.deleted=0 and emp.emp_type_id=empt.pk_empt_id");
return (List<Employee>) query.getResultList();

Replied on December 11, 2017
Try selecting only employee.

SELECT emp FROM Employee as emp, EmployeeType as empt WHERE  emp.deleted=0 and emp.emp_type_id=empt.pk_empt_id

Replied on December 11, 2017
If i select only emp from employee.. am getting data from only employee table and not from both employee and employee_type tables.

Write Answer

©2019 concretepage.com | Privacy Policy | Contact Us