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
?
?
?
