利用objc中的反射机制拼接Sql语句

      【Qboy】原创 2012年5月13日

　　我们知道，objc中的反射机制是很强大的，现在很多语言都已经支持反射机制，Java、.net等，。在公司利用.net的架构中就是采用.net反射机制来实现三层架构，感觉使用起来挺方便，所以就想在ObjC也有反射机制，能不能利用这个帮我们也做些事情呢？刚好在一个项目中要用到数据库，所以想到采用ObjC的反射机制，做一个在ObjC中的简单三层架构。

      我们采用的数据库是sqlite3，这是在Iphone/Ipad开发的数据库。

首先我们写好一个Sqlite3Helper类，作为操作数据库的操作类：

+(void)executeNonQuery:(NSString*)sql{
sqlite3 *db;
NSString*dbPath;
dbPath =[self getDBFilePahth];
int openResult=sqlite3_open([dbPath UTF8String],&db);
 
char*error;
if(openResult==SQLITE_OK){
NSLog(@"Open Db Success");
 
if(sqlite3_exec(db,[sql UTF8String], NULL, NULL,&error )!= SQLITE_OK)
{
NSAssert(0,@"Error: Can't Execute by Sql = %@ ", sql);
}
}
else{
NSAssert(0,@"Error:Can't Open DBFile, DbFilePath =%@ ",dbPath);
}
 
sqlite3_close(db);
}
 
+(sqlite3_stmt *)execQuery:(NSString*)sql{
 
sqlite3 *db;
NSString*dbPath;
dbPath =[self getDBFilePahth];
int openResult=sqlite3_open([dbPath UTF8String],&db);
 
sqlite3_stmt *statement = nil;
 
if(openResult==SQLITE_OK){
NSLog(@"Open Db Success");
 
if(sqlite3_prepare_v2(db,[sql UTF8String],-1,&statement, NULL)!= SQLITE_OK)
{
NSAssert(0,@"Error: Can't Execute by Sql = %@ ", sql);
}
}
else{
NSAssert(0,@"Error:Can't Open DBFile, DbFilePath =%@ ",dbPath);
}
 
sqlite3_close(db);
 
return statement;
}

 

 

+(void)executeNonQuery:(NSString*)sql{
    sqlite3 *db;
    NSString*dbPath;
    dbPath =[self getDBFilePahth];
    int openResult=sqlite3_open([dbPath UTF8String],&db);
 
    char*error;
    if(openResult==SQLITE_OK){
        NSLog(@"Open Db Success");
       
        if(sqlite3_exec(db,[sql UTF8String], NULL, NULL,&error )!= SQLITE_OK)
        {
            NSAssert(0,@"Error: Can't Execute by Sql = %@ ", sql);
        }
    }
    else{
        NSAssert(0,@"Error:Can't Open DBFile, DbFilePath =%@ ",dbPath);
    }
   
    sqlite3_close(db);
}
 
+(sqlite3_stmt *)execQuery:(NSString*)sql{
   
    sqlite3 *db;
    NSString*dbPath;
    dbPath =[self getDBFilePahth];
    int openResult=sqlite3_open([dbPath UTF8String],&db);
   
    sqlite3_stmt *statement = nil;
   
    if(openResult==SQLITE_OK){
        NSLog(@"Open Db Success");
 
         if(sqlite3_prepare_v2(db,[sql UTF8String],-1,&statement, NULL)!= SQLITE_OK)
         {
             NSAssert(0,@"Error: Can't Execute by Sql = %@ ", sql);
         }
    }
    else{
        NSAssert(0,@"Error:Can't Open DBFile, DbFilePath =%@ ",dbPath);
    }
   
    sqlite3_close(db);
   
    return statement;
}

主要有两个方法一个是+(void)executeNonQuery:(NSString*)sql用于操作数据库insert,update等，执行类语句。另外一个是+(sqlite3_stmt *)execQuery:(NSString*)sql,用于执行查询类语句，并且返回一个查询结果。

 创建一个BaseModel，作为所有Model的基类。目前在BaseModel中没有任何的方法和属性。

创建一个BaseService方法AddModel如下：

-(void)addModel:(BaseModel*)model{
    unsignedint numofvars =0;
    NSString*sql;
   
    NSString*vals=@"";
    NSString*values=@"";
   
    Ivar* ivars=class_copyIvarList([model class],&numofvars);
    int i=0;
    for(constIvar* p = ivars; p< ivars+numofvars;p++){
        Ivarconst ivar=*p;
        constchar* keytype=ivar_getTypeEncoding(ivar);
       
        NSString* keyTypeValue  =[[NSString stringWithUTF8String:keytype] stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]];
       
        NSLog(@"%i:%@",(++i),keyTypeValue);
       
        NSString* key =[NSString stringWithUTF8String:ivar_getName(ivar)];
        NSLog(@"val = %@",key);
        if([model valueForKey:key]!=nil){
            vals=[NSString stringWithFormat:@"%@,%@",vals,key];
            values=[NSString stringWithFormat:@"%@,\'%@\'",values,[model valueForKey:key]];
        }
       
    }
   
    if(![values isEqualToString:@""]){
        values =[values substringFromIndex:1];
    }
    if(![vals isEqualToString:@""]){
        vals=[vals substringFromIndex:1];
    }
    sql=[NSString stringWithFormat:@"insert into %@ (%@) values (%@)",tablename_,vals,values];
    NSLog(@"sql = %@ ",sql);
   
    [Sqlite3Helper executeNonQuery:sql];
   
    NSLog(@"Add Success");
}

在查询中主要有以下几个函数，其中-(id)convertRowToModel:(Class)cls stmt:(sqlite3_stmt *)stmt 是把查出来的一行记录转换为Model的一个实例。-(id)getModel:(NSString*)pkid returnClass:(Class)cls则是根据主键查询某个值。-(NSMutableArray*)getCollection:(NSString*)strWhere orderby:(NSString*)orderby cls:(Class)cls则是根据查询条件排序字段等返回一个Array。

-(id)convertRowToModel:(Class)cls stmt:(sqlite3_stmt *)stmt {
    id model=nil;
    model =[[cls alloc] init];
   
    int coloumncount=sqlite3_column_count(stmt);
   
    for(int i=0; i<coloumncount; i++){
        char* colname=(char*)sqlite3_column_name(stmt, i);
       
        NSString* strcolname=[NSString stringWithUTF8String:colname];
       
        char* deltyp =(char*)sqlite3_column_decltype(stmt, i);
       
        NSLog(@"chartype = %s",deltyp);
       
        int ctype=sqlite3_column_type(stmt, i);
       
        char* cvalue=(char*)sqlite3_column_text(stmt, i);
       
        if(cvalue!=NULL){
            [model setValue:[NSString stringWithUTF8String:cvalue] forKey:strcolname];
        }
    }
    return model;
}
 
-(id)getModel:(NSString*)pkid returnClass:(Class)cls{
   
    NSString*sql=[NSString stringWithFormat:@"select * from %@ where %@ = '%@'",tablename_,tablePK_,pkid];
   
    NSLog(@"GetModel Sql =%@",sql);
   
    sqlite3_stmt* stmt =[Sqlite3Helper execQuery:sql];
    id model=nil;
    if(stmt!=nil){
        if(sqlite3_step(stmt)==SQLITE_ROW){
            model =[self convertRowToModel:cls stmt:stmt];
        }
 
        sqlite3_finalize(stmt);
    }
   
    return model;
}
-(NSMutableArray*)getCollection:(NSString*)strWhere orderby:(NSString*)orderby cls:(Class)cls {
   
    NSString*sql=[NSString stringWithFormat:@"select * from %@ where 1＝1 ",tablename_];
   
    if(strWhere!=nil&&![strWhere isEqualToString:@""]){
        sql=[sql stringByAppendingFormat:@" and %@",strWhere];
    }
   
    if(orderby!=nil&&![orderby isEqualToString:@""]){
        sql =[sql stringByAppendingFormat:@" order by %@ ",orderby];
    }
   
    NSLog(@"GetModel Sql =%@",sql);
   
    sqlite3_stmt* stmt =[Sqlite3Helper execQuery:sql];
    id model=nil;
    NSMutableArray* arr=[NSMutableArray array];
    if(stmt!=nil){
        while(sqlite3_step(stmt)==SQLITE_ROW){
            model =[self convertRowToModel:cls stmt:stmt];
            [arr addObject:model];
        }
       
        sqlite3_finalize(stmt);
    }
    return arr;
}

这样，我们就把BaseService的方法实现了，还差一个Update没有实现，我想根据这个也应该很简单。由于在于实现过程需要知道表名和主键，所以在BaseService中定义了两个变量 tablename_和tablePK_。BaseService.h定义如下，特别要主要添加了objc.h和runtime.h。

#import <Foundation/Foundation.h>
#import "BaseModel.h"
#import <objc/objc.h>
#import <objc/runtime.h>
#import "Sqlite3Helper.h"
 
@interfaceBaseService:NSObject
{
    NSString* tablename_;
    NSString* tablePK_;
}
 
-(void)addModel:(BaseModel*)model;
-(void)updateModel:(BaseModel*)model;
-(bool)deleteModel:(NSString*)pkid;
-(bool)deleteAll;
//-(BaseModel*)getModel:(NSString *)pkid returnModel:(BaseModel*)model;
-(id)getModel:(NSString*)pkid returnClass:(Class)cls;
-(NSMutableArray*)getCollection:(NSString*)strWhere returnClass:(Class)cls;
-(NSMutableArray*)getCollection:(NSString*)strWhere orderby:(NSString*)orderby cls:(Class)cls;
 
-(BOOL)isExist:(NSString*)pkid;
 
-(NSMutableArray*)getCollection:(NSString*)strWhere;
-(NSMutableArray*)getAll;
-(id)getModel:(NSString*)pkid;
-(int)getCountBystrwhere:(NSString*)strwhere;
 
@end

在实现中BaseModel的子类只要实现好与数据库表中各个字段对应好关系例如其中一个BaseModel的子类如下：

@interfaceTicketResult:BaseModel
{
    NSString* sectionID;
    NSString* result;
    NSNumber* type;
}
@property(nonatomic,retain)NSString*sectionID;
@property(nonatomic,retain)NSString*result;
@property(nonatomic,retain)NSNumber*type;

@end

 其数据库表为

create table T_TicketResult(

sectionID varchar(24) primary key,

result varchar(32) not null,

int type

)

把int 转换成NSNumber主要是为了判断有没有给type赋值，或者如果转换成int类型则判断，因为int的初始值为0。

反射机制的最权威资料尽在http://developer.apple.com/library/mac/#documentation/Cocoa/Reference/ObjCRuntimeRef/Reference/reference.html#//apple_ref/c/func/class_getClassVariable

里面有很多很好的方法，这里就没有使用到了。

posted @ 2012-05-13 22:19  qboy2010  阅读(1464)  评论(0)    收藏  举报