我有一个与SQL数据库对话的应用程序,一个示例表由OBSERVATIONSID(主键(、LEVEL0、LEVEL1、LEVEL2、LEVEL3、LEVEL4和EDITABLE组成。
目前,每次我想划船时,我读取数据库以确保行不存在。存储数据再次读取数据库以获取刚刚添加的主键。
存储数据
+ (void) ObservationsEditSaveData : (NSString*) Level1 : (NSString*) Level2 : (NSString*)Level3 : (NSString*) Level4 : (NSString*) Level0{
sqlite3_stmt *updateStmt;
sqlite3_open([dbObservationPathString UTF8String], &ObservationDB);
NSString* updateSQL = [NSString stringWithFormat: @"INSERT INTO OBSERVATIONS(LEVEL0, LEVEL1, LEVEL2, LEVEL3, LEVEL4, EDITABLE) values (?,?,?,?,?,?)"];
sqlite3_prepare_v2(ObservationDB, [updateSQL UTF8String], -1, &updateStmt, NULL);
if(sqlite3_step(updateStmt)==SQLITE_DONE)
{
sqlite3_bind_text(updateStmt, 1, [Level0 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(updateStmt, 2, [Level1 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(updateStmt, 3, [Level2 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(updateStmt, 4, [Level3 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_text(updateStmt, 5, [Level4 UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_int(updateStmt, 6, 0);
sqlite3_step(updateStmt);
sqlite3_finalize(updateStmt);
}
sqlite3_close(ObservationDB);
}
正在读取ID/正在检查是否存在。(在检查是否存在时,如果返回ID,我假设它存在(
+ (int) ObservationGetID : (NSString*) Level1 : (NSString*) Level2 : (NSString*) Level3 : (NSString*) Level4 : (NSString*) Level0{
int ReturnValue = 0;
sqlite3_stmt *statement;
if (sqlite3_open([dbObservationPathString UTF8String], &ObservationDB)==SQLITE_OK)
{
NSString *querySql = [NSString stringWithFormat:@"SELECT OBSERVATIONSID FROM OBSERVATIONS WHERE LEVEL0 = '%@' AND LEVEL1 = '%@' AND LEVEL2 = '%@' AND LEVEL3 = '%@' AND LEVEL4 = '%@'", Level0 ,Level1, Level2, Level3, Level4];
const char* query_sql = [querySql UTF8String];
if (sqlite3_prepare(ObservationDB, query_sql, -1, &statement, NULL)==SQLITE_OK)
{
while (sqlite3_step(statement)==SQLITE_ROW)
{
ReturnValue = sqlite3_column_int(statement, 0);
}
}
}
sqlite3_close(ObservationDB);
return ReturnValue;
}
id想做的是减少sql调用的数量,我可以将这些命令合并为一个吗?
顺便说一句,如果我把代码搞砸了,请说,我不会被冒犯的。
感谢
在绑定值之前调用sqlite3_step
(在if
子句中(。您必须先绑定值,然后才能使用sqlite3_step
执行SQL。只需将if
子句和sqlite3_step
一起删除即可。
您可以更改observationsEditSaveData
方法,以便:
-
在调用
step
函数之前先调用bind
函数。 -
让函数返回插入行的行id(零表示有错误(。
-
检查所有SQLite调用的结果。
-
符合Cocoa命名约定(以小写字母开头的方法和变量名(。
这产生:
+ (sqlite3_int64) observationsEditSaveData : (NSString*) level1 : (NSString*) level2 : (NSString*)level3 : (NSString*) level4 : (NSString*) level0 {
sqlite3_stmt *updateStmt;
if (sqlite3_open([dbObservationPathString UTF8String], &observationDB) != SQLITE_OK) {
NSLog(@"open failed");
return 0;
}
const char *updateSQL = "INSERT INTO OBSERVATIONS(LEVEL0, LEVEL1, LEVEL2, LEVEL3, LEVEL4, EDITABLE) values (?,?,?,?,?,?)";
if (sqlite3_prepare_v2(observationDB, updateSQL, -1, &updateStmt, NULL) != SQLITE_OK)
NSLog(@"prepare failed: %s", sqlite3_errmsg(observationDB));
if (sqlite3_bind_text(updateStmt, 1, [level0 UTF8String], -1, SQLITE_TRANSIENT) != SQLITE_OK)
NSLog(@"bind 1 failed: %s", sqlite3_errmsg(observationDB));
if (sqlite3_bind_text(updateStmt, 2, [level1 UTF8String], -1, SQLITE_TRANSIENT) != SQLITE_OK)
NSLog(@"bind 2 failed: %s", sqlite3_errmsg(observationDB));
if (sqlite3_bind_text(updateStmt, 3, [level2 UTF8String], -1, SQLITE_TRANSIENT) != SQLITE_OK)
NSLog(@"bind 3 failed: %s", sqlite3_errmsg(observationDB));
if (sqlite3_bind_text(updateStmt, 4, [level3 UTF8String], -1, SQLITE_TRANSIENT) != SQLITE_OK)
NSLog(@"bind 4 failed: %s", sqlite3_errmsg(observationDB));
if (sqlite3_bind_text(updateStmt, 5, [level4 UTF8String], -1, SQLITE_TRANSIENT) != SQLITE_OK)
NSLog(@"bind 5 failed: %s", sqlite3_errmsg(observationDB));
if (sqlite3_bind_int(updateStmt, 6, 0))
NSLog(@"bind 1 failed: %s", sqlite3_errmsg(observationDB));
if (sqlite3_step(updateStmt) != SQLITE_DONE)
NSLog(@"step failed: %s", sqlite3_errmsg(observationDB));
sqlite3_finalize(updateStmt);
sqlite3_int64 rowid = sqlite3_last_insert_rowid(observationDB);
sqlite3_close(observationDB);
return rowid;
}