Hibernate Criteria Query JOIN

Question

I have a problem where I have to write a criteria query to select records where there is a match between PK column in the selected table and the FK column in the table that is being used to JOIN along with some where clauses on the fields in the joined table.

Entities used: Employee, Employee Assignment, SubCompany, Branch, Designation

Entities used in JOIN : Employee and Employee Assignment

Description of Entities: An employee can be a part of a Big Company/Organization which is divided into sub companies where the employee can be assigned to. Each employee can be assigned to multiple sub companies belonging to different branches with a different designation.

The relationship between Employee and EmployeeAssignment is oneToMany.

SQL Query that needs to be written using Criteria API

    SELECT DISTINCT E.* FROM EMP E, EMP_ASSIGN EA
      WHERE
    E.ID = EA.EMP_ID AND (EA.SUB_COMP_ID IN (ACTUAL VALUES) 
    OR EA.BRANCH_ID IN (ACTUAL VALUES) 
    OR EA.DESG_ID IN (ACTUAL VALUES))

Employee.java

    public class Employee{
         private String id;
         private String firstName;
         private String lastName;
         private Set<EmployeeAssignment> employeeAssignments;

         //getters and setters
     }

Employee.hbm.xml

   <hibernate-mapping>
     <class name = "Employee" table="EMP" >
        <id name="id" column="ID"/>
        <property name="firstName" column="FIRST_NAME"/>
        <property name="lastName" column="LAST_NAME"/>
        <set name="employeeAssignments" table="EMP_ASSIGN" inverse="true" fetch="join" cascade="save-update" lazy="true"
   where="trunc(SYSDATE) BETWEEN strt_dt and end_dt">
          <key>
            <column name="EMP_ID" not null="true"/>
          </key>
            <one-to-many notfound="ignore" class="com.someorganization.entity.EmployeeAssignment"/>
        </set>
    </class>
  </hibernate-mapping>

Employee Table

           CREATE TABLE EMP(
           ID VARCHAR(20) PRIMARY KEY,
           FIRST_NAME VARCHAR(30) NOT NULL,
           LAST_NAME VARCHAR(30) NOT NULL
           );

EmployeeAssignment.java

           public class EmployeeAssignment{
           private String id;
           private String employeeId;
           private Date startDate;
           private Date endDate;
           private SubCompany company;
           private Branch branch;
           private Designation designation;

           //getters and setters
           }

EmployeeAssignment.hbm.xml

 <hibernate-mapping>
     <class name = "EmployeeAssignment" table="EMP_ASSIGN" >
           <id name="id" column="ID"/>
           <property name="employeeId" column="EMP_ID"/>
           <property name="startDate" column="STRT_DATE"/>
           <property name="endDate" column="END_DATE"/>

           <many-to-one name="subCompany" lazy="false" class="com.someorganization.entity.SubCompany">
            <column name="SUB_COMP_ID" not-null="false"/>
           </many-to-one>

           <many-to-one name="branch" lazy="false" class="com.someorganization.entity.Branch">
            <column name="BRANCH_ID" not-null="false"/>
           </many-to-one>

           <many-to-one name="designation" lazy="false" class "com.someorganization.entity.Designation">
           <column name="DESG_ID" not-null="false"/>
           </many-to-one>
     </class>
 </hibernate-mapping>

EmployeeAssignment Table

    CREATE TABLE EMP_ASSIGN(
    ID VARCHAR(20) PRIMARY KEY,
    EMP_ID VARCHAR(20) NOT NULL,
    STRT_DATE DATE,
    END_DATE DATE,
    SUB_COMP_ID VARCHAR(20) NOT NULL,
    BRANCH_ID VARCHAR(20) NOT NULL,
    DESG_ID VARCHAR(20) NOT NULL
    FOREIGN KEY(EMP_ID) REFERENCES EMP(ID),
    FOREIGN KEY(SUB_COMP_ID) REFERENCES SUB_COMP(ID),
    FOREIGN KEY(BRANCH_ID) REFERENCES BRANCH(ID),
    FOREIGN KEY(DESG_ID) REFERENCES DESG(ID))

SubCompany.java

    public class SubCompany{
    private String id;
    private String subCompanyName;

      //getters and setters
    }

SubCompany.hbm.xml

    <hibernate-mapping>
      <class name = "SubCompany" table="SUB_COMP" >
        <id name="id" column="ID"/>
        <property name="subCompanyName" column="COMPANY_NAME"/>
      </class>
    </hibernate-mapping>

SubCompany Table

     CREATE TABLE SUB_COMP(
       ID VARCHAR(20) PRIMARY KEY,
       COMPANY_NAME VARCHAR(30) NOT NULL
     );

Branch.java

    public class Branch{
       private String id;
       private String branchName;

       //getters and setters
    }

Branch.hbm.xml

   <hibernate-mapping>
     <class name = "Branch" table="BRANCH">
       <id name="id" column="ID"/>
       <property name="branchName" column="BRANCH_NAME"/>
     </class>
   </hibernate-mapping>

Branch Table

    CREATE TABLE BRANCH(
      ID VARCHAR(20) PRIMARY KEY,
      BRANCH_NAME VARCHAR(30) NOT NULL
      );

Designation.java

     public class Designation{
       private string id;
       private String designationName;

       //getters and setters
       }

Designation.hbm.xml

      <hibernate-mapping>
       <class name = "Designation" table="DESG">
         <id name="id" column="ID"/>
         <property name="designationName" column="DESG_NAME"/>
       </class>
      </hibernate-mapping>

Designation Table

      CREATE TABLE DESG(
        ID VARCHAR(20) PRIMARY KEY,
        DESG_NAME VARCHAR(30) NOT NULL
       );

I need help in writing the criteria query using the SQL provided.

Answer 1

Please check if criteria like this works. I am setting up local instance to check, if you could test it and provide update that would be awesome.

Criteria criteria = session.createCriteria(Employee.class)
                .createAlias(“employeeAssignments.id” , “emp_id”)
                .createAlias(“employeeAssignments.subCompany” , “subCompany”)
                .createAlias(“subCompany.id” , “subCompany_id”)
                .createAlias(“employeeAssignments.branch” , “branch”)
                .createAlias(“branch.id” , “branch_id”)
                .createAlias(“employeeAssignments.designation” , “designation”)
                .createAlias(“designation.id” , “designation_id”);
Criterion em_assig_id = Resitriction.eq("emp_id”, 601);
Criterion id = Restriction.eq(“id”, 601);
Disjunction or = Restrictions.disjunction();
Or.add(Restriction.in(“subCompany_id”, new String[] {“”,””,””} ));
Or.add(Restriction.in(“branch_id”, new String[] {“”,””,””} ));
Or.add(Restriction.in(“designation_id”, new String[] {“”,””,””} ));
Criterion final = Restriction.and(or, Restriction.and(em_assig_id, id));
criteria.add(final);