用面向对象的思想简化MIS系统中的查询代码（三）——问题的解决

之前描述了问题和想法，现在开始讨论问题的解决方法。当然，这里给出的只是我个人的想法，希望路过的朋友多提宝贵意见。

      首先，再来确定一下要解决的问题：“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：

private QueryInfo QueryDefine
{
    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定义的形式：

<QueryInfo Alias="SITE_FACILITY">
    <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>

执行查询的代码如下：

public TFacilityTable Select_ByFilterCollection(TccFilterCollection filters)
{
    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、什么都未选择

select FACILITY.* from FACILITY 

2、选择了站点时

select FACILITY.* from ((FACILITY left outer join PROJECT on FACILITY.MASTER_GUID = PROJECT.ID ) left outer join SITE on SITE.ID = PROJECT.SITE_GUID ) where SITE.NAME = :p1 

3、选择了站点、工程名称、设备名称、设备编目时

select FACILITY.* from (((FACILITY left outer join PROJECT on FACILITY.MASTER_GUID = PROJECT.ID ) left outer join SITE on SITE.ID = PROJECT.SITE_GUID ) left outer join FACILITY_CATALOG on FACILITY.CATALOG_GUID = FACILITY_CATALOG.ID ) where FACILITY_CATALOG.CN_NAME = :p1 and FACILITY.CN_NAME = :p2 and PROJECT.CN_NAME = :p3 and SITE.NAME = :p4

4、选择了站点和设备编目时

select FACILITY.* from ((FACILITY left outer join PROJECT on FACILITY.MASTER_GUID = PROJECT.ID ) left outer join FACILITY_CATALOG on FACILITY.CATALOG_GUID = FACILITY_CATALOG.ID ) where FACILITY_CATALOG.CN_NAME = :p1 and PROJECT.CN_NAME = :p2 

从上面的代码可以看出，定义了QueryInfo之后，不需进行判断，只需传入需要的的过滤条件、针对的数据库类型，参数的前缀，即可生成SQL语句。这从一定程度上减少了开发员进行判断生成SQL语句的麻烦也避免了一次性写入所有Join所带来的性能问题。对于实现用户自定义条件查询，也可以带来一定的便利。
      目前存在的问题：
          1、QueryInfo的定义代码还是有些麻烦。不过定义的代码方面可以很容易做一些简化，还可以考虑使用生成器自动生成。
          2、生成SQL的代码没有进行优化，效率还没达到最优。这个可以改进，也不难。
          3、还没有做到针对不同的数据库，进行一些有针对性的优化。这个问题就需要认真考虑一下了。