How to get the JPQL/SQL String From a CriteriaQuery in JPA ?

I.T. is full of complex things that should (and sometimes could) be simple. Getting the JQPL/SQL String representation for a JPA 2.0 CriteriaQuery is one of them.

By now you all know the JPA 2.0 Criteria API : a type safe way to write a JQPL query. This API is clever in the way that you don’t use Strings to build your query, but is quite verbose… and sometimes you get lost in dozens of lines of Java code, just to write a simple query. You get lost in your CriteriaQuery, you don’t know why your query doesn’t work, and you would love to debug it. But how do you debug it ? Well, one way would be by just displaying the JPQL and/or SQL representation. Simple, isn’t it ? Yes, but JPA 2.0 javax.persistence.Query doesn’t have an API to do this. You then need to rely on the implementation… meaning, the code is different if you use EclipseLink, Hibernate or OpenJPA.

The CriteriaQuery we want to debug

Let’s say you have a simple Book entity and you want to retrieve all the books sorted by their id. Something like SELECT b FROM Book b ORDER BY b.id DESC. How would you write this with the CriteriaQuery ? Well, something like these 5 lines of Java code :

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Book> q = cb.createQuery(Book.class);
Root<Book> b = q.from(Book.class);
q.select(b).orderBy(cb.desc(b.get("id")));
TypedQuery<Book> findAllBooks = em.createQuery(q);

So imagine when you have more complex ones. Sometimes, you just get lost, it gets buggy and you would appreciate to have the JPQL and/or SQL String representation to find out what’s happening. You could then even unit test it.

Getting the JPQL/SQL String Representations for a Criteria Query

So let’s use an API to get the JPQL/SQL String representations of a CriteriaQuery (to be more precise, the TypedQuery created from a CriteriaQuery). The bad news is that there is no standard JPA 2.0 API to do this. You need to use the implementation API hoping the implementation allows it (thank god that’s (nearly) the case for the 3 main JPA ORM frameworks). The good news is that the Query interface (and therefore TypedQuery) has an unwrap method. This method returns the provider’s query API implementation. Let’s see how you can use it with EclipseLinkHibernate and OpenJPA.

EclipseLink

EclipseLink‘s Query representation is the org.eclipse.persistence.jpa.JpaQuery interface and the org.eclipse.persistence.internal.jpa.EJBQueryImpl implementation.  This interface gives you the wrapped native query (org.eclipse.persistence.queries.DatabaseQuery) with two very handy methods : getJPQLString() and getSQLString(). Unfortunatelly the getJPQLString() method will not translate a CriteriaQuery into JPQL, it only works for queries originally written in JPQL (dynamic or named query). The getSQLString() method relies on the query being “prepared”, meaning you have to run the query once before getting the SQL String representation.

findAllBooks.unwrap(JpaQuery.class).getDatabaseQuery().getJPQLString(); // doesn't work for CriteriaQuery
findAllBooks.unwrap(JpaQuery.class).getDatabaseQuery().getSQLString();

Hibernate

Hibernate‘s Query representation is org.hibernate.Query. This interface has several implementations and the very useful method that returns the SQL query string : getQueryString(). I couldn’t find a method that returns the JPQL representation, if I’ve missed something, please let me know.

findAllBooks.unwrap(org.hibernate.Query.class).getQueryString()

OpenJPA

OpenJPA‘s Query representation is org.apache.openjpa.persistence.QueryImpl and also has a getQueryString() method that returns the SQL (not the JPQL). It delegates the call to the internal org.apache.openjpa.kernel.Query interface. I couldn’t find a method that returns the JPQL representation, if I’ve missed something, please let me know.

findAllBooks.unwrap(org.apache.openjpa.persistence.QueryImpl.class).getQueryString()

Unit testing

Once you get your SQL String, why not unit test it ? Hey, but I don’t want to test my ORM, why would I do that ? Well, it happens that I’ve discovered a but in the new releases of OpenJPA by unit testing a query… so, there is a use case for that. Anyway, this is how you could do it :

assertEquals("SELECT b FROM Book b ORDER BY b.id DESC", findAllBooksCriteriaQuery.unwrap(org.apache.openjpa.persistence.QueryImpl.class).getQueryString());

Conclusion

As you can see, it’s not that simple to get a String representation for a TypedQuery. Here is a digest of the three main ORMs :

ORM Framework Query implementation How to get the JPQL String How to get the SPQL String
EclipseLink JpaQuery getDatabaseQuery().getJPQLString()* getDatabaseQuery().getSQLString()**
Hibernate Query N/A getQueryString()
OpenJPA QueryImpl getQueryString() N/A

(*) Only possible on a dynamic or named query. Not possible on a CriteriaQuery
(**) You need to execute the query first, if not, the value is null

To illustrate all that I’ve written simple test cases using EclipseLinkHibernate and OpenJPA that you can download from GitHub. Give it a try and let me know.

And what about having an API in JPA 2.1 ?

For a developers’ point of view it would be great to have two methods in the javax.persistence.Query (and therefore javax.persistence.TypedQuery) interface that would be able to easily return the JPQL and SQL String representations, e.g : Query.getJPQLString() and Query.getSQLString(). Hey, that would be the perfect time to have it in JPA 2.1 that will be shipped in less than a year. Now, as an implementer, this might be tricky to do, I would love to ear your point of view on this.

Anyway, I’m going to post an email to the JPA 2.1 Expert Group… just in case we can have this in the next version of JPA ;o)

References

Comments
7 Responses to “How to get the JPQL/SQL String From a CriteriaQuery in JPA ?”
  1. rmannibucau says:

    That’s something clearly interesting. That’s why i added it something closed in TomEE (http://rmannibucau.wordpress.com/2012/05/21/get-jpql-queries-from-criteria-api-with-openejb-and-tomee/)

    Thanks!

  2. Thanks Antonio.
    Adding those methods in JPA 2.1 would be really good.
    But I think first of all JPA expert group should revisit JPA criteria query! because as you mentioned it’s toooo verbose! And our developers are always complaining about it.
    please take a look at following links:
    http://blog.mysema.com/2010/04/querydsl-as-alternative-to-jpa-2.html
    http://www.querydsl.com/

  3. We have a similar mechanism implemented in Spring Data JPA. See the according class on Github [0]. For Hibernate we don’t just cast to the interface as it doesn’t expose the required method but cast into HibernateQuery and use the String representation returned by it as it suits our needs although strictly speaking it’s Hibernate QL not JPA QL.

    Anyway, thanks for bringing this to the table of the expert group, definitely a good idea!

    [0] https://github.com/SpringSource/spring-data-jpa/blob/master/src/main/java/org/springframework/data/jpa/repository/support/PersistenceProvider.java

  4. Andy says:

    Obviously also trivial for DataNucleus JPA, using toString() on the CriteriaQuery hence not needing to do any casting to DataNucleus classes

  5. Serge says:

    I’m not sure how it works for criteria query but for usual JPQLquery getQueryString() in hibernate return just JPQL and I can’t find a single method to return SQL, Tried with 4.1.4 version.

  6. Daniel Kaupp says:

    I achieved the same by having my DB-Log turned on and then ‘tail -f logfile’ while debugging. This may not be the nicest solution, but it works.

  7. I’ve tried your method to get SQL in OpenJPA 2.2.2:

    findAllBooks.unwrap(org.apache.openjpa.persistence.QueryImpl.class).getQueryString()

    and the result was JPQL instead of SQL.

    Thanks for your helpful post btw.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 7,476 other followers

%d bloggers like this: