Hibernate Criteria查询连接

Question

我有一个问题，我必须编写一个条件查询来选择记录，其中所选表中的PK列与用于连接的表中的FK列以及连接表中字段上的一些where子句之间存在匹配。

使用的实体:员工、员工分配、SubCompany、分支机构、指定

连接中使用的实体: Employee和Employee Assignment

主体描述:员工可以是大公司/组织的一部分，大公司/组织被划分为子公司，员工可以被分配到子公司。每个员工可以分配到多个子公司，这些子公司属于不同的分支机构，具有不同的名称。

Employee和EmployeeAssignment之间的关系是oneToMany。

需要使用Criteria API编写的SQL查询

    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>

员工表

           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表

    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表

     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>

分支表

    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>

指定表

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