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); } |
|
来自: 昵称26765941 > 《待分类1》