JPA Critera API Join 3 Tables get 1 Type

Question

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    |

A Contract is the relationship between a Company and Employee
An employee may belong to one or more Companies
A company has one or more employees

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?

score 1 · Accepted Answer · edited Mar 01 '17 at 10:08

1

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.

edited Mar 01 '17 at 10:08

Sebastian

1,642
13
26

answered Mar 01 '17 at 08:01

perissf

15,979
14
80
117

That´s the problem, The type Employee has no field "contracts". – Sebastian Mar 01 '17 at 08:07
Why can't you add it like in all JPA examples concerning OneToMany and ManyToOne relationships? – perissf Mar 01 '17 at 08:08
1

If I add the field "@OneToMany contracts" to Employee, hibernate would add another table to my database. Isn´t that a bit redundant, since the information could be read from the Contracts table itself? – Sebastian Mar 01 '17 at 08:18
You are right, you don't need other tables. I am not a fan of Hibernate, and I never let it create or update my db. You should be able to define the correct relationship without letting Hibernate add new tables. But this seems to belong to a new question... – perissf Mar 01 '17 at 08:23
1

The settings for letting / preventing HIbernate create the db tables: [Does Hibernate create tables in the database automatically](http://stackoverflow.com/questions/4507142/does-hibernate-create-tables-in-the-database-automatically). Moreover if Hibernate wants to create a new table you probably need to map the name with `name` property of `@Table` annotation and check if the relationship is defined correctly as shown [here](https://en.wikibooks.org/wiki/Java_Persistence/OneToMany) – perissf Mar 01 '17 at 09:17
The @JoinTable annotation in your reference looks like the right hint. I'll try that later. – Sebastian Mar 01 '17 at 09:40

JPA Critera API Join 3 Tables get 1 Type

1 Answers1