iOS - 将 NSDictionary 复制到 SQLite3

Question

我有一个接收表名和 NSDictionary 的方法。在该方法中，我想要一个循环为每条记录创建一个 SQL Insert 语句，将 NSDictionary 的内容复制到相应的 table 中。 NSDictionary 在之前的方法中通过 JSON web 服务 填充。

我遇到的问题是以正确的格式从 NSDictionary 中获取信息并放入 Insert 语句中。

我想要的是每条记录都有一个类似这样的插入语句:

INSERT INTO speaker VALUES (0f32ae3f-ad56-e311-a863-000c29beef51,<null>,1385478793, Mike Smith,<null>,BVSc PhD DSAM DipECVIM-ca MRCVS)

但我得到的是:

INSERT INTO speaker VALUES (
   {
        ID = "0f32ae3f-ad56-e311-a863-000c29beef51";
        ImageURL = "<null>";
        LastMod = 1385478793;
        Name = "Mike Smith";
        Profile = "<null>";
        Qualifications = "BVSc PhD DSAM DipECVIM-ca MRCVS";
   }
)  

到目前为止，这是我的代码:

-(void)populateTable:(NSString *)tableName dictonary:(NSMutableDictionary *)tempDict
{

    sqlite3_stmt *stmt;

    NSMutableString *keys = [NSMutableString string];
    NSMutableString *values = [NSMutableString string];

    for (NSString *key in tempDict.allKeys) { //edited from tempDict to tempDict.allKeys
        [keys appendFormat:@"%@,", key];
        [values appendFormat:@"%@,", [tempDict objectForKey:key]];
    }

    [values deleteCharactersInRange:NSMakeRange([values length] -1, 1)];
    NSString *queryStr = [NSString stringWithFormat:@"INSERT INTO %@ VALUES %@", tableName, values];
    NSLog(@"Query = %@", queryStr);
    NSLog(@"GetData   -   populateTable   -   qry : %@",queryStr);
    const char *sql = [queryStr UTF8String]; ;

    if((sqlite3_open([[self filePath] UTF8String], &congressDB)==SQLITE_OK))
    {
        NSLog(@"GetData   -   populateTable   -   str = %@", queryStr);
        if (sqlite3_prepare(congressDB, sql, -1, &stmt, NULL)==SQLITE_OK)
        {
            NSLog(@"GetData   -   populatTable   -   About to carry out SQL statement");
            sqlite3_step(stmt);
            sqlite3_finalize(stmt);
        }
        else
        {
            NSLog(@"GetData   -   populateTable   -   Problem with prepare statement: %s", sqlite3_errmsg(congressDB));
            NSLog(@"GetData   -   populateTable   -   stmt = %@", stmt);
        }
        sqlite3_close(congressDB);

    }

}

Answer 1

试试这个更正:

- (void)populateTable:(NSString *)tableName dictonary:(NSMutableDictionary *)tempDict
{

    sqlite3_stmt *stmt;

    NSMutableString *values = [NSMutableString string];

    for (NSString *key in tempDict.allKeys)
    {
        [values setString:@""];
        NSArray *array = [tempDict objectForKey:key];
        if (array.count)
        {
            NSDictionary * dict = [array objectAtIndex:0];
            for (NSString *keyInDict in dict.allKeys)
            {
                [values appendFormat:@"%@,", [dict objectForKey:keyInDict]];
            }

            [values deleteCharactersInRange:NSMakeRange([values length] -1, 1)];
            NSString *queryStr = [NSString stringWithFormat:@"INSERT INTO %@ VALUES %@", tableName, values];
            NSLog(@"Query = %@", queryStr);
            NSLog(@"GetData   -   populateTable   -   qry : %@",queryStr);
            const char *sql = [queryStr UTF8String]; ;

            if((sqlite3_open([[self filePath] UTF8String], &congressDB)==SQLITE_OK))
            {
                NSLog(@"GetData   -   populateTable   -   str = %@", queryStr);
                if (sqlite3_prepare(congressDB, sql, -1, &stmt, NULL)==SQLITE_OK)
                {
                    NSLog(@"GetData   -   populatTable   -   About to carry out SQL statement");
                    sqlite3_step(stmt);
                    sqlite3_finalize(stmt);
                }
                else
                {
                    NSLog(@"GetData   -   populateTable   -   Problem with prepare statement: %s", sqlite3_errmsg(congressDB));
                    NSLog(@"GetData   -   populateTable   -   stmt = %@", stmt);
                }
                sqlite3_close(congressDB);
            }
        }
    }
}