How to create nested selects with sql?

Question

I want to select all entities that are tied by a custom hierarchy:

@Entity
class Employee {
    @Id
    private long id;

    @ManyToOne
    private Company company;
}

@Entity
class Company {
    @Id
    private long id;

    @ManyToOne
    private LeisureGroup leisureGroup;
}

@Entity
class LeisureGroup {
    @Id
    private long id;
}

//select all companies that belong to a LeisureGroup

Select * from company where leisureGroupId = '123'

TODO: how can I select all employees that belong to the LeisureGroup (tied by the Company reference)? Do I have to use joins here? If yes, how?

Answer 1

You don't use SQL if you want to query through JPA, you use JPQL (Java Persistence Query Language) or HQL (Hibernate Query Language) (for Hibernate users).

JPQL query (requires an EntityManager variable called em)

public List<Employee> findEmployeesInLeisureGroup(final Integer leisureGroupId) {
    final TypedQuery<Employee> query =
        em.createQuery("select e " +
                       "from Employee e join e.company c join c.leisureGroup l " +
                       "where l.id = :leisureGroupId", Employee.class);
    query.setParameter("leisureGroupId", leisureGroupId);
    return query.getResultList();
}

SQL equivalent:

select * from Employee
where company_id in (select id from Company where leisureGroup_id = 123);

Answer 2

try something like this:

Select e from Employee e where e.company.id= (select c.id from Company c where c. leisureGroup.id=:id