iOS-FMDB本地存储之一种封装思想

版权地址：https://www.jianshu.com/p/5f8e56a17d85 作者：有毒的程序猿 说明：八点钟学院二期学员 学习链接：https://ke.qq.com/course/171725 交流群：272306631

1.前提工作

安装cocoapods,并且导入

pod 'FMDB/FTS', '2.5' # sqlite操作

pod 'FCFileManager', '1.0.17' # sqlite操作

pod 'GCJSONKit', '1.5.0' # JSON和对象互转

2.创建可视化表

首先创建.bundle文件用来装所有表(便于管理)

然后在.string里面规定自己表定义格式如下图:

表名:user_friends

主键:USER_CODE

字段:数组

3.加载表定义

创建一个manager 来管理表,加载表定义,需要传入你.bundle文件的名字和数据库存储路径.

/**

* 初始化数据对象

* @param defineFileName : 数据库定义文件

* @param filePath : 数据库文件路径

*/

- (instancetype)initWithDBDefine:(NSString * )bundleName filePath:(NSString *)filePath {

if (self =[super init]) {

self.dbFile = filePath;

[self loadTableDefinition:bundleName];

}

return self;

}

4.创建表

/**

* 根据定义创建数据库表

*

*/

- (BOOL)createTableWithConfig:(NSDictionary *)tableDefine withDb:(FMDatabase *)db

数据库创建表具体sql语句

CREATE TABLE IF NOT EXISTS user_friends(USER_CODE TEXT primary key not null,USER_NAME TEXT,FRIEND_CODE TEXT,USER_IMG_SRC TEXT,USER_SHORT_NAME TEXT,USER_EN_NAME TEXT,USER_SEX TEXT,DEPT_NAME TEXT,USER_POST TEXT)

你所需要的是表名,主键,及各种字段名字拼成以上格式,这里不多说可以文章底部github下载demo自己研究这里不多说

5.打开数据库的管理类

在这创建manger,并传入存储路径

保证数据库,操作在同一线程操作,当我们在程序中运用到多线程的时候，那么你必须要考虑的就是各线程抢占资源的问题，不能让同一时间多个线程去抢一个资源，比如你两个线程同时去操作sql，就会造成有脏读数据或者查不到数据，或者查的是脏数据.

#import "SQliteUser.h"

@interface SQliteUser ()

@property (nonatomic, strong) SqliteManager *manager;

@end

@implementation SQliteUser

SINGLETON_FOR_CLASS(SQliteUser);//宏单例

- (SqliteManager *)manager {

if (_manager) {

return _manager;

}

@synchronized(self) {

if (!_manager) {

NSString *dbPath = [NSString stringWithFormat:@"%@%@",NSHomeDirectory(),@"/Documents/yuhechuan.db"];

_manager = [[SqliteManager alloc] initWithDBDefine:@"user" filePath:dbPath];

}

}

return _manager;

}

@end

synchronized同步锁,保证一个线程执行完成,再执行其他线程任务

一般说synchronized是加锁，或者说是加对象锁，其实对象锁只是synchronized在实现锁机制中的一种锁（重量锁，用这种方式互斥线程开销大所以叫重量锁，或者叫对象monitor），而synchronized的锁机制会根据线程竞争情况在运行会有偏向锁、轻量锁、对象锁，自旋锁（或自适应自旋锁）等

参考链接：http://www.jianshu.com/p/5dbb07c8d5d5

6.创建DAO

@implementation SQBaseDAO

子类继承baseDAO 实现下面两个父类的方法,来告诉manger,表名和主键

-(NSString *)getPK {

return @"需要子类实现";

}

- (NSString *)getTable {

return @"需要子类实现";

}

获取Sqlite管理类和其中的FMDatabaseQueue

- (SqliteManager *)getDataSource {

return [[SQliteUser sharedInstance] manager];

}

7.实现增,删,改,查

首先调用数据库的方法应在FMDB提供的方法里面执行

- (void)inDatabase:(void (^)(FMDatabase *db))block

这个方法提供了一个代码块。操作数据库的代码写在block里，如：

FMDatabaseQueue是一个串行队列，它不支持串行任务嵌套执行

以下代码比较简单不予多说自己看

增

//增

- (BOOL)insert:(NSDictionary *)data {

//安全线程

FMDatabaseQueue *dbQueue = [[self getDataSource] dbQueue];

__block BOOL execute = NO;

[dbQueue inDatabase:^(FMDatabase *db) {

NSMutableDictionary *mData = [NSMutableDictionary dictionaryWithDictionary:data];

NSString *pk = [self getPK];//主键

NSString * table = [self getTable];//表名

//如果主键没值,主动生成主键

if (![mData objectForKey:pk]) {

[mData setObject:[[NSUUID UUID] UUIDString] forKey:pk];

}

NSMutableString *insertKey = [NSMutableString stringWithCapacity:0];

NSMutableString *insertValuesString = [[NSMutableString alloc] init];

NSMutableArray *insertValues = [[NSMutableArray alloc] init];

NSArray *columnArray = [mData allKeys];

for (int i = 0; i

NSString *columnName = columnArray[i];

id value = [mData objectForKey:columnName];

if (!value) {

continue;

}

if (insertKey.length > 0) {

[insertKey appendString:@","];

[insertValuesString appendString:@","];

}

[insertKey appendString:columnName];

[insertValuesString appendString:@"?"];

[insertValues addObject:value];

}

// 拼接insertSQL 语句 采用 replace 插入

NSString *insertSQL = [NSString stringWithFormat:@"replace into %@(%@) values(%@)", table, insertKey, insertValuesString];

execute = [db executeUpdate:insertSQL withArgumentsInArray:insertValues];

//打印日志,如果在主线程执行,方便查找

[self printSQLLog:insertSQL values:insertValues];

}];

return execute;

}

删

//删

- (BOOL)remove:(SQConditionBean *)condition {

FMDatabaseQueue *dbQueue = [[self getDataSource] dbQueue];

__block BOOL execute = NO;

[dbQueue inDatabase:^(FMDatabase *db) {

NSString *table = [self getTable];

NSMutableString *deleteSQL = [NSMutableString stringWithFormat:@"delete from %@ ", table];

// 添加where 语句

NSMutableArray *valuearray = [NSMutableArray array];

//获取条件字符串

NSDictionary *dict = [condition conditionDict];

NSString *sqlwhere = [self dictionaryToSqlWhere:dict andValues:valuearray];

if (sqlwhere.length > 0) {

[deleteSQL appendString:@" where "];

[deleteSQL appendString:sqlwhere];

}

execute = [db executeUpdate:deleteSQL withArgumentsInArray:valuearray];

//打印日志,如果在主线程执行,方便查找 [self printSQLLog:deleteSQL values:valuearray];

}];

return execute;

}

改

//改

- (BOOL)modify:(NSDictionary *)data {

FMDatabaseQueue *dbQueue = [[self getDataSource ] dbQueue];

__block BOOL execute = NO;

[dbQueue inDatabase:^(FMDatabase *db) {

NSString *pk = [self getPK];//主键

NSString * table = [self getTable];//表名

NSMutableString *updateKey = [NSMutableString string];//更新key集合

NSMutableArray *updateValues = [[NSMutableArray alloc] init];//更新的value集合

NSArray *columnArray = data.allKeys;

for (NSString *key in columnArray) {

//不是主键字段

if (![key isEqualToString:pk]) {

if (updateKey.length > 0) {

[updateKey appendString:@","];

}

[updateKey appendFormat:@"%@=?", key];

}

id value = [data objectForKey:key];

[updateValues addObject:value];

}

NSMutableString *updateSQL = [NSMutableString stringWithFormat:@"update %@ set %@ where %@=?", table, updateKey,[updateValues lastObject]];

execute = [db executeUpdate:updateSQL withArgumentsInArray:updateValues];

//打印日志,如果在主线程执行,方便查找

[self printSQLLog:updateSQL values:updateValues];

}];

return execute;

}

查

//查

- (NSMutableArray *)query:(SQConditionBean *)condition{

FMDatabaseQueue *dbQueue = [[self getDataSource ] dbQueue];

__block NSMutableArray *results = [[NSMutableArray alloc] init];

[dbQueue inDatabase:^(FMDatabase *db) {

NSString * table = [self getTable];

NSString *columnsString = @"*";

//build query

NSMutableString *query = [NSMutableString stringWithFormat:@"select %@ from %@", columnsString, table]; NSMutableArray *whereValues = [NSMutableArray array];

//build where

NSString *wherekey = [self dictionaryToSqlWhere:[condition conditionDict] andValues:whereValues];

if ( wherekey.length > 0) {

[query appendFormat:@" where %@", wherekey];

}

//execute

FMResultSet *set = nil;

if (whereValues.count == 0) {

set = [db executeQuery:query];

} else {

set = [db executeQuery:query withArgumentsInArray:whereValues];

}

//打印日志,如果在主线程执行,方便查找

[self printSQLLog:query values:whereValues];

while ([set next]) {

NSDictionary *rowData = [self resultToDic:set];

[results addObject:rowData];

}

[set close];

}];

return results;

}

8.创建条件SQConditionBean 拼接where语句

携带一个字典

@property (nonatomic, strong) NSMutableDictionary *conditionDict;

/**

* 添加条件(操作符是AND)

*

* @param value 值

* @param key 字段

*/

-(void) set:(id)value forKey:(NSString *)key {

if (value) {

[conditionDict setValue:value forKey:key];

}

}

/**

* 添加 != 条件 (操作符是AND)

*

* @param value 值

* @param key 字段

*/

-(void) andNE:(id)value forKey:(NSString *)key {

NSString * query = [NSString stringWithFormat:@"%@ != ", key];

[conditionDict setValue:value forKey:query];

}

参考资料:http://www.jianshu.com/p/5dbb07c8d5d5

Demo下载地址:https://github.com/yuhechuan/SqliteTools