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

Question

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...

Answer 1

You can do the following using the Hibernate criteria projection:

public List extractEmployeeAttributes() {
    log.debug("extractEmployeeAttributes");
    try {
                    Session session = sessionFactory.getCurrentSession();
                    session.beginTransaction();
           Criteria c1 = session.createCriteria(employee_info.class,emp_info);
           Criteria c2 = session.createCriteria(employee_login.class,emp_log);
           c1.setProjection(Projections.projectionList()
                                .add(Projections.property("empid"))
                                .add(Projections.property("empname"))
                                .add(Projections.property("jobtitle"))
                                .add(Projections.property("employee_info "))
                                .add(Restrictions.and(Property.eqName(emp_info.empId,emp_log.empId))
          return c1.list();

    } catch (RuntimeException re) {
        log.error("extractEmployeeAttributes failed", re);
        throw re;

    }

}

Answer 2

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.

Answer 3

You are thinking in database / pure SQL terms when you talk about performing joins with select statements. The power (and danger) of Hibernate is that it abstracts this away from you and lets you think in Object terms. What you need is a relationship between the 2 objects and then let Hibernate handle this relationship.

I recommend you spend some time reading this:

http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/associations.html

to get a better understanding of how Hibernate can help.