使用FMDB和NSDictionary将NULL值添加到SQLite数据库

I have run into a bit of a catch 22. I am using FMDB's fancy withParameterDictionary method to insert data into my SQLite database like this:

我遇到了一些问题22.我正在使用FMDB的花式withParameterDictionary方法将数据插入到我的SQLite数据库中,如下所示:

NSDictionary *aircraftDict = [NSDictionary dictionaryWithObjectsAndKeys:
                              self.aircraftIdTextField.text, @"aircraft_id",
                              self.makeModelTextField.text, @"make_model",
                              self.categoryClassTextField.text, @"category_class",                                  
                              @YES, @"updated_flag",                                 
                              nil];

NSString *addAircraftQuery = @"INSERT INTO aircrafts (aircraft_id, make_model, category_class, updated_flag) VALUES (:aircraft_id, :make_model, :category_class, :updated_flag)";

[db executeUpdate:addAircraftQuery withParameterDictionary:aircraftDict];

The problem is that when one of those text fields is blank, it truncates the NSDictionary since a nil value tells the dictionary that it has arrived at the end of its list of values.

问题是,当其中一个文本字段为空时,它会截断NSDictionary,因为nil值告诉字典它已到达其值列表的末尾。

I can work around that issue just fine by making sure I have a blank object for each value in the dictionary (e.g. like a string @"").

通过确保字典中的每个值都有一个空白对象(例如字符串@“”),我可以解决这个问题。

But then my values arrive in my SQLite database as a "blank" value instead of a NULL. This may not be a big deal, but I've heard that using NULL will take up less space in the database.

但是我的值在我的SQLite数据库中作为“空白”值而不是NULL。这可能不是什么大问题,但我听说使用NULL会占用数据库中较少的空间。

How can I insert NULL into my database without prematurely truncating my NSDictionary?

如何在不过早截断我的NSDictionary的情况下将NULL插入到我的数据库中?

3 个解决方案

#1

You can do it by not using the "fancy" withParameterDictionary method, and instead using the boring executeUpdate like this:

你可以不使用“fancy”withParameterDictionary方法,而是像这样使用枯燥的executeUpdate:

NSString *addAircraftQuery = @"INSERT INTO aircrafts (aircraft_id, make_model, category_class, updated_flag) VALUES (?, ?, ?, ?)";

[db executeUpdate:addAircraftQuery, self.aircraftDict, self.makeModelTextField.text, self.categoryClassTextField.text, @YES];

#2

Spent a while researching this one too. The solution for me (using Swift, but Objective C should be the same) is to use NSNull instead of nil

花了一段时间研究这个。对我来说(使用Swift,但Objective C应该是相同的)的解决方案是使用NSNull而不是nil

#3

To keep it fancy, give this a shot, works for me:

为了保持它的幻想,给它一个机会,适合我:

NSDMutableDictionary *aircraftDict = [NSDMutableDictionary dictionary];
// only insert non-nil text fields
if(self.aircraftIdTextField.text){
    aircraftDict[@"aircraft_id"] = self.aircraftIdTextField.text;
}
etc for all other text fields...

NSArray* keys = [aircraftDict allKeys];
NSMutableArray *prefixedKeys = [NSMutableArray array];
            [keys enumerateObjectsUsingBlock:^(id obj, NSUInteger idx, BOOL *stop) {
                [prefixedKeys addObject:[NSString stringWithFormat:@":%@",obj]];
            }];
NSString *addAircraftQuery = [NSString stringWithFormat: @"INSERT INTO aircrafts (%@) VALUES (%@)",[keys componentsJoinedByString:@","],[prefixedKeys componentsJoinedByString:@","]];
[db executeUpdate:addAircraftQuery withParameterDictionary:aircraftDict];

Now the insert only contains the fields that you want to insert, so its super efficient.

现在插入只包含您要插入的字段,因此它的超级效率。

#1