代码�:
首先导入头文件(#import <sqlite3.h>)
#pragma mark ** 单例
+ (instancetype)shareDataBaseHandler {
// 在静态区, 只初始化一次.
static DataBaseHandler *dataBase = nil;
if (dataBase == nil) {
dataBase = [[DataBaseHandler alloc] init];
}
return dataBase;
}
#pragma mark ** 定义一个sqlite 结构体(对象)
sqlite3 *db;
#pragma mark ** 打开数据库
- (void)openDataBase {
// API: sqlite3_open.
if (db != nil) {
NSLog(@"数据库已经开启!!!");
return;
}
// 文件路径.
NSString *file = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"collect.sqlite"];
int result = sqlite3_open(file.UTF8String, &db);
if (result == SQLITE_OK) {
// NSLog(@"数据库打开成功!");
} else {
NSLog(@"数据库打开失败, code = %d", result);
}
}
#pragma mark ** 创建表格
- (void)createTable {
// API: sqlite3_exec.
// 创建SQL 语句.
NSString *createTableSQL = @"CREATE TABLE IF NOT EXISTS model(number integer PRIMARY KEY AUTOINCREMENT, name text, photo text, id text)";
int result = sqlite3_exec(db, createTableSQL.UTF8String, NULL, NULL, nil);
if (result == SQLITE_OK) {
// NSLog(@"创建表格成功");
} else {
NSLog(@"表格创建失败, code = %d", result);
}
}
#pragma mark ** 插入数据
- (void)insertModel:(ModelForTravels *)model {
// API: sqlite3_exec.
NSString *insertSQL = [NSString stringWithFormat:@"INSERT into model(name, photo, id) VALUES('%@', '%@', '%@')", model.name, model.front_cover_photo_url, model.id_count];
int result = sqlite3_exec(db, insertSQL.UTF8String, nil, nil, nil);
if (result == SQLITE_OK) {
} else {
}
}
#pragma mark ** 删除数据
- (void)deleteWithName:(NSString *)name {
// API: sqlite3_exec.
NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM model WHERE name = '%@'", name];
int result = sqlite3_exec(db, deleteSQL.UTF8String, nil, nil, nil);
if (result == SQLITE_OK) {
} else {
}
}
#pragma mark ** 查询数据
- (NSMutableArray *)selectAllData {
// 存放符合条件的数据(元素是Model 对象).
NSMutableArray *array = [NSMutableArray array];
// sql 语句.
NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM model"];
// API: stmt 结构体, sqlite3_prepareV2, sqlite3_step, sqlite3_column.
// 声明一个stmt 对象(结构体).
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
if (result == SQLITE_OK) {
// 每行都执行查询语句.
while (sqlite3_step(stmt) == SQLITE_ROW) {
// 如果查询条件匹配, 通过sqlite3_column (函数簇), 取出值.
// 取出哪一列的数据.
const unsigned char *name = sqlite3_column_text(stmt, 1);
const unsigned char *photo = sqlite3_column_text(stmt, 2);
const unsigned char *id_count = sqlite3_column_text(stmt, 3);
// 创建Model 对象, 赋值之后放到数组中.
ModelForTravels *model = [[ModelForTravels alloc] init];
model.name = [NSString stringWithUTF8String:(const char *) name];
model.front_cover_photo_url = [NSString stringWithUTF8String:(const char *) photo];
model.id_count = [NSString stringWithUTF8String:(const char *) id_count];
[array insertObject:model atIndex:0];
}
// 销毁对象.
sqlite3_finalize(stmt);
} else {
// 销毁对象.
sqlite3_finalize(stmt);
}
return array;
}
#pragma mark ** 删除表格
- (void)dropTable {
NSString *dropSQL = @"DROP TABLE model";
int result = sqlite3_exec(db, dropSQL.UTF8String, nil, nil, nil);
if (result == SQLITE_OK) {
} else {
}
}
#pragma mark ** 关闭数据库
- (void)closeDB {
// API: sqlite3_close.
int result = sqlite3_close(db);
if (result == SQLITE_OK) {
db = nil;
} else {
}
}