CODEWITHSUNDEEP
javahibernatehqlone-to-manypersistent-set
Arun Sudhakaran
Arun Sudhakaran

Reputation: 2405

What should be done to get the Persistent Set filtered using a query condition

I'm having two DAO classes FeeGroup and FeeHeading like this

class FeeGroup {
    private int id;
    private String name;
    private Set<FeeHeading> feeHeadings;
    private booelan isActive;
    //getters & setters
}
class FeeHeading {
    private int id;
    private String name;
    private FeeGroup feeGroup;
    private booelan isActive;
    //getters & setters
}

This is the HQL query to fetch results based on isActive values of both FeeHeading and FeeGroup.

select fg.feeHeadings 
from FeeHeading fh 
join fh.feeGroup fg
where fg.isActive = 1
and fg.isOptional = 0
and fh.isActive = 1
and fg.id = 78

The FeeGroup with id 78 is having 10 FeeHeadings under it and only one heading is having isActive as 1. The current query is returning 10 records even if I have given the filter condition as fh.isActive = 1. But when I change my query to

select fh.name 
from FeeHeading fh 
join fh.feeGroup fg
where fg.isActive = 1
and fg.isOptional = 0
and fh.isActive = 1
and fg.id = 78

Only one record is coming which is the only active record. So why I'm not getting a filtered Set in the first place even if the condition is given or what should I do in the query to get it filtered?

Upvotes: 1

Views: 204

Answers (2)

Georg Leber
Georg Leber

Reputation: 3580

In the first statement you are selecting all the FeeGroups and returning their associated FeeHeadings. You cannot create a sublist of a list querying the parent object. JPA is filtering your query and then must load all the requested objects with there complete elements. If FeeGroup meets your requirements (id = 78 and isOptional = 0 and at least one FeeHeading with isActive = 1) it must load the complete Group.

In your second query you are selecting the FeeHeadingsdirectly, so you can create a sublist of them, because JPA must only create that objects.

UPDATE

Not tested so far, but you might test if the following JPQL gives you the desired result:

select fh
from FeeHeading fh 
where fh.feeGroup.isActive = 1
and fh.feeGroup.isOptional = 0
and fh.isActive = 1
and fh.feeGroup.id = 78

Upvotes: 1

Anton Tupy
Anton Tupy

Reputation: 969

In first query you select all heading from group in which (group) has at least one heading satisfy the condition. I.e. all 10 headings from your group.

In second query you select only headings which satisfy the condition. I.e. only one heading.

Upvotes: 1

Related Questions