Spring Data JPA Specification: Filtering complex data

Question

I am trying to filter some data from the table shown below. I have successfully done that using native query. But some how the requirement got changed and now i want to do it using JPA Criteria specification , As i am very new to JPA specification i am finding it very hard to get to the solution. Any help is greatly appreciated.

id	account_id	code	associated_id	percentage
1	A608	QPR	null	100
2	A609	BU00Q	null	100
3	A610	BU00R	null	100
4	A611	BU00P	17	40
5	A611	BU00P	null	100
6	A609	BU00Q	17	60
7	A611	BU00P	20	70
8	A610	BU00R	17	60

What i am trying to achieve is when i provide associate_id as input ,i need to show all the account_id rows that are used by it along with the unused one with 100 percentage.

for ex: if i provide associated_id as 17..it has to display entries with id:4,6,8 and 1(since it is unused and has 100 percent).I have already acheived this using query

Query

select * from (select * from account_entities2 where associated_id=17
UNION
select * from account_entities2 where account_id not in(select account_id from account_entities2 where associated_id=17) and percentage=100) e order by account_id ;

Output:

id	account_id	code	associated_id	percentage
1	A608	QPR	null	100
6	A609	BU00Q	17	60
8	A610	BU00R	17	60
4	A611	BU00P	17	40

for associated_id=20 output will be(since only one account_id is used and remaining are unused as shown below)

id	account_id	code	associated_id	percentage
1	A608	QPR	null	100
2	A609	BU00Q	null	100
3	A610	BU00R	null	100
7	A611	BU00P	20	70

AS said the query was working fine but ...i need to use Spring data JPA specification to achieve the same result ,what i have till now is..

final Specification spec = criteria.getSpecification()
                .and((root, criteriaQuery, criteriaBuilder) ->
                        criteriaBuilder.equal(root.get("associated_id"), associateId))
                .or((root, criteriaQuery, criteriaBuilder) ->
                     root.get("associated_id").isNull())
                .and((root, criteriaQuery, criteriaBuilder) ->
                        criteriaBuilder.greaterThan(root.get("percentage"), 0));




public class AccountRecord {

    @Id
    @Column(name="id")
    private String id;

    @Column(name = "account_id", nullable = false)
    protected String account_id;
 
    @Column(name = "code", nullable = false)
    private String code;
 
    @Column(name = "associated_id", nullable = false)
    private Integer associated_id;

    @Column(name = "percentage", nullable = false)
    private BigDecimal percentage;
}

I am very naïve to JPA Specification, and any help is greatly appreciated, Thankyou

Spring Data JPA Specification: Filtering complex data

0 Answers