之前描述了问题和想法,现在开始讨论问题的解决方法。当然,这里给出的只是我个人的想法,希望路过的朋友多提宝贵意见。
首先,再来确定一下要解决的问题:“MIS系统中,总需要进行查询,而且往往都是多表关联查询。但是,并不是所有表都需要进行Join,只有当用户选择了某一个表的字段作为条件时,才对所选择的表进行Join。要做到这一点,就需要开发人员自己利用if语句进行判断,但是这在表多时,编写代码很容易出错。所以很多情况下,开发人员都选择了一次性在SQL语句中写入所有可能用到的Join,但这又带来了性能上的问题。是否能有一种工具能将所有需要的字段、联结、分组、排序都定义好,由工具自动根据过滤条件选择所需的联结生成SQL语句?”下面,我将把我自己的解决方法告诉大家。
对象图(时间问题,为了方便理解,简单画一个放上,下次有空了我再补充)
对象的简要说明:
1、QueryInfo:查询的信息。
2、Fields:字段的集合。
3、Field:单个字段。
4、Joins:联结的集合。
5、Join:联结。
6、Filters:过滤条件的集合。
7、IFilter:过滤条件。
7、Filter:单个过滤条件。
8、Relation:关联条件。
9、FilterGroup:一组IFilter(用“()”)。
10、GroupBys:GroupBy的集合。
11、GroupBy:Group By。
12、Having:Having子句。
13、Exists:Exists子句。
14、OrderBys:OrderBy的集合。
15、OrderBy:Order By。
16、SubQuerys:子查询(Query)的集合。
17、SimpleParameterInfo:参数的简单信息。
18、QueryBuilder:查询生成器。
根据之前的叙述和问题的描述,我们可以很快确定,解决问题的先决条件是“所需的联结”。为了确定“所需的联结”我们就必须先确定“所需的表”。在SQL语句中表在哪些地方定义呢?一般都是写在From后面,仔细观察一下From后面的表名实际上完全来自查询中的字段、过滤条件、排序、分组,所使用的表,比如:select TABLE1.FIELD1, TABLE2.FIELD1 from TABLE1, TABLE2 where TABLE1.PK = TABLE2.FK and TABLE1.FIELD1 = 'VALUE'或select TABLE1.FIELD1, TABLE2.FIELD1 from TABLE1 left join TABLE2 on TABLE1.PK = TABLE2.FK where TABLE1.FIELD1 = 'VALUE'。Fields、Exists、Filters、GroupBys、Having、OrderBys部分所使用到的表都很容易确定,又因为一个查询语句里的Join只有一个主表,所以根据Join的从表就很容易确定是否应该使用了。但是,只这样还不够。因为之前的例子我们可以看到根据SITE联结时,还必须先与PROJECT建立联结,否则会出错。所以,在Joins里还维护了一个JoinRefrencesTable(表示Join互相引用情况。我偷懒,直接用Dictionary<>)。利用前面说的方法和JoinRefrencesTable就可以很容易生成SQL语句所需的Join部分了。
搞定了Join,剩下一个问题就是子查询。当Filter使用In操作付时可能使用子查询,Join的也可能用子查询,Exists子句也是个子查询。因为子查询也是一个QueryInfo,所以我采用的是利用QueryInfo.Alias引用的方法。因此,这里还需要判断一下,所使用到的子查询。最终生成SQL语句的时候,我目前是根据标记,对子查询进行替换。当然这个部分就简单多了。
差点漏掉了一点,这里还有一个问题,就是之前所说用DbParameter解决不同数据库的差异问题。这一点主要针对的是Filter,就好像Oracle和Access的日期型字段在查询时的差异,Oracle必须写成“to_date(日期, 格式)”而Access则用“#日期#”。如果用DbParameter就可以很容易解决这个问题。我所使用的方法是对Filter中除了like和子查询之外的Value1、Value2、Values,根据其DataType定义,生成DbParameter,然后再SQL语句中相应的位置生成一个标记,最后生成的时候,再进行替换。这么做的理由如下:1、对于不同的数据库该如何创建参数名我不知道,这里只能由用户自己在QueryBuilder中定义。2、OracleClient和SqlClient中的DbParameter.Name支持自定义名称。但是,不少OleDb驱动却没有这个功能,都只能用“?”来。3、因为OleDb中很多DbParameter.Name必须使用“?”,所以最终生成的时候很可能还不需对所有的DbParameters进行排序,这样才能正确生成所需的DbParameter。因为这三点,QueryInfo生成的只是原始的、带有标记的SQL语句。QueryBuilder则根据数据驱动,修改成真正可用的SQL语句(GetFixedSqlStqtement方法)。
最后,总结一下完整的生成SQL语句的流程(时间问题,就不上流程图了,用文字简单描述一下):
1、检查Fields中是否为空,不为空则生成“select 字段1,字段2,字段3……”,为空则生成“select *”。将所使用的表记录下来。如果Field.Function不为空则添加方法,方法的参数由FuncArgs定义。将FuncArgs中的“{f}”替换为Field.Name(用于实现Decode、Length之类的方法)。
2、检查Exists和Filters,不为空则生成where语句,并在生成的where语句中对参数和子查询进行标记。将参数值、所使用的表和子查询记录下来。为了方便,使用先Exists后Filters的方法。当Filter在Filters的第一位时,判断PrevLogic是否为Not,是则生成“not FIELD = VALUE”否则忽略PrevLogic。
3、检查GroupBys是否为空,不为空则生成group by部分。然后检查Having是否空,不为空则生成having子句。如果GroupBys为空则跳过Having的判 断。将所使用的表和子查询记录下来。
4、检查OrderBys是否为空,不为空则生成order by部分。将所使用的表记录下来。
5、根据所使用的表和JoinRefrencesTable确定使用的Join。将所使用的表和子查询记录下来。如果使用的Join为空,则直接生成“from TABLE1,,TABLE2”生成,否则生成“from MASTER join SLAVETABLE on MASTERTABLE.FIELD1 = SLAVETABLE.FIELD2”。
6、将所有的部分合并,将子查询标记替换为“(QueryInfo.ToSQL()) QueryInfo.Alias”。
7、最终使用时,QueryBuilder根据用户定义的参数前缀生成DbParameter,如“@”、“:”+生成自动编号或直接用参数前缀(针对OleDb数据驱动)。
以下是实际使用中的例子,下面这个是通过编程方式定义QueryInfo:
{
get
{
Join join = null ;
// 定义主查询
QueryInfo queryDefine = new QueryInfo( " SITE_FACILITY " );
// 定义字段,全部则用“*”,否则写具体的字段名
queryDefine.Fields.Add( new Field( " * " , " FACILITY " ));
// 定义Join
queryDefine.Joins.MasterTable = " FACILITY " ;
join = new Join( " SITE " , JoinTypeCode.LeftOuter);
join.Add( new Relation( " SITE " , " ID " , " PROJECT " , " SITE_GUID " ));
queryDefine.Joins.Add(join);
join = new Join( " PROJECT " , JoinTypeCode.LeftOuter);
join.Add( new Relation( " FACILITY " , " MASTER_GUID " , join.SlaveTable, " ID " ));
queryDefine.Joins.Add(join);
join = new Join( " FACILITY_CATALOG " , JoinTypeCode.LeftOuter);
join.Add( new Relation( " FACILITY " , " CATALOG_GUID " , join.SlaveTable, " ID " ));
queryDefine.Joins.Add(join);
return queryDefine;
}
}
另一种以XML定义的形式:
< Fields Distinct ="False" >
< Field Name ="*" Table ="FACILITY" ></ Field >
</ Fields >
< Joins MasterTable ="FACILITY" >
< Join JoinType ="LeftOuter" SlaveTable ="SITE" >
< Relation PrevLogic ="And" MasterTable ="SITE" MasterField ="ID" Operator ="=" SlaveTable ="PROJECT" SlaveField ="SITE_GUID" ></ Relation >
</ Join >
< Join JoinType ="LeftOuter" SlaveTable ="PROJECT" >
< Relation PrevLogic ="And" MasterTable ="FACILITY" MasterField ="MASTER_GUID" Operator ="=" SlaveTable ="PROJECT" SlaveField ="ID" ></ Relation >
</ Join >
< Join JoinType ="LeftOuter" SlaveTable ="FACILITY_CATALOG" >
< Relation PrevLogic ="And" MasterTable ="FACILITY" MasterField ="CATALOG_GUID" Operator ="=" SlaveTable ="FACILITY_CATALOG" SlaveField ="ID" ></ Relation >
</ Join >
</ Joins >
</ QueryInfo >
执行查询的代码如下:
{
TFacilityTable table = new TFacilityTable();
QueryInfo qi = QueryDefine;
//由于系统是之前完成的,前台的搜索控件并没有和QueryInfo结合,所以在qi.Filters是在最后才添加的。如果直接和表现层控件邦定则可以省掉下面for循环
for(int i = 0; i < filters.Count; i++)
{
if(filters[i].Checked)
{
Filter c = new Filter();
TccBaseFilter f = filters[i];
c.Field.Table = f.TableName;
c.Field.Name = f.FieldName;
c.Operator = ParseOpera(f.Opera);
c.Value1 = f.m_Value1;
qi.Filters.Add(c);
}
}
QueryBuilder qb = new QueryBuilder(qi);
GMIS.DataAccess.Context.DbContext dbContext = null;
if(DbContext.DbConnection is OleDbConnection)
{
//将QueryBuilder设置为针对Access数据库的
qb.AutoParameterNumber = false;
qb.ParameterPrefix = "?";
dbContext = new GMIS.DataAccess.Context.DbContext("System.Data.OleDb", DbContext.ConnectionString);
}
else
{
//将QueryBuilder设置为针对Oracle数据库的
qb.AutoParameterNumber = true;
qb.ParameterPrefix = ":";
dbContext = new GMIS.DataAccess.Context.DbContext("System.Data.OracleClient", DbContext.ConnectionString);
}
qb.FillDataTable(dbContext, table);
return table;
}
根据filters参数的不同,自动生成的SQL语句如下:
1、什么都未选择
2、选择了站点时
3、选择了站点、工程名称、设备名称、设备编目时
4、选择了站点和设备编目时
从上面的代码可以看出,定义了QueryInfo之后,不需进行判断,只需传入需要的的过滤条件、针对的数据库类型,参数的前缀,即可生成SQL语句。这从一定程度上减少了开发员进行判断生成SQL语句的麻烦也避免了一次性写入所有Join所带来的性能问题。对于实现用户自定义条件查询,也可以带来一定的便利。
目前存在的问题:
1、QueryInfo的定义代码还是有些麻烦。不过定义的代码方面可以很容易做一些简化,还可以考虑使用生成器自动生成。
2、生成SQL的代码没有进行优化,效率还没达到最优。这个可以改进,也不难。
3、还没有做到针对不同的数据库,进行一些有针对性的优化。这个问题就需要认真考虑一下了。