Hibernate CriteriaBuilder to join multiple tables

Question

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 } 

---

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' 

---

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, 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_.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.

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>