SQLite简单运用

• 重要提醒：请使用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就算完成了。希望对大家有所帮助。