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 DESC. How would you write this with the CriteriaQuery ? Well, something like these 5 lines of Java code :

[sourcecode language=”java” highlight=”5″]
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Book> q = cb.createQuery(Book.class);
Root<Book> b = q.from(Book.class);"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‘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.

[sourcecode language=”java”]
findAllBooks.unwrap(JpaQuery.class).getDatabaseQuery().getJPQLString(); // doesn’t work for CriteriaQuery


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.

[sourcecode language=”java”]


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.

[sourcecode language=”java”]

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 :

[sourcecode language=”java”]
assertEquals("SELECT b FROM Book b ORDER BY DESC", findAllBooksCriteriaQuery.unwrap(org.apache.openjpa.persistence.QueryImpl.class).getQueryString());


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)


10 thoughts on “How to get the JPQL/SQL String From a CriteriaQuery in JPA ?

  1. 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!


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

  3. 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.

  4. 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.

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


    and the result was JPQL instead of SQL.

    Thanks for your helpful post btw.

  6. Thanks for your post ! But what about the parameters of the query ?
    all I get is :
    SELECT foo FROM bar WHERE foo2 = ? and foo3 = ?
    How can I get the values for the first and second parameters ?

  7. No idea what a criteria query is. I see the name and I don’t care. It is one of those things that looks to make something simple way more complex and hard to manage than there is even any remote need to do.

Leave a Reply