Passing entire where clause to native spring jpa @Query [duplicate]

Question

Currently I'm unable to know the fields that will be within the SQL where clause (think adhoc) when creating the native SQL in my spring @Query. Therefore I'm passing in the entire where clause.

If I output the SQL to the console and paste it in to my sql editor I'm able to receive a valid resultset.

SELECT * FROM lorder WHERE order_id = 1196077

Last SQL output to the console was: Hibernate:

/* dynamic native SQL query */ SELECT
    * 
FROM
    lorder 
WHERE
    ?
and the where clause value being passed in to the @query is:
order_id = 1196077

This is what I am currently doing which is not working.

@Query(
    value = "SELECT * FROM lorder WHERE :where",
    nativeQuery = true)
List<OrderEntity> listSelected(@Param("where") String where);  

Not sure if passing the entire where clause is possible but I'm expecting a list. However I'm currently getting an empty collection.

Answer 1

Since using the @Query annotation you can only use named parameters (your :where) or ordinal parameters (e.g. ?1, ?2) of a specific Java-type, it's not possible to inject partial SQL-expressions.

However you could use a TypedQuery to add partial SQL to a query:

  public List<OrderEntity> getOrdersUsingWhereClause(EntityManager em, String whereClause) {
    TypedQuery<OrderEntity> query = em.createQuery(
        "SELECT o FROM lorders o WHERE " + whereClause,
        OrderEntity.class);
    return query.getResultList();
  }

See https://www.objectdb.com/java/jpa/query/parameter#Parameters_vs.Literals