How to get values from db with a given range coming as a list using postgresql and jpa

Question

I have a requirement where in, the controller class receives a list as a request param. This list can contain a range of values, which is salary column in the table. ex: {500-10000 , 10000-12000, 12000-14000..} using this range of values I need to query db to return values coming in this range.below is sample db structure(its different , but just wanted to show the salary column):

so the query has to be on salary column. In case, the list is empty, I have to return based on a default range, which is every 10000. ex: 0-10000, 10001-20001.. I have seen examples where I can use between clause with And, but how would I iterate the list in the query? maybe this solution, I can use, but here multiple db calls:

how to get values in discrete ranges from a DB table , using SQL Queries

there is one qay I see from postgres documentation- https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY:~:text=43.6.7.%C2%A0Looping%20through-,Arrays,-The%20FOREACH%20loop But I don't know how to do this using JPA in @Query. Any help or suggestion is appreciated. Thanks!

Edit 1: There is a change in the requirement and now what I need is the max, min and difference of max and min. below query works fine in query editor:

SELECT max(cast(salary as INTEGER)) as maxSalary, min(cast(salary as INTEGER)) as minSalary , max(cast(salary as INTEGER)) - min(cast(salary as INTEGER)) as salaryDiff from employee_role_and_salary;

But, I need this to be in the repository method and return all three. I already tried the solution from- https://www.baeldung.com/jpa-queries-custom-result-with-aggregation-functions and https://www.baeldung.com/spring-data-jpa-projections. But they send one error or other. I see that its mentioned that the projection classes need to have the same constructor parameter as the root class, but that not in my case. Please suggest if there is a way to achieve this. Thanks!

Maybe start small - what do you have working? For a range query, you would likely need to dynamically build it, with JPQL of the form "select entity from Entity entity where (:minVal1 < salary AND :maxValue1 >= salary) or (:minVal2 < salary AND :maxValue2 >= salary)" , with a new OR clause for each pair of min/max values you pass in. How to handle parameters is going to be up to your method, as is how you build that query: JPA doesn't have controller classes or @Query annotations, that is Spring and not likely to be able to dynamically handle what you seem to want. — Chris, Sep 07 '22 at 18:42
hmm.. I thought that this can be the only way out. I get your idea, can send multiple arguments to the repository method and each argument being a pair and build the query around it. But, thanks for the confirmation that JPA doesn't have any build in ways to achieve it. — Abhinash Jha, Sep 07 '22 at 19:25
You can use `TypedQuery#~setMaxResults(int)` and `TypedQuery#setFirstResult(int)` — grigouille, Sep 08 '22 at 06:58
This question (my interpretation) is on filtering based on a data range, salary in this case. max/first affect the number of rows returned and do not filter based on the data, and there is no way to get rows 0-100 and then 200-1000 without separate queries either. — Chris, Sep 08 '22 at 15:45
@AbhinashJha JPA can absolutely do this, as my query shows, it is just you cannot statically define a method and have Spring generate the query dynamically for you - you have to build it yourself when you have the parameters. — Chris, Sep 08 '22 at 15:46

score 1 · Answer 1 · answered Sep 08 '22 at 08:24

I wrote for you sample query. You can make some small changes yourself. The basic logic is written.

CREATE TABLE table_main (
    "name" varchar(100) NULL,
    salary int4 NULL
);

INSERT INTO table_main ("name", salary) VALUES('abc', 6000);
INSERT INTO table_main ("name", salary) VALUES('def', 10000);
INSERT INTO table_main ("name", salary) VALUES('xyz', 12000);
INSERT INTO table_main ("name", salary) VALUES('dmp', 50000);


CREATE TABLE table_condition (
    con varchar NULL
);


INSERT INTO table_condition (con) VALUES('{500-10000 , 10000-12000, 12000-14000}');
INSERT INTO table_condition (con) VALUES('{15000-16000, 20000-21000}');


-- this is qyery for do it 
select distinct t1.* from table_main t1 
inner join 
    (
        select  
            string_to_array(unnest(
                string_to_array(regexp_replace(con, '[^0-9,-]', '', 'g'), ',')), '-') as con 
        from table_condition
    ) t2 on t1.salary >= t2.con[1]::int4 and t1.salary <= t2.con[2]::int4

score 0 · Answer 2 · answered Sep 13 '22 at 18:34

Well, after looking around, I got the answer from this resource: JPA Data Repositories with SqlResultSetMapping and native queries

Basically, create a new Entity class, and add @SqlResultSetMapping and @NamedNativeQuery to that class. In the repository class, add @Query to the repository method. All the steps are mentioned in the url mentioned.

How to get values from db with a given range coming as a list using postgresql and jpa

2 Answers2