IOS sqlite3数据库的使用

最新推荐文章于 2025-06-07 19:00:00 发布

书弋江山

最新推荐文章于 2025-06-07 19:00:00 发布

阅读量656

点赞数 1

CC 4.0 BY-SA版权

分类专栏： IOS IOS开发文章标签： ios 数据库 sqlite3

本文链接：https://blog.youkuaiyun.com/bobbob32/article/details/54405965

IOS开发同时被 2 个专栏收录

83 篇文章

订阅专栏

IOS

81 篇文章

订阅专栏

本文详细介绍了使用Objective-C进行SQLite数据库操作的方法，包括数据库的打开与创建、数据的插入与更新、数据的删除以及数据的查询等核心功能，并提供了具体的代码实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

之前开发犯了一个很大的错误，认为数据库的打开和关闭就像socket的开和闭一样，结果频繁的打开和关闭数据库导致线程崩溃，即使给操作加锁也无法解决这样导致的效率低下的问题，其实数据库的开和闭是很耗资源的，而且数据库如果长时间不操作会自动关闭，所以数据库在设计的时候模式是长连接。我们项目老大这样说：“你是我遇到第二个这样做的人”。其实我很想知道那个哥们是谁！
一. 打开和创建数据库

-(void)openFileWith:(NSString*)tableName{
    NSArray *array=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *path=[array objectAtIndex:0];
    NSString *dataPath=[path stringByAppendingPathComponent:@"she.sqlite"];
    DLog(@"+++++++++++:%@",dataPath);
    myPath=dataPath;
    int result=sqlite3_open([dataPath UTF8String],&database);
    if (result==SQLITE_OK) {
        DLog(@"打开成功");
        NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@(Name TEXT ,Contents TEXT ,Object TEXT ,Time TEXT ,Type TEXT)",tableName];
        DLog(@"COMMAND:%@",sql);
        int  result1 = sqlite3_exec(database, sql.UTF8String, NULL, NULL, NULL);
        if (result1==SQLITE_OK) {
            DLog(@"创建表成功");
        }
        else{
            DLog(@"创建表失败");
        }
    }else{
        sqlite3_close(database);
        DLog(@"打开数据库失败");
    }

}

二. 插入数据
如果数据存在就修改数据

-(void)insertIntoName:(NSString*)name Content:(NSString*)content To:(NSString*)to Type:(NSString*)type TableName:(NSString*)tablename{
    NSString *time=[self GetDetailTime];
    NSString *sql1=[NSString stringWithFormat:@"INSERT  INTO '%@'(Name,Contents,Object,Time,Type) VALUES('%@','%@','%@','%@','%@')",tablename,name,content,to,time,type];
    char *ERROR1;
    int resut2=sqlite3_exec(database, [sql1 UTF8String], NULL, NULL, &ERROR1);
    if (resut2==SQLITE_OK) {
        DLog(@"插入数据成功");
    }else{
        DLog(@"插入数据失败:%s",ERROR1);
//        sqlite3_stmt*statement;
//        NSString *sql3=[NSString  stringWithFormat:@"update PERSIONINFO set Time='%@' where Name='%@'",time,name];
//        char *error;
//        int reesult4=sqlite3_exec(database,[sql3 UTF8String],NULL,NULL,&error);
//        if (reesult4==SQLITE_OK) {
//            DLog(@"修改数据成功");
//        }else{
//            DLog(@"修改数据失败:%s",error);
//        }
        //sqlite3_finalize(statement);
    }

}

三.删除数据

-(BOOL)deleteObjectFromDb:(NSString*)type{
    sqlite3_stmt *stmt = nil;
    NSString *sqlStr=[NSString stringWithFormat:@"delete from PERSIONINFO where Type=%@",type];
    int result=sqlite3_prepare_v2(database, [sqlStr UTF8String], -1, &stmt, NULL);
    if (result==SQLITE_OK) {
        if (sqlite3_step(stmt)==SQLITE_ROW) {
            if (sqlite3_step(stmt)==SQLITE_DONE) {
                sqlite3_finalize(stmt);
                sqlite3_close(database);
                return YES;
            }
        }
        return YES;
    }else{
        return NO;
    }

}

四.查询数据

-(NSArray*)getdataFrom:(NSString*)tableName Name:(NSString*)name Object:(NSString*)object Type:(NSString*)type{
    NSMutableArray *mArr=[[NSMutableArray alloc]init];
    NSString *sql3=[NSString stringWithFormat:@"select * from %@ where Name='%@' and Object='%@';",tableName,name,object];
      sqlite3_stmt *stmt;
    int result=sqlite3_prepare_v2(database,[sql3 UTF8String],-1,&stmt,nil);
    DLog(@"+++++====%d",result);
    //    int result=sqlite3_exec(database, [sql3 UTF8String], NULL, NULL, &error);
    if (result==SQLITE_OK) {
        while (sqlite3_step(stmt)==SQLITE_ROW) {
            char*name =(char*)sqlite3_column_text(stmt,0);
            NSString *name1=[[NSString alloc]initWithUTF8String:name];

            char*content = (char*)sqlite3_column_text(stmt,1);
            NSString *content1=[[NSString alloc]initWithUTF8String:content];

            char*objec = (char*)sqlite3_column_text(stmt,2);
            NSString *objec1=[[NSString alloc]initWithUTF8String:objec];

            char*time = (char*)sqlite3_column_text(stmt,3);
            NSString *time1=[[NSString alloc]initWithUTF8String:time];

            DLog(@"+++++++ ==+:%@,%@,%@,%@,%@",name1,content1,objec1,time1,type);
            Mes *m=[[Mes alloc]initWithName:name1 Content:content1 To:objec1 Type:type Time:time1];
            [mArr addObject:m];

        }
        DLog(@"查询成功");
        sqlite3_finalize(stmt);

    }else{
        DLog(@"查询失败");

    }
    sqlite3_close(database);
    NSArray *array=mArr;
    //对数据进行排序
    NSArray*sortArr=[array sortedArrayUsingComparator:^NSComparisonResult(Mes*p1, Mes*p2) {
        return [self compareDate:p1.time withDate:p2.time];
    }];
    mArr=nil;
    return sortArr;
}

排序函数

-(int)compareDate:(NSString*)date01 withDate:(NSString*)date02{
    int ci;
    NSDateFormatter *df = [[NSDateFormatter alloc] init];
    [df setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
    NSDate *dt1 = [[NSDate alloc] init];
    NSDate *dt2 = [[NSDate alloc] init];
    dt1 = [df dateFromString:date01];
    dt2 = [df dateFromString:date02];
    NSComparisonResult result = [dt1 compare:dt2];
    switch (result)
    {
            //date02比date01大
        case NSOrderedAscending: ci=1; break;
            //date02比date01小
        case NSOrderedDescending: ci=-1; break;
            //date02=date01
        case NSOrderedSame: ci=0; break;
        default: DLog(@"erorr dates %@, %@", dt2, dt1); break;
    }
    return ci;
}