Sqlit3 简单操作

1。创建数据库并且打开

- (void)open_db

{

    @synchronized(self){

        NSArray *pathArray =NSSearchPathForDirectoriesInDomains(NSCachesDirectory,NSUserDomainMask, YES);

        

        NSString *path = [[pathArrayobjectAtIndex:0]stringByAppendingPathComponent:@"Amt.db"];

        

        if (sqlite3_open([pathUTF8String], &database) != SQLITE_OK) {

            

            sqlite3_close(database);

            

            NSLog(@"创建数据库失败");

        }

    }  

}

2。关闭数据库

sqlite3_close(database);

3。创建表

- (void)create_table:(NSString*)tableName

{

    tableDBName = tableName;

    

    [[NSUserDefaults standardUserDefaults] setObject:tableNameforKey:TABLENAME];

    

    char *errMsg;

    

    NSString *createTableSql = [NSStringstringWithFormat:

                                @"create table if not exists 'Amt_%@'(\

                                row integer primay key ,\

                                msgid,\

                                msgtitle,\

                                msgcontent,\

                                channelid,\

                                channelname,\

                                channeltype)",tableDBName];

    

    if (sqlite3_exec(database, [createTableSqlUTF8String], NULL, NULL, &errMsg)!= SQLITE_OK) {

        

        NSLog(@"创建表失败");

        

        sqlite3_free(errMsg);

        

        [self close];

    }  

}

4。添加表数据

- (void)addMesg:(MsgInfo*)msgInfo

{

    char *errMsg;

    

    tableDBName = [[NSUserDefaultsstandardUserDefaults] objectForKey:TABLENAME];

    

    NSString *addSql = [NSStringstringWithFormat:@"insert or replace into 'Amt_%@'(msgid,msgtitle,msgcontent,channelid,channelname,channeltype) values('%@','%@','%@','%@','%@','%@'); ",tableDBName,msgInfo.msgid,msgInfo.msgtitle,msgInfo.msgcontent,msgInfo.channelid,msgInfo.channelname,msgInfo.channeltype];

    

    if (sqlite3_exec(database, [addSqlUTF8String], NULL, NULL, &errMsg)!= SQLITE_OK) {

        

        NSLog(@"插入数据失败");

        

        sqlite3_free(errMsg);

        

        [self close];

    }   

}

5。筛选数据

- (NSMutableArray *)getChannelIDInfo :(NSString *)channleIDInfo

{

     NSMutableArray *msgArray = [[NSMutableArrayalloc] init];

    

    MsgInfo *msgInfo = [[MsgInfoalloc] init];

    

    tableDBName = [[NSUserDefaultsstandardUserDefaults] objectForKey:TABLENAME];

    

    sqlite3_stmt *statement;

    

    NSString *selSql = [NSStringstringWithFormat:@"select * from 'Amt_%@' where channelid = '%@'",tableDBName,channleIDInfo];

    if (sqlite3_prepare_v2(database, [selSqlUTF8String], -1, &statement, nil) == SQLITE_OK) {

        while (sqlite3_step(statement) ==SQLITE_ROW) {

                //通过循环检索所有结果

            char * msgid = (char*)sqlite3_column_text(statement,1);

            char * msgtitle = (char*)sqlite3_column_text(statement,2);  

            char * msgcontent = (char *)sqlite3_column_text(statement,3);

            char * channelid  = (char *)sqlite3_column_text(statement,4);

            char * channelname = (char *)sqlite3_column_text(statement,5);

            char * channeltype = (char *)sqlite3_column_text(statement,6);

            

            NSString *msgidStr = [NSStringstringWithCString:msgid encoding:NSUTF8StringEncoding];

            NSString *msgtitleStr = [NSStringstringWithCString:msgtitle encoding:NSUTF8StringEncoding];

            NSString *msgcontentStr = [NSStringstringWithCString:msgcontent encoding:NSUTF8StringEncoding];

            NSString *channelidStr  = [NSStringstringWithCString:channelid encoding:NSUTF8StringEncoding];

            NSString *channelnameStr = [NSStringstringWithCString:channelname encoding:NSUTF8StringEncoding];

            NSString *channeltypeStr    = [NSStringstringWithCString:channeltype encoding:NSUTF8StringEncoding];

            

            msgInfo.msgid  =  msgidStr;

            msgInfo.msgtitle = msgtitleStr;

            msgInfo.msgcontent = msgcontentStr;

            msgInfo.channelid  = channelidStr;

            msgInfo.channelname = channelnameStr;

            msgInfo.channeltype = channeltypeStr;

            [msgArray addObject:msgInfo];

        }    

        

    }

    sqlite3_finalize(statement);

    return [msgArray autorelease];

}

6. 删除数据

- (void)delOneMsg:(NSString *)channleIDInfo

{

    char *errMsg;

    

    tableDBName = [[NSUserDefaults standardUserDefaults] objectForKey:TABLENAME];

    

    NSString *delSql = [NSString stringWithFormat:@"delete from 'Amt_%@' where channelid = '%@'",tableDBName,channleIDInfo];

    

    

    if (sqlite3_exec(database, [delSql UTF8String],NULL , NULL, &errMsg)!= SQLITE_OK) {

        

        NSLog(@"删除失败");

        sqlite3_free(errMsg);

    }

}