2

For example let my query be as shown below:

String query="select * from table_name where column_name1 > ?1 and column_name2 < ?2";

@Query(value = query, nativeQuery = true)
public List<Object[]> getResult(String filterValue1,Integer filterValue2);

how to pass the operator(<,>,=) also as a parameter?

I am using postgreSQL database.

Thank you.

Vamsi Krishna
  • 21
  • 2
  • 1
    Do you mean to pass the operators as part of the value being passed to the `1`, `2` etc.? If yes, you can do it. Shouldn't be an error and work in expected manner – Debargha Roy Oct 21 '20 at 17:08
  • No.I meant pass the operator seperately as another parameter.I tried Passing it as another parameter and replaced them as ?2 ?3. Where ?2 is ">" and ?3 is value(say 2). But it was not working. Any other way to do that? – Vamsi Krishna Oct 21 '20 at 17:46

1 Answers1

0

If you have an option to construct/concat a String prior to run the query, there is no problem:

public String methodOne(String firstOperator
, String secondOperator) {
return "select * from table_name where column_name1 " 
+ firstOperator + " ?1 and column_name2 "
 + secondOperator +" ?2"; 
}

It is more complicated if you use SpringData repositories. There isn't a lot you can do with native queries parameters because SpringData is looking for native SQL operators inside the query string. But you can try to do some tricks with LIKE operator and built-in functions (in SQL, sometimes >,< can be replaced with LIKE)

(not completely an answer to your question, but)

A condition that can be omitted

 @Query(value =
... AND column_name LIKE IIF(:myParam <> '%', :myParam,'%')
<skipped>

... repositoryMethod(@Param("myParam") String myParam);

IIF - a ternary operator function in MSSQL, you can find something like this in your RDBMS

when you send myParam='actualValue' it will be transformed into

and column_name LIKE 'actualValue'

i.e. column_name='actualValue'

when you send myParam='%' it will be transformed into

and column_name LIKE '%'

i.e. "and TRUE"

Danny Briskin
  • 144
  • 7