10

I am very confused about when and when not to use native queries in JPA 2.0. I was under the impression that using native queries could cause me to get out of sync with the JPA cache. If I can accomplish the same thing with a JPQL or CriteriaBuilder query, is there any good reason to use a native query? Similarly, is there any danger in using a native query if I can accomplish the same thing with JPQL or CriteriaBuilder? And finally, if there is a danger in using a native query as far as getting out of sync with the JPA cache, would the same danger exist in executing an equivalent query with JPQL or CriteriaBuilder?

My philosophy has been do avoid native queries, but surely there are times where they are necessary. It seems to me that if I can do it with JPQL or CriteriaBuilder, then I should.

Thanks.

user1148956
  • 303
  • 1
  • 3
  • 9

1 Answers1

15

I agree with your philosophy.

The main problem with native queries, IMHO, is the maintainability. First of all, they're generally more complex and longer than JPQL queries. But they also hardcode table and column names, rather than using class and property names.

JPQL queries are already problematic when refactoring, because they hard-code class and property names in Strings. But native queries are even worse, because they hard-code table and column names everywhere.

I don't think native select queries are a problem regarding the cache. Native update, insert and delete queries are a problem, though, because they modify data behind the back of the first and second-level cache. So these might become stale.

Another problem is that your native queries could use a syntax that is recognized by one database but not by another, making the application harder to migrate from one database to another.

JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • 1
    Wonderful explanation @JB Nizet as always. The question also asks about Criteria Builder. My understanding is that Criteria queries are useful to maintain type safety (which JPQL doesn't provided as mentioned in your answer). However, I personally find Criteria API very bulky and difficult to read. Are there any other advantages of Criteria API over JPQL ?? – HopeKing May 17 '17 at 07:06
  • 2
    I also find this API very cumbersome and unintuitive to use, and harder to read. I only use this API when I need to compose a query dynamically based on... criteria (hence the name of the API). For example, when a user submits a search form with several optional criteria (name, firstname, min bith date, max birth date, whatever), various where clause elements must be added or not depending on the presence or absence of those criteria. – JB Nizet May 17 '17 at 07:31