CODEWITHSUNDEEP
.netsqlnhibernate
WayneC
WayneC

Reputation: 2560

How to obtain NHibernate generated SQL in code at runtime?

I know you can view the NHibernate generated SQL by hooking it up to log4net or piping it out to the console ("show_sql" option), but is there any way to obtain the generated SQL in code at runtime?

What I would like to be able to do is take an ICriteria object (or IQuery) and dump the generated SQL to the screen or custom log (not log4net). Something like...

var sql = criteria.GetGeneratedSql() // Wishful thinking

Can something like this be done?

EDIT: Thanks to DanP's excellent find of a "Hibernate Criteria to SQL Translation" class for Java, I took a first crack at porting this to NHibernate. Seems to work for simple cases, but definitely could use some improvement (i.e. error handling, etc.)

using NHibernate.Engine;
using NHibernate.Hql.Ast.ANTLR;
using NHibernate.Impl;
using NHibernate.Loader;
using NHibernate.Loader.Criteria;
using NHibernate.Persister.Entity;

public class HibernateHqlAndCriteriaToSqlTranslator
{
    public HibernateHqlAndCriteriaToSqlTranslator() { }

    public ISessionFactory SessionFactory { get; set; }

    public string ToSql(ICriteria criteria)
    {
        var c = (CriteriaImpl) criteria;
        var s = (SessionImpl)c.Session;
        var factory = (ISessionFactoryImplementor)s.SessionFactory;
        String[] implementors = factory.GetImplementors(c.EntityOrClassName);
        var loader = new CriteriaLoader(
            (IOuterJoinLoadable)factory.GetEntityPersister(implementors[0]),
            factory, 
            c, 
            implementors[0], 
            s.EnabledFilters);

        return ((OuterJoinLoader)loader).SqlString.ToString();
    }

    public string ToSql(string hqlQueryText)
    { 
        if (!String.IsNullOrEmpty(hqlQueryText))
        {
            var translatorFactory = new ASTQueryTranslatorFactory();
            var factory = (ISessionFactoryImplementor) this.SessionFactory;
            var translator = translatorFactory.CreateQueryTranslator(
                hqlQueryText, 
                hqlQueryText, 
                new Dictionary<String, IFilter>(), 
                factory);
            translator.Compile(new Dictionary<String, String>(), false);
            return translator.SQLString;
        }

        return null;
    }
}

Upvotes: 9

Views: 6826

Answers (3)

David McClelland
David McClelland

Reputation: 2756

With NHibernate 3.2, this seems to work 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);
    }
}

Upvotes: 2

DanP
DanP

Reputation: 6478

Here is an article describing how to get the underlying sql from hql or criteria in Hibernate; I'd imagine porting this to use NHibernate wouldn't be too tricky:

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

Upvotes: 5

Philip Kelley
Philip Kelley

Reputation: 40319

In the past, I was able to view the code generated and sent to SQL by hibernate via the SQL Profiler tool. Depending on your goals, it may be able to provide what you need.

Upvotes: 0

Related Questions