OC的SQLite可参考的方法

看别人的代码，觉得有些代码封装得不错，所以保留下来，希望有帮助。

操作数据库的接口部分：

#import <Foundation/Foundation.h>
#import <sqlite3.h>

@interface BaseDB : NSObject
//创建表
- (void)createTable:(NSString *)sql;
/**
 * 接口描述：插入数据、删除数据、修改数据
 * 参数：  sql: SQL语句
 * 返回值：是否执行成功 *
 */
- (BOOL)dealData:(NSString *)sql paramsarray:(NSArray *)params;

/**
 *  接口描述：查询数据
 *  参数：  sql:SQL语句
 *  返回值：[
                [“字段值1”，“字段值2”，“字段值3”],
                [“字段值1”，“字段值2”，“字段值3”],
                [“字段值1”，“字段值2”，“字段值3”], 
           ]
 */
- (NSMutableArray *)selectData:(NSString *)sql columns:(int)number;
@end

 

每个接口的实现部分：

#import "BaseDB.h"
#define kFilename  @"data.sqlite"
@implementation BaseDB
- (NSString *)filePath {
    NSString *filePath = [NSHomeDirectory() stringByAppendingFormat:@"/Documents/%@",kFilename];
    return filePath;
}
- (void)createTable:(NSString *)sql {
    sqlite3 *sqlite = nil;
    //打开数据库
    if (sqlite3_open([self.filePath UTF8String], &sqlite) != SQLITE_OK) {
        NSLog(@"打开数据库失败");
        sqlite3_close(sqlite);
        return;
    }
    
    //执行创建表SQL语句
    char *errmsg = nil;
    if (sqlite3_exec(sqlite, [sql UTF8String], NULL, NULL, &errmsg) != SQLITE_OK) {
        NSLog(@"创建表失败：%s",errmsg);
        sqlite3_close(sqlite);
    }
    //关闭数据库
    sqlite3_close(sqlite);   
}

/**
 * 接口描述：插入数据、删除数据、修改数据
 * 参数：  sql: SQL语句
 * 返回值：是否执行成功 *
 */
// INSERT INTO User(username,password,email) values(?,?,?)
- (BOOL)dealData:(NSString *)sql paramsarray:(NSArray *)params {
    sqlite3 *sqlite = nil;
    sqlite3_stmt *stmt = nil;    
    //打开数据库
    if (sqlite3_open([self.filePath UTF8String], &sqlite) != SQLITE_OK) {
        NSLog(@"打开数据库失败");
        sqlite3_close(sqlite);
        return NO;
    }
    
    //编译SQL语句
    if (sqlite3_prepare_v2(sqlite, [sql UTF8String], -1, &stmt, NULL) != SQLITE_OK) {
        NSLog(@"SQL语句编译失败");
        sqlite3_close(sqlite);
        return NO;
    }
    
    //绑定数据
    for (int i=0; i<params.count; i++) {
        NSString *value = [params objectAtIndex:i];
        sqlite3_bind_text(stmt, i+1, [value UTF8String], -1, NULL);
    }

    //执行SQL语句
    if(sqlite3_step(stmt) == SQLITE_ERROR) {
        NSLog(@"SQL语句执行失败");
        sqlite3_close(sqlite);
        return NO;        
    }

    //关闭数据库
    sqlite3_finalize(stmt);
    sqlite3_close(sqlite);
    return YES;
}

/**
 *  接口描述：查询数据
 *  参数：  sql:SQL语句
 *  返回值：[
    [“字段值1”，“字段值2”，“字段值3”],
    [“字段值1”，“字段值2”，“字段值3”],
    [“字段值1”，“字段值2”，“字段值3”],
  ]
 */
//SELECT username,password,email FROM User
- (NSMutableArray *)selectData:(NSString *)sql columns:(int)number {
    sqlite3 *sqlite = nil;
    sqlite3_stmt *stmt = nil;
    
    //打开数据库
    if (sqlite3_open([self.filePath UTF8String], &sqlite) != SQLITE_OK) {
        NSLog(@"打开数据库失败");
        sqlite3_close(sqlite);
        return NO;
    }
    
    //编译SQL语句
    if (sqlite3_prepare_v2(sqlite, [sql UTF8String], -1, &stmt, NULL) != SQLITE_OK) {
        NSLog(@"SQL语句编译失败");
        sqlite3_close(sqlite);
        return NO;
    }
    
    //查询数据
    int result = sqlite3_step(stmt);
    NSMutableArray *data = [NSMutableArray array];
    while (result == SQLITE_ROW) {
        NSMutableArray *row = [NSMutableArray arrayWithCapacity:3];
        for (int i=0; i<number; i++) {
            char *columnText = (char *)sqlite3_column_text(stmt, i);
            NSString *string = [NSString stringWithCString:columnText encoding:NSUTF8StringEncoding];
            [row addObject:string];
        }
        [data addObject:row];        
        result = sqlite3_step(stmt);
    }

    //关闭数据库
    sqlite3_finalize(stmt);
    sqlite3_close(sqlite);
    return data;
}
@end 

 

 如何使用这些接口呢？

具体例子：

#import "UserDB.h"
static UserDB *instnce;
@implementation UserDB
+ (id)shareInstance {/*单例模式*/
    if (instnce == nil) {
        instnce = [[[self class] alloc] init];
    }
    return instnce;
}

- (void)createTable {
    NSString *sql = @"CREATE TABLE IF NOT EXISTS User (username TEXT primary key,password TEXT,age TEXT);";
    [self createTable:sql];
}

- (BOOL)addUser:(UserModel *)userModel {
    NSString *sql = @"INSERT OR REPLACE INTO User (username,password,age) VALUES (?,?,?)";
    NSArray *params = [NSArray arrayWithObjects:userModel.username,
                                                userModel.password,
                                                userModel.age, nil];    
    return [self dealData:sql paramsarray:params];
}

- (NSArray *)findUsers {
    NSString *sql = @"SELECT username,password,age FROM User";
    NSArray *data = [self selectData:sql columns:3];
    NSMutableArray *users = [NSMutableArray array];
    for (NSArray *row in data) {
        NSString *username = [row objectAtIndex:0];
        NSString *password = [row objectAtIndex:1];
        NSString *age = [row objectAtIndex:2];        
        UserModel *user = [[UserModel alloc] init];
        user.username = username;
        user.password = password;
        user.age = age;
        [users addObject:user];
        [user release];
    }    
    return users;
}

@end