CODEWITHSUNDEEP
javahibernate
javaMan
javaMan

Reputation: 6680

How to get columns from two tables using join in hibernate

I have two tables Employee and Department as below:

import java.sql.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;


@Entity
@Table(name="EMPLOYEE")
public class Employee {

@Id
@GeneratedValue
@Column(name="employee_id")
private Long employeeId;

@Column(name="firstname")
private String firstname;

@Column(name="lastname")
private String lastname;

@Column(name="birth_date")
private Date birthDate;

@Column(name="cell_phone")
private String cellphone;

@ManyToOne
@JoinColumn(name="department_id")
private Department department;

public Employee() {

}

public Employee(String firstname, String lastname, String phone) {
    this.firstname = firstname;
    this.lastname = lastname;
    this.birthDate = new Date(System.currentTimeMillis());
    this.cellphone = phone;
}

// Getter and Setter methods
}

and 

import java.util.Set;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

 @Entity
@Table(name="DEPARTMENT")
  public class Department {

@Id
@GeneratedValue
@Column(name="DEPARTMENT_ID")
private Long departmentId;

@Column(name="DEPT_NAME")
private String departmentName;

@OneToMany(mappedBy="department")
private Set<Employee> employees;

// Getter and Setter methods
}

Tables look something like this:

enter image description here

Now I want to get Employee with last name as Mayers and Department name as sales.

So I thought of doing join and below is the HQL query I wrote:

String hql="    select e.employeeId, e.firstName from Employee E join Department D on E.department.departmentId = D.departmentId where e.lastName= :param1 and d.departmentName= :param2"

        query.setParameterList("parm1", "Mayers");
        query.setParameterList("parm2", "sales"));

I am getting an exception saying path expected for join. I tried giving fully qualified name com.myexample.Department Then I got dot node with no left-hand-side. Can you guys point me in right direction.

Upvotes: 0

Views: 1856

Answers (1)

Predrag Maric
Predrag Maric

Reputation: 24433

Try this

select e.employeeId, e.firstName from Employee e join e.department d where e.lastName= :param1 and d.departmentName= :param2

Upvotes: 2

Related Questions