// 先引入 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
