Java Hibernate CriteriaBuilder 连接多个表

Hibernate CriteriaBuilder to join multiple tables

提问by ktgirish

I'm trying to join 4 tables using hibernate criteriabuilder..
Below are the tables respectively.. `

我正在尝试使用 hibernate 标准构建器连接 4 个表..
下面分别是这些表..`

@Entity
public class BuildDetails {
    @Id
    private long id;
    @Column
    private String buildNumber; 
    @Column
    private String buildDuration;
    @Column
    private String projectName;

}   

@Entity
public class CodeQualityDetails{
    @Id
    private long id;
    @Column
    private String codeHealth;
    @ManyToOne
    private BuildDetails build; //columnName=buildNum
}

@Entity
public class DeploymentDetails{
    @Id
    private Long id;
    @Column
    private String deployedEnv;
    @ManyToOne
    private BuildDetails build; //columnName=buildNum
}

@Entity
public class TestDetails{
    @Id
    private Long id;
    @Column
    private String testStatus;
    @ManyToOne
    private BuildDetails build; //columnName=buildNum
}

---

---

In these 4 tables I would like to perform the below sql script for MySQL:

在这 4 个表中，我想为 MySQL 执行以下 sql 脚本：

SELECT b.buildNumber, b.buildDuration,
       c.codeHealth, d.deployedEnv, t.testStatus
FROM BuildDetails b
INNER JOIN CodeQualityDetails c ON b.buildNumber=c.buildNum
INNER JOIN DeploymentDetails d ON b.buildNumber=d.buildNum
INNER JOIN TestDetails t ON b.buildNumber=t.buildNum
WHERE b.buildNumber='1.0.0.1' AND
      b.projectName='Tera'

---

---

So, How can I achieve this using Hibernate CriteriaBuilder? Please help...

Thanks in advance.......

那么，如何使用 Hibernate CriteriaBuilder 实现这一点？请帮助......

提前致谢......

采纳答案by Roland

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery query = cb.createQuery(/* Your combined target type, e.g. MyQueriedBuildDetails.class, containing buildNumber, duration, code health, etc.*/);

Root<BuildDetails> buildDetailsTable = query.from(BuildDetails.class);
Join<BuildDetails, CopyQualityDetails> qualityJoin = buildDetailsTable.join(CopyQualityDetails_.build, JoinType.INNER);
Join<BuildDetails, DeploymentDetails> deploymentJoin = buildDetailsTable.join(DeploymentDetails_.build, JoinType.INNER);
Join<BuildDetails, TestDetails> testJoin = buildDetailsTable.join(TestDetails_.build, JoinType.INNER);

List<Predicate> predicates = new ArrayList<>();
predicates.add(cb.equal(buildDetailsTable.get(BuildDetails_.buildNumber), "1.0.0.1"));
predicates.add(cb.equal(buildDetailsTable.get(BuildDetails_.projectName), "Tera"));

query.multiselect(buildDetails.get(BuildDetails_.buildNumber),
                  buildDetails.get(BuildDetails_.buildDuration),
                  qualityJoin.get(CodeQualityDetails_.codeHealth),
                  deploymentJoin.get(DeploymentDetails_.deployedEnv),
                  testJoin.get(TestDetails_.testStatus));
query.where(predicates.stream().toArray(Predicate[]::new));

TypedQuery<MyQueriedBuildDetails> typedQuery = entityManager.createQuery(query);

List<MyQueriedBuildDetails> resultList = typedQuery.getResultList();

I assume you built the JPA metamodel for your classes. If you don't have the metamodel or you simply don't want to use it, just replace BuildDetails_.buildNumberand the rest with the actual names of the column as String, e.g. "buildNumber".

我假设您为您的类构建了 JPA 元模型。如果您没有元模型或者您只是不想使用它，只需将BuildDetails_.buildNumber其余部分替换为列的实际名称String，例如"buildNumber".

Note that I could not test the answer (was also writing it without editor support), but it should at least contain everything you need to know to build the query.

请注意，我无法测试答案（也在没有编辑器支持的情况下编写它），但它至少应该包含构建查询所需的一切。

How to build your metamodel? Have a look at hibernate toolingfor that (or consult How to generate JPA 2.0 metamodel?for other alternatives). If you are using maven it can be as simple as just adding the hibernate-jpamodelgen-dependency to your build classpath. As I do not have any such project now available I am not so sure about the following (so take that with a grain of salt). It might suffice to just add the following as dependency:

如何构建你的元模型？看一看hibernate 工具（或咨询如何生成 JPA 2.0 元模型？其他替代方案）。如果您使用的是 maven，它可以像将hibernate-jpamodelgen-dependency添加到您的构建类路径一样简单。由于我现在没有任何此类项目可用，因此我对以下内容不太确定（因此请谨慎对待）。只需将以下内容添加为依赖项即可：

<dependency>
  <groupId>org.hibernate</groupId>
  <artifactId>hibernate-jpamodelgen</artifactId>
  <version>5.3.7.Final</version>
  <scope>provided</scope> <!-- this might ensure that you do not package it, but that it is otherwise available; untested now, but I think I used it that way in the past -->
</dependency>