ios封装sqllite3的接口

//
#import
#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]; 
}