Postgres Spring Data JPA pass list as values

Question

I have a table with a name column that is important to me.

insert into tab (name) values ('a');
insert into tab (name) values ('b');
insert into tab (name) values ('c');
insert into tab (name) values ('d');

From my Spring Data Repository backed by Hibernate 5, I want to pass a List of names and return which names are not present in the table from what was passed in. So for list ('a', 'b', 'c', 'baz') it should return a single row with value baz.

The following query would work for this scenario if written by hand.

WITH list(name) AS (VALUES ('a'), ('b'), ('c'), ('baz'))
SELECT name
FROM list
    EXCEPT
SELECT name
FROM tab
WHERE name IN (SELECT name FROM list)

So I add the following method to my JpaRepository.

@Query(value = "WITH list(name) "
    + "    AS (VALUES :names) "
    + "SELECT name "
    + "FROM list "
    + "    EXCEPT "
    + "SELECT name "
    + "FROM tab "
    + "WHERE name IN (SELECT name FROM list)", nativeQuery = true)
List<String> findNamessMissingFromGivenList(Collection<String> names);

However, the way that Spring Data passes in :name Collection is by placing ('a', 'b', 'c', 'baz') instead of ('a'), ('b'), ('c'), ('baz'). So Collections work great for IN clauses but not this scenario. Essentially I need to pass in the list and make them into a pseudo-table like what VALUES does.

I would prefer not to build up any queries by hand and to avoid SQL injection. Is there a way to modify this query to accept a Collection from JPA?

coladict · Accepted Answer · 2020-10-23 08:45:51Z

Unfortunately VALUES :names will not produce VALUES (?),(?),(?),(?) which you need, but instead will have VALUES (?,?,?,?). You need a function that returns a set of the desired type. To get that list for your database version, you can use the following query:

SELECT 
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types"
FROM pg_catalog.pg_proc p
WHERE pg_catalog.pg_get_function_result(p.oid) LIKE 'SETOF %';

Since what you seek is a text type, the viable functions (at least in postgres 9.5) are:

unnest(anyarray) returning setof anyelement
regexp_split_to_table(target text,pattern text, flags text) returning setof text
json_object_keys(json) returning setof text
json_object_keys(jsonb) returning setof text

The first option needs support for passing arrays as parameters. You can do this either with by adding the com.mopano:hibernate-array-contributor library to your project, or compile the strings into the postgresql array-as-string syntax and using a typecast, thus making your sub-query:

WITH list(name) AS (select unnest(cast('{a,b,c,baz}' as text array)))

Avoid type-casting with the ::text[] syntax, because Hibernate will parse that as an input parameter and replace it with a question mark. Some people try using the array constructor like ARRAY[ :values ], but that only works if you're typing out each value directly into the query, not binding them via JDBC.

The second requires that you first compile the parameters into one string with a separator that is not present in any of them, then use that separator as a regex, and the third parameter must be 'g' to ensure it splits all, instead of the first match. While this is difficult and error-prone, it can be done with no additional libraries.

The third option is to compile a json object in which your values are the keys, and you might also need an additional library to support json parameters. I have two of them published, but this is a big workaround for something achievable by simpler means.

Finally, whichever one you choose, the where condition in your query is also backwards. The final result (assuming you went with the array-as-string option and no external libraries) should look like this (as tested locally):

WITH list(name) AS (select unnest(cast('{a,b,c,baz}' as text array)))
SELECT name
FROM list WHERE name NOT IN (SELECT name FROM tab);
 name 
------
 baz
(1 row)

Collectives™ on Stack Overflow

Postgres Spring Data JPA pass list as values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related