11

How to get the SQL query from JPQL or JPA Criteria - Vlad Mihalcea

 3 years ago
source link: https://vladmihalcea.com/get-sql-from-jpql-or-criteria/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Last modified: Jul 12, 2020
Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

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.

Hibernate Types project

The Hibernate Types 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 Hibernate Types 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 Hibernate Types 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)
]

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 27th of August about Batch Processing Best Practices with JPA and Hibernate.

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.

Transactions and Concurrency Control eBook

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK