使用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); }
}
}
}
本类由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();
}
}
}
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 ) )
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
)
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
