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

Question

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?

score 1 · Answer 1 · answered Jan 19 '18 at 05:19

1

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.

answered Jan 19 '18 at 05:19

Anton Tupy

951
5
16

Georg Leber · Accepted Answer · 2018-01-19T05:46:08.523

1

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

edited Jan 19 '18 at 05:46

answered Jan 19 '18 at 05:21

Georg Leber

3,470
5
40
63

so even if I give the isActive condition, the Set retrieved from FeeGroup wont be having any impact due to that condition? – Arun Sudhakaran Jan 19 '18 at 05:26
1

No, because when at least one `FeeHeading` in the `FeeGroup` meet the requirements, the complete `FeeGroup` is loaded by JPA. – Georg Leber Jan 19 '18 at 05:29
so how to get it filtered from the query itself – Arun Sudhakaran Jan 19 '18 at 05:32
Thanks for the answer but actually we want to fetch the FeeGroups, so that in the later part of code we iterate the FeeGroup and take the Set of FeeHeadings from individual groups and perform the nested iteration to populate a Map. – Arun Sudhakaran Jan 19 '18 at 05:50
1

Ok, than you have to filter the groups manually. (Hint: do not remove the FeeHeadings from List, when the FeeGroup is still attached to an underlying Transaction, otherwise they will be removed from database) – Georg Leber Jan 19 '18 at 05:51
the Map is used for display purpose and not for any database activities so even if I filter the headings based on isActive condition while iterating, it won't be causing any problems right? – Arun Sudhakaran Jan 19 '18 at 05:59

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

2 Answers2