OC的SQLite可参考的方法

death114

看别人的代码，觉得有些代码封装得不错，所以保留下来，希望有帮助。
操作数据库的接口部分：

#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