// 先引入 libsqlite3.0 关系型数据库

#import<Foundation/Foundation.h>

#import@interface DataBaseHelper : NSObject

// 查询 sql 语句

- (NSArray *) queryWithSql:(NSString *)sql;

// 执行处查询外的 sql 语句 （delete,update,insert into）

-(BOOL) excuteNoResultSetWithSql:(NSString *)sql operation:(NSString *) operStr;

// 为库文件命名

- (void) creatDBPathWithFileName:(NSString *)fileName;

// 单例对象

+ (DataBaseHelper *) sharedDatanaseHelper;

@end

#import<Foundation/Foundation.h>

#import "DataBaseHelper.h"

#import<sqlite3.h>

@interface DataBaseHelper ()

{

sqlite3 *sqliteDB;

}

@property (nonatomic, copy) NSString *dbPath; // 数据库文件路径

@end

@implementation DataBaseHelper

// 单列

+ (DataBaseHelper *) sharedDatanaseHelper{

static DataBaseHelper *databaseHelper = nil;

if (databaseHelper == nil) {

databaseHelper = [[DataBaseHelper alloc]init];

}

return databaseHelper;

}

// 创建数据库路径

- (void) creatDBPathWithFileName:(NSString *)fileName{

NSString *documents = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];

if (![fileName containsString:@".sqlite"]) {

fileName = [fileName stringByAppendingString:@".sqlite"];

}

self.dbPath = [documents stringByAppendingPathComponent:fileName];

}

// 创建或打开数据库

- (sqlite3 *) creatOrOpenDatabase{

int result = sqlite3_open(self.dbPath.UTF8String, &sqliteDB);

if (result == SQLITE_OK) {

NSLog(@"数据库创建成功");

return sqliteDB;

}else{

NSLog(@"数据库创建失败 ---- %d",result);

return NULL;

}

}

// 执行无返回值的操作

-(BOOL) excuteNoResultSetWithSql:(NSString *)sql operation:(NSString *) operStr{

// 打开数据库

sqlite3 *dbHandle = [self creatOrOpenDatabase];

// 执行 sql语句

int result = sqlite3_exec(dbHandle, sql.UTF8String, NULL, NULL, NULL);

// 关闭数据库

sqlite3_close(dbHandle);

// 判断 sql 语句的是否执行成功

if (result == SQLITE_OK) {

NSLog(@"%@执行语句成功",operStr);

return YES;

}else{

NSLog(@"%@执行 sql语句失败--- %d",operStr,result);

return  NO;

}

}

// 执行有返回值的 查询操作

- (NSArray *) queryWithSql:(NSString *)sql{

// 打开数据库

sqlite3 *dbHandle = [self creatOrOpenDatabase];

// 声明伴随指针

sqlite3_stmt *stament = NULL;

// 准备 sql 语句

int result = sqlite3_prepare(dbHandle, sql.UTF8String, -1, &stament, NULL);

// 初始化数组

NSMutableArray *allResultArr = [NSMutableArray array];

// 判断 sql语句是否正确

if (result == SQLITE_OK) {

// 取出每一条记录

while (sqlite3_step(stament) == SQLITE_ROW) {

// 获得列个数

int sumCol = sqlite3_column_count(stament);

// 初始化字典

NSMutableDictionary *bdDic = [NSMutableDictionary dictionary];

// 遍历每一列

for (int i = 0 ; i < sumCol; i++) {

// 获得字段类型

int typeCol = sqlite3_column_type(stament, i);

// 获得字段名

const char *nameCol = sqlite3_column_name(stament, i);

NSString *key = [NSString stringWithUTF8String:nameCol];

// 判断当前列的类型存储到字典中

switch (typeCol) {

case SQLITE_INTEGER:{

int value = sqlite3_column_int(stament, i);

[bdDic setObject:[NSNumber numberWithInt:value] forKey:key];

}

break;

case SQLITE_TEXT:{

const unsigned char *value = sqlite3_column_text(stament, i);

NSString *valueStr = [NSString stringWithUTF8String:(const char *)value];

[bdDic setObject:valueStr forKey:key];

}

break;

default:

break;

}

}

[allResultArr addObject:bdDic];

}

}else{

NSLog(@"查询有误 ----- %d",result);

}

// 释放资源，返回数组

sqlite3_finalize(stament);

sqlite3_close(dbHandle);

return allResultArr;

}

@end