Query hint parameters in JpaSpecificationExecutor [DATAJPA-144]

[spring-projects-issues]

Anssi Törmä opened DATAJPA-144 and commented

The Specification pattern is useful when complex specifications are combined from simple ones. However, query performance becomes an issue. Possibility to pass query hints to methods of JpaSpecificationExecutor would be very useful. That is, I would like something along these lines

JpaSpecificationExecutor.findAll(Specification<T>, List<QueryHint>)
JpaSpecificationExecutor.findAll(Specification<T>, Sort, List<QueryHint>)
…

where QueryHint is a name-value pair. Note that it must be possible to pass multiple hint values with the same name. For example "eclipselink.batch" -> "org.manager", "eclipselink.batch" -> "org.employees".

As a workaround, I've had to create my own JpaSpecificationExecutor with such methods using the source code in SimpleJpaRepository

---

7 votes, 6 watchers

[spring-projects-issues]

Oliver Drotbohm commented

The method signatures imply that you might want to use a Specification with different sets of QueryHint instances. Is that correct or isn't there a stringer relationship between the spec and the hints applied to it? If so we could think about supporting @QueryHints annotations on a Specification implementation and applying those to the query by collecting all annotations on the potentially combined specifications. Does the order of the hints actually matter?

[spring-projects-issues]

Anssi Törmä commented

Yes, the idea is to use a Specification with different query hints. I have a service object that depends on a JpaSpecificationExecutor and builds Specification instances for various use cases. The use cases require their own sets of query hints. What the hints mainly do is define batching or join fetching of associations of the main objects returned by the JpaSpecificationExecutor, depending on the anticipated use of the returned instances in the gui, access control checks required after the query, etc. So what I meant by "query performance" is not the specification query itself but what happens after it.

I'm not entirely sure if a @QueryHints annotation on Specification implementation would do the trick. Putting the annotations on the simple, predefined Specifications could lead to undesired results when they are combined or when using a Specification to filter out entities (Specifications.not(Specification<T>). Writing complex, predefined Specifications just to be able to put annotations on them seems to be against the purpose of the Specification pattern. But, to be honest, your suggestion could probably serve the 80%. Also, if SimpleJpaRepository were more amenable to extension, I could still have my own QueryHintAwareJpaSpecificationExecutor for the 20% without having to create it from scratch.

Java EE 6 API for Query.setHint(String, Object does not specify whether the order in which the method is a called is important. So I would guess that the order doesn't matter

[spring-projects-issues]

Matt Jensen commented

Especially in light of JPA 2.1 fetch/load graphs, this would be very useful in tuning Specification queries to the data needs of specific use cases. I am currently using a clunky solution which involved introducing a sub-interface of JpaSpecificationExecutor with findXxx() methods accepting a map of query hints, then implementing that interface via a subclass of SimpleJpaRepository which uses a ThreadLocal to pass hints around out of band. Obviously not an ideal solution.

I would be willing to take a stab at implementing this if it is really as simple as it appears to be on its face: just introduce the appropriate find method overloads on JpaSpecificationExecutor, then pass hints through the call chain within SimpleJpaRepository. The only tricky part would be merging passed-in hints with those pulled from method signatures and so forth. Which would take precedence if the same hint comes in from two sources?

[G10-Patel]

Any ETA to resolve this issue? I am still not able to add Sql Hint to JpaSpecificationExecutor

[ujhazib]

I would find this useful in a repository named query because you just can't define dynamic values for a hint currently with QueryHint (even though looks like not too much work to add something like a spel expression run on the value parameter before passing towards the processing below). I wanted to make a timeout setting today to some of my queries and I can't achieve it with QueryHint since I want to have it in a config, and I can't pass any hint object into the method as well.