HQL to SQL converter

Question

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

score 2 · Answer 1 · edited Dec 14 '09 at 19:26

2

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.

edited Dec 14 '09 at 19:26

mdb

52,000
11
64
62

answered Nov 20 '09 at 07:23

Johannes Rudolph

35,298
14
114
172

I am using MYSQL and NHProf. But it generate SQL which cannot be executed against the DB. INSERT INTO Table1 (Column1,Column2,Column3,Column4,Column5) VALUES (?,?,?,?,?) – ranj Nov 22 '09 at 07:17
well, this is just a parametrized query, somewhere above that must be the parameter declaration and initialization. I am pretty sure NHProf supports copy&paste SQL. Else ask here: http://groups.google.com/group/nhprof – Johannes Rudolph Nov 22 '09 at 08:12

score 1 · Answer 2 · answered Feb 26 '13 at 14:53

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>

score 0 · Answer 3 · answered Dec 22 '09 at 02:24

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);

score 0 · Answer 4 · answered May 06 '10 at 14:41

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.

Sÿl · Answer 5 · 2019-04-05T19:50:58.203

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();
}

David McClelland · Answer 6 · 2012-03-20T18:59:38.010

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);
    }
}

HQL to SQL converter

6 Answers6

Linked