iOS sqllite封装,不用写SQL语句才是封装的目的.

#import <Foundation/Foundation.h>

NS_ASSUME_NONNULL_BEGIN

@class SQTool;
@class Cloumn;

typedef enum : NSUInteger
{
    Typeinteger,
    Typetext,
    Typereal,
} Type;

typedef void(^CreatTable)(NSString *tableName,NSArray*arr);
typedef void(^CreatTableNew)(NSString *name,NSArray<Cloumn *>*arr);
typedef void(^InsertInto)(NSString *tableName,NSArray *key,NSArray *valus);



// delete from team where stu_id=3
typedef void(^Delete)(NSString *tableName,NSDictionary *dic);


// update team setstu_name='zl',stu_password='zl123',stu_login='zhangsan' where stu_id=4
typedef void(^Update)(NSString *tableName ,NSDictionary *dicUpdate,NSDictionary *whereData);

typedef SQTool *(^Select)(NSArray *arr);
typedef SQTool *(^From)(NSString *str);
typedef SQTool *(^Where)(id obj);
typedef SQTool *(^Log)(void);

@interface SQTool : NSObject

@property (nonatomic, strong) NSString  *sql;
@property (nonatomic, copy) CreatTable creatTable;
@property (nonatomic, copy) CreatTableNew creatTableNew;
@property (nonatomic, copy) Select select;
@property (nonatomic, copy) From from;
@property (nonatomic, copy) Where where;
@property (nonatomic, copy) Log log;

@property (nonatomic, copy) InsertInto insertInto;
@property (nonatomic, copy) Delete deleteSql;
@property (nonatomic, copy) Update updateSql;

/**
 单例初始化方法 可以init方法
 */
+(instancetype)shareInstance;

/**  打开数据并创建表  */
-(BOOL)openDB;

/**
 关闭数据路
 */
-(void)closeDB;

/**
 增
 */
- (void)insertData:(NSArray *)sqlarr;

/**
 删
 */
-(void)deleteSql:(NSString *)sqlstr;

/**
 改
 @param sqlstr sql语句
 @return <#return value description#>
 */
-(BOOL)updateSql:(NSString *)sqlstr;

/**
 查
 @param SQL  SQL查询语句
 @return 返回查询结果 包装成数组
 */
-(NSArray *)querySQL:(NSString *)SQL;

/**
 数据库添加字段 字段存在会添加失败（没有判断表中是否存在此字段 用的不多所以没做）
 @param tableName <#tableName description#>
 @param cloumn <#cloumn description#>
 */
-(void)alterColum:(NSString *)tableName cloumn:(Cloumn *)cloumn;



@end


@interface Cloumn : NSObject

/**
 字段名
 */
@property (nonatomic, strong) NSString *name;

/**
 数据存储类型
 */
@property (nonatomic, assign) Type type;


/**
 跟新数据库添加字段默认值
 */
@property (nonatomic, strong) NSString *value;

/**
 非空自增
 */
@property (nonatomic, assign) BOOL isAUTOINCREMENT;


/**
 初始化方法
 @param name 列的标题 名字 性别 年龄
 @param type 类型 整型 字符型等
 @return 返回sql单个字段模型
 */
-(instancetype)initWitNmae:(NSString *)name type:(Type)type;

@end

NS_ASSUME_NONNULL_END

#import "SQTool.h"
#import <sqlite3.h>

#define selectDefine @"select"
#define whereDefine  @"where"
#define fromDefine   @"from"

#define deleteDefine   @"delete"

#define deleteDefine   @"delete"



#define  _NOT_NULL_PRIMARY_KEY_AUTOINCREMENT @" NOT NULL PRIMARY KEY AUTOINCREMENT"
#define create_table_if_not_exists @"create table if not exists"
#define INSERT_INTO @"INSERT INTO"
#define VALUES @"VALUES"


#define SqlTableArrName @"SqlTableArrName"

/**
 * SQL语句常用操作
 增、删、改、查,CRUD,Create[新建], Retrieve[检索], Update[更新], Delete[删除]。
 
 * SQL语法写法特点
 1、不区分大小写（CREATE = create）
 2、每条语句以分号（;）结尾
 3、关键字建议大写
 
 * SQL语句常用关键字
 select、insert、update、delete、from、create、where、desc、order、by、group、table、alter、view、index等
 数据定义语句（DDL）：包括create和drop等操作，在数据库中创建新表或删除表（create table或 drop table）
 数据操作语句（DML）：包括insert、update、delete等操作，分别用于添加、修改、删除表中的数据
 数据查询语句（DQL）：可以用于查询获得表中的数据，关键字select是DQL（也是所有SQL）用得最多的操作，其他DQL常用的关键字有where，order by，group by和having
 
 */

@interface SQTool ()

@property (nonatomic,assign) sqlite3 *db;

@end


@implementation SQTool

static SQTool *instance;
+(instancetype)shareInstance{
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        instance = [[self alloc] init];
    });
    return instance;
}

#pragma mark ————————— sql语句 —————————————

/**
 create table 表名 (字段名1 字段类型1, 字段名2 字段类型2, …) ;
 create table if not exists 表名 (字段名1 字段类型1, 字段名2 字段类型2, …) ;
 create table t_student (id integer, name text, age integer, score real) ;
 @return <#return value description#>
 */
-(CreatTable)creatTable
{
    return ^(NSString *name,NSArray *arr)
    {
        if (name) {
            
            self.sql = [NSString string];
            
            if (arr.count > 0)
            {
                self.sql = [NSString stringWithFormat:@"%@ %@ (%@)",create_table_if_not_exists, name,[arr componentsJoinedByString:@","]];
            }
            
            [self saveTableSqlWithName:name sql:self.sql];
        }
        
    };
}

#pragma mark ————————— 类型转换 —————————————
-(NSString *)stringFromType:(Type)type
{
    NSString *typeStr;
    switch (type) {
        case Typetext:
        {
            typeStr = @"text";
        }
            break;
        case Typeinteger:
        {
            typeStr = @"integer";
        }
            break;
        case Typereal:
        {
            typeStr = @"real";
        }
            break;
            
        default:
            break;
    }
    return typeStr;
}


/**
 create table 表名 (字段名1 字段类型1, 字段名2 字段类型2, …) ;
 create table if not exists 表名 (字段名1 字段类型1, 字段名2 字段类型2, …) ;
 create table t_student (id integer, name text, age integer, score real) ;
 @return <#return value description#>
 */
-(CreatTableNew)creatTableNew
{
    return ^(NSString *name,NSArray *arr)
    {
        if (name)
        {
            self.sql = [NSString string];
            
            if (arr.count > 0)
            {
                NSString *all=[NSString string];
                for (Cloumn *cl in arr)
                {
                    NSString * str = [NSString stringWithFormat:@"%@ %@",cl.name,[self stringFromType:cl.type]];
                    
                    if (cl.isAUTOINCREMENT == YES)
                    {
                        str = [str stringByAppendingString:_NOT_NULL_PRIMARY_KEY_AUTOINCREMENT];
                    }
                    
                    if (all.length > 0)
                    {
                        all = [all stringByAppendingString:@","];
                    }
                    
                    all = [all stringByAppendingString:str];
                }
                self.sql = [NSString stringWithFormat:@"%@ %@ (%@)",create_table_if_not_exists,name,all];
            }
            
        }
        [self saveTableSqlWithName:name sql:self.sql];
        
        [self openDB];
        [self closeDB];
    };
}

#pragma mark ————————— 保存建表语句 —————————————
-(void)saveTableSqlWithName:(NSString *)name sql:(NSString *)sql
{
    NSUserDefaults *user = [NSUserDefaults standardUserDefaults];
    NSMutableDictionary *arrName = [NSMutableDictionary dictionary];
    if ([user objectForKey:SqlTableArrName])
    {
        NSDictionary *dic  = [user objectForKey:SqlTableArrName];
        arrName = dic.mutableCopy;
    }
    
    if (sql)
    {
        [arrName setObject:sql forKey:name];
    }
    [user setObject:arrName forKey:SqlTableArrName];
    [user synchronize];
}

-(Select)select
{
    return ^(NSArray *name)
    {
        if (name.count > 0)
        {
            self.sql = [NSString stringWithFormat:@"%@ %@", selectDefine,[name componentsJoinedByString:@","]];
        }
        else
        {
            self.sql = [NSString stringWithFormat:@"%@ *",selectDefine];
        }
        return self;
    };
}

-(From)from
{
    return ^(NSString * str)
    {
        NSString *fromStr = [NSString stringWithFormat:@" %@ %@", fromDefine,str];
        self.sql = [self.sql stringByAppendingString:fromStr];
        return self;
    };
}

-(Where)where
{
    return ^(id obj)
    {
        NSString *str = [NSString string];
        if ([obj isKindOfClass:[NSDictionary class]])
        {
            NSDictionary *dic = obj;
            NSArray *arr = [dic allKeys];
            for (int i = 0; i <arr.count ; i++)
            {
                NSString *key = arr[i];
                NSString *value = [dic valueForKey:key];
                NSString *last  = [NSString stringWithFormat:@"%@ = '%@'",key,value];
                if (str.length > 0)
                {
                    last = [@" and " stringByAppendingString:last];
                }
                str = [str stringByAppendingString:last];
            }
            
        }
        else
        {
           str = [NSString stringWithFormat:@"%@",obj];
        }

        NSString *whereStr = [NSString stringWithFormat:@" %@ %@", whereDefine,str];
        self.sql = [self.sql stringByAppendingString:whereStr];
        return self;
    };
}

-(Log)log
{
    return ^()
    {
        NSLog(@"%@",self.sql);
        return self;
    };
}

#pragma mark ————————— 插入语句 —————————————
- (InsertInto)insertInto
{
    return ^(NSString *name, NSArray *key ,NSArray *values)
    {
        NSString *strm  =[NSString string];
        
        for (NSString * str  in values)
        {
            NSString *s = [NSString stringWithFormat:@"'%@'",str];
            if (strm.length > 0)
            {
                s = [@"," stringByAppendingString:s];
            }
            strm = [strm stringByAppendingString:s];
        }
        self.sql = [NSString stringWithFormat:@"%@ %@ (%@) %@ (%@);",INSERT_INTO,name,[key componentsJoinedByString:@","],VALUES,strm];
    };
}

#pragma mark ————————— 增 —————————————
- (void)insertData:(NSArray *)sqlarr
{
    [self openDB];
    //1.创建插入数据的sql语句（多条）
    NSMutableString * mstr = [NSMutableString string];
    for (NSString *str  in sqlarr)
    {
        [mstr appendString:str];
    }
    //2.执行sql语句
    int ret = sqlite3_exec(_db, mstr.UTF8String, NULL, NULL, NULL);
    //3.判断执行结果
    if (ret == SQLITE_OK)
    {
        NSLog(@"插入成功");
    }
    else
    {
        NSLog(@"插入失败");
    }
    [self closeDB];
}



#pragma mark ————————— 删除语句 —————————————
-(Delete)deleteSql
{
    return ^(NSString *name, NSDictionary *dic)
    {
        NSString *strm  = [NSString string];
        NSArray *arr = [dic allKeys];
        for (int i = 0; i< arr.count; i++)
        {
            NSString *key = arr[i];
            NSString *valeu = [dic objectForKey:key];
            NSString *s = [NSString stringWithFormat:@"%@ = '%@'",key,valeu];
            if (strm.length > 0)
            {
                s = [@" and " stringByAppendingString:s];
            }
            strm = [strm stringByAppendingString:s];
        }
        // @"delete from mqttlist where uid = '%@' and indexCount = '0'"
      self.sql =  [NSString stringWithFormat:@"delete from %@ where %@",name,strm];
    };
}

#pragma mark ————————— 删 —————————————
-(void)deleteSql:(NSString *)sqlstr
{
    [self openDB];
    // sql语句（多条）
    //    NSMutableString * mstr = [NSMutableString string];
    //    for (NSString *str  in sqlarr)
    //    {
    //        [mstr appendString:str];
    //    }
    sqlite3_stmt *stmt;
    int rst = sqlite3_prepare_v2(_db, sqlstr.UTF8String, -1, &stmt, NULL);
    
    // 判断是否删除成功
    if (rst==SQLITE_OK)
    {
        if (SQLITE_DONE == sqlite3_step(stmt))
        {
            NSLog(@" delete ok");
        }else
        {
            NSLog(@"delete fail");
        }
    }
    else
    {
       NSLog(@"delete fail");
    }
    [self closeDB];
}

#pragma mark ————————— 改语句 —————————————
-(Update)updateSql
{
    return ^(NSString *tableName ,NSDictionary *updateDic,NSDictionary *whereDic)
    {
        NSString *strm  = [NSString string];
        NSArray *arr = [whereDic allKeys];
        for (int i = 0; i< arr.count; i++)
        {
            NSString *key = arr[i];
            NSString *valeu = [whereDic objectForKey:key];
            
            NSString *s = [NSString stringWithFormat:@"%@ = '%@'",key,valeu];
            if (strm.length > 0)
            {
                s = [@" and " stringByAppendingString:s];
            }
            strm = [strm stringByAppendingString:s];
        }
        
        NSString *upstr = [NSString string];
        NSArray *upArr = [updateDic allKeys];
        
        
        for (int i = 0; i< upArr.count; i++)
        {
            NSString *key = upArr[i];
            NSString *valeu = [updateDic objectForKey:key];
            
            NSString *s = [NSString stringWithFormat:@"%@ = '%@'",key,valeu];
            if (upstr.length > 0)
            {
                s = [@"," stringByAppendingString:s];
            }
            upstr = [upstr stringByAppendingString:s];
        }
        // update team set stu_name='zl',stu_password='zl123',stu_login='zhangsan' where stu_id=4
        self.sql =  [NSString stringWithFormat:@"update %@ set %@ where %@",tableName,upstr,strm];
        
    };
}

#pragma mark ————————— 改 —————————————
// "update team setstu_name='zl',stu_password='zl123',stu_login='zhangsan' where stu_id=4"
-(BOOL)updateSql:(NSString *)sqlstr
{
    [self openDB];
    // sql语句（多条）
    //    NSMutableString * mstr = [NSMutableString string];
    //    for (NSString *str  in sqlarr)
    //    {
    //        [mstr appendString:str];
    //    }
    sqlite3_stmt *stmt;
    int rst = sqlite3_prepare_v2(_db, sqlstr.UTF8String, -1, &stmt, nil);
    // 判断是否修改成功
    if (rst == SQLITE_OK)
    {
        if (SQLITE_DONE == sqlite3_step(stmt))
        {
            NSLog(@"update ok");
            return YES;
        }
        else
        {
            NSLog(@"update fail = %d",rst);
            return NO;
        }
    }
    else
    {
        NSLog(@"update fail = %d",rst);
        return NO;
    }
}


#pragma mark ————————— 查 —————————————
-(NSArray *)querySQL:(NSString *)SQL
{
    [self openDB];
    
    //准备查询
    // 1> 参数一:数据库对象
    // 2> 参数二:查询语句
    // 3> 参数三:查询语句的长度:-1
    // 4> 参数四:句柄(游标对象)
    //    sqlite3_prepare_v2(<#sqlite3 *db#>, <#const char *zSql#>, <#int nByte#>, <#sqlite3_stmt **ppStmt#>, <#const char **pzTail#>)
    sqlite3_stmt *stmt = nil;
    if (sqlite3_prepare_v2(self.db, SQL.UTF8String, -1, &stmt, nil) != SQLITE_OK)
    {
        NSLog(@"准备查询失败!");
        return NULL;
    }
    //准备成功,开始查询数据
    //定义一个存放数据字典的可变数组
    NSMutableArray *dictArrM = [[NSMutableArray alloc] init];
    while (sqlite3_step(stmt) == SQLITE_ROW)
    {
        //一共获取表中所有列数(字段数)
        int columnCount = sqlite3_column_count(stmt);
        //定义存放字段数据的字典
        NSMutableDictionary *dict = [[NSMutableDictionary alloc] init];
        for (int i = 0; i < columnCount; i++)
        {
            // 取出i位置列的字段名,作为字典的键key
            const char *cKey = sqlite3_column_name(stmt, i);
            NSString *key = [NSString stringWithUTF8String:cKey];
            
            //取出i位置存储的值,作为字典的值value
            const char *cValue = (const char *)sqlite3_column_text(stmt, i);
            NSString *value;
            if (cValue)
            {
                value = [NSString stringWithUTF8String:cValue];
            }
            else
            {
                value = [NSString stringWithFormat:@"nil"];
            }
            
            //将此行数据 中此字段中key和value包装成 字典
            [dict setObject:value forKey:key];

        }
        
        [dictArrM addObject:dict];
        
    }
    return dictArrM;
}


-(void)alterColum:(NSString *)tableName cloumn:(Cloumn *)cloumn
{
    
    //在已经创建好的表格中插入add字段的SQ语句
    NSString *addStr = [NSString stringWithFormat:@"ALTER TABLE %@ ADD COLUMN '%@' %@ default %@",tableName,cloumn.name,[self stringFromType:cloumn.type],cloumn.value];
    
    sqlite3_stmt *stmt;
    int rst = sqlite3_prepare_v2(_db, addStr.UTF8String, -1, &stmt, nil);
    // 判断是否修改成功
    if (rst == SQLITE_OK)
    {
        if (SQLITE_DONE == sqlite3_step(stmt))
        {
            NSLog(@"alter ok");
        }
        else
        {
            NSLog(@"alter fail = %d",rst);
        }
    }
    else
    {
        NSLog(@"alter fail = %d",rst);
    }
// 增加表字段ALTERTABLE表名ADDCOLUMN字段名字段类型删除表字段ALTERTABLE表名DROPCOLUMN字段名修改表字段ALTERTABLE表名RENAMECOLUMN旧字段名TO新字段名 
}

#pragma mark -
#pragma mark ————————— 数据库擦做 —————————————
-(BOOL)openDB
{
    //app内数据库文件存放路径-一般存放在沙盒中
    NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *DBPath = [documentPath stringByAppendingPathComponent:@"appDB.sqlite"];
    //创建(指定路径不存在数据库文件)/打开(已存在数据库文件) 数据库文件
    //sqlite3_open(<#const char *filename#>, <#sqlite3 **ppDb#>)  filename:数据库路径  ppDb:数据库对象
    if (sqlite3_open(DBPath.UTF8String, &_db) != SQLITE_OK)
    {
        NSLog(@"数据库打开失败");
        return NO;
    }
    else
    {
        NSLog(@"打开成功创建表");
        return [self creatTablesl];
    }
}

#pragma mark ————————— 建表sql语句  —————————————
-(BOOL)creatTablesl
{
    NSUserDefaults *user = [NSUserDefaults standardUserDefaults];
    NSDictionary *dic = [user objectForKey:SqlTableArrName];
    NSArray * SQL_ARR = [dic allValues];
    return [self creatTableExecSQL:SQL_ARR];
}

#pragma mark ————————— 便利数组创建表 —————————————
-(BOOL)creatTableExecSQL:(NSArray *)SQL_ARR
{
    for (NSString *SQL in SQL_ARR)
    {
        if (![self execSQL:SQL])
        {
            return NO;
        }
    }
    return YES;
}

#pragma mark ————————— 执行建表 SQL 语句 —————————————
-(BOOL)execSQL:(NSString *)SQL
{
    char *error;
    if (sqlite3_exec(self.db, SQL.UTF8String, nil, nil, &error) == SQLITE_OK)
    {
        NSLog(@"创建表成功");
        return YES;
    }
    else
    {
        NSLog(@"SQL语句出错:%s",error);
        return NO;
    }
}

#pragma mark 关闭数据库
- (void)closeDB
{
    int result = sqlite3_close(_db);
    if (result == SQLITE_OK)
    {
        NSLog(@"数据库关闭成功");
        _db = nil;
    }
    else
    {
        NSLog(@"数据库关闭失败");
    }
}

@end


@implementation Cloumn

-(instancetype)initWitNmae:(NSString *)name type:(Type)type;
{
    self = [super init];
    if (self)
    {
        self.name = name;
        self.type = type;
        self.isAUTOINCREMENT = NO;
    }
    return self;
}

@end

// 调用
#import "ViewController.h"
#import "SQTool.h"

@interface ViewController ()


@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    // Do any additional setup after loading the view, typically from a nib.
}

- (void)touchesBegan:(NSSet<UITouch *> *)touches withEvent:(UIEvent *)event
{
    SQTool *sq3 = [[SQTool alloc]init];
    
    // 表名
    NSString *tableName = @"Student";
    
    // 建表添加的字段模型如下
    Cloumn *cloumn  = [[Cloumn alloc]initWitNmae:@"id" type:(Typeinteger)];
    // id 为自增
    cloumn.isAUTOINCREMENT = YES;
    Cloumn *cloumn2  = [[Cloumn alloc]initWitNmae:@"name" type:(Typetext)];
    Cloumn *cloumn3  = [[Cloumn alloc]initWitNmae:@"time" type:(Typetext)];
    
    // 传入模型创建表
    sq3.creatTableNew(tableName, @[cloumn,cloumn2,cloumn3]);
    
    //-----------增---------------
    
    NSDictionary *dic = @{@"name":@"joHasns",@"time":[NSDate date]};
    // 插入数据t单条 每调用一侧创建一条 insertInto 创建的sql语句 存在 sq3.sql 里面
    sq3.insertInto(tableName, [dic allKeys], [dic allValues]);
    
    //  insertData  支持多条插入
    //    [sq3 insertData:@[sq3.sql]];
    
    //------------删--------------
    NSDictionary *dic2 = @{@"id":@"10", @"name":@"joHasns"};
    sq3.deleteSql(tableName, dic2);
    [sq3 deleteSql:sq3.sql];
    
    //--------------改------------
    NSDictionary *upDic = @{@"name":@"ABCD"};
    NSDictionary *whereDic = @{@"id":@"9"};
    sq3.updateSql(tableName, upDic, whereDic);
    [sq3 updateSql:sq3.sql];

    //------------更新数据库 添加字段--------------
    Cloumn *cloumn4  = [[Cloumn alloc]initWitNmae:@"age" type:(Typetext)];
    cloumn4.value = @"0";
//    [sq3 alterColum:tableName cloumn:cloumn4];
    
    //------------查--------------
    // 方式 - 每个条件对应一个字典的 key Value（）
    NSDictionary *chaDic = @{@"id":@"1",@"name":@"joHasns"};
    
    // 带条件查找 参数可以是字典
//     sq3.select(@[]).from(tableName).where(chaDic);
    
    // 方式二 字符串
    sq3.select(@[]).from(tableName).where(@"id = '1'");
    
    // 不带条件查找语句
    //    sq3.select(@[]).from(tableName);
    
    // 查找
    NSArray *arr = [sq3 querySQL:sq3.sql];
    NSLog(@"%@",arr );
}
@end