Can I get the SQL string from a JPA Query object?

Question

May I know how can I get the sql from a JPA query? or let's say, convert the JPA query to a SQL string? Thank you very much!

Answer 1

For Eclipselink: you can extract the SQL the following way:

query.unwrap(EJBQueryImpl.class).getDatabaseQuery().getSQLString()

It works only after the query has been executed.

Answer 2

If you only want to know how your JPQL or Criteria Query gets translated to the SQL dialect of your database you can enable fine grained logging in the persistence xml and then look into your log files.

The property name and value depends on your JPA implementation. Here is an example of the relevant part of persistence.xml for EclipseLink:

<properties>
    <property name="eclipselink.logging.level" value="FINEST"/>
</properties>

Answer 3

JPA Specification

While there is not standard JPA functionality to achieve this goal, you can still extract the SQL query from a JPQL or Criteria API Query using the JPA provider-specific API.

Hibernate Types

Starting with the 2.9.11 version, the Hibernate Types open-source project offers the SQLExtractor utility that allows you to get the SQL query from any JPQL or Criteria API query, no matter you are using Hibernate 5.4, 5.3, 5.2, 5.1, 5.0, 4.3, 4.2, or 4.1.

Get the SQL statement from a JPQL Query

Let's assume we have the following JPQL query:

Query jpql = entityManager.createQuery("""
    select 
       YEAR(p.createdOn) as year, 
       count(p) as postCount 
    from 
       Post p 
    group by 
       YEAR(p.createdOn)
    """, Tuple.class
);

With Hibernate Types, extracting the Hibernate-generated SQL query is as simple as that:

String sql = SQLExtractor.from(jpql);

And, if we log the extracted SQL query:

LOGGER.info("""
    The JPQL query: [
        {}
    ]
    generates the following SQL query: [ 
        {}
    ]
    """,
    jpql.unwrap(org.hibernate.query.Query.class).getQueryString(),
    sql
);

We get the following output:

- The JPQL query: [
    select    
        YEAR(p.createdOn) as year,    
        count(p) as postCount 
    from    
        Post p 
    group by    
        YEAR(p.createdOn)
]
generates the following SQL query: [
    SELECT 
        extract(YEAR FROM sqlextract0_.created_on) AS col_0_0_,
        count(sqlextract0_.id) AS col_1_0_
    FROM 
        post p
    GROUP BY 
        extract(YEAR FROM p.created_on)
]

Notice that we unwrapped the JPA Query to the Hibernate org.hibernate.query.Query interface which provided the getQueryString method we can use to log the associated JPQL query string.

Get the SQL statement from a JPA Criteria API Query

The SQLExtractor is not limited to JPQL queries. You can use it with Criteria API queries as well, as illustrated by the following example:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();

CriteriaQuery<PostComment> criteria = builder.createQuery(PostComment.class);

Root<PostComment> postComment = criteria.from(PostComment.class);
Join<PostComment, Post> post = postComment.join("post");

criteria.where(
    builder.like(post.get("title"), "%Java%")
);

criteria.orderBy(
    builder.asc(postComment.get("id"))
);

Query criteriaQuery = entityManager.createQuery(criteria);

String sql = SQLExtractor.from(criteriaQuery);

assertNotNull(sql);

LOGGER.info("""
    The Criteria API, compiled to this JPQL query: [
        {}
    ]
    generates the following SQL query: [
        {}
    ]
    """,
    jpql.unwrap(org.hibernate.query.Query.class).getQueryString(),
    sql
);

When running the above test case, we get the following SQL query:

- The Criteria API, compiled to this JPQL query: [
    select 
        pc 
    from 
        PostComment as pc 
    inner join 
        pc.post as p 
    where 
        p.title like :param0 
    order by 
        pc.id asc
]
generates the following SQL query: [
    SELECT 
        pc.id AS id1_1_,
        pc.post_id AS post_id3_1_,
        pc.review AS review2_1_
    FROM 
        post_comment pc
    INNER JOIN 
        post p ON pc.post_id=p.id
    WHERE 
        p.title LIKE ?
    ORDER BY 
        pc.id ASC
]

The Criteria API is first compiled to a JPQL query, as illustrated by the getQueryString() method call.

The intermediary JPQL query is further translated to an SQL query, which is properly resolved by the SQLExtractor utility.

Answer 4

Following Karol's answer - It is possible to retrieve the SQL before executing the statement in EclipseLink :

Session session = em.unwrap(JpaEntityManager.class).getActiveSession();
DatabaseQuery databaseQuery = query.unwrap(EJBQueryImpl.class).getDatabaseQuery();
databaseQuery.prepareCall(session, new DatabaseRecord());
Record r = databaseQuery.getTranslationRow();
String bound = databaseQuery.getTranslatedSQLString(session, r);
String sqlString = databaseQuery.getSQLString();

To retrieve the SQL String During/After execution it is probably best to do so using persistence properties rather than in-code :

<property name="eclipselink.logging.parameters" value="true"/>
<property name="eclipselink.logging.level" value="FINE"/>

Answer 5

Beside enabling the logging like @Matt Handy mentioned it is also possible to get the SQL String for a specific query with eclipselink at runtime as described here.

Answer 6

Using Hibernate as a provider you can enable the following properties:

hibernate.show_sql  

Write all SQL statements to console. This is an alternative to setting the log category org.hibernate.SQL to debug. (e.g. true | false)

hibernate.format_sql

Pretty print the SQL in the log and console. (e.g. true | false)

Or, as stated above you can enable logging to the debug level for the logger

org.hibernate.SQL

Log all SQL DML statements as they are executed

Answer 7

You are probably interested if there's a way to 'extract' JPQL string (either with placeholders for params, or final JPQL after params are filled-in) out of javax.persistence.Query (one of it's possible subclasses to be more precise),- in this case it's not possible according to JPA specification contract. However, this hypothetically might be possible by JPA implementation (e.g., NamedQueryImpl could have #toJPQLString(), which you could access via casting), but I doubt about that. And even if it's possible I don't think it's a good code performing such manipulations. I would suggest finding another design solutions (and for that you could specify what kind of actual problem do you have). E.g., if you are building your queries dynamically, JPA Criteria API could be used for that, and along with 'building' JPA query, you could maintain your internal data structure reflecting the logic of your query.

Answer 8

This blog contains instructions: http://narcanti.keyboardsamurais.de/hibernate-hql-to-sql-translation.html

Answer 9

You could use p6spy. At the following link there are instructions for its operation:

• https://p6spy.readthedocs.io/en/latest/install.html