重要提醒：请使用FMDB等第三方类库！本文仅仅是对SQLite的一些简单运用，没有防注入，没有事务，仅供自己学习使用！再次强调，请使用第三方类库（FMDB、PlausibleDatabase、sqlitepersistentobjects等）！
要使用SQLite3，项目中要添加SQLite3，并且相关头文件要包含"sqlite3.h"
SQLite3并不是很方便我们使用（所以才有了上述各种第三方类库），因此我们考虑自己对其进行简单封装成DBHelper类。
#import <Foundation/Foundation.h>
@interface DBHelper : NSObject
@property (nonatomic) BOOL isDBOpen;
+ (instancetype)getInstance;
- (int)execNoQueryWithSQL:(NSString*)sql;
- (NSMutableArray*)execQueryWithSQL:(NSString*)sql;
@end
我们只实现一个“数据库是否打开”的属性，一个获取实例的类方法，一个执行查询语句的成员方法，一个执行非查询语句的成员方法。
- 获取实例的类方法：在一个程序中，重复多次对数据库进行连接是有很多问题的，会造成数据库负担过大。我们只想保持一个数据连接，所以在.m文件中，我们定义了几个静态变量：
static NSArray* docPath;
static NSString* dbPath;
static sqlite3 *db;
static DBHelper* dbhelper;
在类方法中，实现获取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;
}
- 我们希望数据库的打开关闭由我们自己控制，所以定义了两个私有实例方法：
- (void)open {
_isDBOpen = sqlite3_open([dbPath UTF8String], &db) == SQLITE_OK;
}
- (void)close {
sqlite3_close(db);
_isDBOpen = NO;
}
- 非查询语句的执行比如好实现，代码如下：
- (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;
}
}
执行失败返回-1，执行成功返回“执行SQL后改变的数据数量”。
- 查询语句的执行比如复杂，我们希望返回一个“结果集”，它是一个NSMutableArray，里面有“查询结果数量”的NSMutableDictionary，每一个NSMutableDictionary都包含了“查询结果”中对应的字段名、字段值。代码如下：
- (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;
}
}
- 我们应该如何运用？还记得<a href="http://www.jianshu.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就算完成了。希望对大家有所帮助。
