Criteria for retriving data involving multiple joins

Question

I have four tables with sample data related with eachother as below

Employee

EMP_ID|EMP_NAME
---------------
101   |John

EmployeeDtl

EMP_DTL_ID  |EMP_SKILLS |EMP_ID
-----------------------------------
1001        |Java       |101
1002        |SQL        |101

EmpDeptDtl

EMP_DTL_ID  |DEPT_ID
--------------------
1001        |22
1002        |33

Dept

DEPT_ID |DEPT_NAME
----------------------
22      |XYZ
33      |PQR

Below are my corresponding pojos

@Entity
public class Employee {
   private static final long serialVersionUID = 1L;

   @Id
   @Column(name="EMP_ID")
   private Long empId;

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

   //bi-directional many-to-one association to EmployeeDtl 
   @OneToMany(mappedBy="employee", cascade=CascadeType.ALL, fetch=FetchType.EAGER)
   private Set<EmployeeDtl> empDtls;


@Entity
@Table(name="EMP_DTL")
public class EmployeeDtl {

   @Id
   @Column(name="EMP_DTL_ID")
   private Long empDtlId;

   @Column(name="EMP_GRP")
   private String employeeGrp;


   //bi-directional many-to-one association to Employee 
    @ManyToOne
   @JoinColumn(name="EMP_ID")
   private Employee employee;

    @Column(name="EMP_ID", insertable=false, updatable=false)
   private Long empId;

   //bi-directional many-to-one association to EmpDeptDtl
   @OneToMany(mappedBy="id.empDtlId", cascade= CascadeType.ALL)
   private Set<EmpDeptDtl> empDeptDtls;



@Entity
public class EmpDeptDtl {

   @EmbeddedId
   private EmpDeptDtlPK id;

   @Column(name="EMP_DTL_ID", insertable = false, updatable = false)
   private Long empDtlId;

   //bi-directional many-to-one association to Dept
    @ManyToOne(fetch=FetchType.LAZY)
   @JoinColumn(name="DEPT_ID", insertable = false, updatable = false)
   private Dept deptId;



@Embeddable
public class EmpDeptDtlPK implements Serializable {
   //default serial version id, required for serializable classes.
   private static final long serialVersionUID = 1L;

   @Column(name="DEPT_ID")
   private Long deptId;

   //bi-directional many-to-one association to EmpDeptDtl
    @ManyToOne(fetch=FetchType.LAZY)
   @JoinColumn(name="EMP_DTL_ID")
   private EmployeeDtl employeeDtl;


@Entity
public class Dept  {

   @Id
   @Column(name = "DEPT_ID")
   private Long deptId;

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

        // bi-directional many-to-one association to EmpDeptDtl 
   @OneToMany(mappedBy = "deptId")
   private Set<EmpDeptDtl> empDeptDtls;

Now, Can anyone suggest me on how to retrieve the list of Dept records for the given EMP_ID using CRITERIA. In the above sample data given, for the EMP_ID=101, the dept records with dept_ids 22 and 33 should be returned.

I got the solution using Named query and native SQl query. But I couln't get it through the hibernate CRITERIA. Can anyone please help me in this?

Thanks Harish

Answer 1

This is a direct translation from .net to java and I am not sure if the syntax is correct but you can wokr around it ( not sure, cos from the looks of it you dont seem to know a lot abt criteria, I would suggest read abt it as this is one of the easiest queries to write)

session.createCriteria(Employee.class,"emp")
.createAlias("emp.empDtls","empDetail")
.createAlias("empDetail.empDeptDtls","empDetail")
.createAlias("empDetail.deptId","dept")
.add(Restrictions.Eq("emp.empId", 101))
.setProjection(projections.property("dept.deptId"))
.toList<long>();

The answer doesnt necessarily mean I agree with your data model.. Also some of the member names could be changed to denote what they really represent..