项目中需要使用Object-C来操作SQLite3,一开始想到使用第三方框架FMDB,但仅仅只是对几张简单表的增删改,导入第三方框架觉得臃肿,因此还是自己写了个简单的封装类。

一、暴露一些常用的SQLite操作接口

  1. openDB 打开一个数据库连接
  2. exec执行一条SQL
  3. query查询结果集
  4. queryFirst查询第一个结果
  5. count返回统计类数据,例如COUNT(1),SUM(number)
  6. 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简单封装
Tagged on:

发表评论