iphone - 从 sqlite 数据库存储和检索数据

Question

我正在构建一个应用程序，它在一个 View 中包含一个表单，用户在其中填写所有字段，当他单击保存按钮时，数据必须保存到数据库中，并且在返回后，还有另一个 View ，当输入时，必须显示保存的数据(事件)。

我已经创建了一个数据库并学习了几个 sqlite3 教程；

我已经根据我的要求对我的代码进行了所有其他更改。但是，当我使用此语句检查数据是否插入数据库时​​:

SELECT * FROM reminders;

我什么也没得到，我对是否插入数据感到困惑。

如何正确保存它，如何从数据库中检索数据以使用并在其他 View 中显示？

Answer 1

首先你应该创建 sqlite3 数据库文件(check this link)，然后你应该将它包含到你的项目中。现在要连接到它，您可以使用以下代码:

#pragma mark -
#pragma mark Create/Load Database
+ (void)createEditableCopyOfDatabaseIfNeeded {
    // First, test for existence.
    NSArray * paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString * documentsDirectory = [paths objectAtIndex:0];
    NSString * writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"DATABASENAME.DB"];

    BOOL success;
    NSFileManager * fileManager = [NSFileManager defaultManager];
    success = [fileManager fileExistsAtPath:writableDBPath];
    if (success) {
        return;
    }

    // The writable database does not exist, so copy the default to the appropriate location.
    NSError * error;
    NSString * defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"DATABASENAME.DB"];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
    if (!success) {
        NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
    }
}
+ (sqlite3 *)getDBConnection {
    [DatabaseController createEditableCopyOfDatabaseIfNeeded];

    NSArray * paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString * documentsDirectory = [paths objectAtIndex:0];
    NSString * path = [documentsDirectory stringByAppendingPathComponent:@"DATABASENAME.DB"];

    // Open the database. The database was prepared outside the application.
    sqlite3 * newDBConnection;
    if (sqlite3_open([path UTF8String], &newDBConnection) == SQLITE_OK) {
        //NSLog(@"Database Successfully Opened :)");
    } else {
        //NSLog(@"Error in opening database :(");
    }
    return newDBConnection;
}

然后插入一条记录，您可以使用以下代码:

+ (void)insertEvent:(Event *)newEvent {
    sqlite3 * connection = [DatabaseController getDBConnection];
    const char * text = "INSERT INTO Event (Serial, Name, Date) VALUES (?, ?, ?)";
    sqlite3_stmt * insert_statement;
    int prepare_result = sqlite3_prepare_v2(connection, text, -1, &insert_statement, NULL);
    if ((prepare_result != SQLITE_DONE) && (prepare_result != SQLITE_OK)) {
        // Error
        sqlite3_close(connection);
        return;
    }

    sqlite3_bind_int(insert_statement, 1, newEvent.Serial);
    sqlite3_bind_text(insert_statement, 2, [newEvent.Name UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_double(insert_statement, 3, [newEvent.Date timeIntervalSince1970]);

    int statement_result = sqlite3_step(insert_statement);
    if ((statement_result != SQLITE_DONE) && (statement_result != SQLITE_OK)) {
        //Error
        sqlite3_close(connection);
        return;
    }

    sqlite3_finalize(insert_statement);

    // Get the Id of the inserted event
    int rowId = sqlite3_last_insert_rowid(connection);
    newEvent.Id = rowId;

    sqlite3_close(connection);
}

现在得到一个事件:

+ (Event *)getEventById:(int)id {
    Event * result = nil;
    sqlite3 * connection = [DatabaseController getDBConnection];

    const char * text = "SELECT * FROM Event WHERE Id = ?";
    sqlite3_stmt * select_statement;

    int prepare_result = sqlite3_prepare_v2(connection, text, -1, &select_statement, NULL);
    if ((prepare_result != SQLITE_DONE) && (prepare_result != SQLITE_OK)) {
        // error
        sqlite3_close(connection);
        return result;
    }

    sqlite3_bind_int(select_statement, 1, id);

    if (sqlite3_step(select_statement) == SQLITE_ROW) {
        result = [[[Event alloc] init] autorelease];

        result.Id = sqlite3_column_int(select_statement, 0);
        result.Serial = sqlite3_column_int(select_statement, 1);
        result.Name = (((char *) sqlite3_column_text(select_statement, 2)) == NULL)? nil:[NSString stringWithUTF8String:((char *) sqlite3_column_text(select_statement, 2))];
        result.Date = [NSDate dateWithTimeIntervalSince1970:sqlite3_column_double(select_statement, 3)];
    }
    sqlite3_finalize(select_statement);

    sqlite3_close(connection);
    return (result);
}