CODEWITHSUNDEEP
javasqljpa
membersound
membersound

Reputation: 86627

How to create nested selects with sql?

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?

Upvotes: 2

Views: 120

Answers (2)

Safwan Hijazi
Safwan Hijazi

Reputation: 2089

try something like this:

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

Upvotes: 2

Tobb
Tobb

Reputation: 12180

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);

Upvotes: 1

Related Questions