0

Below query is wokring in postgres SQL and giving required result, but when trying from JAVA it is giving error.

select * from res where 
'{"^\\d{3}\\d{1,}133\\d{1,}$","^\\d{3}7483"}' is null
or exists (select from unnest(cast('{"^\\d{3}\\d{1,}133\\d{1,}$","^\\d{3}7483"}' as text[])) rx where cast (r.res_value as text) ~ rx);

java code:-

@Query(value = "select * from res where 
'{?1 is null
or exists (select from unnest(cast(?1 as text[])) rx where cast (r.res_value as text) ~ rx)",nativeQuery = true)
public List<Res> getData(List<String> patterns);

List of patterns = 
^\\d{3}\\d{1,}133\\d{1,}$
^\\d{3}7483

Error getting - "ERROR: malformed array literal: ^\d{3}\d{1,}133\d{1,}$ Detail: Array value must start with "{" or dimension information.

How i can add { } to these.. any solution in JPA

andrew
  • 79
  • 9
  • could you also post your Res class? – sawim Nov 18 '20 at 10:21
  • @sawim Res is just a normal object having id, name , value etc. here issue is { } is not passed when we pass list of patterns.. so it is expecting same format like i mentioned top query which is working fine on postgres when i execute direclty – andrew Nov 18 '20 at 10:26

1 Answers1

0

It's better to convert the List of String to String in java and pass it as String param to the JPA Query.

Step 1: Convert List of String to String in java before passing to JPA
Java: String patternsStr = patterns.stream().map(p -> "\"" + p + "\"").collect(Collectors.joining(",", "{", "}"));

Step 2: Change the type of parameter from List to String in @Query method
JPA: public List<Res> getData(String patternsStr);

The postgres SQL query you mentioned is accepting regx array as string. So it's easy to pass the regx list as string to JPA Query.

Joseph Kurian
  • 58
  • 5
  • tried with above approach Pattern formed is like {^\d{3}\d{1,}133\d{1,}$,^\d{3}7483} and as it is string it automatically appends " (double quotes at the start and end) . ideally it should start with '(Single quote and end with single quote) and inside that regex patterns should be in double quotes like this '{"^\\d{3}\\d{1,}133\\d{1,}$","^\\d{3}7483"}' Hence getting below error ERROR: malformed array literal: "{^\d{3}\d{1,}133\d{1,}$,^\d{3}7483}" Detail: Unexpected "{" character. – andrew Nov 19 '20 at 03:27
  • 1
    "{".concat(StringUtils .wrap(StringUtils .join(searchPattern, "\", \""), "\"").replace("\\", "\\\\")) .concat("}") This is worked for me – andrew Nov 19 '20 at 06:40
  • For the regex pattern in double quotes of the patternsString. Just add a map function to the stream for appending quotes at both end. The below added line of list of String to String converter will work in your case. String patternsStr = patterns.stream().map(p -> "\"" + p + "\"").collect(Collectors.joining(",", "{", "}")); – Joseph Kurian Nov 19 '20 at 08:42
  • I wonder whether this solution introduces an SQL injection vulnerability to your code – maciekb May 18 '22 at 15:06