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

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. Looping through-,Arrays,-The FOREACH loop 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!

CodePudding user response：

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

CodePudding user response：

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.