UI进阶——SQL数据库

一、IOS开发中支持sqlite3轻量级数据库

在使用数据库之前，需要导入数据库相关的包。

在这里，我们封装一个数据库对象来操作数据库：

创建单例：

//单例
+(DataBaseHelper *)ShareSingleton{
    
    static DataBaseHelper* dataBaseHelper = nil;
    
    @synchronized(self) {
        if (dataBaseHelper == nil) {
             dataBaseHelper = [[DataBaseHelper alloc]init];
        }
    }
    
    return dataBaseHelper;
}

//创建数据库路径
-(void)dataBasePathWithName:(NSString*)fileName{
    
    
    //首先判断文件是否包含后缀名
    if (![fileName containsString:@".sqlite"]) {
        fileName = [fileName stringByAppendingString:@".sqlite"];
    }
    
    //拼接完整的数据库路径
    self.fileName = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0] stringByAppendingPathComponent:fileName];
}

//创建数据库
-(sqlite3*)openOrCreateDB{
    
    /*//存储路径
    NSString* path = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0] stringByAppendingPathComponent:@"db.sqlite"];*/
    
    //创建数据库指针
    sqlite3* sqlite = nil;
    //打开数据库
    int isOpen = sqlite3_open(self.fileName.UTF8String, &sqlite);
    
    //判断 是否打开成功
    if (isOpen == SQLITE_OK) {
        NSLog(@"打开成功");
        return sqlite;
    }else{
        NSLog(@"打开失败");
        return NULL;
    }
}

//执行无返回结果的操作
- (void)execSqlWithSQLString:(NSString*)SQLString message:(NSString*)message{
    
    //打开数据库
     sqlite3* sqlite = [self openOrCreateDB];
    //执行语句
    int isSuccessful = sqlite3_exec(sqlite, SQLString.UTF8String, NULL, NULL, NULL);
    //执行信息
    if (isSuccessful == SQLITE_OK) {
        NSLog(@"执行成功:%@ is successful",message);
    }else{
        NSLog(@"执行失败:%@ is fail",message);
    }
    //关闭数据库
    sqlite3_close(sqlite);
}

//通用的查询方法
-(NSArray*)querywithSQL:(NSString*)sqlStr{
    
    //打开数据库
    sqlite3 *sqlite = [self openOrCreateDB];
    
    //声明stmt指针
    sqlite3_stmt *stament;
    
    //初始化可变数据，用来盛放所有记录
    NSMutableArray* array = [[NSMutableArray alloc]init];
    
    int result = sqlite3_prepare_v2(sqlite, sqlStr.UTF8String, -1, &stament, NULL);
    
    if (result == SQLITE_OK) {
        
        //遍历没一条执行信息，并存储
        while (sqlite3_step(stament) == SQLITE_ROW) {
            
            //动态获得记录的字段数
            int count = sqlite3_column_count(stament);
            //存放数据信息
            NSMutableDictionary *mDic = [[NSMutableDictionary alloc]init];
            for (int i = 0 ; i < count ; i ++) {
                //确定该字段的类型
                int type = sqlite3_column_type(stament, i);
                //确定该字段的关键字
                const char *name = sqlite3_column_name(stament, i);
                NSString *key = [NSString stringWithCString:name encoding:NSUTF8StringEncoding];
                //根据类型取值
                switch (type) {
                    case SQLITE_TEXT:
                    {//string
                        NSString* str = [NSString stringWithCString:(const char*)sqlite3_column_text(stament, i) encoding:NSUTF8StringEncoding];
                        [mDic setObject:str forKey:key];
                    }
                    break;
                    case SQLITE_INTEGER:
                    {//int
                        NSInteger value = sqlite3_column_int(stament, i);
                        [mDic setObject:@(value) forKey:key];
                    }
                    break;
                    case SQLITE_BLOB:
                    {
                        
                    }
                    break;
                        
                    default:
                        break;
                }
            }
            //将记录添加到数组-
            [array addObject:mDic];
        }
        //关闭数据库，释放资源
        sqlite3_close(sqlite);
        //释放指针
        sqlite3_finalize(stament);
        //返回该值
        return array;
    }else{
        NSLog(@"查询语句有问题");
        //关闭数据库，释放资源
        sqlite3_close(sqlite);
        //释放指针
        sqlite3_finalize(stament);
        return  array;
    }
}