NHibernate代码解析 - SqlCommand - SqlBuilder Template StringTokenizer
SqlBuilder几个类用于构造SELECT、INSERT、DELETE、UPDATE的SQL语句,类图如下
![](https://www.cnblogs.com/images/cnblogs_com/riccc/nhibernate_code_sqlcommand0.gif)
1. SqlBaseBuilder
主要提供对WHERE子句生成的支持。NH可能对简单的属性生成col1=?这样的WHERE条件,也可能对CollectionType的属性生成 col1=? AND col2=?这样的组合条件(无需使用者自己一个个column循环处理,而是把CollectionType对应的columns和属性的IType传入,由SqlBaseBuilder来处理)。这些WHERE条件子句的生成处理都封装在SqlBaseBuilder中。
2. INSERT语句里面没有WHERE子句,所以SqlInsertBuilder不从SqlBaseBuilder继承。 SqlUpdateBuilder、SqlDeleteBuilder等其它几个都会有WHERE子句所以都从SqlBaseBuilder继承下来。
NH的原则是中间处理过程中的SQL语句基本都以SqlString对象表示,只在执行之前才由SqlString生成SQL语句,因此这几个 SqlBuilder类的产出就是一个SQL语法上完整的SqlString对象,因此都实现了ISqlStringBuilder接口。 WhereBuilder只是方便其它地方需要构造WHERE条件子句之用(SqlBaseBuilder是abstract类),它的产出并非一个完整的SQL语句,因此没有实现ISqlStringBuilder接口,通过WhereClause方法返回WHERE条件子句的SqlString。
3. SqlSelectBuilder、SqlSimpleSelectBuilder的区别
SqlSelectBuilder用于构造一个稍复杂的SQL语句,可能包含JOIN子句、子查询等;而SqlSimpleSelectBuilder只是用于构造简单的SQL。为什么要这样,觉得没什么必要,是NH升级扩展的产物还是作者的随意?
SqlUpdateBuilder测试代码
4. Util\StringTokenizer
作用类似string.Split()函数,进行了扩展改造,可以返回定界字符,实现了IEnumerable接口,以枚举的方式读取切分后的各个部分。因为在Template中有用到所以了解一下这个类。
5. Template
在SQL语句的列名前面添加字符串标签;把列名引用括号对``(注意不是两个单引号)替换成Dialect指定的列名引用开闭符号。
例如:
![](https://www.cnblogs.com/images/cnblogs_com/riccc/nhibernate_code_sqlcommand0.gif)
1. SqlBaseBuilder
主要提供对WHERE子句生成的支持。NH可能对简单的属性生成col1=?这样的WHERE条件,也可能对CollectionType的属性生成 col1=? AND col2=?这样的组合条件(无需使用者自己一个个column循环处理,而是把CollectionType对应的columns和属性的IType传入,由SqlBaseBuilder来处理)。这些WHERE条件子句的生成处理都封装在SqlBaseBuilder中。
2. INSERT语句里面没有WHERE子句,所以SqlInsertBuilder不从SqlBaseBuilder继承。 SqlUpdateBuilder、SqlDeleteBuilder等其它几个都会有WHERE子句所以都从SqlBaseBuilder继承下来。
NH的原则是中间处理过程中的SQL语句基本都以SqlString对象表示,只在执行之前才由SqlString生成SQL语句,因此这几个 SqlBuilder类的产出就是一个SQL语法上完整的SqlString对象,因此都实现了ISqlStringBuilder接口。 WhereBuilder只是方便其它地方需要构造WHERE条件子句之用(SqlBaseBuilder是abstract类),它的产出并非一个完整的SQL语句,因此没有实现ISqlStringBuilder接口,通过WhereClause方法返回WHERE条件子句的SqlString。
3. SqlSelectBuilder、SqlSimpleSelectBuilder的区别
SqlSelectBuilder用于构造一个稍复杂的SQL语句,可能包含JOIN子句、子查询等;而SqlSimpleSelectBuilder只是用于构造简单的SQL。为什么要这样,觉得没什么必要,是NH升级扩展的产物还是作者的随意?
SqlUpdateBuilder测试代码
{
Configuration cfg = new Configuration();
ISessionFactory factory = cfg.BuildSessionFactory();
ISessionFactoryImplementor factoryImpl = (ISessionFactoryImplementor)factory;
SqlUpdateBuilder update = new SqlUpdateBuilder(factoryImpl);
update.SetTableName("ItemTable");
//test AddColumn( string columnName, object val, ILiteralType literalType )
update.AddColumn("TestCol", "my test", (NHibernate.Type.ILiteralType)NHibernateUtil.AnsiString);
//test AddColumns( string[] columnNames, bool[] updateable, IType propertyType )
update.AddColumns(new string[] { "Col0" }, null, NHibernateUtil.AnsiString);
update.AddColumns(new string[] { "Col1" }, null, NHibernateUtil.Int32);
//test SetIdentityColumn( string[ ] columnNames, IType identityType )
update.SetIdentityColumn(new string[] { "UserID" }, NHibernateUtil.Guid);
//test AddWhereFragment( string[ ] columnNames, IType type, string op )
update.AddWhereFragment(new string[] { "CreateTime" } , NHibernateUtil.DateTime, ">");
//test ToSqlString()
//string sql = update.ToSqlString().ToString();
SqlString sqlstring = update.ToSqlString();
SqlStringFormatter formater = new SqlStringFormatter((ISqlParameterFormatter)(factoryImpl.ConnectionProvider.Driver));
sqlstring.Visit(formater);
string sql = formater.GetFormattedText();
string sqlExpected = "UPDATE ItemTable SET TestCol = 'my test', Col0 = @p0, Col1 = @p1 WHERE UserID = @p2 AND CreateTime>@p3";
Assert.AreEqual(sqlExpected, sql, "SQL String");
}
SqlSelectBuilder测试代码Configuration cfg = new Configuration();
ISessionFactory factory = cfg.BuildSessionFactory();
ISessionFactoryImplementor factoryImpl = (ISessionFactoryImplementor)factory;
SqlUpdateBuilder update = new SqlUpdateBuilder(factoryImpl);
update.SetTableName("ItemTable");
//test AddColumn( string columnName, object val, ILiteralType literalType )
update.AddColumn("TestCol", "my test", (NHibernate.Type.ILiteralType)NHibernateUtil.AnsiString);
//test AddColumns( string[] columnNames, bool[] updateable, IType propertyType )
update.AddColumns(new string[] { "Col0" }, null, NHibernateUtil.AnsiString);
update.AddColumns(new string[] { "Col1" }, null, NHibernateUtil.Int32);
//test SetIdentityColumn( string[ ] columnNames, IType identityType )
update.SetIdentityColumn(new string[] { "UserID" }, NHibernateUtil.Guid);
//test AddWhereFragment( string[ ] columnNames, IType type, string op )
update.AddWhereFragment(new string[] { "CreateTime" } , NHibernateUtil.DateTime, ">");
//test ToSqlString()
//string sql = update.ToSqlString().ToString();
SqlString sqlstring = update.ToSqlString();
SqlStringFormatter formater = new SqlStringFormatter((ISqlParameterFormatter)(factoryImpl.ConnectionProvider.Driver));
sqlstring.Visit(formater);
string sql = formater.GetFormattedText();
string sqlExpected = "UPDATE ItemTable SET TestCol = 'my test', Col0 = @p0, Col1 = @p1 WHERE UserID = @p2 AND CreateTime>@p3";
Assert.AreEqual(sqlExpected, sql, "SQL String");
}
{
Configuration cfg = new Configuration();
ISessionFactory factory = cfg.BuildSessionFactory();
ISessionFactoryImplementor factoryImpl = (ISessionFactoryImplementor)factory;
SqlSelectBuilder select = new SqlSelectBuilder(factoryImpl);
select.SetSelectClause("pi.PlantCode, pi.ItemCode, pi.SafeStock");
select.SetFromClause("PlantItem", "pi");
select.SetOuterJoins(
new SqlString(" INNER JOIN Plant p ON p.PlantCode=pi.PlantCode LEFT OUTER JOIN Item i ON i.ItemCode=pi.ItemCode "),
new SqlString(" i.ItemType='A' "));
select.SetOrderByClause("pi.PlantCode ASC, pi.ItemCode DESC");
select.SetGroupByClause("pi.PlantCode, pi.ItemCode");
select.SetWhereClause("pi", new string[] { "Status" }, NHibernateUtil.YesNo);
string sql = select.ToSqlString().ToString();
string expected = "SELECT pi.PlantCode, pi.ItemCode, pi.SafeStock FROM PlantItem pi INNER JOIN Plant p ON p.PlantCode=pi.PlantCode LEFT OUTER JOIN Item i ON i.ItemCode=pi.ItemCode WHERE i.ItemType='A' AND pi.Status = ? GROUP BY pi.PlantCode, pi.ItemCode ORDER BY pi.PlantCode ASC, pi.ItemCode DESC";
Assert.AreEqual(sqlExpected, sql, "SQL String");
}
Configuration cfg = new Configuration();
ISessionFactory factory = cfg.BuildSessionFactory();
ISessionFactoryImplementor factoryImpl = (ISessionFactoryImplementor)factory;
SqlSelectBuilder select = new SqlSelectBuilder(factoryImpl);
select.SetSelectClause("pi.PlantCode, pi.ItemCode, pi.SafeStock");
select.SetFromClause("PlantItem", "pi");
select.SetOuterJoins(
new SqlString(" INNER JOIN Plant p ON p.PlantCode=pi.PlantCode LEFT OUTER JOIN Item i ON i.ItemCode=pi.ItemCode "),
new SqlString(" i.ItemType='A' "));
select.SetOrderByClause("pi.PlantCode ASC, pi.ItemCode DESC");
select.SetGroupByClause("pi.PlantCode, pi.ItemCode");
select.SetWhereClause("pi", new string[] { "Status" }, NHibernateUtil.YesNo);
string sql = select.ToSqlString().ToString();
string expected = "SELECT pi.PlantCode, pi.ItemCode, pi.SafeStock FROM PlantItem pi INNER JOIN Plant p ON p.PlantCode=pi.PlantCode LEFT OUTER JOIN Item i ON i.ItemCode=pi.ItemCode WHERE i.ItemType='A' AND pi.Status = ? GROUP BY pi.PlantCode, pi.ItemCode ORDER BY pi.PlantCode ASC, pi.ItemCode DESC";
Assert.AreEqual(sqlExpected, sql, "SQL String");
}
4. Util\StringTokenizer
作用类似string.Split()函数,进行了扩展改造,可以返回定界字符,实现了IEnumerable接口,以枚举的方式读取切分后的各个部分。因为在Template中有用到所以了解一下这个类。
5. Template
在SQL语句的列名前面添加字符串标签;把列名引用括号对``(注意不是两个单引号)替换成Dialect指定的列名引用开闭符号。
例如:
{
Dialect dialect = new MsSql2000Dialect();
string whereFragment = "col1='v`al`1' and `c_1` = 1";
string sql = Template.RenderWhereStringTemplate(whereFragment, dialect);
//sql : $PlaceHolder$.col1='v`al`1' and $PlaceHolder$.[c_1] = 1
whereFragment = "select col1, col2 from itemtable as i where col1='v`al`1' and `c_1` = 1";
sql = Template.RenderWhereStringTemplate(whereFragment, dialect);
//sql : select $PlaceHolder$.col1, $PlaceHolder$.col2 from itemtable as i where $PlaceHolder$.col1='v`al`1' and $PlaceHolder$.[c_1] = 1
}
用途为,插入了字符串标签后,适当的位置可以将字符串标签进行替换,例如对表使用了别名等情况。Dialect dialect = new MsSql2000Dialect();
string whereFragment = "col1='v`al`1' and `c_1` = 1";
string sql = Template.RenderWhereStringTemplate(whereFragment, dialect);
//sql : $PlaceHolder$.col1='v`al`1' and $PlaceHolder$.[c_1] = 1
whereFragment = "select col1, col2 from itemtable as i where col1='v`al`1' and `c_1` = 1";
sql = Template.RenderWhereStringTemplate(whereFragment, dialect);
//sql : select $PlaceHolder$.col1, $PlaceHolder$.col2 from itemtable as i where $PlaceHolder$.col1='v`al`1' and $PlaceHolder$.[c_1] = 1
}