NHibernate-如何使用参数值记录命名参数化查询? [英] NHibernate - How to log Named Parameterised Query with parameter values?
问题描述
我有一个像这样的参数化查询:
I have a parameterised named Query like this :
Query moveOutQuery = session.createSQLQuery(moveOutQueryStr.toString())
.addEntity(MyClass.class)
.setParameter("assignmentStatus", Constants.CHECKED_OUT)
我想看到带有参数的实际SQL查询.但是，在调试时，我只会得到以下查询:
I want to see the actual SQL query with parameters filled in. However while debugging I only get the following query:
Select * from my_assignment WHERE assignment_status in ( :assignmentStatus )
为什么assignmentStatus不能代替其实际值?
Why isn't the assignmentStatus being substituted for its real value?
推荐答案
为什么不使用AssignmentStatus替换其实际值?
Why isn't the assignmentStatus being substituted for its real value?
这是因为NHibernate使用查询参数来输入值.这在许多情况下非常有效，并且还有助于抵御SQL注入攻击.参数是单独发送的.如果按照以下说明记录了SQL，则可以在底部找到它们.
This is because NHibernate use query parameters to input values. This is efficient in many cases and also helpful against SQL Injection attack. Parameters are sent separately. You can find them at the bottom if SQL is logged as explained below.
您可以按照以下说明将每个SQL记录到文件中.
You may log each SQL to file as explained below.
这是通过 log4net.dll 实现的；您需要添加参考.
This is implemented through log4net.dll; you need to add reference.
添加如下名称空间:
using log4net;
using log4net.Appender;
using log4net.Core;
using log4net.Layout;
using log4net.Repository.Hierarchy;
在NHibernate中配置log4net，如下所示:
Configure log4net in NHibernate as below:
Hierarchy hierarchy = (Hierarchy)LogManager.GetRepository();
hierarchy.Root.RemoveAllAppenders();
FileAppender fileAppender = new FileAppender();
fileAppender.Name = "NHFileAppender";
fileAppender.File = logFilePath;
fileAppender.AppendToFile = true;
fileAppender.LockingModel = new FileAppender.MinimalLock();
fileAppender.Layout = new PatternLayout("%d{yyyy-MM-dd HH:mm:ss}:%m%n%n");
fileAppender.ActivateOptions();
Logger logger = hierarchy.GetLogger("NHibernate.SQL") as Logger;
logger.Additivity = false;
logger.Level = Level.Debug;
logger.AddAppender(fileAppender);
hierarchy.Configured = true;
您还需要在进行以下配置时设置ShowSql:
You also need to set ShowSql while configuration as below:
configuration.SetProperty(NHibernate.Cfg.Environment.ShowSql, "true");
configuration.SetProperty(NHibernate.Cfg.Environment.FormatSql, "true");
您需要在应用程序启动时调用一次此代码.输出日志还包括参数值.
You need to call this code once at startup of your application. Output log includes values of parameters as well.
以下是代码:
session.CreateSQLQuery("SELECT * FROM MyEntity WHERE MyProperty = :MyProperty")
.AddEntity(typeof(MyEntity))
.SetParameter("MyProperty", "filterValue")
.UniqueResult<MyEntity>();
以下是已记录的查询:
2020-01-09 14:25:39:
SELECT
*
FROM
MyEntity
WHERE
MyProperty = @p0;
@p0 = 'filterValue' [Type: String (4000:0:0)]
如您所见，参数值filterValue列在底部.
As you can see, parameter value filterValue is listed at the bottom.
这适用于所有查询API，例如IQueryOver，IQuery，ISQLQuery等.
This works for all query APIs like IQueryOver, IQuery, ISQLQuery etc.
这将记录成功和失败的语句.您可以使用 FileAppender 和 Logger 类可以满足您的其他要求.
This logs both success and failed statements. You can play with FileAppender and Logger class to meet your additional requirements.
还请参考PatternLayout ="http://logging.apache.org/log4net/release/sdk/?topic=html/T_log4net_Layout_PatternLayout.htm" rel ="nofollow noreferrer">文档.还可以在此处，此处和此处. 此问/答对此进行了讨论.
Also refer PatternLayout from documentation. More details can also be found here, here and here. This Q/A discusses the same.
遵循问题解答可能也会有所帮助:
Following Q/A may also help:
- Get executed SQL from nHibernate
- Using log4net to write to different loggers
- How to log SQL calls with NHibernate to the console of Visual Studio?
如您所见，这会将参数值记录在查询的底部.如果要将那些记录的日志嵌入查询中，请参阅此文章.
As you see, this logs the parameter values at bottom of the query. If you want those logged embedded in the query, please refer to this article.
这篇关于NHibernate-如何使用参数值记录命名参数化查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
