Criteria JPA 2 With 3 Tables

Criteria JPA 2 with 3 tables

Each join takes you from the leftish type parameter to the rightish one. So, the details join of my code (second line) starts from fromUpdates, that is a Path<Update>, and creates something which is behind the scenes also a Path<Detail>. From that, you can build other joins. Try this (code not tested):

Root<Update> fromUpdates = query.from(Update.class);
Join<Update, Detail> details = fromUpdates.join("details");
Join<Detail, Associate> associate = details.join("associate");
List<Predicate> conditions = new ArrayList();
conditions.add(builder.equal(associate.get("associateId"), associateId));
conditions.add(builder.isNull(details.get("ack_date")));

TypedQuery<Update> typedQuery = em.createQuery(query
        .select(fromUpdates)
        .where(conditions.toArray(new Predicate[] {}))
        .orderBy(builder.asc(fromUpdates.get("updateId")))
        .distinct(true)
);

JPA Critera API Join 3 Tables get 1 Type

Start with a Root of Employees and make a chain of joins:

CriteriaQuery<Employee> query = cb.createQuery(Employee.class);
Root<Employee> employee = query.from(Employee.class);
Join<Employee, Contract> contractJoin = employee.join("contracts"); // assuming that Employee has a collection property named contracts
Join<Contract, Company> companyJoin = contractJoin.join("company");

---

This is the correct Awnser with the following addition:

The Types "Employee" and "Company" have to have a field "companies" / "employees" with the @JoinTable annotation like follows:

Employee:

...
@OneToMany
@JoinTable(name="Contract" ...)
private List<Company> companies;
...

Company

...
@OneToMany
@JoinTable(name="Contract" ...)
private List<Employee> employees;
...

The "@JoinTable" annotation prevents hibernate to create a relation table on its own.

See the comments for more info.

Hibernate CriteriaBuilder to join multiple tables

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>

JPA Criteria API join on 3 tables and some null elements

Finally got the solution : to fetch all the corresponding results, I had to add the type of the join which would be left join, since I wanted to fetch all ParentObjects regardless of owning childOne or ChildTwo objects.

final Join<ParentObject, ChildOne> joinchildOne = 
                 root.join("childOne", JoinType.LEFT);

                 final Join<ParentObject, ChildTwo > joinchildTwo = 
                 root.join("childTwo", JoinType.LEFT);

How to use JPA Criteria API when joining many tables

If you use canonical Metamodel, you'll avoid this kind of errors.
In your code you have misused the "dentist" keyword, that's probably the cause of your error, because "dentist" is not a field in Company entity.

However, looking at how you defined your class in the other question, the way to define that join using Metamodel is this:

SetJoin<Company,Product> products = companyRoot.join(Company_.products); 

As you can see, Metamodel avoids the use of strings, and so avoids a lot of runtime errors. If anyway you don't use Metamodel, try this:

SetJoin<Company,Product> products = companyRoot.join("products"); 

If you now want to add a predicate, i.e. something after the where, you'll write something like:

Predicate predicate = criteriaBuilder.equal(products.get(Product_.category), "dentist");
criteria.where(predicate);

If you want to add a join for the City entity:

Join<Company, City> city = companyRoot.join(Company_.city);
predicate = criteriaBuilder.and(predicate, criteriaBuilder.equal(city.get(City_.cityName), "Leeds");
criteria.where(predicate);

(supposing that the field cityName is the correct field name for your city).

How to join 3 table using hibernate criteria?

You just create a root for every entity and add the condition to the where clause. Something like this:

Root<Parent> parent = criteriaQuery.from(Parent.class);
Root<ParentChildMapping> mapping = criteriaQuery.from(ParentChildMapping.class);
Root<Child> child = criteriaQuery.from(Child.class);
criteriaQuery.select(parent.get("parentId"));
criteriaQuery.where(
  criteriaBuilder.and(
    criteriaBuilder.equal(parent.get("parentId"), mapping.get("mappingParentId")),
    criteriaBuilder.equal(child.get("childId"), mapping.get("mappingChildId")),
    criteriaBuilder.equal(child.get("childName"), "abc")
  )
);

---

Related Topics