iPhone开发之SQLite使用详解

skyleemon

2012-06-28

SQLite是一个开源的嵌入式关系数据库，它在2000年由D. Richard Hipp发布，它的减少应用程序管理数据的开销，SQLite可移植性好，很容易使用，很小，高效而且可靠。

SQLite嵌入到使用它的应用程序中，它们共用相同的进程空间，而不是单独的一个进程。从外部看，它并不像一个RDBMS，但在进程内部，它却是完整的，自包含的数据库引擎。

嵌入式数据库的一大好处就是在你的程序内部不需要网络配置，也不需要管理。因为客户端和服务器在同一进程空间运行。SQLite 的数据库权限只依赖于文件系统，没有用户帐户的概念。SQLite 有数据库级锁定，没有网络服务器。它需要的内存，其它开销很小，适合用于嵌入式设备。你需要做的仅仅是把它正确的编译到你的程序。

ios下使用sqlite首先导入SQLite3.0的lib库。然后包含头文件#import <sqlite3.h>

下面的代码主要对常用的数据库操作如查询、插入、删除、更新等进行封装，方便以后使用。

头文件代码如下：

#import <Foundation/Foundation.h>
#import <sqlite3.h>
#import "User.h"
#define kFileName @"database.sqlite"
@interface SQLite3Util : NSObject{
sqlite3_stmt *stmt;
sqlite3 *database;
}
- (NSString *)dataFilePath;
- (int)getCountOfDB;
- (BOOL)insertOrUpdateUser:(NSString *)sql;
- (BOOL)deleteUser:(NSInteger)userId;
- (NSMutableArray *)getUsers;
- (User *)getUser:(NSInteger)userId;
- (BOOL)openDatabase;
- (void)closeDatabase;
@end

实现如下：

#import "SQLite3Util.h"
@implementation SQLite3Util
//Return Database path
- (NSString *)dataFilePath{
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUSErDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
return [documentsDirectory stringByAppendingPathComponent:kFileName];
}
//Insert or update data
- (BOOL)insertOrUpdateUser:(NSString *)sql{
if ([self openDatabase]) {
if (sqlite3_exec(database, [sql UTF8String], nil, &stmt, nil) != SQLITE_OK) {
NSLog(@"Insert or update is failed!");
return NO;
}else{
// sqlite3_finalize(stmt);
NSLog(@"Insert or update successfully!");
return YES;
}
sqlite3_close(database);
}
return NO;
}
//Delete a user from database
- (BOOL)deleteQuestion:(NSInteger)userId{
NSString *sql = [NSString stringWithFormat:@"Delete FROM User WHERE id = %i", userId];
if ([self openDatabase]) {
if (sqlite3_exec(database, [sql UTF8String], nil, &stmt, nil) != SQLITE_OK) {
NSLog(@"Delete data is failed!");
return NO;
}else{
sqlite3_finalize(stmt);
NSLog(@"Delete data successfully!");
return YES;
}
sqlite3_close(database);
}
return NO;
}
//Get users
- (NSMutableArray *)getUsers{
NSMutableArray *users = [[NSMutableArray alloc] init];
NSString *sql = [NSString stringWithFormat:@"SELECT * FROM User];
if ([self openDatabase]) {
if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt, nil) == SQLITE_OK) {
// NSLog(@"SQL is Prepared!");
while (sqlite3_step(stmt) == SQLITE_ROW) {
User *user = [[Question alloc] init];
char *name = (char *)sqlite3_column_text(stmt, 0);
[user setName:[NSString stringWithUTF8String:name]];
char *index = (char *)sqlite3_column_text(stmt, 1);
[user setId:[[NSString stringWithUTF8String:index] intValue]];
[users addObject: user];
}
sqlite3_finalize(stmt);
}
}
sqlite3_close(database);
return users;
}
//Get count of the users
- (int)getCountOfDB{
int count = 0;
NSString *sql = [NSString stringWithFormat:@"SELECT * FROM User"];
if ([self openDatabase]) {
if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt, nil) == SQLITE_OK) {
while (sqlite3_step(stmt) == SQLITE_ROW) {
count ++;
}
sqlite3_finalize(stmt);
}
}
return count;
}
- (User *)getUser:(NSInteger)userId{
User *user = [[User alloc] init];
NSString *sql = [NSString stringWithFormat:@"SELECT * FROM User WHERE id = %i", userId];
// NSLog(@"sql = %@", sql);
if ([self openDatabase]) {
if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt, nil) == SQLITE_OK) {
NSLog(@"SQL is Prepared!");
while (sqlite3_step(stmt) == SQLITE_ROW) {
char *name = (char *)sqlite3_column_text(stmt, 0);
[user setName:[NSString stringWithUTF8String:name]];
[user setId:userId];
}
sqlite3_finalize(stmt);
}
}
return user;
}
- (BOOL)openDatabase{
if (sqlite3_open([[self dataFilePath] UTF8String], &database) == SQLITE_OK) {
NSLog(@"SQL is Open!");
return YES;
}
return NO;
}
- (void)closeDatabase{
sqlite3_close(database);
}
@end