捕获 NHibernate 生成的 SQL 并在运行前进行修改

发布于 2024-12-13 02:52:09 字数 560 浏览 4 评论 0原文

是否可以在代码中获取由 nhibernate 创建的 sql，而无需实际运行它？

我有一个通过标准 API 构建的复杂标准对象。该条件对象构成了各种选择语句的基础。然后，我可以以此为基础，添加我在整个应用程序的不同场景中所需的附加标准。

我现在需要向我的一个 select 语句添加一个having 子句，显然这不是使用 criteria api 的选项。我可以创建我需要的投影，如果我查看生成的 sql，我需要添加到现有标准底部的所有内容是...

HAVING SUM(J.HoursAssigned) <> sum(JTB.HourQty)

如此接近，但我似乎无法获得额外的一行来标记到生成的 SQL 的底部。

我在想，如果我可以提取生成的 SQL，我就可以标记我需要的having 子句，并且可以通过 Nhibernate SQLQueryCriteria 提交整个内容。

我知道这并不理想，但对我来说，这似乎比用 HQL 或 SQL 编写一个查询更好，而其余查询共享一个共同的标准基础。

这可能吗？是个好主意吗？任何替代方案也将受到欢迎。

原文

Is it possible to obtain the sql that would be created by nhibernate in your code without actually running it?

I have a complex criteria object that I have built through the criteria API. This criteria object forms the base of various select statements. I can then take this base and add on the additional criteria I require in differing scenarios throughout my application.

I now have the need to add a having clause to one of my select statements and apparently this is not an option using the criteria api. I can create the projection I require and if I view the sql generated all I need to add to the bottom of the existing criteria is...

HAVING SUM(J.HoursAssigned) <> sum(JTB.HourQty)

Its very frustrating to be so close but I cannot seem to get one extra line to tag onto the bottom of the generated SQL.

I am thinking that if I could extract the generated SQL I could then tag on the having clause that I need and I could submit the whole thing through the Nhibernate SQLQueryCriteria.

I know its not ideal but this would seem better to me than having one query written in HQL or SQL when the rest share a common criteria base.

Is this a possible and is it a good idea? Any alternatives would also be welcomed.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

筑梦 2024-12-20 02:52:09

在上面评论中提到的帖子中，我发现以下代码片段效果很好。

public static string GenerateSQL(ICriteria criteria)
    {
        NHibernate.Impl.CriteriaImpl criteriaImpl = (NHibernate.Impl.CriteriaImpl)criteria;
        NHibernate.Engine.ISessionImplementor session = criteriaImpl.Session;
        NHibernate.Engine.ISessionFactoryImplementor factory = session.Factory;

        NHibernate.Loader.Criteria.CriteriaQueryTranslator translator = 
            new NHibernate.Loader.Criteria.CriteriaQueryTranslator(
                factory, 
                criteriaImpl, 
                criteriaImpl.EntityOrClassName, 
                NHibernate.Loader.Criteria.CriteriaQueryTranslator.RootSqlAlias);

        String[] implementors = factory.GetImplementors(criteriaImpl.EntityOrClassName);

        NHibernate.Loader.Criteria.CriteriaJoinWalker walker = new NHibernate.Loader.Criteria.CriteriaJoinWalker(
            (NHibernate.Persister.Entity.IOuterJoinLoadable)factory.GetEntityPersister(implementors[0]),
                                translator,
                                factory,
                                criteriaImpl,
                                criteriaImpl.EntityOrClassName,
                                session.EnabledFilters);

        return walker.SqlString.ToString();
    }

In the post mentioned in the comment above I found the following snippet of code which works great.

public static string GenerateSQL(ICriteria criteria)
    {
        NHibernate.Impl.CriteriaImpl criteriaImpl = (NHibernate.Impl.CriteriaImpl)criteria;
        NHibernate.Engine.ISessionImplementor session = criteriaImpl.Session;
        NHibernate.Engine.ISessionFactoryImplementor factory = session.Factory;

        NHibernate.Loader.Criteria.CriteriaQueryTranslator translator = 
            new NHibernate.Loader.Criteria.CriteriaQueryTranslator(
                factory, 
                criteriaImpl, 
                criteriaImpl.EntityOrClassName, 
                NHibernate.Loader.Criteria.CriteriaQueryTranslator.RootSqlAlias);

        String[] implementors = factory.GetImplementors(criteriaImpl.EntityOrClassName);

        NHibernate.Loader.Criteria.CriteriaJoinWalker walker = new NHibernate.Loader.Criteria.CriteriaJoinWalker(
            (NHibernate.Persister.Entity.IOuterJoinLoadable)factory.GetEntityPersister(implementors[0]),
                                translator,
                                factory,
                                criteriaImpl,
                                criteriaImpl.EntityOrClassName,
                                session.EnabledFilters);

        return walker.SqlString.ToString();
    }

回复收藏 0 原文

~没有更多了~