Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to handle join query in Hibernate and Spring with annotations?

I am developing application using Spring and Hibernate with MySQL. I am new to Hibernate and did basic tasks...

Now I need to apply joins in select query to get data from multiple table using annotations. I have searched for it but still I didn't get any idea...

Here my database tables and bean classes :

Table 1: 'employee_info' ( id, empid, empname, doj and jobtitle )

Table 2: 'employee_login' ( username, password, status and empid )

And my bean classes are:

EmployeeInfoForm.java

@Entity()
@Table(name = "employee_info")
public class EmployeeInfoForm {

@Id
@GeneratedValue
@Column(name = "id", unique = true, nullable = true)
private int id;

@Column(name = "empId")
private int empId;

@Column(name = "empname")
private String empName;

@Column(name = "doj")
private Date empDoj;

@Column(name = "jobtitle")
private String empJobTitle;

public int getEmpId() {
    return empId;
}

public void setEmpId(int empId) {
    this.empId = empId;
}

public void setEmpDoj(Date empDoj) {
    this.empDoj = empDoj;
}

public String getEmpName() {
    return empName;
}

public void setEmpName(String empName) {
    this.empName = empName;
}

public Date getEmpDoj() {
    return empDoj;
}

public void setEmp_Doj(Date empDoj) {
    this.empDoj = empDoj;
}

public String getEmpJobTitle() {
    return empJobTitle;
}

public void setEmpJobTitle(String empJobTitle) {
    this.empJobTitle = empJobTitle;
}


}

EmployeeLoginForm.java

@Entity()
@Table(name = "employee_login")
public class EmployeeLoginForm {

@Id
@Column(name = "username")
private String empUserName;

@Column(name = "password")
private String empPassword;

@Column(name = "status")
private String empStatus;

@Column(name = "empid")
private int empId;

public String getEmpUserName() {
    return empUserName;
}

public int getEmpId() {
    return empId;
}

public void setEmpId(int empId) {
    this.empId = empId;
}

public void setEmpUserName(String empUserName) {
    this.empUserName = empUserName;
}

public String getEmpPassword() {
    return empPassword;
}

public void setEmpPassword(String empPassword) {
    this.empPassword = empPassword;
}

public String getEmpStatus() {
    return empStatus;
}

public void setEmpStatus(String empStatus) {
    this.empStatus = empStatus;
}

}

Requirement:

I want to select fields empid, empname, jobtitle from employee_info and field status from employee_login table when the empid matches on both table...

Please help me to complete my work...

Any suggestions and guidance are appreciated...

like image 668
sree Avatar asked Mar 11 '13 13:03

sree


1 Answers

There is an association between EmployeeInfoForm and EmployeeLoginForm that I am not seeing in your code. Maybe there is an Employee class there? If that is the case then you need to add that. So let us assume that each employee has many forms. Then you will code the Employee side of the relationship like this:

 public class Employee{

      @OneToMany(cascade = CascadeType.ALL, mappedBy = "employee")
      private Set<EmployeeLoginForm> loginForms = new HashSet<EmployeeLoginForm>();

      ...
 }

And the Many side of the relationship in the EmployeeLoginForm class:

 @ManyToOne
 Employee employee;

This will create the table structure such that:

 emploee = (id, etc ...)
 employeelogin = (id, employee, ....)

Now, any time you need a list of the Logins of an Employee you get it from the Employee object without needing a Query.

 Set<EmployeeLoginForm> logins = e.getLoginForms(); //where e is an employee object.

If you did want to query you can do

 select o from EmployeeLoginForm o join o.employee

But that is unnecessary in this case.

like image 86
Vincent Ramdhanie Avatar answered Oct 13 '22 13:10

Vincent Ramdhanie