重要提醒：請使用FMDB等第三方類庫屡谐！本文僅僅是對SQLite的一些簡單運(yùn)用述么，沒有防注入，沒有事務(wù)愕掏，僅供自己學(xué)習(xí)使用度秘！再次強(qiáng)調(diào)，請使用第三方類庫（FMDB饵撑、PlausibleDatabase剑梳、sqlitepersistentobjects等）！
要使用SQLite3滑潘，項目中要添加SQLite3垢乙，并且相關(guān)頭文件要包含"sqlite3.h"
SQLite3并不是很方便我們使用（所以才有了上述各種第三方類庫），因此我們考慮自己對其進(jìn)行簡單封裝成DBHelper類语卤。
#import <Foundation/Foundation.h>
@interface DBHelper : NSObject
@property (nonatomic) BOOL isDBOpen;
+ (instancetype)getInstance;
- (int)execNoQueryWithSQL:(NSString*)sql;
- (NSMutableArray*)execQueryWithSQL:(NSString*)sql;
@end
我們只實(shí)現(xiàn)一個“數(shù)據(jù)庫是否打開”的屬性追逮，一個獲取實(shí)例的類方法，一個執(zhí)行查詢語句的成員方法粹舵，一個執(zhí)行非查詢語句的成員方法钮孵。
- 獲取實(shí)例的類方法：在一個程序中，重復(fù)多次對數(shù)據(jù)庫進(jìn)行連接是有很多問題的眼滤，會造成數(shù)據(jù)庫負(fù)擔(dān)過大巴席。我們只想保持一個數(shù)據(jù)連接，所以在.m文件中诅需，我們定義了幾個靜態(tài)變量：
static NSArray* docPath;
static NSString* dbPath;
static sqlite3 *db;
static DBHelper* dbhelper;
在類方法中漾唉，實(shí)現(xiàn)獲取dbhelper：
+ (instancetype)getInstance {
if (dbhelper == nil) {
dbhelper = [[DBHelper alloc]init];
}
return dbhelper;
}
- (id)init {
self = [super init];
if ([self class] == [DBHelper class]) {
docPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
dbPath = [[docPath objectAtIndex:0]stringByAppendingPathComponent:@"guessCityDB.sqlite"];
}
return self;
}
- 我們希望數(shù)據(jù)庫的打開關(guān)閉由我們自己控制睬关，所以定義了兩個私有實(shí)例方法：
- (void)open {
_isDBOpen = sqlite3_open([dbPath UTF8String], &db) == SQLITE_OK;
}
- (void)close {
sqlite3_close(db);
_isDBOpen = NO;
}
- 非查詢語句的執(zhí)行比如好實(shí)現(xiàn)，代碼如下：
- (int)execNoQueryWithSQL:(NSString*)sql {
[self open];
if (self.isDBOpen) {
const char* execSQL = [sql UTF8String];
char *error = NULL;
int result = -1;
if (sqlite3_exec(db, execSQL, NULL, NULL, &error) == SQLITE_OK) {
result = sqlite3_changes(db);
}
if (error != NULL)
{
sqlite3_free(error);
}
[self close];
return result;
} else {
[self close];
return -1;
}
}
執(zhí)行失敗返回-1毡证，執(zhí)行成功返回“執(zhí)行SQL后改變的數(shù)據(jù)數(shù)量”电爹。
- 查詢語句的執(zhí)行比如復(fù)雜，我們希望返回一個“結(jié)果集”料睛，它是一個NSMutableArray丐箩，里面有“查詢結(jié)果數(shù)量”的NSMutableDictionary，每一個NSMutableDictionary都包含了“查詢結(jié)果”中對應(yīng)的字段名恤煞、字段值屎勘。代碼如下：
- (NSMutableArray*)execQueryWithSQL:(NSString*)sql {
[self open];
if (self.isDBOpen) {
NSMutableArray* result = [NSMutableArray arrayWithCapacity:100];
const char* execSQL = [sql UTF8String];
sqlite3_stmt* statement = NULL;
if (sqlite3_prepare(db, execSQL, -1, &statement, NULL) == SQLITE_OK) {
while(sqlite3_step(statement) == SQLITE_ROW) {
int colNum = sqlite3_column_count(statement);
NSMutableDictionary *dic = [NSMutableDictionary dictionaryWithCapacity:colNum];
for (int i=0; i<colNum; i++) {
const char* colName = sqlite3_column_name(statement, i);
const char* value = (const char*)sqlite3_column_text(statement, i);
if (value != NULL) {
[dic setObject:[NSString stringWithUTF8String:value] forKey:[[NSString stringWithUTF8String:colName]uppercaseString]];
} else {
[dic setObject:@"" forKey:[[NSString stringWithUTF8String:colName]uppercaseString]];
}
}
[result addObject:dic];
}
sqlite3_finalize(statement);
[self close];
return result;
} else {
[self close];
return nil;
}
} else {
[self close];
return nil;
}
}
- 我們應(yīng)該如何運(yùn)用？還記得<a href="http://www.reibang.com/p/f64e43ac4453/">VO -- ValueObject</a>中提到的DAO嗎居扒？
#import "CityDAO.h"
#import "DBHelper.h"
@implementation CityDAO
- (NSMutableArray<CityVO*>*)getCityDB {
NSString* sql = @"select * from city";
NSMutableArray* result = [[DBHelper getInstance] execQueryWithSQL:sql];
NSMutableArray* cityVOs = [NSMutableArray arrayWithCapacity:result.count];
for (int i=0; i<result.count; i++) {
NSMutableDictionary* dic = [result objectAtIndex:i];
[cityVOs addObject:[self translateCityVOByNSMutableDictionary:dic]];
}
return cityVOs;
}
- (CityVO*)getCityByID:(int)cityid {
NSString* sql = [NSString stringWithFormat:@"select * from city where cityid=%d", cityid];
NSMutableArray* result = [[DBHelper getInstance] execQueryWithSQL:sql];
if (result.count == 1) {
NSMutableDictionary* dic = [result objectAtIndex:0];
return [self translateCityVOByNSMutableDictionary:dic];
} else {
return nil;
}
}
- (UIImage*)getIamgeFromCity:(CityVO*)cityVO {
return [UIImage imageNamed:cityVO.imageName];
}
- (CityVO*)translateCityVOByNSMutableDictionary:(NSMutableDictionary*)dic {
int cityid = [(NSString*)[dic objectForKey:@"CITYID"] intValue];
NSString* cityname = (NSString*)[dic objectForKey:@"NAME"];
NSString* simplename = (NSString*)[dic objectForKey:@"SIMPLENAME"];
NSString* imagename = (NSString*)[dic objectForKey:@"IMAGENAME"];
return [[CityVO alloc]initWithID:cityid WithName:cityname WithSimpleName:simplename WithImageName:imagename];
}
@end
- 那么一個簡單的DBHelper就算完成了概漱。希望對大家有所幫助。
