Hibernate CriteriaBuilder join multiple tables

Question

I am 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 } 

---

In these 4 tables, I would like to execute 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' 

---

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

Thanks in advance...

Answer 1

 CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery query = cb.createQuery(/* Your combined target type, eg 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(); 

I assume that you have created a JPA metamodel for your classes. If you don’t have a metamodel or just don’t want to use it, just replace BuildDetails_.buildNumber and the rest with the actual column names like String , for example "buildNumber" .

Please note that I could not verify the answer (also wrote it without editor support), but it should contain at least everything that you need to know to build the request.

How to build a metamodel? Look at the hibernation tool for this (or refer to the “How to Create a JPA 2.0 Metamodel” section for other alternatives). If you use maven, it can be as simple as adding hibernate-jpamodelgen -dependency to your build class path. Since I don't have such a project right now, I'm not sure about the following (so do it with a piece of salt). Just add the following as a dependency:

 <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-jpamodelgen</artifactId> <version>5.3.7.Final</version> <scope>provided</scope> <!-- this ensures that you do not package it, but that it is otherwise available --> </dependency>