HQL to SQL converter

Question

Does anyone know how to convert NHibernate HQL to SQL Scripts?

Answer 1

I'm not familiar with all the parameters, but this seems to work:

ISessionFactory sessionFactory = ...
var sf = (SessionFactoryImpl) sessionFactory;
var hql = "from Person";
var qt = sf.Settings.QueryTranslatorFactory.CreateQueryTranslator("", hql, new Dictionary<string, IFilter>(), (ISessionFactoryImplementor) sessionFactory);
qt.Compile(new Dictionary<string, string>(), true);
var sql = qt.SQLString;
Console.WriteLine(sql);

Answer 2

With NHibernate 3.2, this seems to be the easiest way to get the SQL from an HQL query:

private string GetSQL(string hql)
{
    using (var iSession = ...)
    {
        var session = (NHibernate.Engine.ISessionImplementor)iSession;
        var sf = (NHibernate.Engine.ISessionFactoryImplementor)iSession.SessionFactory;

        var sql = new NHibernate.Engine.Query.HQLStringQueryPlan(hql, true, session.EnabledFilters, sf);

        return string.Join(";", sql.SqlStrings);
    }
}

Answer 3

My old trainings. That was beta-version. Here it is! (hql2sql.jsp)

<SCRIPT type="text/javascript">
    <%  
        org.hibernate.Session ThisSession = SessionFactory.getSession();
        org.hibernate.engine.SessionImplementor ThisSessionImplementor = (org.hibernate.engine.SessionImplementor) ThisSession;
        org.hibernate.engine.SessionFactoryImplementor ThisSessionFactory = (org.hibernate.engine.SessionFactoryImplementor) ThisSession.getSessionFactory();
        String HQL_Query = "SELECT ... ";
        String SQL_Query;
        try{
            org.hibernate.engine.query.HQLQueryPlan HQL_Query_Plan = new org.hibernate.engine.query.HQLQueryPlan(HQL_Query, true, ThisSessionImplementor.getEnabledFilters(), ThisSessionFactory);
            SQL_Query = org.apache.commons.lang.StringUtils.join(HQL_Query_Plan.getSqlStrings(), ";");
        }catch(Exception e){SQL_Query = "ERROR!!  ::  " + e.getMessage();}
    %>
    $(document).ready(function(){
        $('span[role="HQL"]').text(" <%=HQL_Query%>");
        $('span[role="SQL"]').text(" <%=SQL_Query%>");
    });
</SCRIPT>
<div style="border:2px solid brown">
    Ваш запрос на HQL:
    <br/><br/><span role="HQL">&nbsp;</span>
</div>
<br>
<div style="border:2px solid green">
    Ваш запрос на SQL:
    <br/><br/><span role="SQL">&nbsp;</span>
</div>

Answer 4

Here is how to do it with NH 5.2 (see https://stackoverflow.com/a/55542462/2047306)

public static string HqlToSql(string hql, ISession session)
{
    var sessionImp = (ISessionImplementor)session;
    var translatorFactory = new ASTQueryTranslatorFactory();
    var translators = translatorFactory.CreateQueryTranslators(new NHibernate.Hql.StringQueryExpression(hql),
         null, false, sessionImp.EnabledFilters, sessionImp.Factory);
    var hqlSqlGenerator = new HqlSqlGenerator(((QueryTranslatorImpl)translators[0]).SqlAST, sessionImp.Factory);
    hqlSqlGenerator.Generate();
    return hqlSqlGenerator.Sql.ToString();
}

Answer 5

Since HQL translation depends on your mappings and also runtime behaviour, I think it is unlikely there is a way to do so statically.

You could run the HQL against a real database and capture the generated SQL either via a profiler for your specific rdbms or NHProf.

Answer 6

I'm not sure what the value of auto-converting HQL to SQL is dynamically...

What exactly are you trying to accomplish by this?

The easiest way would be to run your code while running SQL Server Profiler to see the generated SQL. But a better approach would be to download nhProf (www.nhprof.com) and use that with your code. You will be able to see exactly what your code is outputting in SQL and it will format and color code it and also give you tips on ways to improve your usage of nhibernate.