休眠 CriteriaBuilder 以联接多个表

java mysql hibernate hibernate-mapping hibernate-criteria

2022-09-02 01:26:16

我正在尝试使用休眠标准构建器连接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
}

在这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'

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

提前致谢...

答案 1

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

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

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

如何构建您的元模型？看看休眠工具（或者参阅如何生成JPA 2.0元模型？以获取其他替代方案）。如果您使用的是 maven，则只需将 -依赖项添加到构建类路径即可。由于我现在没有任何这样的项目可用，我不太确定以下内容（所以请谨慎对待）。只需添加以下内容作为依赖项可能就足够了：hibernate-jpamodelgen

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

答案 2