4

In our application we have many native queries (usually, overall when we need to perform some JOIN, LEFT JOIN or FROM clauses using a view on the fly). Moreover, many queries are repeated in many EJBs but the only things they change are some conditions in the WHERE-clause. Obviously, we would like to avoid repeating all those native queries only to change some silly filtering parameters in the end, so it is possible using Hibernate EntityManager to add a WHERE-clause dynamically with some conditions?

I know that with entities and JPQL we can use first CriteriaBuilder then CriteriaQuery<T> and finally, by adding some Predicate, we get a fully built TypedQuery<T> to execute. But, can we get the same goal using NATIVE queries?

SagittariusA
  • 5,289
  • 15
  • 73
  • 127
  • I guess you use `@Query(value = "select ...", nativeQuery = true)`? – Valentin Grégoire Sep 24 '18 at 13:47
  • I guess it could me something similar. All our native queries are inside a very big .xml file like ` queryName ![CDATA[SQL TEXT]] `. We have a sort of internal framework where a query is loaded from this file given its name. Then, we call `createNativeQuery` on entity manger with `.setParameter()` where required, if the query contains any `:paramName` strings. – SagittariusA Sep 24 '18 at 14:00
  • So, in the end we have a native query loaded from this file and to which we would like to add dynamic where clauses. – SagittariusA Sep 24 '18 at 14:01
  • Can't you use the :param to fill in the whole where dynamic where clause? If it's an in house framework, can't you just modify it a bit? – Valentin Grégoire Sep 24 '18 at 14:09
  • Our in house framework simply lets us collect all queries in an XML file and recover it by name. Anyway, we would like to write queries WITHOUT any where clause and add a where clause from java code, with as many "AND" conditions as we want. In this way the query is unique and we use it in different contexts without having to hard code any `:paramName` in the query as number of `:paramName` will vary – SagittariusA Sep 24 '18 at 14:15

0 Answers0