Hibernate CriteriaBuilder连接多个表 [英] Hibernate CriteriaBuilder to join multiple tables

问题描述

我正在尝试使用hibernate criteriabuilder加入4个表。

下面分别是表格。
`

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

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

---

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

---

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

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'

---

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



提前致谢.......

---

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

Thanks in advance.......

推荐答案

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(BuildDetails_.buildNumber, "1.0.0.1"));
predicates.add(cb.equal(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();

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

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_.buildNumber and the rest with the actual names of the column as String, e.g. "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.

如何构建元模型？请查看 hibernate工具（或咨询如何为其他替代方案生成JPA 2.0元模型？。如果您正在使用maven，那么只需将 hibernate-jpamodelgen -dependency添加到构建类路径即可。因为我现在还没有任何这样的项目，所以我对以下内容不太了解（所以请稍等一下）。将以下内容添加为依赖项可能就足够了：

How to build your metamodel? Have a look at hibernate tooling for 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:

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

这篇关于Hibernate CriteriaBuilder连接多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！