CODEWITHSUNDEEP
javasqlhibernatehqljpql
user2707882
user2707882

Reputation:

How to convert HQL to SQL Query programmatically (without logging)

I am executing the following HQL and it is executing properly 

String hql = "FROM Employee";
Query query = session.createQuery(hql);
List results = query.list();

Now, I also want to log the sql generated at the backend in logs for support users.

I want to make use of QueryTranslator please advise how can I generate the sql for the corresponding HQL please advise how to achieve this.

Upvotes: 18

Views: 46602

Answers (9)

WesternGun
WesternGun

Reputation: 12728

For HQL in literal:

    @Test
    public void translateHQLToSQL() {
        String hql = "SELECT m.field1, m.field2 FROM MyEntity m WHERE m.field3 in (:list) order by field1";
        QueryTranslatorFactory translatorFactory = new ASTQueryTranslatorFactory();
        SessionImplementor hibernateSession = realEntityManager.unwrap(SessionImplementor.class);
        QueryTranslator translator = translatorFactory.createQueryTranslator(hql, hql, Collections.EMPTY_MAP, hibernateSession.getFactory());
        translator.compile(Collections.EMPTY_MAP, false);
        System.out.println(translator.getSQLString());
    }

For named query:

    @Test
    public void translateNamedQueryToSQL() {
        QueryTranslatorFactory translatorFactory = new ASTQueryTranslatorFactory();
        SessionImplementor hibernateSession = realEntityManager.unwrap(SessionImplementor.class);
        Query namedQuery = hibernateSession.getNamedQuery("MyEntity.findByField3");
        namedQuery.setLockMode("foo", PESSIMISTIC_WRITE); // nor sure but any string will do
        String hql = namedQuery.getQueryString();
        System.out.println("HQL: " + hql);
        QueryTranslator translator = translatorFactory.createQueryTranslator(hql, hql, Collections.EMPTY_MAP, hibernateSession.getFactory());
        translator.compile(Collections.EMPTY_MAP, false);
        System.out.println("Translated SQL: " + translator.getSQLString());
    }

Need to do some persistent unit config in tests but this is working.

Note that if you are not using real, same database in tests, you may got slightly different SQLs. And params are never printed.

Upvotes: 0

Lovro Pandžić
Lovro Pandžić

Reputation: 6380

When upgrading to Hibernate 6 following code stopped compiling (which is suggested as fix for the issue with versions prior to 6)

public static String extractNativeSql(String hql, EntityManager em) {
    QueryTranslator queryTranslator = new ASTQueryTranslatorFactory().createQueryTranslator(
            EMPTY, hql, EMPTY_MAP, em.unwrap(SessionImplementor.class).getFactory(), null
    );
    queryTranslator.compile(EMPTY_MAP, false);
    return queryTranslator.getSQLString();
}

because those classes no longer exist.

I managed to get it working with

public static String extractNativeSql(String hql, EntityManager em) {
    var session = em.unwrap(SessionImplementor.class);
    return SQLExtractor.from(session.createQuery(hql));
}

Since I was using spring-boot-dependencies 3.1.5 which uses hibernate 6.2.13 I had to add the following dependency for SQLExtractor

<dependency>
    <groupId>io.hypersistence</groupId>
    <artifactId>hypersistence-utils-hibernate-62</artifactId>
    <version>3.6.0</version>
</dependency>

Upvotes: 0

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153760

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 (HQL) Query

Let's assume we have the following JPQL (HQL) 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 JPQL (HQL) Query to the Hibernate org.hibernate.query.Query interface which provided the getQueryString method we can use to log the associated JPQL query string.

Upvotes: 2

bharal
bharal

Reputation: 16174

This works, other answers have a problem for modern versions of hibernate:

String hqlQueryString = query.unwrap(org.hibernate.Query.class).getQueryString();
ASTQueryTranslatorFactory queryTranslatorFactory = new ASTQueryTranslatorFactory();
SessionImplementor hibernateSession = entityManager.unwrap(SessionImplementor.class);
QueryTranslator queryTranslator = queryTranslatorFactory.createQueryTranslator("", hqlQueryString, java.util.Collections.EMPTY_MAP, hibernateSession.getFactory(), null);
queryTranslator.compile(java.util.Collections.EMPTY_MAP, false);
String sqlQueryString = queryTranslator.getSQLString();

Upvotes: 2

Michael Rountree
Michael Rountree

Reputation: 325

This is also possible with TypedQuery in later versions of Hibernate using the following code

String hqlQueryString=typedQuery.unwrap(org.hibernate.query.Query.class).getQueryString();
        ASTQueryTranslatorFactory queryTranslatorFactory = new ASTQueryTranslatorFactory();
        SessionImplementor hibernateSession = entityManager.unwrap(SessionImplementor.class);
        QueryTranslator queryTranslator = queryTranslatorFactory.createQueryTranslator("", hqlQueryString, java.util.Collections.EMPTY_MAP, hibernateSession.getFactory(), null);
        queryTranslator.compile(java.util.Collections.EMPTY_MAP, false);
        String sqlQueryString = queryTranslator.getSQLString();

Upvotes: 3

CPrescott
CPrescott

Reputation: 285

I believe you want a combination of the top 2 answers 

  String hqlQueryString = query.unwrap(org.hibernate.Query.class).getQueryString();
  ASTQueryTranslatorFactory queryTranslatorFactory = new ASTQueryTranslatorFactory();
  SessionImplementor hibernateSession = em.unwrap(SessionImplementor.class);
  QueryTranslator queryTranslator = queryTranslatorFactory.createQueryTranslator("", hqlQueryString, java.util.Collections.EMPTY_MAP, hibernateSession.getFactory());
  queryTranslator.compile(java.util.Collections.EMPTY_MAP, false);
  String sqlQueryString = queryTranslator.getSQLString();

Upvotes: 6

Alex
Alex

Reputation: 2126

I found next solution on the web:

QueryTranslatorFactory translatorFactory = new ASTQueryTranslatorFactory();
SessionFactoryImplementor factory = (SessionFactoryImplementor) getSessionFactory();
QueryTranslator translator = translatorFactory.
        createQueryTranslator(hqlQueryText, hqlQueryText, Collections.EMPTY_MAP, factory);
translator.compile(Collections.EMPTY_MAP, false);
translator.getSQLString();

Source: http://narcanti.keyboardsamurais.de/hibernate-hql-to-sql-translation.html

Upvotes: 3

Abolfazl Hashemi
Abolfazl Hashemi

Reputation: 764

You can use hibernate QueryTranslator:

String hqlQueryString = hqlQuery.getQueryString();
ASTQueryTranslatorFactory queryTranslatorFactory = new ASTQueryTranslatorFactory();
SessionImplementor hibernateSession = entityManager.unwrap(SessionImplementor.class);
QueryTranslator queryTranslator = queryTranslatorFactory.createQueryTranslator("", hqlQueryString, java.util.Collections.EMPTY_MAP, hibernateSession.getFactory());
queryTranslator.compile(java.util.Collections.EMPTY_MAP, false);
String sqlQueryString = queryTranslator.getSQLString();

Upvotes: 10

Ashish Thukral
Ashish Thukral

Reputation: 1485

you can get the Query out by using the unwrap method.

String queryString = query.unwrap(org.hibernate.Query.class).getQueryString();

Upvotes: -3

Related Questions