#

Hibernate HQL to SQL Translation

For debugging purposes using hibernate it’s often usefull to have the generated SQL query to an appropriate HQL query.
But setting the hibernate configuration hibernate.show_sql to true leads to logging any query made within an application (which can be a lot in some applications). With the following class the hibernate API is used to generate the correct SQL query from a given HQL query and can be used anywhere to log the generated query.

Of course, for that work to be done, the class needs a reference to the SessionFactory.

import org.hibernate.SessionFactory;
import org.hibernate.engine.SessionFactoryImplementor;
import org.hibernate.hql.QueryTranslator;
import org.hibernate.hql.QueryTranslatorFactory;
import org.hibernate.hql.ast.ASTQueryTranslatorFactory;
 
public class HqlToSqlTranslator {
  private SessionFactory sessionFactory;
 
  public void setSessionFactory(SessionFactory sessionFactory){
    this.sessionFactory = sessionFactory;
  }
 
  public String toSql(String hqlQueryText){
    if (hqlQueryText!=null && hqlQueryText.trim().length()>0){
      final QueryTranslatorFactory translatorFactory = new ASTQueryTranslatorFactory();
      final SessionFactoryImplementor factory = 
        (SessionFactoryImplementor) sessionFactory;
      final QueryTranslator translator = translatorFactory.
        createQueryTranslator(
          hqlQueryText, 
          hqlQueryText, 
          Collections.EMPTY_MAP, factory
        );
      translator.compile(Collections.EMPTY_MAP, false);
      return translator.getSQLString(); 
    }
    return null;
  }
}
Tags:, , ,

2 Responses to “Hibernate HQL to SQL Translation” »»

  1. Comment by Matthias Pichler | 14:24 07.10.09|X

    Nice snippet!

    But how can you convert a javax.persistence.Query object to a sql-String?

  2. Comment by Parvez Ahmad | 16:35 26.11.10|X

    Very useful.

Leave a Reply »»

Note: All comments are manually approved to avoid spam. So if your comment doesn't appear immediately, that's ok. Have patience, it can take some days until I have the time to approve my comments.