使用PDF.NET数据开发框架的实体操作语言OQL构造复杂查询条件

PDF.NET数据开发框架（Pwmis Data develop Framework，http://www.pwmis.com/sqlmap) 是一套借鉴iBatis、Hibernate、Linq等数据访问框架而来的轻量级数据开发框架，主要特点是具有iBatis特点的SQL-MAP功能和框架独特的实体对象查询语言--OQL，下面我们使用OQL来构造一个复杂的实体查询。

 

首先定义两个实体类：用户类和订单类，可以使用框架提供的实体类生成器生成，下面是详细代码：

 

/* 
 本类由PWMIS 实体类生成工具(Ver 4.1)自动生成
 http://www.pwmis.com/sqlmap
 使用前请先在项目工程中引用 PWMIS.Core.dll
 2010/12/8 15:01:22
*/

using System;
using PWMIS.Common;
using PWMIS.DataMap.Entity;

namespace LocalDB 
{
    /// <summary>
    /// 用户表
    /// </summary>
  [Serializable()]
  public partial class Table_User : EntityBase
  {
    public Table_User()
    {
            TableName = "Table_User";
            EntityMap=EntityMapType.SqlMap;
            //IdentityName = "标识字段名";
    IdentityName="UID";

            //PrimaryKeys.Add("主键字段名");
    PrimaryKeys.Add("UID");

            
            PropertyNames = new string[] { "UID","Name","Sex","Height","Birthday" };
            PropertyValues = new object[PropertyNames.Length]; 

    }


      /// <summary>
      /// 用户标识
      /// </summary>
      public System.Int32 UID
      {
          get{return getProperty<System.Int32>("UID");}
          set{setProperty("UID",value );}
      }

      /// <summary>
      /// 姓名
      /// </summary>
      public System.String Name
      {
          get{return getProperty<System.String>("Name");}
          set{setProperty("Name",value ,50);}
      }

      /// <summary>
      /// 性别
      /// </summary>
      public System.Boolean Sex
      {
          get{return getProperty<System.Boolean>("Sex");}
          set{setProperty("Sex",value );}
      }

      /// <summary>
      /// 身高
      /// </summary>
      public System.Double Height
      {
          get{return getProperty<System.Double>("Height");}
          set{setProperty("Height",value );}
      }

      /// <summary>
      /// 出生日期
      /// </summary>
      public System.DateTime Birthday
      {
          get{return getProperty<System.DateTime>("Birthday");}
          set{setProperty("Birthday",value );}
      }


  }

/// <summary>
/// 订单表
/// </summary>
  [Serializable()]
  public partial class Table_Order : EntityBase
  {
      public Table_Order()
      {
          TableName = "Table_Order";
          EntityMap = EntityMapType.SqlMap;
          //IdentityName = "标识字段名";
          IdentityName = "OID";

          //PrimaryKeys.Add("主键字段名");
          PrimaryKeys.Add("OID");


          PropertyNames = new string[] { "OID", "UID", "ProductName", "BuyCount", "OrderDate" };
          PropertyValues = new object[PropertyNames.Length];

      }

      /// <summary>
      /// 订单编号
      /// </summary>
      public System.Int32 OID
      {
          get { return getProperty<System.Int32>("OID"); }
          set { setProperty("OID", value); }
      }


      /// <summary>
      /// 用户号
      /// </summary>
      public System.Int32 UID
      {
          get { return getProperty<System.Int32>("UID"); }
          set { setProperty("UID", value); }
      }

      /// <summary>
      /// 产品名字
      /// </summary>
      public System.String Name
      {
          get { return getProperty<System.String>("ProductName"); }
          set { setProperty("ProductName", value, 50); }
      }

      /// <summary>
      /// 购买数量
      /// </summary>
      public System.Int32 Count
      {
          get { return getProperty<System.Int32>("BuyCount"); }
          set { setProperty("BuyCount", value); }
      }

      
      /// <summary>
      /// 购买日期
      /// </summary>
      public System.DateTime OrderDate
      {
          get { return getProperty<System.DateTime>("OrderDate"); }
          set { setProperty("OrderDate", value); }
      }


  }
}

 

然后，我们来构造两个复杂的查询条件，直接上代码：

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PWMIS.DataMap.Entity;

namespace TestSqlMapEntity
{
    class Program
    {
        static void Main(string[] args)
        {
            LocalDB.Table_User user = new LocalDB.Table_User();
            List<OQLCompare> OrCmp1 = new List<OQLCompare>();
            OQLCompare cmp = new OQLCompare(user);

            OrCmp1.Add(cmp.Comparer(user.UID, "=", 1));
            OrCmp1.Add(cmp.Comparer(user.UID, "=", 2));
            OrCmp1.Add(cmp.Comparer(user.UID, "=", 3));

            List<OQLCompare> OrCmp2 = new List<OQLCompare>();
            OrCmp2.Add(cmp.Comparer(user.Name, "=", "aaa"));
            OrCmp2.Add(cmp.Comparer(user.Name, "=", "bbb"));

            OQLCompare result = new OQLCompare(
                cmp.Comparer(OrCmp1, OQLCompare.CompareLogic.OR), 
                OQLCompare.CompareLogic.AND,
                cmp.Comparer(OrCmp2, OQLCompare.CompareLogic.OR));

            
            OQL oql = OQL.From(user).Select().Where(result).END;
            Console.WriteLine("OQL To SQL is:\r\n" + oql.ToString());
            /*
             * 
             * OQL To SQL is:
SELECT UID,Name,Sex,Height,Birthday
 FROM Table_User
   Where  ( ( UID = @CP1 Or UID = @CP2 Or UID = @CP3 )  AND  ( Name = @CP4 Or Na
me = @CP5 ) )

             */
            oql.ReSet();

            LocalDB.Table_Order order = new LocalDB.Table_Order();
            OQL q_order = OQL.From(order).Select(order.UID ).Where(
                new OQLCompare(order).Comparer(order.OrderDate, ">=", DateTime.Now.AddDays(-10))
                ).END ;

            oql.Select().Where(oql.Condition.AND(user.Sex ,"=",true).IN(user.UID, q_order));

            Console.WriteLine("OQL To SQL is:\r\n" + oql.ToString());
            /*
             * 
OQL To SQL is:
SELECT UID,Name,Sex,Height,Birthday
 FROM Table_User
   Where  Sex = @Sex0 AND UID IN (
SELECT UID
 FROM Table_Order
   Where OrderDate >= @CP1
    )

             */

            Console.Read();
        }
}
}

 

程序中有两段代码，第一段代码是为了构造一个复杂的Where条件：

Where  ( ( UID = @CP1 Or UID = @CP2 Or UID = @CP3 )  AND  ( Name = @CP4 Or Name = @CP5 ) )

意思是查询符合条件的多个UID并且查询符合条件的多个用户姓名，输出的完整SQL语句如下：

 

SELECT UID,Name,Sex,Height,Birthday
 FROM Table_User
   Where  ( ( UID = @CP1 Or UID = @CP2 Or UID = @CP3 )  AND  ( Name = @CP4 Or Name = @CP5 ) )

 

第二段代码是为了以UID为外键，从订单表中查询哪些用户在10日内提交了订单的用户信息，程序声明了两个OQL对象：

OQL oql;//用户相关的OQL对象；

OQL q_order //订单相关的OQL对象；

关键点在于q_order 对象作为oql对象的 IN 查询的参数，实现了SQL的IN子查询：

 

oql.Select().Where(oql.Condition.AND(user.Sex ,"=",true).IN(user.UID, q_order));

 

该条查询输出的完整SQL语句如下：

 

SELECT UID,Name,Sex,Height,Birthday
 FROM Table_User
   Where  Sex = @Sex0 AND UID IN (
SELECT UID
 FROM Table_Order
   Where OrderDate >= @CP1
    )

 

OK，到此为止，我们可以使用我们的OQL查询出真正的实体集合了：

 

List<LocalDB.Table_User> result=EntityQuery<LocalDB.Table_User>.Query(oql);

 

一行代码搞定，是不是很简单？

 

PDF.NET的宗旨就是为了最大化精简你的数据开发，有兴趣请看我的博客其它文章或者到官网：http://www.pwmis.com/sqlmap