sqlite应用 in iphone

    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 -- Account: %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);