4

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<AccountRecord> 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

raghuveer
  • 113
  • 1
  • 6
  • 1
    `UNION` is not supported by JPA. Instead you can transform your original native query to `SQL VIEW` and use Specifications just to parametrize this View. Example is here https://stackoverflow.com/a/59979203/2224047. P.S. you shouldn't name variables like `account_id` and `associated_id` in java code, better stick to camel-case naming -- `accountId` and `associatedId` – Nikolai Shevchenko Apr 28 '21 at 06:54
  • Sorry if I don't have a solution (don't know that "Specification API"), but it looks to me your "WHERE" conditions are not quite correct: You want `associated_id==123 OR ( associated_id==null AND precentage==100)`. So this would translate to `...getSpecification().and( criteriaBuilder.equal(root.get("associated_id"), associateId).or(criteriaBuilder.and(root.get("associated_id").isNull(),criteriaBuilder.greaterThan(root.get("percentage"), 0))))`. (sorry for the wrong syntax - I hope you still get the idea) – GameDroids Apr 28 '21 at 07:32
  • Thanks Nikolai,But i am not going with my UNION query approch...i want to acheive the same with spring JPA specification. – raghuveer Apr 28 '21 at 07:45
  • Any help please.. – raghuveer Apr 28 '21 at 09:17
  • I think you can use only two queries in one transaction instead of `UNION` – Grigorii Riabov Apr 28 '21 at 12:00
  • i am not intending to use queries at all ....rather i want to go with spring data specification API... – raghuveer Apr 29 '21 at 05:18

0 Answers0