iOS中持久化存储SQLite(一)

在iOS中做持久化存储有多种方案，其中包括plist, preference, sqlite, core data,其中：
（1）plist, preference适合小型数据存储，因为每次存储前都必须将文件内容读到内存中，因此如果数据量过大就会占用大量的内存，同时修改与查询也不是很方便，都必须读入内存中，自己来写相应的查询函数来查询结果。
（2）sqlite为嵌入式系统中使用的持久化存储数据库方案，其量级比较轻，因此只需要占用几百空间就能达到数据库的效果，方便数据的增删查改。在iOS中其数据库操作分为纯C语言接口以及对其进行OC封装的接口第三方库FMDB
（3）Core Data为苹果自己在Sqlite的上封装的数据存储方案，虽然其屏蔽了数据库操作的语句，但是其量级比较重。
下面就介绍如何使用纯C语言接口来操作数据库：
1.数据库的打开
- (void)setUpDB {
NSString *fileName = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@”shop.sqlite”];
int status = sqlite3_open(fileName.UTF8String, &_db);
if (status == SQLITE_OK) {
NSLog(@”打开数据库成功”);
// 创表
const char *sql = “CREATE TABLE IF NOT EXISTS t_shop (id integer PRIMARY KEY, name text NOT NULL, price real)”;
char *errmsg = NULL;
sqlite3_exec(self.db, sql, NULL, NULL, &errmsg);
if (errmsg) {
NSLog(@”创建表失败 - %s”, errmsg);
}
} else {
NSLog(@”打开数据库失败”);
}

}
2.安装数据
- (void)setUpData {
const char *sql = “SELECT name, price FROM t_shop”;
sqlite3_stmt *stmt = NULL;
int status = sqlite3_prepare_v2(self.db, sql, -1, &stmt, NULL);

if (status == SQLITE_OK) {
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        const char *name = (const char *)sqlite3_column_text(stmt, 0);
        const char *price = (const char *)sqlite3_column_text(stmt, 1);

        FPShop *shop = [[FPShop alloc] initWithName:[NSString stringWithUTF8String:name] price:[NSString stringWithUTF8String:price]];
        [self.shops addObject:shop];
    }
}

}
3.插入数据
- (IBAction)insert:(UIButton *)sender {
NSString *sql = [NSString stringWithFormat:@”INSERT INTO t_shop(name, price) VALUES (‘%@’, %f);”, self.nameField.text, self.priceField.text.doubleValue];
sqlite3_exec(self.db, sql.UTF8String, NULL, NULL, NULL);

FPShop *shop = [[FPShop alloc] initWithName:self.nameField.text price:self.priceField.text];
[self.shops addObject:shop];
[self.tableView reloadData];

}
4.搜索数据
- (void)searchBar:(UISearchBar )searchBar textDidChange:(NSString )searchText {
[self.shops removeAllObjects];
NSString *sql = [NSString stringWithFormat:@”SELECT name, price FROM t_shop WHERE name LIKE ‘%%%@%%’ OR price LIKE ‘%%%@%%’”, searchText, searchText];
sqlite3_stmt *stmt = NULL;

int status = sqlite3_prepare_v2(self.db, sql.UTF8String, -1, &stmt, NULL);
if (status == SQLITE_OK) {
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        const char *name = (const char *) sqlite3_column_text(stmt, 0);
        const char *price = (const char *)sqlite3_column_text(stmt, 1);
        FPShop *shop = [[FPShop alloc] initWithName:[NSString stringWithUTF8String:name] price:[NSString stringWithUTF8String:price]];
        [self.shops addObject:shop];

    }
}

[self.tableView reloadData];

}