iOS对sqlite3进行封装

sqlite3是轻量级的数据库, 多用于移动端, 由于sqlite3多是C语言函数, 操作起来比较麻烦, 下面进行封装

demo地址:https://github.com/NieYinlong/DataBaseDemo.git

首先创建数据库单例类 DataBaseHandle

////  DataBaseHandle.m//  DataBaseDemo////  Created by 聂银龙 on 2017/7/28.//  Copyright © 2017年 nieyinlong. All rights reserved.//#import "DataBaseHandle.h"static DataBaseHandle *_instance;@implementation DataBaseHandle+ (instancetype)shareInstance {        static dispatch_once_t onceToken;    dispatch_once(&onceToken, ^{        _instance = [[DataBaseHandle alloc] init];        });    return _instance;}+ (instancetype)allocWithZone:(struct _NSZone *)zone {    static dispatch_once_t onceToken;    dispatch_once(&onceToken, ^{        _instance = [super  allocWithZone:zone];    });    return _instance;}- (id)copyWithZone:(NSZone *)zone{    return _instance;}#pragma mark ---关闭数据库(链接)-(void)closeDB{        int ret = sqlite3_close(_db);    if (ret == SQLITE_OK) {        printf("关闭成功");        _db = NULL;    } else {        printf("关闭失败");    }    }// sqlite3 * 表示咱们的数据库- (sqlite3 *)openDB{    if (_db != nil) {        return _db;    }        NSString *docuPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject;        // 数据库路径    NSString *db_suffix = [NSString stringWithFormat:@"%@.db", @"YLDataBase"];        NSString *dbPath = [docuPath stringByAppendingPathComponent:db_suffix];        int result = sqlite3_open(dbPath.UTF8String, &_db);    if (result == SQLITE_OK) {        NSLog(@"创建(链接)数据库成功! %d = %@",result, dbPath);    } else {        NSLog(@"创建(链接)数据库失败! %d",result);    }    return _db;}#pragma mark ---创建数据库中的表(table)-(void)creatTableWithSql:(NSString *)sql {     [self openDB];        char *errmsg = NULL;    int ret = sqlite3_exec(_db, sql.UTF8String, NULL, NULL, &errmsg);        if (ret == SQLITE_OK) {        NSLog(@"创建表成功");            }else {                printf("创建表失败: %s", errmsg);    }    }#pragma mark - 向指定表中插入数据- (void)insertDataSql:(NSString *)sql {    [self openDB];    char *errmsg = NULL;        int result = sqlite3_exec(_db, sql.UTF8String, NULL, NULL, &errmsg);        if (result == SQLITE_OK) {                NSLog(@"插入成功");            } else {                NSLog(@"插入失败");            }    }#pragma mark -  删除某表中的一条数据- (void)deleteDataSql:(NSString *)sql {    [self openDB];    char *errmsg = NULL;        int result = sqlite3_exec(_db, sql.UTF8String, NULL, NULL, &errmsg);        if (result == SQLITE_OK) {                NSLog(@"删除成功");            } else {                NSLog(@"删除失败");    }}#pragma mark -更新数据 - (void)updateDataSql:(NSString *)sql {            [self openDB];    char *errmsg = NULL;        int result = sqlite3_exec(_db, sql.UTF8String, NULL, NULL, &errmsg);        if (result == SQLITE_OK) {                NSLog(@"更新成功");            } else {                NSLog(@"更新失败 ");    }}#pragma mark -  查询- (id)selectDataSql:(NSString *)sql {    NSMutableArray *arr = [NSMutableArray array];        [self openDB];    // 查询的跟随指针    sqlite3_stmt *stmt;        int ret = sqlite3_prepare(_db, sql.UTF8String, -1, &stmt, NULL);    if (ret == SQLITE_OK) {        NSLog(@"查询成功");                while (sqlite3_step(stmt) == SQLITE_ROW) {                                            // 一条数据中 第0个字段            NSString *v0 = [NSString stringWithUTF8String:( char *)sqlite3_column_text(stmt, 0)];            //  第1个字段            NSString *v1 = [NSString stringWithUTF8String:( char *)sqlite3_column_text(stmt, 1)];                        //  第2个字段            NSString *v2 = [NSString stringWithUTF8String:( char *)sqlite3_column_text(stmt, 2)];            //  第3个字段            NSString *v3 = [NSString stringWithUTF8String:( char *)sqlite3_column_text(stmt, 3)];                              // 打印所有数据            NSLog(@"=== %@  %@  %@  %@", v0, v1, v2, v3);                        NSDictionary *dic = @{@"stuID" : v0,                                  @"name"  : v1,                                  @"age"   : v2,                                  @"weight": v3};                        [arr addObject:dic];                                }            } else {        NSLog(@"查询失败");    }    return arr;}@end

在ViewController使用

////  ViewController.m//  DataBaseDemo////  Created by 聂银龙 on 2017/7/28.//  Copyright © 2017年 nieyinlong. All rights reserved.//#import "ViewController.h"#import "DataBaseHandle.h"#import <sqlite3.h>@interface ViewController ()@property(nonatomic, strong) DataBaseHandle *dbHandle;@end@implementation ViewController- (void)viewDidLoad {    [super viewDidLoad];       _dbHandle = [DataBaseHandle shareInstance];    }- (IBAction)actionCreateTable:(UIButton *)sender {    // 创建学生表    NSString *sql1 = @"CREATE TABLE  IF NOT EXISTS students (stuID text paimary key not NULL, name TEXT, age INTEGER, weight INTEGER);";    [_dbHandle creatTableWithSql:sql1];}- (IBAction)actionInsert:(UIButton *)sender {        // 向表中循环插入数据    for (int i = 0; i < 5; i++) {                NSString *sql2 = [NSString stringWithFormat:@"INSERT INTO students (stuID, name, age, weight) values ('%d', '张飞%d号', '%d', '%d')",  i,  i, 20 + i, 60 + i];                [_dbHandle insertDataSql:sql2];    }}- (IBAction)actionDelete:(UIButton *)sender {        // 删除    NSString *sql3 = @"delete from students where name = '张飞2号' ";    [_dbHandle deleteDataSql:sql3];        }- (IBAction)actionUpdate:(UIButton *)sender {        NSString *sql = @"update  students set age = 1000 , name = '诸葛亮' where name = '张飞1号' ";    [_dbHandle updateDataSql:sql];}- (IBAction)actionQuery:(UIButton *)sender {        NSString *sql = @"select * from students";    id v = [_dbHandle selectDataSql:sql];    NSLog(@"查询结果