由于過(guò)年，好久沒(méi)寫(xiě)了~??????????
FMDB三方庫(kù) github地址：https://github.com/ccgus/fmdb
其實(shí)本質(zhì)就是“字符串操作”旺遮，使用FMDatabase對(duì)象執(zhí)行SQL語(yǔ)句** 就好了纠修！
優(yōu)點(diǎn)：不管項(xiàng)目中使用的是ARC己肮，還是MRC，對(duì)FMDB的使用 都沒(méi)有 任何影響泻拦，F(xiàn)MDB會(huì)在編譯項(xiàng)目時(shí)進(jìn)行自動(dòng)匹配。
FMDB中三個(gè)重要的類：
FMDatabase：是一個(gè)提供 SQLite 數(shù)據(jù)庫(kù)的類黔牵，用于執(zhí)行** SQL 語(yǔ)句聪轿。
FMResultSet：用在 FMDatabase 中執(zhí)行查詢的結(jié)果的類(FMDatabase結(jié)果集)。
FMDatabaseQueue：在多線程下** 查詢和更新數(shù)據(jù)庫(kù)用到的類猾浦。
數(shù)據(jù)庫(kù)的創(chuàng)建
FMDatabase是通過(guò)一個(gè)SQLite數(shù)據(jù)庫(kù)文件 路徑 創(chuàng)建的陆错，其路徑一般在以下三者之中：
- 一個(gè)文件的系統(tǒng)路徑 →→→ 磁盤(pán)中可以不存在此文件，因?yàn)槿绻淮嬖跁?huì)自動(dòng)為你創(chuàng)建好金赦。
- 一個(gè)空的字符串:@"" →→→ 會(huì)在臨時(shí)位置創(chuàng)建一個(gè)空的數(shù)據(jù)庫(kù)音瓷，當(dāng) FMDatabase 連接關(guān)閉時(shí)，該數(shù)據(jù)庫(kù)會(huì)被刪除夹抗。
- NULL →→→ 會(huì)在內(nèi)存中創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)绳慎，當(dāng)FMDatabase連接關(guān)閉時(shí)，該數(shù)據(jù)庫(kù)亦會(huì)被銷(xiāo)毀漠烧。
// 創(chuàng)建數(shù)據(jù)庫(kù)示例
FMDatabase * _db = [FMDatabase databaseWithPath:@"/tmp/goyohol.db"];
配置操作：
導(dǎo)入FMDB三方庫(kù) ( 我是下載下來(lái)直接拖入的杏愤，簡(jiǎn)單粗暴~~~~~?????????????? )
FMDB的導(dǎo)入
一推錯(cuò)誤 ??????????
一片 紅 ????????????.png
在“Link Binary With Libraries”中加入 libsqlite3.tbd
只需加入“l(fā)ibsqlite3.tbd”依賴庫(kù)，工程就可以運(yùn)行了
當(dāng)然已脓！要是嫌麻煩的話珊楼，可以直接使用“CocoaPods”來(lái)導(dǎo)入~~
數(shù)據(jù)庫(kù) 基本操作 ??
//創(chuàng)建并且打開(kāi)數(shù)據(jù)庫(kù)
//數(shù)據(jù)庫(kù)類對(duì)象 全局變量
FMDatabase * dataBase;
// 1.獲取數(shù)據(jù)庫(kù)對(duì)象
NSString * pathStr = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0]; //路徑自選
pathStr = [pathStr stringByAppendingPathComponent:@"goyohol.sqlite"];
// 創(chuàng)建數(shù)據(jù)庫(kù)
dataBase = [FMDatabase databaseWithPath:pathStr];
// 2.打開(kāi)數(shù)據(jù)庫(kù)（若不存在，會(huì)創(chuàng)建并且打開(kāi)）
BOOL open = [dataBase open];
if(open){
NSLog(@"dataBase open successfully");
}
//3.創(chuàng)建表
NSString * createTableStr = @"create table if not exists tab_user(id integer primary key,name varchar)"; // 簡(jiǎn)單粗暴地執(zhí)行 SQL語(yǔ)句
//autoincrement修飾(id integer autoincrement primary key) 會(huì)報(bào)錯(cuò)：DB Error: 1 "near "autoincrement": syntax error"
//原因：SQLite不支持關(guān)鍵字AUTO_INCREMENT度液。
//在SQLite中厕宗，自增字段需要使用關(guān)鍵字INTEGER PRIMARY KEY
BOOL createtab_Succ = [dataBase executeUpdate:createTableStr];
if(createtab_Succ){
NSLog(@"create table successfully");
}
//4.插入數(shù)據(jù)
NSString * insertSqlStr = @"insert into tab_user(id,name) values(?,?)";
// “?”,相當(dāng)于OC里面的“%@”。
// 插入語(yǔ)句1
bool insert_Succ_1 = [dataBase executeUpdate:insertSqlStr,@(2),@"Hellen"];
// 插入語(yǔ)句2
bool insert_Succ_2 = [dataBase executeUpdate:insertSqlStr withArgumentsInArray:@[@(5),@"Amanda"] ];
// 插入語(yǔ)句3
bool insert_Succ_3 = [dataBase executeUpdateWithFormat:@"insert into tab_user(id,name) values(%d,%@)",6,@"Bob"];
//5.刪除語(yǔ)句
NSString * deleteStr = @"delete from tab_user";
BOOL delete_Succ = [dataBase executeUpdate:deleteStr];
if(delete_Succ){
NSLog(@"delete successfully");
}
//6.修改語(yǔ)句
NSString * updateStr = @"update tab_user set name=? ";
BOOL update_Succ = [dataBase executeUpdate:updateStr,@"Jack"];
if(update_Succ){
NSLog(@"update successfully");
}
//7.查詢數(shù)據(jù)FMDB的FMResultSet堕担，提供了多個(gè)方法來(lái)獲取不同類型的數(shù)據(jù)
NSString * sqlStr = @" select * from tab_user ";
FMResultSet * result = [dataBase executeQuery:sqlStr];
while(result.next){ // 依次遍歷FMResultSet
int ids = [result intForColumn:@"id"];
NSString * name = [result stringForColumn:@"name"];
//int ids = [result intForColumnIndex:0]; //第1列
//NSString * name = [result stringForColumnIndex:1]; //第2列
NSLog(@"%@,%d",name,ids);
}
效果：
產(chǎn)生的數(shù)據(jù)庫(kù)：“goyohol.sqilte”文件已慢。在沙盒路徑中找到，使用“Navicat Premium”打開(kāi)霹购。
打開(kāi)“goyohol.sqilte”文件
Navicat Premium購(gòu)買(mǎi)地址：https://www.navicat.com.cn/store/navicat-premium
Navicat Premium
查看數(shù)據(jù)庫(kù)里面的數(shù)據(jù)：
表
表中的數(shù)據(jù)
在應(yīng)用中佑惠，若使用了多線程 操作數(shù)據(jù)庫(kù)， 就需要使用FMDatabaseQueue來(lái)保證線程安全了。
應(yīng)用中不可以 在多個(gè)線程中共同使用一個(gè)FMDatabase對(duì)象 操作數(shù)據(jù)庫(kù)膜楷，否則會(huì)引起 數(shù)據(jù)庫(kù)數(shù)據(jù)混亂乍丈。
為了多線程 操作數(shù)據(jù)庫(kù)安全，F(xiàn)MDB使用了FMDatabaseQueue：
首先用一個(gè)數(shù)據(jù)庫(kù)文件地址來(lái)初使化FMDatabaseQueue把将；
然后就可以將一個(gè) 閉包(block) 傳入inDatabase方法中。 在閉包中 操作 數(shù)據(jù)庫(kù)忆矛，而不直接參與FMDatabase的管理察蹲。
多線程操作：
//1.創(chuàng)建并且打開(kāi)數(shù)據(jù)庫(kù)（根據(jù)數(shù)據(jù)庫(kù)的存儲(chǔ)路徑）
NSString *pathStr = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
pathStr = [pathStr stringByAppendingPathComponent:@"goyohol.sqlite"];
FMDatabaseQueue * queue = [FMDatabaseQueue databaseQueueWithPath:pathStr];
//2.創(chuàng)建表
[queue inDatabase:^(FMDatabase * db) {
NSString * createStr = @"create table if not exists tab_book(id integer,name varchar)";
BOOL createtab_Succ = [db executeUpdate:createStr];
if(createtab_Succ ){
NSLog(@"create table successfully");
}
}];
//3.插入 （刪除、修改 類似）
[queue inDatabase:^(FMDatabase *db) {
NSString * insertSqlStr = @"insert into tab_book(id,name) values(?,?)";
//插入語(yǔ)句
bool insert_Succ = [db executeUpdate:insertSqlStr,@(2),@"Jane Eyre"];
if(insert_Succ){
NSLog(@"insert successfully");
}
}];
//4.查詢表
[queue inDatabase:^(FMDatabase *db) {
FMResultSet * returnData = [db executeQuery:@" select * from tab_book "];
while (returnData.next) { // 依次遍歷FMResultSet
int ids = [returnData intForColumn:@"id"];
NSString * name = [returnData stringForColumn:@"name"];
NSLog(@"%@",name);
NSLog(@"%i",ids);
}
}];
以上即是對(duì)FMDB的使用催训、操作洽议。
接下來(lái)就進(jìn)入主題吧！自定制一個(gè) 單例的數(shù)據(jù)庫(kù)管理類
自定制數(shù)據(jù)庫(kù) 管理類
創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)單例管理類漫拭，
并在單例管理類中導(dǎo)入“FMDB.h”,進(jìn)行單例(數(shù)據(jù)庫(kù))管理類的書(shū)寫(xiě)
數(shù)據(jù)庫(kù)管理單例類 創(chuàng)建
“SqliteTool.h”文件里：
#import <Foundation/Foundation.h>
#import "FMDB.h"
@interface SqliteTool : NSObject
/**
* 可以存儲(chǔ)數(shù)據(jù)類型 text integer blob boolean date
* keyTypes 存儲(chǔ)的字段 以及對(duì)應(yīng)數(shù)據(jù)類型
* keyValues 存儲(chǔ)的字段 以及對(duì)應(yīng)的值
*/
/**
* 數(shù)據(jù)庫(kù)工具單例
*
* @return 數(shù)據(jù)庫(kù)工具對(duì)象
*/
+(SqliteTool *)shareTool;
/**
* 創(chuàng)建數(shù)據(jù)庫(kù)
*
* @param dbName 數(shù)據(jù)庫(kù)名稱(帶后綴.sqlite)
*/
-(FMDatabase *)getDBWithDBName:(NSString *)dbName;
/**
* 給指定數(shù)據(jù)庫(kù)建表
*
* @param db 指定數(shù)據(jù)庫(kù)對(duì)象
* @param tableName 表的名稱
* @param keyTypes 所含字段以及對(duì)應(yīng)字段類型 字典
*/
-(void)DataBase:(FMDatabase *)db createTable:(NSString *)tableName keyTypes:(NSDictionary *)keyTypes;
/**
* 給指定數(shù)據(jù)庫(kù)的表添加值
*
* @param db 數(shù)據(jù)庫(kù)名稱
* @param keyValues 字段及對(duì)應(yīng)的值
* @param tableName 表名
*/
-(void)DataBase:(FMDatabase *)db insertKeyValues:(NSDictionary *)keyValues intoTable:(NSString *)tableName;
/**
* 給指定數(shù)據(jù)庫(kù)的表更新值
*
* @param db 數(shù)據(jù)庫(kù)名稱
* @param keyValues 要更新字段及對(duì)應(yīng)的值
* @param tableName 表名
*/
-(void)DataBase:(FMDatabase *)db updateTable:(NSString *)tableName setKeyValues:(NSDictionary *)keyValues;
/**
* 條件更新
*
* @param db 數(shù)據(jù)庫(kù)名稱
* @param tableName 表名稱
* @param keyValues 要更新的字段及對(duì)應(yīng)值
* @param condition 條件字典
*/
-(BOOL)DataBase:(FMDatabase *)db updateTable:(NSString *)tableName setKeyValues:(NSDictionary *)keyValues whereCondition:(NSDictionary *)condition;
#pragma mark --順序查詢 數(shù)據(jù)庫(kù)表中的所有值（無(wú)限制全部 ??升序??）
/**
* 按條件查詢
*
* @param db 數(shù)據(jù)庫(kù)名稱
* @param keyTypes 表名稱
* @param tableName 要更新的字段及對(duì)應(yīng)值
* @param colunm 要查詢的類型 (順序查詢)
*
* @return 數(shù)組
*/
-(NSArray *)SortAllInformationDataBase:(FMDatabase *)db selectKeyTypes:(NSDictionary *)keyTypes fromTable:(NSString *)tableName colunm:(NSString *)colunm;
#pragma mark --查詢數(shù)據(jù)庫(kù)表中的所有值（無(wú)限制全部）
/**
* 按條件查詢所有數(shù)據(jù)
*
* @param db 數(shù)據(jù)庫(kù)名稱
* @param keyTypes 字典{數(shù)據(jù)格式及名稱}
* @param tableName 表名
*
* @return 數(shù)據(jù)返回?cái)?shù)組
*/
-(NSArray *)AllInformationDataBase:(FMDatabase *)db selectKeyTypes:(NSDictionary *)keyTypes fromTable:(NSString *)tableName;
//按條件
-(NSArray *)AllInformationDataBase:(FMDatabase *)db selectKeyTypes:(NSDictionary *)keyTypes fromTable:(NSString *)tableName whereCondition:(NSDictionary *)condition;
/**
* 查詢數(shù)據(jù)庫(kù)表中的所有值 限制數(shù)據(jù)條數(shù)10
*
* @param db 數(shù)據(jù)庫(kù)名稱
* @param keyTypes 查詢字段以及對(duì)應(yīng)字段類型 字典
* @param tableName 表名稱
* @return 查詢得到數(shù)據(jù)
*/
-(NSArray *)DataBase:(FMDatabase *)db selectKeyTypes:(NSDictionary *)keyTypes fromTable:(NSString *)tableName;
/**
* 條件查詢數(shù)據(jù)庫(kù)中的數(shù)據(jù) 限制數(shù)據(jù)條數(shù)10
*
* @param db 數(shù)據(jù)庫(kù)名稱
* @param keyTypes 查詢字段以及對(duì)應(yīng)字段類型 字典
* @param tableName 表名稱
* @param condition 條件
*
* @return 查詢得到數(shù)據(jù) 限制數(shù)據(jù)條數(shù)10
*/
-(NSArray *)DataBase:(FMDatabase *)db selectKeyTypes:(NSDictionary *)keyTypes fromTable:(NSString *)tableName whereCondition:(NSDictionary *)condition;
/**
* 模糊查詢 某字段以指定字符串開(kāi)頭的數(shù)據(jù) 限制數(shù)據(jù)條數(shù)10
*
* @param db 數(shù)據(jù)庫(kù)名稱
* @param keyTypes 查詢字段以及對(duì)應(yīng)字段類型 字典
* @param tableName 表名稱
* @param key 條件字段
* @param str 開(kāi)頭字符串
*
* @return 查詢所得數(shù)據(jù) 限制數(shù)據(jù)條數(shù)10
*/
-(NSArray *)DataBase:(FMDatabase *)db selectKeyTypes:(NSDictionary *)keyTypes fromTable:(NSString *)tableName whereKey:(NSString *)key beginWithStr:(NSString *)str;
/**
* 模糊查詢 某字段包含指定字符串的數(shù)據(jù) 限制數(shù)據(jù)條數(shù)10
*
* @param db 數(shù)據(jù)庫(kù)名稱
* @param keyTypes 查詢字段以及對(duì)應(yīng)字段類型 字典
* @param tableName 表名稱
* @param key 條件字段
* @param str 所包含的字符串
*
* @return 查詢所得數(shù)據(jù)
*/
-(NSArray *)DataBase:(FMDatabase *)db selectKeyTypes:(NSDictionary *)keyTypes fromTable:(NSString *)tableName whereKey:(NSString *)key containStr:(NSString *)str;
/**
* 模糊查詢 某字段以指定字符串結(jié)尾的數(shù)據(jù) 限制數(shù)據(jù)條數(shù)10
*
* @param db 數(shù)據(jù)庫(kù)名稱
* @param keyTypes 查詢字段以及對(duì)應(yīng)字段類型 字典
* @param tableName 表名稱
* @param key 條件字段
* @param str 結(jié)尾字符串
*
* @return 查詢所得數(shù)據(jù)
*/
-(NSArray *)DataBase:(FMDatabase *)db selectKeyTypes:(NSDictionary *)keyTypes fromTable:(NSString *)tableName whereKey:(NSString *)key endWithStr:(NSString *)str;
#pragma mark - 清除
/**
* 清理指定數(shù)據(jù)庫(kù)中的數(shù)據(jù) （只刪除數(shù)據(jù)不刪除數(shù)據(jù)庫(kù)）
*
* @param db 指定數(shù)據(jù)庫(kù)
*/
-(void)clearDatabase:(FMDatabase *)db from:(NSString *)tableName;
/**
* 刪除表
*/
-(void)dropTableFormDatabase:(FMDatabase *)db table:(NSString *)tableName;
/**
* 刪除指定表(數(shù)據(jù)庫(kù)) 中的 單條數(shù)據(jù) (單一指定條件)
*/
-(void)deleteOneDataFormDatabase:(FMDatabase *)db fromTable:(NSString *)tableName whereConditon:(NSDictionary *)condition;
#pragma mark - 查詢
/**
* 特定條件查integer
*/
- (NSInteger)DHSelectIntegerWithDB:(FMDatabase *)db table:(NSString *)table colunm:(NSString *)colunm whereCondition:(NSDictionary *)conditon;
/**
* 特定條件查text
*/
- (NSString *)DHSelectTextWithDB:(FMDatabase *)db table:(NSString *)table colunm:(NSString *)colunm whereCondition:(NSDictionary *)condition;
/**
* 通過(guò)特定條件返回data數(shù)據(jù)
* @param conditon 字典:@{@"某行":@"值"}
*
* @return data
*/
- (NSData *)DHSelectDataWithDB:(FMDatabase *)db table:(NSString *)table colunm:(NSString *)colunm whereCondition:(NSDictionary *)conditon;
#pragma mark - 更新
/**
* 特定條件更新,注意：***條件只有一個(gè)鍵值對(duì)***
*/
- (BOOL)DHUpdateWithDB:(FMDatabase *)db table:(NSString *)table
setKeyValue:(NSDictionary *)keyValue
condition:(NSDictionary *)condition;
#pragma mark - 判斷是否存在
/**
* 判斷有沒(méi)有該字段
*/
- (BOOL)DHisExistObjectInDataBase:(FMDatabase *)db fromTable:(NSString *)tableName colunm:(NSString *)colunm identify:(NSString *)identify;
/**
* 判斷有沒(méi)有該字段,多個(gè)條件的
*/
- (BOOL)DHisExistObjectInDataBase:(FMDatabase *)db fromTable:(NSString *)tableName condition:(NSDictionary *)condition;
/**
* 判斷有沒(méi)表
*/
- (BOOL)DHisExistTable:(NSString *)tableName DataBase:(FMDatabase *)db;
#pragma mark - other
/**
* 查找最后一行
*/
- (NSArray *)DHLastLineDataBase:(FMDatabase *)db fromTable:(NSString *)tableName colunm:(NSString *)colunm;
@end
#import "SqliteTool.h"
static SqliteTool * tool = nil;
@implementation SqliteTool
+ (SqliteTool *)shareTool {
static dispatch_once_t onceToken;
dispatch_once(&onceToken, ^{
if (tool == nil) {
tool = [[self alloc] init];
}
});
return tool;
}
+(instancetype)allocWithZone:(struct _NSZone *)zone {
static dispatch_once_t onceToken;
dispatch_once(&onceToken, ^{
if (tool == nil) {
tool = [super allocWithZone:zone];
}
});
return tool;
}
#pragma mark --創(chuàng)建數(shù)據(jù)庫(kù)
-(FMDatabase *)getDBWithDBName:(NSString *)dbName
{
NSArray *library = NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES);// 沙盒路徑
NSString *dbPath = [library[0] stringByAppendingPathComponent:dbName];
FMDatabase *db = [FMDatabase databaseWithPath:dbPath];
NSLog(@"sqlite地址-->%@",dbPath);
if (![db open]) {
NSLog(@"無(wú)法獲取數(shù)據(jù)庫(kù)");
return nil;
}
return db;
}
#pragma mark --給指定數(shù)據(jù)庫(kù)建表
-(void)DataBase:(FMDatabase *)db createTable:(NSString *)tableName keyTypes:(NSDictionary *)keyTypes
{
if ([self isOpenDatabese:db]) {
NSMutableString *sql = [[NSMutableString alloc] initWithString:[NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ (id integer primary key,",tableName]];
int count = 0;
for (NSString *key in keyTypes) {
count++;
[sql appendString:key];
[sql appendString:@" "];
[sql appendString:[keyTypes valueForKey:key]];
if (count != [keyTypes count]) {
[sql appendString:@", "];
}
}
[sql appendString:@")"];
[db executeUpdate:sql];
}
}
#pragma mark --給指定數(shù)據(jù)庫(kù)的表添加值
-(void)DataBase:(FMDatabase *)db insertKeyValues:(NSDictionary *)keyValues intoTable:(NSString *)tableName
{
if ([self isOpenDatabese:db]) {
NSArray *keys = [keyValues allKeys];
NSArray *values = [keyValues allValues];
NSMutableString *sql = [[NSMutableString alloc] initWithString:[NSString stringWithFormat:@"INSERT INTO %@ (", tableName]];
NSInteger count = 0;
for (NSString *key in keys) {
[sql appendString:key];
count ++;
if (count < [keys count]) {
[sql appendString:@", "];
}
}
[sql appendString:@") VALUES ("];
for (int i = 0; i < [values count]; i++) {
[sql appendString:@"?"];
if (i < [values count] - 1) {
[sql appendString:@","];
}
}
[sql appendString:@")"];
[db executeUpdate:sql withArgumentsInArray:values];
}
}
#pragma mark --給指定數(shù)據(jù)庫(kù)的表更新值
-(void)DataBase:(FMDatabase *)db updateTable:(NSString *)tableName setKeyValues:(NSDictionary *)keyValues
{
if ([self isOpenDatabese:db]) {
for (NSString *key in keyValues) {
NSMutableString *sql = [[NSMutableString alloc] initWithString:[NSString stringWithFormat:@"UPDATE %@ SET %@ = ?", tableName, key]];
[db executeUpdate:sql,[keyValues valueForKey:key]];
}
}
}
#pragma mark --條件更新,跟新data
- (BOOL)DataBase:(FMDatabase *)db updateTable:(NSString *)tableName setKeyValues:(NSDictionary *)keyValues whereCondition:(NSDictionary *)condition
{
BOOL isSuccess = NO;
NSInteger count = [condition allKeys].count;
if ([self isOpenDatabese:db]) {
if (count == 1) {
for (NSString *key in keyValues) {
NSMutableString *sql = [[NSMutableString alloc] initWithString:[NSString stringWithFormat:@"UPDATE %@ SET %@ = ? WHERE %@ = ?", tableName, key, [condition allKeys][0]]];
isSuccess = [db executeUpdate:sql,[keyValues valueForKey:key],[condition valueForKey:[condition allKeys][0]]];
}
} else {
for (NSString *key in keyValues) {
int condition_count = 0;
NSMutableArray *condition_valueArr = [NSMutableArray array];
[condition_valueArr addObject:keyValues[key]];
NSMutableString *sql = [NSMutableString stringWithFormat:@"update %@ set %@ = ? where ",tableName,key];
for (NSString *condition_key in condition) {
condition_count ++;
if (condition_count == count) {
[sql appendFormat:@"%@ = ?",condition_key];
} else {
[sql appendFormat:@"%@ = ? and ",condition_key];
}
[condition_valueArr addObject:condition[condition_key]];
}
isSuccess = [db executeUpdate:sql withArgumentsInArray:condition_valueArr];
}
}
}
return isSuccess;
}
#pragma mark --順序查詢 數(shù)據(jù)庫(kù)表中的所有值（無(wú)限制全部 ??升序??）
-(NSArray *)SortAllInformationDataBase:(FMDatabase *)db selectKeyTypes:(NSDictionary *)keyTypes fromTable:(NSString *)tableName colunm:(NSString *)colunm
{
FMResultSet *result = [db executeQuery:[NSString stringWithFormat:@"SELECT * FROM %@ ORDER BY %@ ASC",tableName,colunm] ];
NSLog(@"---->%@",[NSString stringWithFormat:@"SELECT * FROM %@ sort",tableName]);
return [self getArrWithFMResultSet:result keyTypes:keyTypes];
}
#pragma mark --查詢數(shù)據(jù)庫(kù)表中的所有值（??無(wú)限制全部）
-(NSArray *)AllInformationDataBase:(FMDatabase *)db selectKeyTypes:(NSDictionary *)keyTypes fromTable:(NSString *)tableName
{
FMResultSet *result = [db executeQuery:[NSString stringWithFormat:@"SELECT * FROM %@",tableName]];
NSLog(@"---->%@",[NSString stringWithFormat:@"SELECT * FROM %@",tableName]);
return [self getArrWithFMResultSet:result keyTypes:keyTypes];
}
#pragma mark --條件查詢數(shù)據(jù)庫(kù)中 所有的數(shù)據(jù) （無(wú)限制全部）
-(NSArray *)AllInformationDataBase:(FMDatabase *)db selectKeyTypes:(NSDictionary *)keyTypes fromTable:(NSString *)tableName whereCondition:(NSDictionary *)condition
{
if ([self isOpenDatabese:db]) {
FMResultSet *result = [db executeQuery:[NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ?",tableName, [condition allKeys][0]], [condition valueForKey:[condition allKeys][0]]];
return [self getArrWithFMResultSet:result keyTypes:keyTypes];
}else
return nil;
}
#pragma mark --查詢數(shù)據(jù)庫(kù)表中的所有值 (限制數(shù)據(jù)條數(shù):10)
-(NSArray *)DataBase:(FMDatabase *)db selectKeyTypes:(NSDictionary *)keyTypes fromTable:(NSString *)tableName
{
FMResultSet *result = [db executeQuery:[NSString stringWithFormat:@"SELECT * FROM %@ LIMIT 10",tableName]];
NSLog(@"---->%@",[NSString stringWithFormat:@"SELECT * FROM %@ LIMIT 10",tableName]);
return [self getArrWithFMResultSet:result keyTypes:keyTypes];
}
#pragma mark --條件查詢數(shù)據(jù)庫(kù)中的數(shù)據(jù) (限制數(shù)據(jù)條數(shù):10)
-(NSArray *)DataBase:(FMDatabase *)db selectKeyTypes:(NSDictionary *)keyTypes fromTable:(NSString *)tableName whereCondition:(NSDictionary *)condition;
{
if ([self isOpenDatabese:db]) {
FMResultSet *result = [db executeQuery:[NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = ? LIMIT 10",tableName, [condition allKeys][0]], [condition valueForKey:[condition allKeys][0]]];
return [self getArrWithFMResultSet:result keyTypes:keyTypes];
}else
return nil;
}
#pragma mark --模糊查詢 某字段以??指定字符串 開(kāi)頭??的數(shù)據(jù) (限制數(shù)據(jù)條數(shù):10)
-(NSArray *)DataBase:(FMDatabase *)db selectKeyTypes:(NSDictionary *)keyTypes fromTable:(NSString *)tableName whereKey:(NSString *)key beginWithStr:(NSString *)str
{
if ([self isOpenDatabese:db]) {
FMResultSet *result = [db executeQuery:[NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ LIKE %@%% LIMIT 10",tableName, key, str]];
return [self getArrWithFMResultSet:result keyTypes:keyTypes];
}else
return nil;
}
#pragma mark --模糊查詢 某字段 ??包含??指定字符串的數(shù)據(jù) (限制數(shù)據(jù)條數(shù):10)
-(NSArray *)DataBase:(FMDatabase *)db selectKeyTypes:(NSDictionary *)keyTypes fromTable:(NSString *)tableName whereKey:(NSString *)key containStr:(NSString *)str
{
if ([self isOpenDatabese:db]) {
FMResultSet *result = [db executeQuery:[NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ LIKE %%%@%% LIMIT 10",tableName, key, str]];
return [self getArrWithFMResultSet:result keyTypes:keyTypes];
}else
return nil;
}
#pragma mark --模糊查詢 某字段以指定字符串??結(jié)尾??的數(shù)據(jù) (限制數(shù)據(jù)條數(shù):10)
-(NSArray *)DataBase:(FMDatabase *)db selectKeyTypes:(NSDictionary *)keyTypes fromTable:(NSString *)tableName whereKey:(NSString *)key endWithStr:(NSString *)str
{
if ([self isOpenDatabese:db]) {
FMResultSet *result = [db executeQuery:[NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ LIKE %%%@ LIMIT 10",tableName, key, str]];
return [self getArrWithFMResultSet:result keyTypes:keyTypes];
}else
return nil;
}
#pragma mark --清理指定數(shù)據(jù)庫(kù)中 表里的數(shù)據(jù)
-(void)clearDatabase:(FMDatabase *)db from:(NSString *)tableName
{
if ([self isOpenDatabese:db]) {
[db executeUpdate:[NSString stringWithFormat:@"DELETE FROM %@",tableName]];
}
}
#pragma mark --刪除指定數(shù)據(jù)庫(kù)中的 表
-(void)dropTableFormDatabase:(FMDatabase *)db table:(NSString *)tableName
{
if ([self isOpenDatabese:db]) {
[db executeUpdate:[NSString stringWithFormat:@"DROP TABLE '%@'",tableName]];
}
}
#pragma mark --(單一指定條件)刪除指定數(shù)據(jù)庫(kù)亚兄、表 中的 單條數(shù)據(jù)
-(void)deleteOneDataFormDatabase:(FMDatabase *)db fromTable:(NSString *)tableName whereConditon:(NSDictionary *)condition
{
if ([self isOpenDatabese:db]) {
[db executeUpdate:[NSString stringWithFormat:@"DELETE FROM %@ WHERE %@='%@';",tableName,[condition allKeys][0], [condition allValues][0]]];
}
}
#pragma mark - 查詢語(yǔ)句 (1000條數(shù)據(jù))
/**
* 特定條件查integer
*/
- (NSInteger)DHSelectIntegerWithDB:(FMDatabase *)db table:(NSString *)table colunm:(NSString *)colunm whereCondition:(NSDictionary *)condition {
NSInteger result = 0;
if ([self isOpenDatabese:db]) {
NSString *sql = [NSString stringWithFormat:@"select %@ from %@ WHERE %@ = '%@' limit 0,1000",colunm,table,[condition allKeys][0],[condition allValues][0]];
FMResultSet *rs = [db executeQuery:sql];
while ([rs next]) {
result = [rs intForColumn:colunm];
}
}
return result;
}
/**
* 特定條件查text
*/
- (NSString *)DHSelectTextWithDB:(FMDatabase *)db table:(NSString *)table colunm:(NSString *)colunm whereCondition:(NSDictionary *)condition {
NSString *result_str;
if ([self isOpenDatabese:db]) {
NSString *sql = [NSString stringWithFormat:@"select %@ from %@ WHERE %@ = '%@' limit 0,1000",colunm,table,[condition allKeys][0],[condition allValues][0]];
FMResultSet *rs = [db executeQuery:sql];
while ([rs next]) {
return [rs stringForColumn:colunm];
}
}
return result_str;
}
/**
* 特定條件查data
*/
- (NSData *)DHSelectDataWithDB:(FMDatabase *)db table:(NSString *)table colunm:(NSString *)colunm whereCondition:(NSDictionary *)conditon {
if ([self isOpenDatabese:db]) {
FMResultSet *rs = [db executeQuery:[NSString stringWithFormat:@"select %@ from %@ WHERE %@ = '%@' limit 0,1000",colunm,table,[conditon allKeys][0],[conditon allValues][0]]];
while ([rs next]) {
NSData *data = [rs dataForColumn:colunm];
return data;
}
}
return nil;
}
#pragma mark - update
/**
* 特定條件更新 注意：***條件只有一個(gè)鍵值對(duì),最好用上面那個(gè)***
* 而且還只能是字符串之類
*/
- (BOOL)DHUpdateWithDB:(FMDatabase *)db table:(NSString *)table
setKeyValue:(NSDictionary *)keyValue
condition:(NSDictionary *)condition
{
BOOL isSuccess;
NSArray *keys = [keyValue allKeys];
if ([self isOpenDatabese:db]) {
NSMutableString *sql = [NSMutableString stringWithFormat:@"update %@ set ",table];
int count = 0;
for (NSString *key in keyValue) {
count ++;
count == keys.count ? [sql appendFormat:@"%@ = '%@' ",key,keyValue[key]] : [sql appendFormat:@"%@ = '%@',",key,keyValue[key]];
}
[sql appendFormat:@"where %@ = '%@'",[condition allKeys][0],[condition allValues][0]];
isSuccess = [db executeUpdate:sql];
}
return isSuccess;
}
#pragma mark - 判斷是否存在
/**
* 判斷 有沒(méi)有 該字段
*/
- (BOOL)DHisExistObjectInDataBase:(FMDatabase *)db fromTable:(NSString *)tableName colunm:(NSString *)colunm identify:(NSString *)identify
{
if ([self isOpenDatabese:db]) {
FMResultSet *rs = [db executeQuery:[NSString stringWithFormat:@"select count(1) from %@ where %@ = '%@'",tableName,colunm,identify]];
int a = 0;
while ([rs next]) {
a = [rs intForColumn:@"count(1)"];
}
return a > 0 ? YES : NO;
}else
return NO;
}
/**
* 判斷有沒(méi)有該字段,多個(gè)條件的
*/
- (BOOL)DHisExistObjectInDataBase:(FMDatabase *)db fromTable:(NSString *)tableName condition:(NSDictionary *)condition {
NSArray *keys = [condition allKeys];
int count = 0;
if ([self isOpenDatabese:db]) {
NSMutableString *sql = [NSMutableString stringWithFormat:@"select count(1) from %@ where ",tableName];
for (NSString *key in condition) {
count ++;
[sql appendString:(count == keys.count ? [NSString stringWithFormat:@"%@ = '%@'",key,condition[key]] : [NSString stringWithFormat:@"%@ = '%@' AND ",key,condition[key]])];
}
FMResultSet *rs = [db executeQuery:sql];
int a = 0;
while ([rs next]) {
a = [rs intForColumn:@"count(1)"];
}
return a > 0 ? YES : NO;
}
return NO;
}
/**
* 判斷表的存在
*/
- (BOOL)DHisExistTable:(NSString *)tableName DataBase:(FMDatabase *)db
{
FMResultSet *rs = [db executeQuery:@"select count(*) as 'count' from sqlite_master where type ='table' and name = ?", tableName];
while ([rs next])
{
// just print out what we've got in a number of formats.
NSInteger count = [rs intForColumn:@"count"];
// NSLog(@"isTableOK %d", count);
if (0 == count)
{
return NO;
}
else
{
return YES;
}
}
return NO;
}
/**
* (獲取)查找最后一行
*/
- (NSArray *)DHLastLineDataBase:(FMDatabase *)db fromTable:(NSString *)tableName colunm:(NSString *)colunm
{
if ([self isOpenDatabese:db]) {
FMResultSet *result = [db executeQuery:[NSString stringWithFormat:@"select %@ from %@ order by %@ desc limit 1",colunm,tableName,colunm]];
return [self getArrWithFMResultSet:result keyTypes:@{colunm:@"text"}];
}else
return nil;
}
// 私有方法
#pragma mark -- CommonMethod 確定類型
-(NSArray *)getArrWithFMResultSet:(FMResultSet *)result keyTypes:(NSDictionary *)keyTypes
{
NSMutableArray *tempArr = [NSMutableArray array];
while ([result next]) {
NSMutableDictionary *tempDic = [NSMutableDictionary dictionary];
for (int i = 0; i < keyTypes.count; i++) {
NSString *key = [keyTypes allKeys][i];
NSString *value = [keyTypes valueForKey:key];
if ([value isEqualToString:@"text"] || [value isEqualToString:@"TEXT"]) {
// 字符串
[tempDic setValue:[result stringForColumn:key] forKey:key];
}else if([value isEqualToString:@"blob"] || [value isEqualToString:@"BLOB"])
{
// 二進(jìn)制對(duì)象
[tempDic setValue:[result dataForColumn:key] forKey:key];
}else if ([value isEqualToString:@"integer"] || [value isEqualToString:@"INTEGER"])
{
// 帶符號(hào)整數(shù)類型
[tempDic setValue:[NSNumber numberWithInt:[result intForColumn:key]]forKey:key];
}else if ([value isEqualToString:@"boolean"] || [value isEqualToString:@"BOOLLEAN"])
{
// BOOL型
[tempDic setValue:[NSNumber numberWithBool:[result boolForColumn:key]] forKey:key];
}else if ([value isEqualToString:@"date"] || [value isEqualToString:@"DATA"])
{
// date
[tempDic setValue:[result dateForColumn:key] forKey:key];
}
}
[tempArr addObject:tempDic];
}
return tempArr;
}
#pragma mark -- 數(shù)據(jù)庫(kù) 是否已經(jīng) 打開(kāi)
-(BOOL)isOpenDatabese:(FMDatabase *)db
{
if (![db open]) {
[db open];
}
return YES;
}
@end
#import "SqliteTool.h"
//數(shù)據(jù)庫(kù) 對(duì)象
FMDatabase * _db; //全局變量
@property (nonatomic, strong) SqliteTool * sqlTool; // 數(shù)據(jù)庫(kù)對(duì)象
//數(shù)據(jù)庫(kù)懶加載
-(SqliteTool *)sqlTool
{
if (!_sqlTool) {
_sqlTool = [SqliteTool shareTool];
_db = [self.sqlTool getDBWithDBName:@"goyohol.sqlite"];// 數(shù)據(jù)庫(kù)獲取
}
return _sqlTool;
}
self.sqltool對(duì)象的使用: (name) 升序 查詢數(shù)據(jù)
NSDictionary * beatProcessTimeDic = [NSDictionary dictionaryWithObjectsAndKeys:@"integer",@"id",@"text",@"name", nil];
//查詢 數(shù)據(jù)庫(kù) 中 表是否存在
BOOL isExist = [self.sqlTool DHisExistTable:@"tab_user" DataBase:_db];
//從數(shù)據(jù)庫(kù) 獲取數(shù)據(jù)
if (isExist) { // 表存在
// 順序 打印出 數(shù)組元素
NSArray * allDataArr = [self.sqlTool SortAllInformationDataBase:_db selectKeyTypes:beatProcessTimeDic fromTable:@"tab_user" colunm:@"name"];
//數(shù)據(jù)庫(kù)( ??name升序 )排序
for (int i = 0; i < allDataArr.count; i++) {
NSLog(@"表存在 ! 第%d個(gè)元素 %@",i,allDataArr[i]);
}
}
打印結(jié)果：
name字符串 升序
NSDictionary * beatProcessTimeDic = [NSDictionary dictionaryWithObjectsAndKeys:@"integer",@"id",@"text",@"name", nil];
//查詢 數(shù)據(jù)庫(kù) 中 表是否存在
BOOL isExist = [self.sqlTool DHisExistTable:@"tab_user" DataBase:_db];
//從數(shù)據(jù)庫(kù) 獲取數(shù)據(jù)
if (isExist) { // 表存在
// 順序 打印出 數(shù)組元素
NSArray * allDataArr = [self.sqlTool SortAllInformationDataBase:_db selectKeyTypes:beatProcessTimeDic fromTable:@"tab_user" colunm:@"id"];
//數(shù)據(jù)庫(kù)( ??id升序 )排序
for (int i = 0; i < allDataArr.count; i++) {
NSLog(@"表存在 ! 第%d個(gè)元素 %@",i,allDataArr[i]);
}
}
打印結(jié)果：
id數(shù)字 升序
本單例類只需要 在 對(duì) 管理的數(shù)據(jù)(增、刪采驻、改审胚、查)操作的地方，根據(jù)單例類對(duì)象 調(diào)用相應(yīng)的方法就好礼旅！
數(shù)據(jù)庫(kù)路徑可自由放置：我就沒(méi)創(chuàng)建數(shù)據(jù)庫(kù)了膳叨，直接把之前路徑(“Documents”)里面的數(shù)據(jù)庫(kù) copy了一份 在“Library”路徑下面~~
數(shù)據(jù)庫(kù)新路徑
當(dāng)然我也只是列出了大部分需要的存儲(chǔ)功能，如果有針對(duì)的可以直接在這個(gè)單例類里面封裝好自己所需數(shù)據(jù)處理操作(增痘系、刪菲嘴、改、查) 對(duì)應(yīng)的方法形式汰翠！
或者再根據(jù)自己存儲(chǔ)數(shù)據(jù)對(duì)應(yīng)的類型龄坪，添加一個(gè)(或“多個(gè)”)接口來(lái)實(shí)現(xiàn)存儲(chǔ)也可以（更加 直觀明了）。
封裝出來(lái)之后复唤，調(diào)用只需要一個(gè)確定了文件路徑的單例類健田！操作簡(jiǎn)單便捷！苟穆！
而且更重要的是以OC的形式 來(lái)操作數(shù)據(jù)庫(kù)了抄课，不像使用FMDatabase對(duì)象來(lái) 單獨(dú)執(zhí)行SQL語(yǔ)句 那么容易出錯(cuò)。
