数据库 ios

static DataHandle *data = nil;
@implementation DataHandle

+(instancetype)SharedInstance
{
    if (data == nil) {
        data = [[DataHandle alloc]init];
    }
 
    return data;

}

//声明数据库
static sqlite3 * bd = nil;
#pragma mark  打开数据库
-(void)openDataBase
{
    if (bd != nil) {
        return;
       
}
      //创建数据库
    //1.保存数据库的路径
    NSString *path = [self getDataBasePath];
    NSLog(@"%@",path);
    //2，拼接路径(数据库具体存的文件)
    path = [path stringByAppendingPathComponent:@"/data.sqlite"];
    //3.根据路径去打开数据库（如果数据库不存在 会自动给创建数据库）
   int result =  sqlite3_open([path UTF8String], &bd);
    //4.判断是否打开成功
    if (SQLITE_OK == result) {
        NSLog(@"数据库打开成功");
        
    }else
    {
        NSLog(@"数据库打开失败");
    }

}

#pragma mark 关闭数据库
-(void)closeDataBase
{
   //关闭数据库
  int result = sqlite3_close(bd);
    //判断数据库是否关闭成功
    if (result == SQLITE_OK) {
        NSLog(@"关闭数据库成功");
    }
    else
    {
        NSLog(@"关闭数据库失败");
    
    }
    



}
#pragma mark 创建表格
-(void)createTable:(NSString *)tableName
{
     //创建表格的SQL语句
    NSString *createTableString = [NSString stringWithFormat:@"CREATE TABLE'%@' ('id' INT PRIMARY KEY,'name'TEXT, 'sex'TEXT)",tableName];
     // 执行sql语句
    int result = sqlite3_exec(bd, [createTableString UTF8String], NULL, NULL, NULL);
    //判断表格是否创建成功
    if (result == SQLITE_OK) {
        NSLog(@"表格创建成功");
    }else
    {
        NSLog(@"表格创建失败");
    }

}

#pragma mark 获取数据库的路径
-(NSString *)getDataBasePath
{

return [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES)firstObject];


}
#pragma mark 插入数据
-(void)insertIntoTableWithID:(int)nameID withName:(NSString *)name withSex:(NSString *)sex
{
 //插入语句
    NSString *insetSql = [NSString stringWithFormat:@"INSERT INTO user_wen ('id','name','sex')Values('%d','%@','%@')",nameID,name,sex];
  //执行sql语句
    int result = sqlite3_exec(bd, [insetSql UTF8String], NULL, NULL, NULL);
  //判断执行结果
    if (result== SQLITE_OK) {
        NSLog(@"插入成功");
    }else
    {
    
        NSLog(@"插入失败");
    }


}

#pragma mark 删除数据
-(void)deleteDataFromTableWithID:(int)nameID
{
   //删除数据的sql语句
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM user_wen where 'id' = %d",nameID];
    //执行删除语句
    int result = sqlite3_exec(bd, [deleteSql UTF8String], NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除OK");
    }else
    {
        NSLog(@"删除FAIL");
    
    }

}
#pragma mark 修改数据(更新数据)
-(void)updateDataFromTableWithID:(int)nameID
{
 //写sql语句
    NSString *updataSql = [NSString stringWithFormat:@"UPDATE user_wen SET sex = '东方不败' WHERE id = %d", nameID];

    int result = sqlite3_exec(bd, [updataSql UTF8String], NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"更新成功");
    }
    else
    {
        NSLog(@"更新失败");
    }

   
}
#pragma MARK 查询数据库
-(void)selectDataFromTable
{
   //写查询的SQL语句
    NSString *selectSql = @"SELECT *FROM user_wen";
    
    sqlite3_stmt *stmt = nil;//用来保存查询到的结果集
    //准备状态(预存取)查询数据
    int result = sqlite3_prepare(bd, [selectSql UTF8String], -1, &stmt, NULL);
    
    if (result == SQLITE_OK) {
        //判读是否是最后一行 有没有必要继续读取
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //拿出各列的数据
            //1.拿出ID列的数据
        int numberID = sqlite3_column_int(stmt, 0);
            //2.拿出name列的数据
       const unsigned char *nameChar = sqlite3_column_text(stmt, 1);
            NSString *name = [NSString stringWithUTF8String:(const char *)nameChar];
            //3.拿出 sex的数据
            const unsigned char *sexChar = sqlite3_column_text(stmt, 2);
            NSString *sex = [NSString stringWithUTF8String:(const char *)sexChar];
            NSLog(@"%d %@ %@",numberID,name,sex);
        }
        //结束查询
        sqlite3_finalize(stmt);
    }