iPhone/iPad sqlite3数据库操作之fmdb

首先来看一下如何使用sqlite3来实现数据库的操作，先上码吧。。。

头文件如下：

@interface DbBase :NSObject

{

    sqlite3  *database;         // database

    NSString *filePath;         // File path.

    BOOL      isOpen;           // Database is opened.

    NSString *defaultDb;       // if db is not exist, so we use the default db named db.db

}

@propertyBOOL      isOpen;    // database is opened?

@propertyNSString *filePath;  // save the database file path

// open the database and return the result of operation

// params - file : database name

// return TRUE or FALSE if database is opened.

- (BOOL) open:(NSString*) file;

// close opened database

- (void) close;                   

// execute sql commands like CRUD

// params - sql   : sql string

//          param : values

// return 

- (BOOL)execute:(NSString *)sql paramArray:(NSArray *)param;

// do some initializations when this object is created.

- (id) init;

// clear

- (void) dealloc;

@end

实现方法如下：

@implementation DbBase

@synthesize isOpen;

@synthesize filePath;

- (BOOL) open:(NSString*) file

{

    BOOL success;

   NSFileManager *fileManager = [NSFileManagerdefaultManager];

    NSError *error;

   NSArray *paths =NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES);

    NSString *documentsDirectory = [paths objectAtIndex:0];

    NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent: file];

    

   // check file is exist. create one if file is not exist.

    success = [fileManagerfileExistsAtPath: writableDBPath];

    if (!success) {

        NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] 

                                  stringByAppendingPathComponent:defaultDb];

        

        success = [fileManagercopyItemAtPath:defaultDBPathtoPath:writableDBPatherror:&error];

        if (!success) {

           NSLog(@"Failed to create writable database file with message '%@'.", 

                      [errorlocalizedDescription]);

        }

    }

    

    isOpen = FALSE;

   // open database file

     if (sqlite3_open([writableDBPath UTF8String], &database) == SQLITE_OK) {

         isOpen = TRUE;

     }else {

        NSLog(@"open database failed.");

         isOpen = FALSE;

     }

    

    return isOpen;

}

- (void) close

{

   sqlite3_close(database);

}

- (BOOL)execute:(NSString *)sql paramArray:(NSArray *)param 

{

   if (!isOpen)returnFALSE;

    sqlite3_stmt *statement = nil;

    int success = sqlite3_prepare_v2(database, [sqlUTF8String], -1, &statement,NULL);

    if (success != SQLITE_OK) {

       NSLog(@"Error: failed to prepare");

        return NO;

    }

    

    NSInteger max = [param count];

    for (int i=0; i<max; i++) {

        NSString *temp = [param objectAtIndex:i];

        sqlite3_bind_text(statement, i+1, [tempUTF8String], -1,SQLITE_TRANSIENT);

    }

    success = sqlite3_step(statement);

    sqlite3_finalize(statement);

    if (success == SQLITE_ERROR) {

       NSLog(@"Error: failed to insert into the database");

        return NO;

    }

   returnTRUE;

}

- (id) init

{

    self = [super init];

   defaultDb =@"db.db";

    

   returnself;

}

- (void) dealloc

{

    [selfclose];

    isOpen = FALSE;

}

@end

基本功能也可以实现，在网上发现一个对sqlite3数据库操作的一个封装类（fmdb），看起来比较优雅，拿来研究一下。首先从这个网址：https://github.com/ccgus/fmdb下载fmdb的源码，将Src文件夹下的文件复制到您当前工程文件目录下，然后将代码导入到您的工程中（除了fmdb.m），编译一下，如果有错误，可能是您未导入slite3的链接库，打开Build Phases，如将libsqlite3.dylib添加进来，然后重新编译。

使用fmdb操作sqlite3数据库将变的十分简单，下面我们来看一下对一个用户数据表的CRUD操作：

     首先定义用户对象，我在这里只简单的定义三个属性信息

     

@interface User :NSObject<NSCopying>

@propertyNSInteger  userId;

@propertyNSString * userName;

@propertyNSString * password;

@end

      下面是其m文件下的实现代码

@implementation User

@synthesize userId;

@synthesize userName;

@synthesize password;

- (id) init

{

    self = [super init];

    

   return self;

}

-(id) copyWithZone: (NSZone *) zone

{

    User * user = [[User allocWithZone: zone] init];

    if (user) {

        [user setUserId:userId];

        [user setUserName:[userName copy]];

        [user setPassword:[password copy]];

    }

   

    return user;

}

- (NSString*) description

{

   return [NSStringstringWithFormat:@"userId:%d userName:%@ password:%@", 

           userId,userName,password];

}

@end

再来看一下对用户的数据库UserDbService的定义及实现，

@interface UserDbService :NSObject

// properties define

@propertyFMDatabase * db;

// properties end

// methods

// get one user by it's id

- (BOOL) getUserById:(NSInteger)userId result:(User*)user;

// insert one user into the database

- (BOOL) saveUser:(User*) user;

// update user

- (BOOL) updateUser:(User*)user;

// delete user

- (BOOL) deleteUser:(User*)user;

// check user

- (BOOL) userIsExist:(NSString*)userName;

// user login

- (BOOL) userLogin:(User*)user;

// get one user from a set

- (BOOL) getFromTable:(FMResultSet*)rs result:(User*)user;

// get all users

- (void) getAll:(NSMutableArray*)userArr;

@end

@implementation UserDbService

@synthesize db;

-(BOOL) getUserById:(NSInteger)userId result:(User*)user

{

   NSAssert(db,@"database is not opened.");

    if (!user) return FALSE;

    

    if (db) {

       NSString * sql = [NSStringstringWithFormat:@"select * from t_user where userId = %d", userId];

        FMResultSet * rs = [db executeQuery: sql];

        while ([rs next]) {

            if ([self getFromTable:rs result:user]) 

                return TRUE;

        }

    }

    

   returnFALSE;

}

- (BOOL) saveUser:(User*)user

{

    if (!db) {

       returnFALSE;

    }

    

   NSString * sql = [NSStringstringWithFormat:@"insert into t_user(userName, password) values(?,?)"];

    NSArray  * params = [NSArray arrayWithObjects: 

                         user.userName,

                         user.password, 

                        nil];

   return [dbexecuteUpdate:sqlwithArgumentsInArray:params];

}

- (BOOL) updateUser:(User*)user

{

    if (!db) {

       returnFALSE;

    }

    

   NSString * sql = [NSStringstringWithFormat:@"update t_user(userName, password) values(?,?) where userId=?"];

    NSArray  * params = [NSArray arrayWithObjects: 

                         user.userName,

                         user.password,

                         [NSNumbernumberWithInt:user.userId],

                        nil];

   return [dbexecuteUpdate:sqlwithArgumentsInArray:params];

}

- (BOOL) deleteUser:(User*)user

{

   if (!db)returnFALSE;

    

   NSString * sql = [NSStringstringWithFormat:@"delete from t_user where userId = %d", user.userId];

    return [db executeUpdate:sql];

}

- (BOOL) userIsExist:(NSString*)userName

{

    if (!db) {

       returnFALSE;

    }

    

   NSString * sql = [NSStringstringWithFormat:@"select count(*) from t_user where userName='%@')", userName];

    FMResultSet *rs = [db executeQuery: sql];

    return [rs columnCount] > 0 ? TRUE : FALSE;

}

- (BOOL) userLogin:(User*)user

{

   if (!db)returnFALSE;

    

   NSString * sql = [NSStringstringWithFormat:@"select count(*) from t_user where userName=? password=?)"];

    NSArray * params = [NSArray arrayWithObjects:

                        user.userName,

                        user.password,

                       nil];

   FMResultSet *rs = [dbexecuteQuery:sqlwithArgumentsInArray:params];

    while ([rs next]) {

        [selfgetFromTable:rsresult:user];

    }

    

    if (user.userId > 0) {

        return  TRUE;

    }

    

   returnFALSE;

}

- (void) getAll:(NSMutableArray*)userArr

{

    if (!db) return;

    

   NSString * sql =@"select * from t_user order by userName asc";

    FMResultSet *rs = [db executeQuery:sql];

    while ([rs next]) {

        User *tmp = [[User alloc] init];

        [selfgetFromTable:rsresult:tmp];

        [userArr addObject:tmp];

    }

}

- (BOOL) getFromTable:(FMResultSet*)rs result:(User*)user

{

    if (!rs || !user) {

       returnFALSE;

    }

    

    [usersetUserId:[rsintForColumn:@"userId"]];

    [usersetUserName:[rsstringForColumn:@"userName"]];

    [usersetPassword:[rsstringForColumn:@"password"]];

    

   returnTRUE;

}

@end

假如在您的目录下有db.db文件，测试代码如下

- (IBAction)onTest:(id)sender 

{

   NSString *dbPath =@"/Users/wyffywwyf/Desktop/db.db";

    

   FMDatabase *db = [FMDatabasedatabaseWithPath:dbPath];

    if (db == nil || ![dbopen]) {

       NSLog(@"open database failed.");

    }else {

       NSLog(@"open database successed.");

    }

    

    UserDbService *userService = [[UserDbServicealloc]init];

    [userService setDb:db];

    

   NSMutableArray *arr = [NSMutableArrayarray];

    [userService getAll:arr];

    for (User * u in arr) {

        NSLog(@"%@", u);

    }

    

    [db close];

}

非常的简单，希望对您有所帮助。