// 先引入 libsqlite3.0 關(guān)系型數(shù)據(jù)庫(kù)
#import<Foundation/Foundation.h>
#import@interface DataBaseHelper : NSObject
// 查詢(xún) sql 語(yǔ)句
- (NSArray *) queryWithSql:(NSString *)sql;
// 執(zhí)行處查詢(xún)外的 sql 語(yǔ)句 （delete,update,insert into）
-(BOOL) excuteNoResultSetWithSql:(NSString *)sql operation:(NSString *) operStr;
// 為庫(kù)文件命名
- (void) creatDBPathWithFileName:(NSString *)fileName;
// 單例對(duì)象
+ (DataBaseHelper *) sharedDatanaseHelper;
@end
#import<Foundation/Foundation.h>
#import "DataBaseHelper.h"
#import<sqlite3.h>
@interface DataBaseHelper ()
{
sqlite3 *sqliteDB;
}
@property (nonatomic, copy) NSString *dbPath; // 數(shù)據(jù)庫(kù)文件路徑
@end
@implementation DataBaseHelper
// 單列
+ (DataBaseHelper *) sharedDatanaseHelper{
static DataBaseHelper *databaseHelper = nil;
if (databaseHelper == nil) {
databaseHelper = [[DataBaseHelper alloc]init];
}
return databaseHelper;
}
// 創(chuàng)建數(shù)據(jù)庫(kù)路徑
- (void) creatDBPathWithFileName:(NSString *)fileName{
NSString *documents = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
if (![fileName containsString:@".sqlite"]) {
fileName = [fileName stringByAppendingString:@".sqlite"];
}
self.dbPath = [documents stringByAppendingPathComponent:fileName];
}
// 創(chuàng)建或打開(kāi)數(shù)據(jù)庫(kù)
- (sqlite3 *) creatOrOpenDatabase{
int result = sqlite3_open(self.dbPath.UTF8String, &sqliteDB);
if (result == SQLITE_OK) {
NSLog(@"數(shù)據(jù)庫(kù)創(chuàng)建成功");
return sqliteDB;
}else{
NSLog(@"數(shù)據(jù)庫(kù)創(chuàng)建失敗 ---- %d",result);
return NULL;
}
}
// 執(zhí)行無(wú)返回值的操作
-(BOOL) excuteNoResultSetWithSql:(NSString *)sql operation:(NSString *) operStr{
// 打開(kāi)數(shù)據(jù)庫(kù)
sqlite3 *dbHandle = [self creatOrOpenDatabase];
// 執(zhí)行 sql語(yǔ)句
int result = sqlite3_exec(dbHandle, sql.UTF8String, NULL, NULL, NULL);
// 關(guān)閉數(shù)據(jù)庫(kù)
sqlite3_close(dbHandle);
// 判斷 sql 語(yǔ)句的是否執(zhí)行成功
if (result == SQLITE_OK) {
NSLog(@"%@執(zhí)行語(yǔ)句成功",operStr);
return YES;
}else{
NSLog(@"%@執(zhí)行 sql語(yǔ)句失敗--- %d",operStr,result);
return? NO;
}
}
// 執(zhí)行有返回值的 查詢(xún)操作
- (NSArray *) queryWithSql:(NSString *)sql{
// 打開(kāi)數(shù)據(jù)庫(kù)
sqlite3 *dbHandle = [self creatOrOpenDatabase];
// 聲明伴隨指針
sqlite3_stmt *stament = NULL;
// 準(zhǔn)備 sql 語(yǔ)句
int result = sqlite3_prepare(dbHandle, sql.UTF8String, -1, &stament, NULL);
// 初始化數(shù)組
NSMutableArray *allResultArr = [NSMutableArray array];
// 判斷 sql語(yǔ)句是否正確
if (result == SQLITE_OK) {
// 取出每一條記錄
while (sqlite3_step(stament) == SQLITE_ROW) {
// 獲得列個(gè)數(shù)
int sumCol = sqlite3_column_count(stament);
// 初始化字典
NSMutableDictionary *bdDic = [NSMutableDictionary dictionary];
// 遍歷每一列
for (int i = 0 ; i < sumCol; i++) {
// 獲得字段類(lèi)型
int typeCol = sqlite3_column_type(stament, i);
// 獲得字段名
const char *nameCol = sqlite3_column_name(stament, i);
NSString *key = [NSString stringWithUTF8String:nameCol];
// 判斷當(dāng)前列的類(lèi)型存儲(chǔ)到字典中
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(@"查詢(xún)有誤 ----- %d",result);
}
// 釋放資源，返回?cái)?shù)組
sqlite3_finalize(stament);
sqlite3_close(dbHandle);
return allResultArr;
}
@end
