How to create nested selects with sql?

100 views Asked by At

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?

2

There are 2 answers

3
Tobb On BEST ANSWER

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);
3
Safwan Hijazi On

try something like this:

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