从 sqlite 数据库存储和检索数据 [英] Storing and retrieving data from sqlite database

问题描述

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

I am building an app which contains a form in one view,in which the user fills all the fields and when he clicks the save button the data must be saved in to database and after navigating back,there's another view which, when entered, must show the saved data(event).

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

I have created a database and have gone through several sqlite3 tutorials;

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

I have done all other changes to my code according to my requirement. However, when I use this statement to check whether data is inserted in database:

SELECT * FROM reminders;

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

I am getting nothing and I am confused whether data is inserted or not.

如何正确保存，如何从数据库中检索数据以使用并在其他视图中显示?

How do I save it properly, and how do I retrieve data from database to use and display it in other view?

推荐答案

首先你应该创建 sqlite3 数据库文件 (检查此链接)，那么您应该将其包含到您的项目中.现在要连接到它，您可以使用以下代码:

First you should create the sqlite3 database file (check this link), then you should include it into your project. Now to connect to it you can use the following code:

#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;
}

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

then to insert a record you can use this code:

+ (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);
}

这篇关于从 sqlite 数据库存储和检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！