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 = ;
return filePath;
}
- (void)createTable:(NSString *)sql {
sqlite3 *sqlite = nil;
//打开数据库
if (sqlite3_open(, &sqlite) != SQLITE_OK) {
NSLog(@"打开数据库失败");
sqlite3_close(sqlite);
return;
}
//执行创建表SQL语句
char *errmsg = nil;
if (sqlite3_exec(sqlite, , 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(, &sqlite) != SQLITE_OK) {
NSLog(@"打开数据库失败");
sqlite3_close(sqlite);
return NO;
}
//编译SQL语句
if (sqlite3_prepare_v2(sqlite, , -1, &stmt, NULL) != SQLITE_OK) {
NSLog(@"SQL语句编译失败");
sqlite3_close(sqlite);
return NO;
}
//绑定数据
for (int i=0; i<params.count; i++) {
NSString *value = ;
sqlite3_bind_text(stmt, i+1, , -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(, &sqlite) != SQLITE_OK) {
NSLog(@"打开数据库失败");
sqlite3_close(sqlite);
return NO;
}
//编译SQL语句
if (sqlite3_prepare_v2(sqlite, , -1, &stmt, NULL) != SQLITE_OK) {
NSLog(@"SQL语句编译失败");
sqlite3_close(sqlite);
return NO;
}
//查询数据
int result = sqlite3_step(stmt);
NSMutableArray *data = ;
while (result == SQLITE_ROW) {
NSMutableArray *row = ;
for (int i=0; i<number; i++) {
char *columnText = (char *)sqlite3_column_text(stmt, i);
NSString *string = ;
;
}
;
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 = [[ alloc] init];
}
return instnce;
}
- (void)createTable {
NSString *sql = @"CREATE TABLE IF NOT EXISTS User (username TEXT primary key,password TEXT,age TEXT);";
;
}
- (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 ;
}
- (NSArray *)findUsers {
NSString *sql = @"SELECT username,password,age FROM User";
NSArray *data = ;
NSMutableArray *users = ;
for (NSArray *row in data) {
NSString *username = ;
NSString *password = ;
NSString *age = ;
UserModel *user = [ init];
user.username = username;
user.password = password;
user.age = age;
;
;
}
return users;
}
@end
页:
[1]