[FIXED] Spring JPA custom query - is possible to use input as logic operator

Issue

Is possible to use input as logic operator in custom query? I created custom query which returns list of objects with area less or equal paramter:

@Query(value = "SELECT * FROM SHAPES WHERE COUNT_AREA(TYPE, RADIUS, WIDTH, HEIGHT) <= ?1", nativeQuery = true)
List<ShapeEntity> getObjectsWithArea(double area);

but is possible to add one more parameter instead of "<="? I would like to add logic operator as input to avoid having two similar queries, something like:

@Query(value = "SELECT * FROM SHAPES WHERE COUNT_AREA(TYPE, RADIUS, WIDTH, HEIGHT) ?1 ?2", nativeQuery = true)
List<ShapeEntity> getObjectsWithArea(String operator, double area);

instead of nearly equals:

@Query(value = "SELECT * FROM SHAPES WHERE COUNT_AREA(TYPE, RADIUS, WIDTH, HEIGHT) >= ?1", nativeQuery = true)
List<ShapeEntity> getObjectsWithGreaterArea(double area);

@Query(value = "SELECT * FROM SHAPES WHERE COUNT_AREA(TYPE, RADIUS, WIDTH, HEIGHT) <= ?1", nativeQuery = true)
List<ShapeEntity> getObjectsWithLessArea(double area);

Is it possible at all? When I try it I am getting:

SQL Error: 0, SQLState: 42601
SqlExceptionHelper   : ERROR: syntax error at or near "$1"

Solution

In your case, you cannot use @Query.

Write the implementation of getObjectsWithArea(String operator, double area) forging your query with

TypedQuery<ShapeEntity> q = entityManager.createQuery(String.format("SELECT * FROM SHAPES WHERE COUNT_AREA(TYPE, RADIUS, WIDTH, HEIGHT) %s :ca", operator), ShapeEntity.class);
q.setParameter("ca", theWantedValue)

Answered By - Pierre Demeestere
Answer Checked By - Gilberto Lyons (JavaFixing Admin)