基于 iOS SQLite 增删改查基本方法的封装---通用式数据库访问类

最新推荐文章于 2018-12-20 09:53:14 发布

峰宇无阻2016

最新推荐文章于 2018-12-20 09:53:14 发布

阅读量1.4k

点赞数 5

分类专栏： ios 文章标签：数据库 ios sqlite 封装 CURD

本文链接：https://blog.csdn.net/wdgfnhui/article/details/50755385

版权

ios 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

 1.本文使用iOS 对sqlite的基本操作进行封装，可以自动生成拼接SQL语句，自动绑定数据并执行，简化了数据库的四项主要操作，减少代码量。

 首先看下使用样例，新建一个单视图工程，引入所需头文件"WDGDatabaseTool.h"（所需文件见附件），注意添加libsqlite3.0.tbd到工程中。

//
//  ViewController.m
//  WDGSqliteTool
//
//  Created by Wdgfnhui on 16/2/26.
//  Copyright © 2016年 Wdgfnhui. All rights reserved.
//

#import "ViewController.h"
#import "WDGDatabaseTool.h"

@interface ViewController ()

@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];



    //创建表（只需要调用一次就行啦，如果表已经存在可以不调用）
    [WDGDatabaseTool creatTableWithSQL:@"CREATE TABLE if not exists student (id integer PRIMARY KEY AUTOINCREMENT,name text NOT NULL,age integer)"];

    //根据表名获取一个WDGDatabaseTool的对象
    WDGDatabaseTool *studentHandler = [WDGDatabaseTool DBManageWithTableName:@"student"];
    //打开数据库
    [studentHandler openDatabase];

    //用字典插入一行数据 注意，每次运行程序都会插入数据
    [studentHandler insertDataWithDictionary:@{@"name" : @"lisi", @"age" : @(20)}];
    //用字典插入一行数据...
    [studentHandler insertDataWithDictionary:@{@"name" : @"lisi", @"age" : @(19)}];
    [studentHandler insertDataWithDictionary:@{@"name" : @"lisi", @"age" : @(18)}];
    [studentHandler insertDataWithDictionary:@{@"name" : @"lisi", @"age" : @(17)}];
    [studentHandler insertDataWithDictionary:@{@"name" : @"lisi", @"age" : @(16)}];
    [studentHandler insertDataWithDictionary:@{@"name" : @"zhangsan", @"age" : @(18)}];
    [studentHandler insertDataWithDictionary:@{@"name" : @"zhangsan", @"age" : @(17)}];
    [studentHandler insertDataWithDictionary:@{@"name" : @"zhangsan", @"age" : @(16)}];


    [studentHandler insertDataWithDictionary:@{@"name" : @"wangwu", @"age" : @(16)}];

    //生成一个where条件
    WDGWhereCondition *selectCondition = [WDGWhereCondition conditionWithColumnName:@"name" Operator:@"=" Value:@"lisi"];
    //增加复合条件
    [selectCondition addWhereConditionWithRelation:@"and" ColumnName:@"age" Operator:@"=" Value:@(18)];
    //根据条件查询，获取查询结果，用数组接收
    NSArray *selectResult = [studentHandler selectDataWithWhereCondition:selectCondition];
    NSLog(@"%@", selectResult);


    //生成一个where条件
    WDGWhereCondition *deleteCondition = [WDGWhereCondition conditionWithColumnName:@"name" Operator:@"=" Value:@"wangwu"];
    //根据条件删除
    if ([studentHandler deleteDataWithWhereCondition:deleteCondition])
        NSLog(@"删除成功");
    else
        NSLog(@"删除失败");


    //生成一个where条件
    WDGWhereCondition *updateCondition = [WDGWhereCondition conditionWithColumnName:@"name" Operator:@"=" Value:@"lisi"];
    //根据条件和给定数据更新
    if ([studentHandler updateDataWithNewData:@{@"name" : @"lisi2", @"age" : @(19)} WhereCondition:updateCondition])
        NSLog(@"修改成功");
    else
        NSLog(@"修改失败");


    //查询所有数据
    NSLog(@"%@", [studentHandler selectAllData]);


    /****给查询加排序等操作******/
    //按照姓名降序排序并且按照年龄升序排序
    [studentHandler orderWithDictionary:@{@"name" : @"desc", @"age" : @"asc"}];
    //只获取前五行数据
    [studentHandler limitForRows:5];

//    //从第2行开始获取5行数据
//    [studentHandler limitAtIndex:2 Rows:5];
    NSLog(@"%@", [studentHandler selectAllData]);//按照上面的排序和行数现在查询
    /***查询完成，order条件和limit条件只对紧跟着的查询生效***/



    //上面的可以简化为连贯操作
    NSLog(@"%@", [[[studentHandler orderWithDictionary:@{@"name" : @"desc"}] limitAtIndex:0 Rows:5] selectAllData]);


    [studentHandler selectWithFields:@[@"name"] Condition:nil];//只查询姓名列


    [studentHandler closeDatabase];//注意用完关闭数据库






    // Do any additional setup after loading the view, typically from a nib.
}

- (void)didReceiveMemoryWarning {
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}

@end

这里已增加为例子说下对应方法的实现

- (BOOL)insertDataWithDictionary:(NSDictionary *)dataDic {//需要给字典参数
    NSArray *allKeys = [dataDic allKeys];
    NSMutableString *sql = [NSMutableString stringWithFormat:@"insert into %@(", self.tableName];//开始准备SQL语句
    long lessOneCount = allKeys.count - 1;
    int p = 0;
    for (p = 0; p < lessOneCount; p++) {
        [sql appendFormat:@"%@,", allKeys[p]];//根据传入参数进行SQL语句拼接
    }
    [sql appendFormat:@"%@) values(", allKeys[p]];
    for (p = 0; p < lessOneCount; p++) {
        [sql appendFormat:@"?,"];
    }
    [sql appendString:@"?)"];
    STMT *stmt = NULL;
    int result = sqlite3_prepare(db, sql.UTF8String, -1, &stmt, NULL);
    if (result == OK) {
        for (int i = 0; i < allKeys.count; i++) {
            [self bindDataWithSTMT:stmt Order:i + 1 Value:dataDic[allKeys[i]] Type:_tableStuct[allKeys[i]][0]];//根据传入数据进行数据绑定
        }
        BOOL flag = sqlite3_step(stmt) == DONE;
        sqlite3_finalize(stmt);
        return flag;
    }
    sqlite3_finalize(stmt);
    return false;
}