IOS sqlite3数据库的使用

一. 打开和创建数据库

-(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;}