Introduction

In this article, I’m going to show you how you can get the auto-generated SQL query from a JPQL or JPA Criteria API entity query.

Logging SQL statements

As I explained in this article, there are many ways you could log the SQL statements generated by JPA or Hibernate.

For instance, a JDBC DataSource proxy mechanism allows you to intercept and log all executed statements. However, even so, you need to parse the log and correlate the JPA queries with their associated SQL statements.

It would be much easier if there was a tool that could extract the SQL query directly from the JPA Query instance.

Hypersistence Utils project

The Hypersistence Utils open-source project provides many add-ons for Hibernate ORM.

While most developers know it for the extra custom types, like JSON, ARRAY, Interval, IP, the Hypersistence Utils project offers many other utilities as well.

For instance, there’s a Hibernate naming strategy that allows you to map the Java camelCase entity properties to snake_case column names.

And, that’s not all. The ListResultTransformer allows you to transform the default query projection using Java 8 lambdas.

Starting with the 2.9.11 version, the Hypersistence Utils project offers the SQLExtractor utility that allows you to get the SQL query from any JPQL or Criteria API query, no matter if you are using Hibernate 5 or Hibernate 4.

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 Hypersistence Utils, 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)
]

Brilliant!

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: [
        {}
    ]
    """,
    criteriaQuery.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.

Cool, right?

I'm running an online workshop on the 20-21 and 23-24 of November about High-Performance Java Persistence.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Getting the SQL query associated with a JPQL or JPA Criteria API query is a very useful feature. And, you can use to verify the auto-generated SQL queries even without running the JPA Query.

While you could also extract the SQL query from the application log, the SQLExtractor allows you to aggregate the JPA and the SQL queries so that you can produce a log message that contains both queries. And, if you are using a slow query log mechanism, you can then match the JPQL or Criteria API query that generated a particular slow SQL query.

Insert details about how the information is going to be processed

DOWNLOAD NOW