How to obtain NHibernate generated SQL in code at runtime?

Question

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

stackoverflow.com/questions/10704462/…

Mauricio Scheffer
– Mauricio Scheffer

2013-07-01 19:56:59 +00:00
Commented Jul 1, 2013 at 19:56 — Mauricio Scheffer
– Mauricio Scheffer, Commented Jul 1, 2013 at 19:56

DanP · Accepted Answer · 2010-07-20 16:57:30Z

5

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

answered Jul 20, 2010 at 16:57

DanP

6,5184 gold badges45 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jaguar Over a year ago

great find, i've been looking for a neat solution.... now i only have to port this to NHibernate

DanP Over a year ago

@Jaguar: agreed, it's pretty slick...I may just race you to a port ;)

WayneC Over a year ago

@DanP Very cool. Can't believe something like this isn't "built in"

DanP Over a year ago

@WayneC: Well, technically it is, just not really accessable from the outside :)

WayneC Over a year ago

Took a first crack at porting this (see "edit" on the opening post)...seems to work for simple criteria and HQL.

David McClelland · Accepted Answer · 2012-03-20 18:54:45Z

2

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

edited Mar 20, 2012 at 18:54

answered Mar 20, 2012 at 18:04

David McClelland

2,7564 gold badges29 silver badges37 bronze badges

Comments

Philip Kelley · Accepted Answer · 2010-07-20 16:45:13Z

0

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.

answered Jul 20, 2010 at 16:45

Philip Kelley

40.5k11 gold badges62 silver badges93 bronze badges

Collectives™ on Stack Overflow

How to obtain NHibernate generated SQL in code at runtime?

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related