CODEWITHSUNDEEP
javajpacriteria-api
Sebastian
Sebastian

Reputation: 1722

JPA Critera API Join 3 Tables get 1 Type

I´m trying to join 3 tables with JPA Critera API and get the result as a list of type other than the relation table.

The Entities are:

| Employee |   | Contract |   | Company |
|----------|   |----------|   |---------|
| id       |   | Company  |   | id      |
| age      |   | Employee |   | name    |

I try now to get all Employees that work for Company A like so:

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Contract> query = cb.createQuery(Contract.class);

    Root<Contract> contracts = query.from(Contract.class);
    Join<Contract, Company> companyJoin = contracts.join("company");
    Join<Contract, Employee> employeeJoin = contracts.join("employee");

    List<Predicate> conditions = new ArrayList<Predicate>();
    conditions.add(cb.equal(companyJoin.get("name"), "Company A"));

    TypedQuery<Practice> typedQuery = em.createQuery(query
            .select(contracts)
            .where(conditions.toArray(new Predicate[conditions.size()]))
    );

    typedQuery.getResultList();

This gives me a List of Contracts with Empoyees that work in "Company A".

How can I write the Query to get a List of Employees instead of Contracts?

Upvotes: 3

Views: 4708

Answers (1)

perissf
perissf

Reputation: 16273

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.

Upvotes: 1

Related Questions