How to pass operators like "<",">","=" (less than,greater than,equals to) as parameters to a native query in springboot?

Question

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.

Answer 1

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"