Hibernate条件查询联接

Question

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

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

         //getters and setters
     }

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

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

           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
           }

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

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

    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>

子单位表

     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
    }

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

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

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

       //getters and setters
       }

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

指定表

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

我需要帮助编写条件查询使用提供的SQL。

Answer 1

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);