如何获得code NHibernate的生成的SQL在运行时？ [英] How to obtain NHibernate generated SQL in code at runtime?

问题描述

我知道你可以查看NHibernate的挂钩它到log4net的或管道出来到控制台（show_sql选项）生成的SQL，但有什么办法获得code生成的SQL在运行时？

我希望能够做的就是采取的ICriteria对象（或IQUERY）和dump生成的SQL到屏幕或自定义日志（不log4net的）。喜欢的东西...

  VAR SQL = criteria.GetGeneratedSql（）//一厢情愿
 

可以像这样做？

---

编辑：多亏了的 DanP 的优良找到= http://narcanti.keyboardsamurais.de/hibernate-criteria-to-sql-translation.html">"Hibernate标准为SQL翻译类Java的，我拍了第一条裂缝在这个移植到NHibernate的。似乎简单的情况下工作，但肯定可以使用一些改进（即错误处理，等等。）

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

公共类HibernateHqlAndCriteriaToSqlTranslator
{
    公共HibernateHqlAndCriteriaToSqlTranslator（）{}

    公共ISessionFactory SessionFactory的{获得;组; }

    公共字符串ToSql（的ICriteria标准）
    {
        变种C =（CriteriaImpl）标准;
        VAR S =（SessionImpl）c.Session;
        VAR厂=（ISessionFactoryImplementor）s.SessionFactory;
        的String []实施者= factory.GetImplementors（c.EntityOrClassName）;
        VAR装载机=新CriteriaLoader（
            （IOuterJoinLoadable）factory.GetEntityPersister（实施者[0]），
            厂，
            C，
            实施者[0]，
            s.EnabledFilters）;

        返程（（OuterJoinLoader）装载机）.SqlString.ToString（）;
    }

    公共字符串ToSql（字符串hqlQueryText）
    {
        如果（！String.IsNullOrEmpty（hqlQueryText））
        {
            VAR translatorFactory =新ASTQueryTranslatorFactory（）;
            VAR厂=（ISessionFactoryImplementor）this.SessionFactory;
            VAR翻译= translatorFactory.CreateQueryTranslator（
                hqlQueryText，
                hqlQueryText，
                新的字典＆LT;字符串的IFilter＆GT;（）
                厂）;
            translator.Compile（新字典＆LT;字符串，字符串＆GT;（），FALSE）;
            返回translator.SQLString;
        }

        返回null;
    }
}
 

解决方案

下面是一篇文章，描述如何从Hibernate的HQL或条件的基础SQL;我想像这种移植使用的NHibernate不会太棘手：

<一个href="http://narcanti.keyboardsamurais.de/hibernate-criteria-to-sql-translation.html">http://narcanti.keyboardsamurais.de/hibernate-criteria-to-sql-translation.html

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

解决方案

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

这篇关于如何获得code NHibernate的生成的SQL在运行时？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！