sqlite应用 in iphone

时间：2010-08-30 来源：一舟

1. 首先定义define

代码 /*sqlite path*/
#define DBPATH @"kilonet.sqlite"

/*Create sqlite table*/
#define TBAccount "CREATE TABLE IF NOT EXISTS Account(ID INTEGER PRIMARY KEY, \
username TEXT, \
password TEXT, \
isSavePWD INTEGER, \
isAutoLogin INTEGER);"

2. 启动的时候创建数据库：

代码 //DB.m

@implementation DB

+ (NSString*) getDBPath
{
    NSString *dbPath = [[[NSBundle mainBundle] resourcePath]
                        stringByAppendingPathComponent:DBPATH];
    return dbPath;
}

+ (BOOL)createDB{
    NSString *dbPath = [DB getDBPath];
    NSFileManager *fm = [NSFileManager defaultManager];
    BOOL isExist = [fm fileExistsAtPath:dbPath];

    //database is exist
    if (isExist) {
        return YES;
    }

    //database is not exist, create database
    sqlite3 *mdatabase;
    if (!(sqlite3_open([dbPath UTF8String], &mdatabase) == SQLITE_OK)) {
        sqlite3_close(mdatabase);
        NSAssert(0, @"Faild to create database!");

        RELEASE_ENTITY(mdatabase);
        return NO;
    }


    //create table
    char *errmsg;
    if (sqlite3_exec(mdatabase, TBAccount, NULL, NULL, &errmsg) != SQLITE_OK){
        sqlite3_close(mdatabase);
        NSAssert1(0, @"Faild to create table -- CITY: %s", errmsg);
        sqlite3_free(errmsg);

        RELEASE_ENTITY(mdatabase);
        return NO;
    }

    RELEASE_ENTITY(mdatabase);
    return YES;
}

@end

3.数据库的CRUD操作：

代码 #import "EAccountDAO.h"

@implementation EAccountDAO

- (NSString *) add :(id) account {
    EAccount *eAccount = (EAccount*)account;

    const char * sql = "insert into Account (username,password,isSavePWD,isAutoLogin) values (?,?,?,?)";
    sqlite3_stmt *insert_statement = nil;
    NSString *theDBPath = [DB getDBPath];
    if (!(sqlite3_open([theDBPath UTF8String], &_database) == SQLITE_OK)) {
        return NSLocalizedString(@"err_opendb", @"An error opening database, normally handle error here.");
    }
    int success = sqlite3_prepare_v2(_database, sql, -1, &insert_statement, NULL);

    if(success!=SQLITE_ERROR)
    {
        sqlite3_bind_text(insert_statement, 1, [eAccount.username UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(insert_statement, 2, [eAccount.password UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_int(insert_statement, 3, eAccount.isSavePWD);
        sqlite3_bind_int(insert_statement, 4, eAccount.isAutoLogin);
        int success = sqlite3_step(insert_statement);
        if (success == SQLITE_ERROR) {
            return NSLocalizedString(@"err_add",@"failed to insert Account into the database");
        }

        NSLog(@"add database, normally handle  here.");
        sqlite3_finalize(insert_statement);
        sqlite3_close(_database);
    }
    else
    {
        return NSLocalizedString(@"err_add",@"failed to insert Account into the database");
    }
    return nil;


}
- (NSString *) update : (id) account {
    EAccount *eAccount = (EAccount *)account;

    const char * sql = "update Account set password = '?' , isSavePWD =? , isAutoLogin=? where username = '?'";
    sqlite3_stmt *update_statement = nil;

    if (!(sqlite3_open([[DB getDBPath] UTF8String], &_database) == SQLITE_OK)) {
        return NSLocalizedString(@"err_opendb", @"open database error");
    }

    int success = sqlite3_prepare_v2(_database, sql, -1, &update_statement, NULL);
    if(success==SQLITE_OK)
    {
        sqlite3_bind_text(update_statement, 1, [eAccount.password UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_int(update_statement, 2, eAccount.isSavePWD);
        sqlite3_bind_int(update_statement, 3, eAccount.isAutoLogin);
        sqlite3_bind_text(update_statement, 4, [eAccount.username UTF8String], -1, SQLITE_TRANSIENT);

        int success = sqlite3_step(update_statement);
        if (success == SQLITE_ERROR) {
            NSLocalizedString(@"err_update", @"delete error");
        }

        sqlite3_finalize(update_statement);
        sqlite3_close(_database);
    }
    else
    {
        NSLocalizedString(@"err_update", @"delete error");
    }

    return nil;
}

- (NSString *) delAccount : (id) account {
    EAccount *eAccount = (EAccount *)account;

    const char * sql = "delete from Account where username = '?'";
    sqlite3_stmt *delete_statement = nil;
    NSString *theDBPath = [DB getDBPath];

    if (!(sqlite3_open([theDBPath UTF8String], &_database) == SQLITE_OK)) {
            return NSLocalizedString(@"err_opendb", @"open database error");
    }
    int success =sqlite3_prepare_v2(_database, sql, -1, &delete_statement, NULL);
    if(success == SQLITE_OK)
    {
        sqlite3_bind_text(delete_statement, 1, [eAccount.username UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_step(delete_statement);
        sqlite3_finalize(delete_statement);
        sqlite3_close(_database);
    }
    else
    {
        NSLocalizedString(@"err_delete", @"delete error");
    }

    return nil;
}

- (NSString *) delAll {
    const char * sql = "delete from Account ";
    sqlite3_stmt *delete_statement = nil;

    if (!(sqlite3_open([[DB getDBPath] UTF8String], &_database) == SQLITE_OK)) {
        return NSLocalizedString(@"err_opendb", @"open database error");
    }
    int success =sqlite3_prepare_v2(_database, sql, -1, &delete_statement, NULL);
    if(success == SQLITE_OK)
    {
        sqlite3_step(delete_statement);
        sqlite3_finalize(delete_statement);
        sqlite3_close(_database);
    }
    else
    {
        NSLocalizedString(@"err_delete", @"delete error");
    }

    return nil;
}

- (NSMutableArray *) getAll{

    const char * sql = "SELECT * FROM Account ORDER BY id DESC";

    sqlite3_stmt *statement = nil;

    NSMutableArray * accountArray = [[NSMutableArray alloc] init];

    @try{

        if (!(sqlite3_open([[DB getDBPath] UTF8String], &_database) == SQLITE_OK)) {
            NSLog(@"Category : An error opening database, normally handle error here.");
        }

        if (sqlite3_prepare_v2(_database, sql, -1, &statement, NULL) != SQLITE_OK){
            NSLog(@"Category:Error, failed to prepare statement, normally handleerror here.");
        }
        EAccount *eAccount;
        while (sqlite3_step(statement) == SQLITE_ROW) {

            eAccount = [[EAccount alloc] init];
            //int  Id               = sqlite3_column_int(statement, 0);
            eAccount.username       = [[NSString alloc]initWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
            eAccount.password       = [[NSString alloc]initWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
            eAccount.isSavePWD      =  sqlite3_column_int(statement, 3);
            eAccount.isAutoLogin    =  sqlite3_column_int(statement, 4);

            [accountArray addObject:eAccount];
            RELEASE_ENTITY(eAccount);

        }
        if(sqlite3_finalize(statement) != SQLITE_OK){

            NSLog(@"Category:Failed to finalize data statement, normally error handling here.");
        }
        if (sqlite3_close(_database) != SQLITE_OK) {

            NSLog(@"Category:Failed to close database, normally error handling here.");
        }
    } @catch (NSException *e) {

        NSLog(@"Category:An exception occurred: %@", [e reason]);

        return nil;
    }

    return accountArray;
}

@end

4.调用DAO：

代码     EAccountDAO *dao = [[EAccountDAO alloc]  init];
    [dao delAll];

    EAccount *account = [[EAccount alloc] init];
    account.username = @"KiloNet";
    account.password = @"123";
    account.isAutoLogin = NO;
    account.isSavePWD = YES;
    NSLog(@"save account :%@",[dao add:account]);

    account.username = @"KiloNet2";
    account.password = @"456";
    account.isAutoLogin = YES;
    account.isSavePWD = YES;
    NSLog(@"save account :%@\r\n\r\n",[dao add:account]);

    NSMutableArray *list = [dao getAll];

    for (EAccount *obj in list) {

        NSLog(@"username:%@", obj.username);
         NSLog(@"password:%@\r\n\r\n", obj.password);
    }

    RELEASE_ENTITY(account);
    RELEASE_ENTITY(dao);
    RELEASE_ENTITY(list);