5/9 调试了一下午的程序,因为 NHibernate 根据 HQL 生成的 SQL 有问题。
HQL:SELECT t FROM CUser as t
SQL:select cuser0_.GuidUser as GuidUser, cuser0_.UserID as UserID, cuser0_.UserName as UserName, cuser0_.Password as Password from CUser cuser0_
请注意 SQL 字段别名(Alias)中的 Password,在 Access 数据库中该字段与关键字冲突(其他数据库估计也冲突),使用 ADO.NET 执行将发生以下的错误:
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
我对 NHibernate 实在不熟,最初只看错误信息,实在不明白为什么会发生错误。后来配置了 log4net,在 log4net 的帮助下,找到这个错误。前后用了 1 个多小时,好累!
可是,找导致这个错误的原因,用了 3 个小时,我一下午的时光就这么浪费了,哎。同事说,至少你对 NHibernate 熟悉了一些,呵呵。
最后在 NHibernate/Mapping/Column.cs 文件中,找到了 Alias 方法(114 行)。
将长度判断的代码注释后,保证生成的 SQL 语句全部使用了特定的别名,只是,项目不允许修改 NHibernate 的代码。没办法的。
修改后生成的 SQL 语句:
select cuser0_.GuidUser as GuidUser1_, cuser0_.UserID as UserID3_, cuser0_.UserName as UserName4_, cuser0_.Password as Password2_ from CUser cuser0_
下面是 NHibernate 1.0.4 中的该方法:
![](/Images/OutliningIndicators/ContractedBlock.gif)
NHiberate 1.0.4 的代码
1![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
2
/// Gets an Alias for the column name.
3
/// </summary>
4
/// <param name="d">The <see cref="Dialect.Dialect"/> that contains the rules for Aliasing.</param>
5
/// <returns>
6
/// A string that can be used as the alias for this Column.
7
/// </returns>
8
public string Alias( Dialect.Dialect d )
9![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
10
if( quoted || name[0] == StringHelper.SingleQuote || char.IsDigit( name, 0 ) )
11![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
12
return "y" + uniqueInteger.ToString() + StringHelper.Underscore;
13
}
14![](/Images/OutliningIndicators/InBlock.gif)
15
if( name.Length < 11 )
16![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
17
return name;
18
}
19
else
20![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
21
return ( new Alias( 10, uniqueInteger.ToString() + StringHelper.Underscore ) ).ToAliasString( name, d );
22
}
23
}
NHibernate 1.2.x 中,该方法改成了:
![](/Images/OutliningIndicators/ContractedBlock.gif)
NHibernate 1.2.x 中的代码
1![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//**
2
* For any column name, generate an alias that is unique
3
* to that column name, and also 10 characters or less
4
* in length.
5
*/
6
public string GetAlias(Dialect.Dialect dialect)
7![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
8
string alias = name;
9
string unique = uniqueInteger.ToString() + '_';
10
int lastLetter = StringHelper.LastIndexOfLetter(name);
11
if( lastLetter == -1 )
12![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
13
alias = "column";
14
}
15
else if( lastLetter < name.Length-1 )
16![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
17
alias = name.Substring(0, lastLetter+1);
18
}
19
if ( alias.Length > dialect.MaxAliasLength )
20![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
21
alias = alias.Substring( 0, dialect.MaxAliasLength - unique.Length );
22
}
23
bool useRawName = name.Equals(alias) &&
24
!quoted &&
25
!StringHelper.EqualsCaseInsensitive(name, "rowid");
26![](/Images/OutliningIndicators/InBlock.gif)
27
if ( useRawName )
28![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
29
return alias;
30
}
31
else
32![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
33
return alias + unique;
34
}
35
}
我的疑问是:
1、是否可以通过修改配置文件,控制生成的 SQL 语句?增加限定符(Quote)、设置别名后缀等等?2、针对特定数据库的 Dialect 为什么在代码中写?而不通过配置文件的形式解决,还是我没有找到?3、为什么要判断列名长度小于 11。