数据库SQLite应用

1、导入SQLite库和头文件

1 #import <sqlite3.h>

2、打开数据库，如果在打开的时候遇到了问题，则关闭它并抛出一个断言错误。

1     sqlite3 * database;
2     if (sqlite3_open([[self dataFilePath] UTF8String], &database)
3         != SQLITE_OK) {
4         sqlite3_close(database);
5         NSAssert(0, @"Failed to open database");
6     }

3、确保有一个表来保存数据

1     NSString * createSQL = @"CREATE TABLE IF NOT EXISTS FIELDS "
2     "(ROW INTEGER PRIMARY KEY, FIELD_DATA TEXT);";
3     char * errorMsg;
4     if (sqlite3_exec (database, [createSQL UTF8String],
5                       NULL, NULL, &errorMsg) != SQLITE_OK) {
6         sqlite3_close(database);
7         NSAssert(0, @"Error creating table: %s", errorMsg);
8     }

4、加载数据

创建一个SELECT来从数据库请求所有行，并要求SQLite准备我们的SELECT。这里是告诉SQLite按行号排序各行，以便我们总是以相同顺序获取他们。否则SQLite会按内部存储顺序返回各行。

 1     NSString * query = @"SELECT ROW, FIELD_DATA FROM FIELDS ORDER BY ROW";
 2     sqlite3_stmt * statement;
 3     if (sqlite3_prepare_v2(database, [query UTF8String],
 4                            -1, &statement, nil) == SQLITE_OK)
 5     {
 6         while (sqlite3_step(statement) == SQLITE_ROW) {
 7             int row = sqlite3_column_int(statement, 0);
 8             char *rowData = (char *)sqlite3_column_text(statement, 1);
 9             
10             NSString *fieldValue = [[NSString alloc]
11                                     initWithUTF8String:rowData];
12             UITextField *field = self.lineFields[row];
13             field.text = fieldValue;
14         }
15         sqlite3_finalize(statement);
16     }
17     sqlite3_close(database);

5、applicationWillResignActive：方法首先会打开数据库，然后保存数据。

1     UIApplication * app = [UIApplication sharedApplication];
2     [[NSNotificationCenter defaultCenter]
3      addObserver:self
4      selector:@selector(applicationWillResignActive:)
5      name:UIApplicationWillResignActiveNotification
6      object:app];

 1 - (void)applicationWillResignActive:(NSNotification *)notification
 2 {
 3     sqlite3 * database;
 4     if (sqlite3_open([[self dataFilePath] UTF8String], &database)
 5         != SQLITE_OK) {
 6         sqlite3_close(database);
 7         NSAssert(0, @"Failed to open database");
 8     }
 9     for (int i = 0; i < 4; i++) {
10         UITextField *field = self.lineFields[i];
11         // Once again, inline string concatenation to the rescue:
12         char *update = "INSERT OR REPLACE INTO FIELDS (ROW, FIELD_DATA) "
13         "VALUES (?, ?);";
14         char * errorMsg = NULL;
15         sqlite3_stmt * stmt;
16         if (sqlite3_prepare_v2(database, update, -1, &stmt, nil)
17             == SQLITE_OK) {
18             sqlite3_bind_int(stmt, 1, i);
19             sqlite3_bind_text(stmt, 2, [field.text UTF8String], -1, NULL);
20         }
21         if (sqlite3_step(stmt) != SQLITE_DONE)
22             NSAssert(0, @"Error updating table: %s", errorMsg);
23         sqlite3_finalize(stmt);
24     }
25     sqlite3_close(database);
26 }