使用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




    本文转自深蓝医生博客园博客,原文链接:http://www.cnblogs.com/bluedoctor/archive/2011/02/24/1963606.html,如需转载请自行联系原作者


“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页
评论
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值