使用iOS原生sqlite3框架对sqlite数据库进行操作(三)-阿里云开发者社区

开发者社区> 珲yy少> 正文

使用iOS原生sqlite3框架对sqlite数据库进行操作(三)

简介: 使用iOS原生sqlite3框架对sqlite数据库进行操作
+关注继续查看

4.数据库操作对象


       将操作数据库的核心方法封装在这个类中:


YHBaseSQLiteContext.h


/**

*操作的数据库名称

*/

@property(nonatomic,strong)NSString * name;

/**

*内含sqlite3 对象

*/

@property(nonatomic,assign)sqlite3 * sqlite3_db;

/**

* @brief 打开一个数据库 不存在则创建

*

* @param path 数据库路径

*

* @return 是否操作成功

*/

-(BOOL)openDataBaeWithName:(NSString *)path;

/**

*  @brief 再数据库中创建一张表 如果已经存在 会返回错误信息

*

*  @param name 表的名称

*

*  @prarm dic 表中的键 其中字典中需传入 键名:类型  类型的宏定义在YHBaseSQLTypeHeader.h中

*

*  @param callBack 结果回调

*/

-(void)createTableWithName:(NSString *)name

           keysDictionary:(NSDictionary<NSString*,NSString*> *) dic

                 callBack:(void (^)(YHBaseSQLError * error))complete;


/**

*  @brief 向表中添加一条数据

*

*  @param dataDic 添加数据的键值对

*

*  @param name 插入表的名称

*

*  @complete 回调

*/

-(void)insertData:(NSDictionary<NSString *,id>*)dataDic

       intoTable:(NSString *)name

        callBack:(void (^)(YHBaseSQLError * error))complete;

/**

*  @brief 向表中添加一个键

*

*  @param kName 添加的键

*

*  @prarm type 类型

*

*  @prarm tableName 表名称

*

*  @prarm complete 结果回调

*/

-(void)addKey:(NSString *)kName

     keyType:(NSString *)type

   intoTable:(NSString *)tableName

    callBack:(void(^)(YHBaseSQLError *error))complete;

/**

*  @brief 修改数据

*

*  @param dataDic 新的键值

*

*  @param wlStr 条件字符串 一般通过主键找到对应数据修改 可以为nil

*

*  @param complete 结果回调

*/

-(void)update:(NSDictionary<NSString*,id> *)dataDic

     inTable:(NSString *)tableName

 whileString:(NSString *)wlStr

    callBack:(void(^)(YHBaseSQLError * error))complete;

/**

*  @brief 删除数据

*

*  @param tableName 表名

*

*  @param wlStr 条件字符串 一般通过主键找到对应数据删除 可以为nil 不传这个参数将删除所有数据

*

*/

-(void)deleteDataFromTable:(NSString *)tableName

              whereString:(NSString *)wlStr

                 callBack:(void(^)(YHBaseSQLError * error))complete;

/**

*  @brief 删除一张表

*

*  @param tableName 表名

*

*/

-(void)dropTable:(NSString *)tableName

       callBack:(void(^)(YHBaseSQLError * error))complete;

/**

*  @brief 查询数据

*

*  @param keys 要查询的键值 及其对应的数据类型 可以为nil则查询全部

*

*  @param tableName 表名

*

*  @param orderKey 进行排序的键值 可以为nil 则不排序

*

*  @param type 排序方式 在YHBaseSQLTypeHeader中有宏定义

*

*  @param wlstr 查询条件 同于查询单个数据

*

*  @param complete dataArray为查询到的数据 其内为字典

*

*/

-(void)selectKeys:(NSArray<NSDictionary *> *)keys

       fromTable:(NSString*)tableName

         orderBy:(NSString *)orderKey

       orderType:(NSString *)type

        whileStr:(NSString *)wlstr

        callBack:(void(^)(NSArray<NSDictionary *> * dataArray,YHBaseSQLError * error))complete;

/**

*  @brief 关闭数据库上下文操作

*  调用此方法后 这个context对象将不再有效 如果再需要使用 需要YHBaseSQLiteManager中的类方法再次返回

*/

-(void)closeContext;


YHBaseSQLiteContext.m


-(BOOL)openDataBaeWithName:(NSString *)path{

   if (sqlite3_open([path UTF8String], &_sqlite3_db)!=SQLITE_OK) {

       sqlite3_close(_sqlite3_db);

       _sqlite3_db=nil;

       return NO;

   }else{

       return YES;

   }

}

-(void)createTableWithName:(NSString *)name keysDictionary:(NSDictionary<NSString *,NSString *> *)dic callBack:(void (^)(YHBaseSQLError *))complete{

   NSMutableString * keys = [[NSMutableString alloc]init];

   for (int i=0; i<dic.allKeys.count; i++) {

       NSString * key = dic.allKeys[i];

       if (i<dic.allKeys.count-1) {

           [keys appendFormat:@"%@ %@,",key,[dic objectForKey:key]];

       }else{

           [keys appendFormat:@"%@ %@",key,[dic objectForKey:key]];

       }

   }

   NSString * sqlStr = [NSString stringWithFormat:@"create table %@(%@)",name,keys];

   [self runSQL:sqlStr callBack:^(YHBaseSQLError * error) {

       

       if (complete) {

           complete(error);

       }

       

   }];

}

-(void)insertData:(NSDictionary<NSString *,id> *)dataDic intoTable:(NSString *)name callBack:(void (^)(YHBaseSQLError *))complete{

   NSMutableString * keys = [[NSMutableString alloc]init];

   NSMutableString * values = [[NSMutableString alloc]init];

   for (int i=0; i<dataDic.allKeys.count; i++) {

       NSString * key = dataDic.allKeys[i];

       if (i<dataDic.count-1) {

           [keys appendFormat:@"%@,",key];

           [values appendFormat:@"\"%@\",",[dataDic objectForKey:key]];

       }else{

           [keys appendFormat:@"%@",key];

           [values appendFormat:@"\"%@\"",[dataDic objectForKey:key]];

       }

   }

   NSString * sqlStr = [NSString stringWithFormat:@"insert into %@(%@) values(%@)",name,keys,values];

   [self runSQL:sqlStr callBack:^(YHBaseSQLError *error) {

       

       if (complete) {

           complete(error);

       }

       

   }];

}

-(void)addKey:(NSString *)kName keyType:(NSString *)type intoTable:(NSString *)tableName callBack:(void (^)(YHBaseSQLError *))complete{

   NSString * sqlStr = [NSString stringWithFormat:@"alter table %@ add %@ %@",tableName,kName,type];

   [self runSQL:sqlStr callBack:^(YHBaseSQLError *error) {

       if (complete) {

           complete(error);

       }

   }];

}

-(void)update:(NSDictionary<NSString *,id> *)dataDic inTable:(NSString *)tableName whileString:(NSString *)wlStr callBack:(void (^)(YHBaseSQLError *))complete{

   NSMutableString * sqlStr = [[NSMutableString alloc]init];

   [sqlStr appendFormat:@"update %@ set ",tableName];

   for (int i=0; i<dataDic.allKeys.count; i++) {

       NSString * key = dataDic.allKeys[i];

       if (i<dataDic.allKeys.count-1) {

           [sqlStr appendFormat:@"%@=\"%@\",",key,[dataDic objectForKey:key]];

       }else{

           [sqlStr appendFormat:@"%@=\"%@\"",key,[dataDic objectForKey:key]];

           if (wlStr!=nil) {

               [sqlStr appendFormat:@" where %@",wlStr];

           }

       }

   }

   [self runSQL:sqlStr callBack:^(YHBaseSQLError *error) {

       if (complete) {

           complete(error);

       }

   }];

}



-(void)deleteDataFromTable:(NSString *)tableName whereString:(NSString *)wlStr callBack:(void (^)(YHBaseSQLError *))complete{

   NSMutableString * sqlStr = [[NSMutableString alloc]init];

   [sqlStr appendFormat:@"delete from %@",tableName];

   if (wlStr!=nil) {

       [sqlStr appendFormat:@" where %@",wlStr];

   }

   [self runSQL:sqlStr callBack:^(YHBaseSQLError *error) {

       if (complete) {

           complete(error);

       }

   }];

}

-(void)dropTable:(NSString *)tableName callBack:(void (^)(YHBaseSQLError *))complete{

   NSString * sqlStr = [NSString stringWithFormat:@"drop table %@",tableName];

   [self runSQL:sqlStr callBack:^(YHBaseSQLError *error) {

       if (complete) {

           complete(error);

       }

   }];

}

-(void)selectKeys:(NSArray<NSDictionary *> *)keys fromTable:(NSString *)tableName orderBy:(NSString *)orderKey orderType:(NSString *)type whileStr:(NSString *)wlstr callBack:(void (^)(NSArray<NSDictionary *> *, YHBaseSQLError *))complete{

   NSMutableString * sqlStr = [[NSMutableString alloc]init];

   [sqlStr appendFormat:@"select"];

   if (keys==nil||keys.count==0) {

       [sqlStr appendFormat:@" * from %@",tableName];

   }else{

       for (int i=0; i<keys.count; i++) {

           if (i<keys.count-1) {

               [sqlStr appendFormat:@" %@,",keys[i].allKeys.firstObject];

           }else{

               [sqlStr appendFormat:@" %@ from %@",keys[i].allKeys.firstObject,tableName];

           }

           

       }

   }

   if (wlstr) {

       [sqlStr appendFormat:@" where %@",wlstr];

   }

   if (orderKey) {

       [sqlStr appendFormat:@" order by %@",orderKey];

   }

   if (type) {

       [sqlStr appendFormat:@" %@",type];

   }

   NSMutableArray * keysArr = [[NSMutableArray alloc]init];

   NSMutableArray * keysTypeArr = [[NSMutableArray alloc]init];

   if (keys==nil||keys.count==0) {

       NSArray<NSDictionary *> * tmpArr = [self getTheTableAllKeys:tableName];

       for (int i=0; i<tmpArr.count; i++) {

           NSString * key = tmpArr[i].allKeys.firstObject;

           [keysArr addObject:key];

           [keysTypeArr addObject:[tmpArr[i] objectForKey:key]];

       }

   }else{

       for (int i=0; i<keys.count; i++) {

           NSString * key = keys[i].allKeys.firstObject;

           [keysArr addObject:key];

           [keysTypeArr addObject:[keys[i] objectForKey:key]];

       }

   }

   

   [self runSelectSQL:sqlStr withKeys:keysArr withDataType:keysTypeArr callBack:^(NSArray<NSDictionary *> *dataArray, YHBaseSQLError *error) {

       if (complete) {

           complete(dataArray,error);

       }

   }];

 

}

-(void)closeContext{

   sqlite3_close(_sqlite3_db);

   _sqlite3_db = nil;

}


//内部方法 运行创建独立的非查询SQL语句

-(void)runSQL:(NSString *)sql callBack:(void(^)(YHBaseSQLError * error))complete{

   char * err;

   int code = sqlite3_exec(_sqlite3_db, [sql UTF8String], NULL, NULL, &err);

   if (code!=SQLITE_OK) {

       YHBaseSQLError * error = [[YHBaseSQLError alloc]init];

       error.errorInfo = [NSString stringWithCString:err encoding:NSUTF8StringEncoding];

       error.errorCode = code;

       complete(error);

   }else{

       complete(nil);

   }

}

//运行查询语句

-(void)runSelectSQL:(NSString *)sql withKeys:(NSArray *)keys withDataType:(NSArray *)dataType callBack:(void(^)(NSArray<NSDictionary *> * dataArray, YHBaseSQLError * error))complete{

   sqlite3_stmt *stmt =nil;

   int code = sqlite3_prepare_v2(_sqlite3_db, [sql UTF8String], -1, &stmt, NULL);

   if (code!=SQLITE_OK) {

       YHBaseSQLError * error = [[YHBaseSQLError alloc]init];

       error.errorInfo = @"查询失败";

       error.errorCode=code;

       complete(nil,error);

   }else{

       NSMutableArray * resultArray = [[NSMutableArray alloc]init];

       

       while (sqlite3_step(stmt)==SQLITE_ROW) {

           //数据类型的分别解析

           NSMutableDictionary * dic = [[NSMutableDictionary alloc]init];

           for (int i=0; i<dataType.count; i++) {

               NSString * type = dataType[i];

               if ([type isEqualToString:YHBASE_SQL_DATATYPE_BINARY]) {

                   int length = sqlite3_column_bytes(stmt, i);

                   const void *data = sqlite3_column_blob(stmt, i);

                   NSData * value = [NSData dataWithBytes:data length:length];

                   [dic  setObject:value forKey:keys[i]];

               }else if([type isEqualToString:YHBASE_SQL_DATATYPE_BLOB]){

                   int length = sqlite3_column_bytes(stmt, i);

                   const void *data = sqlite3_column_blob(stmt, i);

                   NSData * value = [NSData dataWithBytes:data length:length];

                   [dic  setObject:value forKey:keys[i]];

               }else if([type isEqualToString:YHBASE_SQL_DATATYPE_BOOLEAN]){

                   NSNumber * value = [NSNumber numberWithInt:sqlite3_column_int(stmt, i)];

                   [dic setObject:value forKey:keys[i]];

               }else if([type isEqualToString:YHBASE_SQL_DATATYPE_CURRENCY]){

                   NSNumber * value = [NSNumber numberWithLong:sqlite3_column_int64(stmt, i)];

                   [dic setObject:value forKey:keys[i]];

               }else if([type isEqualToString:YHBASE_SQL_DATATYPE_DATE]){

                   char * cString =(char*)sqlite3_column_text(stmt, i);

                   NSString * value = [NSString stringWithCString:cString?cString:"NULL" encoding:NSUTF8StringEncoding];

                   [dic setObject:value forKey:keys[i]];

               }else if([type isEqualToString:YHBASE_SQL_DATATYPE_DOUBLE]){

                   NSNumber * value = [NSNumber numberWithFloat:sqlite3_column_double(stmt, i)];

                   [dic setObject:value forKey:keys[i]];

               }else if([type isEqualToString:YHBASE_SQL_DATATYPE_FLOAT]){

                   NSNumber * value = [NSNumber numberWithFloat:sqlite3_column_double(stmt, i)];

                   [dic setObject:value forKey:keys[i]];

               }else if([type isEqualToString:YHBASE_SQL_DATATYPE_INTRGER]){

                   

                   NSNumber * value = [NSNumber numberWithInt:sqlite3_column_int(stmt, i)];

                   [dic setObject:value forKey:keys[i]];

               }else if([type isEqualToString:YHBASE_SQL_DATATYPE_REAL]){

                   NSNumber * value = [NSNumber numberWithDouble:sqlite3_column_int(stmt, i)];

                   [dic setObject:value forKey:keys[i]];

               }else if([type isEqualToString:YHBASE_SQL_DATATYPE_SMALLINT]){

                   NSNumber * value = [NSNumber numberWithShort:sqlite3_column_int(stmt, i)];

                   [dic setObject:value forKey:keys[i]];

               }else if([type isEqualToString:YHBASE_SQL_DATATYPE_TEXT]){

                   char * cString =(char*)sqlite3_column_text(stmt, i);

                   NSString * value = [NSString stringWithCString:cString?cString:"NULL" encoding:NSUTF8StringEncoding];

                   [dic setObject:value forKey:keys[i]];

               }else if([type isEqualToString:YHBASE_SQL_DATATYPE_TIME]){

                   char * cString =(char*)sqlite3_column_text(stmt, i);

                   NSString * value = [NSString stringWithCString:cString?cString:"NULL" encoding:NSUTF8StringEncoding];

                   [dic setObject:value forKey:keys[i]];

               }else if([type isEqualToString:YHBASE_SQL_DATATYPE_TIMESTAMP]){

                   NSNumber * value = [NSNumber numberWithLongLong:sqlite3_column_int64(stmt, i)];

                   [dic setObject:value forKey:keys[i]];

               }else if([type isEqualToString:YHBASE_SQL_DATATYPE_VARCHAR]){

                   char * cString =(char*)sqlite3_column_text(stmt, i);

                   NSString * value = [NSString stringWithCString:cString?cString:"NULL" encoding:NSUTF8StringEncoding];

                   [dic setObject:value forKey:keys[i]];

               }

             

           }

            [resultArray addObject:dic];

       }

        sqlite3_finalize(stmt);

       stmt=nil;

       complete(resultArray,nil);

   }

}

//获取表中所有字段名和类型

-(NSArray<NSDictionary *> *)getTheTableAllKeys:(NSString *)tableName{

   NSMutableArray * array = [[NSMutableArray alloc]init];

   NSString * getColumn = [NSString stringWithFormat:@"PRAGMA table_info(%@)",tableName];

   sqlite3_stmt *statement;

   sqlite3_prepare_v2(_sqlite3_db, [getColumn UTF8String], -1, &statement, nil);

   while (sqlite3_step(statement) == SQLITE_ROW) {

       char *nameData = (char *)sqlite3_column_text(statement, 1);

       NSString *columnName = [[NSString alloc] initWithUTF8String:nameData];

       char *typeData = (char *)sqlite3_column_text(statement, 2);

       NSString *columntype = [NSString stringWithCString:typeData encoding:NSUTF8StringEncoding];

       NSDictionary * dic = @{columnName:columntype};

       [array addObject:dic];

   }

    sqlite3_finalize(statement);

   statement=nil;

   return array;

}

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
使用iOS原生sqlite3框架对sqlite数据库进行操作(一)
使用iOS原生sqlite3框架对sqlite数据库进行操作
36 0
使用iOS原生sqlite3框架对sqlite数据库进行操作(五)
使用iOS原生sqlite3框架对sqlite数据库进行操作
23 0
IDA反汇编/反编译静态分析iOS模拟器程序(二)加载文件与保存数据库
启动windows版的IDA,在Quickstart界面点击New,弹出一个对话框选择文件。也可以按取消后再把文件拖进IDA。由于Mac版的IDA没注册,没有save功能,所以只好先把Mac上的东西拷贝到windows再打开了。
1055 0
+关注
594
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《Nacos架构&原理》
立即下载
《看见新力量:二》电子书
立即下载
云上自动化运维(CloudOps)白皮书
立即下载