项目中需要使用Object-C来操作SQLite3,一开始想到使用第三方框架FMDB,但仅仅只是对几张简单表的增删改,导入第三方框架觉得臃肿,因此还是自己写了个简单的封装类。
一、暴露一些常用的SQLite操作接口
- openDB 打开一个数据库连接
- exec执行一条SQL
- query查询结果集
- queryFirst查询第一个结果
- count返回统计类数据,例如COUNT(1),SUM(number)
- close关闭链接
二、封装的类使用单例模式
.h文件
#import <Foundation/Foundation.h> #import <sqlite3.h> @interface XTokenSqliteManager : NSObject { sqlite3 *_db; } /** * 获取单例 * @return */ + (instancetype)getInstance; /** * 打开指定的数据库 * @param db * @return * BOOL */ - (BOOL)openDB:(NSString *)db; /** * 执行SQL * @param sql * SQL语句 * @return * BOOL */ - (BOOL)exec:(NSString *)sql; /** * 查询SQL * @param sql * @return */ - (NSArray *)query:(NSString *)sql; /** * 查询第一条 * @param sql * @return */ - (NSDictionary *)queryFirst:(NSString *)sql; /** * 统计 * @param sql * @return */ - (double)count:(NSString *)sql; /** * 关闭链接 * @return */ - (int)close; @end
.m文件
// // Created by farmer on 2018/6/22. // Copyright (c) 2018 HeapStack Technology Co. Ltd. All rights reserved. // #import "XTokenSqliteManager.h" @implementation XTokenSqliteManager { } static XTokenSqliteManager *instance; + (instancetype)getInstance { static dispatch_once_t onceToken; dispatch_once(&onceToken, ^{ instance = [[self alloc] init]; }); return instance; } /** * 打开数据库 * @param db * @return */ - (BOOL)openDB:(NSString *)db { NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject]; NSString *DBPath = [documentPath stringByAppendingPathComponent:db]; NSLog(@"数据库路径%@", DBPath); if (sqlite3_open(DBPath.UTF8String, &_db) != SQLITE_OK) { return NO; } else { return YES; //打开成功创建表 } } /** * 执行SQL * @param sql * SQL语句 * @return * bool */ - (BOOL)exec:(NSString *)sql { return sqlite3_exec(_db, sql.UTF8String, nil, nil, nil) == SQLITE_OK;; } /** * 查询语句 * @param sql * SQL语句 * @return * 结果集 */ - (NSArray *)query:(NSString *)sql { sqlite3_stmt *pstmt; //结果集游标句柄 if (sqlite3_prepare_v2(_db, sql.UTF8String, -1, &pstmt, NULL) != SQLITE_OK) { NSLog(@"执行SQL失败%@", sql); return NULL; }//查询成功 NSMutableArray *dicArr = [[NSMutableArray alloc] init]; while (sqlite3_step(pstmt) == SQLITE_ROW) { //遍历游标 [dicArr addObject:[self stmt2Dict:pstmt]]; } return dicArr; } /** * 查询第一个结果 * @param sql * @return */ - (NSDictionary *)queryFirst:(NSString *)sql { sqlite3_stmt *pstmt; //结果集游标句柄 if (sqlite3_prepare_v2(_db, sql.UTF8String, -1, &pstmt, NULL) != SQLITE_OK) { NSLog(@"执行SQL失败%@", sql); return NULL; }//查询成功 while (sqlite3_step(pstmt) == SQLITE_ROW) { //遍历游标 return [self stmt2Dict:pstmt]; } return nil; } /** * 将 sqlite3_stmt 转成 Dictionary * @param pstmt * sqlite3_stmt 指针 * @return * Dictionary */ - (NSDictionary *)stmt2Dict:(sqlite3_stmt *)pstmt { int columnCount = sqlite3_column_count(pstmt); //获取列数 NSMutableDictionary *dict = [[NSMutableDictionary alloc] init]; //初始化一个Dict存放单行记录 for (int i = 0; i < columnCount; i++) { const char *key = sqlite3_column_name(pstmt, i); //取列名 int type = sqlite3_column_type(pstmt, i); switch (type) { case SQLITE_INTEGER: { int value = sqlite3_column_int(pstmt, i); //整型 dict[[NSString stringWithUTF8String:key]] = @(value); break; } case SQLITE_FLOAT: { double value = sqlite3_column_double(pstmt, i); //浮点型 dict[[NSString stringWithUTF8String:key]] = @(value); break; } case SQLITE_TEXT: default: { const char *value = reinterpret_cast<const char *>(sqlite3_column_text(pstmt, i)); //字符型 dict[[NSString stringWithUTF8String:key]] = [NSString stringWithUTF8String:value]; break; } } } return dict; } /** * 执行统计类型语句,例如 COUNT(1) ,SUM(col),返回第一个记录 * @param sql * SQL语句 * @return * count */ - (double)count:(NSString *)sql { sqlite3_stmt *pstmt; //结果集游标句柄 if (sqlite3_prepare_v2(_db, sql.UTF8String, -1, &pstmt, NULL) != SQLITE_OK) { NSLog(@"执行SQL失败%@", sql); return NULL; } while (sqlite3_step(pstmt) == SQLITE_ROW) { return sqlite3_column_double(pstmt, 0); } return 0; } /** * 关闭链接 * @return */ - (int)close { return sqlite3_close(_db); } @end
这样就封装好了基本的操作,SELECT的结果封装成NSDictionary,用起来也巨简单;下面是一些基本的操作例子。
第一:创建DB
[XTokenSqliteManager.getInstance openDB:@"my.db"]; //初始化数据库
第二、执行创建表语句
[XTokenSqliteManager.getInstance exec:[NSString stringWithUTF8String:"CREATE TABLE IF NOT EXISTS \"param\" (\n" " \"key\" text,\n" " \"value\" text,\n" " PRIMARY KEY (\"key\")\n" ")"]];
第三、执行删除语句
[XTokenSqliteManager.getInstance exec:[NSString stringWithFormat:@"DELETE FROM param WHERE key = '%@'", @"address"]];
第四、执行COUNT语句
double count = [XTokenSqliteManager.getInstance count:@"SELECT COUNT(1) FROM sqlite_master where type='table' and name='nodes'"];
第五、执行SELECT语句
NSString *address = [XTokenSqliteManager.getInstance queryFirst:[NSString stringWithFormat:@"SELECT * FROM param WHERE key = '%@'", @"address"]][@"value"];
当遇到存在多个数据源时候,可以先close再通过openDB打开新的DB进行切换。
当然不要忘记了添加sqlite库
IOS Object-C操作SQLite简单封装