Ios 封装sqllite3的接口

//
#import <Foundation/Foundation.h> 
#import "sqlite3.h" 
@interface Sqllib : NSObject 
{  
    sqlite3 *m_sql;  
    NSString *m_dbName;  
} 
@property(nonatomic)sqlite3*  m_sql;  
@property(nonatomic,retain)NSString*  m_dbName;
  
-(id)initWithDbName:(NSString*)dbname;  
-(BOOL)openOrCreateDatabase:(NSString*)DbName;  
-(BOOL)createTable:(NSString*)sqlCreateTable;  
-(void)closeDatabase;  
-(BOOL)InsertTable:(NSString*)sqlInsert;  
-(BOOL)UpdataTable:(NSString*)sqlUpdata;  
-(NSArray*)querryTable:(NSString*)sqlQuerry;  
-(NSArray*)querryTableByCallBack:(NSString*)sqlQuerry;  
@end
 
//
#import "sqllib.h"  
@implementation Sqllib 
 
@synthesize m_sql;  
@synthesize m_dbName;
  
-(id) initWithDbName:(NSString*)dbname  
{  
    self = [super init];  
    if (self != nil) {  
       if ([self openOrCreateDatabase:dbname]) {  
            [self closeDatabase];  
        }  
    }  
    return self;  
}  
-(id) init  
{  
    NSAssert(0,@"Never Use this.Please Call Use initWithDbName:(NSString*)");  
    return nil;  
}  
-(void) dealloc  
{  
    self.m_sql = nil;  
    self.m_dbName =nil;  
    [super dealloc];  
}  
//创建数据库  
-(BOOL)openOrCreateDatabase:(NSString*)dbName  
{  
    self.m_dbName = dbName;  
    NSArray *path =NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES);  
    NSString *documentsDirectory = [path objectAtIndex:0];  
    if(sqlite3_open([[documentsDirectorystringByAppendingPathComponent:dbName] UTF8String],&m_sql) !=SQLITE_OK)  
    {  
        NSLog(@"创建数据库失败");  
        return NO;  
    }  
    return YES;  
} 
//关闭数据库  
-(void)closeDatabase  
{  
    sqlite3_close(self.m_sql);   
} 
 
//创建表 
-(BOOL)createTable:(NSString*)sqlCreateTable  
{  
    if (![selfopenOrCreateDatabase:self.m_dbName]) {  
        return NO;  
    }  
    char *errorMsg;  
    if (sqlite3_exec (self.m_sql, [sqlCreateTable UTF8String],NULL, NULL, &errorMsg) != SQLITE_OK)  
    {  
        NSLog(@"创建数据表失败:%s",errorMsg);  
        return NO;  
    }  
    [selfcloseDatabase];  
    return YES;  
}   
//插入表   
-(BOOL)InsertTable:(NSString*)sqlInsert  
{  
    if (![selfopenOrCreateDatabase:self.m_dbName]) {  
        return NO;  
    }  
   char* errorMsg = NULL;  
    if(sqlite3_exec(self.m_sql, [sqlInsertUTF8String],0, NULL, &errorMsg) ==SQLITE_OK)  
  {  
    [selfcloseDatabase];  
    returnYES;
  }  
    else 
    {  
        printf("更新表失败:%s",errorMsg);  
        [selfcloseDatabase];  
        return NO;  
    }  
    return YES;  
}  
//更新表   
-(BOOL)UpdataTable:(NSString*)sqlUpdata
{  
 if (![selfopenOrCreateDatabase:self.m_dbName]) 
 {  
   return NO;  
 }  
 char *errorMsg;  
 if (sqlite3_exec (self.m_sql, [sqlUpdata UTF8String],0, NULL, &errorMsg) !=SQLITE_OK)  
 {  
  [selfcloseDatabase];  
  returnYES;  
 }
 else 
 {  
  returnNO;  
 }    
 return YES;  
}  
//查询表 
-(NSArray*)querryTable:(NSString*)sqlQuerry  
{  
    if (![selfopenOrCreateDatabase:self.m_dbName]) {  
        return nil;  
    }  
    int row = 0;  
    int column = 0;  
    char*    errorMsg = NULL;  
    char**    dbResult = NULL;  
    NSMutableArray*    array = [[NSMutableArrayalloc] init];  
    if(sqlite3_get_table(m_sql, [sqlQuerryUTF8String], &dbResult, &row,&column,&errorMsg ) == SQLITE_OK)  
    {  
       if (0 == row) {  
            [self closeDatabase];  
            return nil;  
        }  
        int index = column;  
        for(int i =0; i < row ; i++ ) {    
            NSMutableDictionary*    dic = [[NSMutableDictionaryalloc] init];  
            for(int j =0 ; j < column; j++ ) {  
                if (dbResult[index]) {  
                    NSString*    value = [[NSStringalloc] initWithUTF8String:dbResult[index]];  
                    NSString*    key = [[NSStringalloc] initWithUTF8String:dbResult[j]];  
                    [dic setObject:value forKey:key];  
                    [value release];  
                    [key release];  
                }  
                index ++;  
            }   
            [array addObject:dic];  
            [dic release];  
        }  
    }else {  
        printf("%s",errorMsg);  
        [selfcloseDatabase];  
        return nil;  
    }  
    [selfcloseDatabase];  
    return [array autorelease];  
}  
//select   
int processData(void* arrayResult,int columnCount,char** columnValue,char** columnName)  
{  
    int i;   
    NSMutableDictionary* dic = [[NSMutableDictionaryalloc] init];  
    for( i = 0 ; i < columnCount; i ++ )   
    {   
       if (columnValue[i]) 
       {  
            NSString* key = [[NSStringalloc] initWithUTF8String:columnName[i]];  
            NSString* value = [[NSStringalloc] initWithUTF8String:columnValue[i]];  
            [dic setObject:value forKey:key];  
        }  
    }   
    [(NSMutableArray*)arrayResult addObject:dic];  
    [dic release];  
    return 0;   
}  
//select   
-(NSArray*)querryTableByCallBack:(NSString*)sqlQuerry  
{  
    if (![selfopenOrCreateDatabase:self.m_dbName]) 
    {  
        return nil;  
    }  
    char*    errorMsg = NULL;  
    NSMutableArray* arrayResult = [[NSMutableArrayalloc] init];  
    if (sqlite3_exec(self.m_sql,[sqlQuerryUTF8String],processData,(void*)arrayResult,&errorMsg) !=SQLITE_OK) 
    {  
        printf("查询出错:%s",errorMsg);  
    }  
    [selfcloseDatabase];  
    return [arrayResult autorelease];  
} 
 
@end
 
 
//
/*IOS开发中sqlite数据库的使用方法。
*sqlite数据库初始化，复制到用户目录，并判断是否数据库已经存在，或者复制是否成功；
*在AppDelegate.m中输入以下代码，以便复制预置数据库到指定doucment目录
*/
- (BOOL) initializeDb 
{  
 NSLog (@"initializeDB");
 NSArray *searchPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);  
 NSString *documentFolderPath = [searchPaths objectAtIndex: 0];  
 //查看文件目录  
 NSLog(@"%@",documentFolderPath);  
 dbFilePath = [documentFolderPath stringByAppendingPathComponent:@"shopping.db"];
 [dbFilePath retain];    
 if (! [[NSFileManager defaultManager] fileExistsAtPath: dbFilePath]) 
 {   
  NSString *backupDbPath = [[NSBundle mainBundle] pathForResource:@"shopping" ofType:@"db"];  
  if (backupDbPath == nil) 
  {    
    return NO;  
  } 
  else 
  {  
    BOOL copiedBackupDb = [[NSFileManager defaultManager] copyItemAtPath:backupDbPath toPath:dbFilePath error:nil];  
    if (! copiedBackupDb) 
    { 
      return NO;  
    }  
  }  
 }  
 return YES;  
}  
- (void)applicationDidFinishLaunching:(UIApplication *)application 
{  
  if (! [self initializeDb]) 
  {  
   NSLog (@"couldn't init db");  
   return;  
  }      
    [window addSubview:tabBarController.view];  
}