iPhone开发进阶（9）--- 用SQLite管理数据库

时间：2010-10-09 来源：鬼手如冰

博主：易飞扬

原文链接 : http://www.yifeiyang.net/iphone-developer-advanced-9-management-database-using-sqlite/

转载请保留上面文字。

今天我们来看看 iPhone 中数据库的使用方法。iPhone 中使用名为 SQLite 的数据库管理系统。它是一款轻型的数据库，是遵守ACID的关联式数据库管理系统，它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如Tcl、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源世界著名的数据库管理系统来讲，它的处理速度比他们都快。

其使用步骤大致分为以下几步：

创建DB文件和表格
添加必须的库文件(FMDB for iPhone, libsqlite3.0.dylib)
通过 FMDB 的方法使用 SQLite

创建DB文件和表格

$ sqlite3 sample.db
sqlite> CREATE TABLE TEST(
   ...> id INTEGER PRIMARY KEY,
   ...> name VARCHAR(255)
   ...> );

简单地使用上面的语句生成数据库文件后，用一个图形化SQLite管理工具，比如 Lita 来管理还是很方便的。

然后将文件(sample.db)添加到工程中。

添加必须的库文件(FMDB for iPhone, libsqlite3.0.dylib)

首先添加 Apple 提供的 sqlite 操作用程序库 ibsqlite3.0.dylib 到工程中。

位置如下/Developer/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS${VER}.sdk/usr/lib/libsqlite3.0.dylib

这样一来就可以访问数据库了，但是为了更加方便的操作数据库，这里使用 FMDB for iPhone。

svn co http://flycode.googlecode.com/svn/trunk/fmdb fmdb

如上下载该库，并将以下文件添加到工程文件中：

FMDatabase.h
FMDatabase.m
FMDatabaseAdditions.h
FMDatabaseAdditions.m
FMResultSet.h
FMResultSet.m

通过 FMDB 的方法使用 SQLite

使用 SQL 操作数据库的代码在程序库的 fmdb.m 文件中大部分都列出了、只是连接数据库文件的时候需要注意 — 执行的时候，参照的数据库路径位于 Document 目录下，之前把刚才的 sample.db 文件拷贝过去就好了。

位置如下/Users/xxxx/Library/Application Support/iPhone Simulator/User/Applications/xxxx/Documents/sample.db

以下为链接数据库时的代码：

BOOL success;
NSError *error;
NSFileManager *fm = [NSFileManager defaultManager];
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"sample.db"];
success = [fm fileExistsAtPath:writableDBPath];
if(!success){
NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"sample.db"];
success = [fm copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
if(!success){
    NSLog([error localizedDescription]);
}
}

// 连接DB
FMDatabase* db = [FMDatabase databaseWithPath:writableDBPath];
if ([db open]) {
[db setShouldCacheStatements:YES];

// INSERT
[db beginTransaction];
int i = 0;
while (i++ < 20) {
    [db executeUpdate:@"INSERT INTO TEST (name) values (?)" , [NSString stringWithFormat:@"number %d", i]];
    if ([db hadError]) {
      NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }
}
[db commit];

// SELECT
FMResultSet *rs = [db executeQuery:@"SELECT * FROM TEST"];
while ([rs next]) {
    NSLog(@"%d %@", [rs intForColumn:@"id"], [rs stringForColumn:@"name"]);
}
[rs close];
[db close];
}else{
NSLog(@"Could not open db.");
}

接下来再看看用 DAO 的形式来访问数据库的使用方法，代码整体构造如下。

首先创建如下格式的数据库文件：

$ sqlite3 sample.db
sqlite> CREATE TABLE TbNote(
   ...> id INTEGER PRIMARY KEY,
   ...> title VARCHAR(255),
   ...> body VARCHAR(255)
   ...> );

创建DTO（Data Transfer Object）

//TbNote.h
#import <Foundation/Foundation.h>

@interface TbNote : NSObject {
int index;
NSString *title;
NSString *body;
}

@property (nonatomic, retain) NSString *title;
@property (nonatomic, retain) NSString *body;

- (id)initWithIndex:(int)newIndex Title:(NSString *)newTitle Body:(NSString *)newBody;
- (int)getIndex;

@end

//TbNote.m
#import "TbNote.h"

@implementation TbNote
@synthesize title, body;

- (id)initWithIndex:(int)newIndex Title:(NSString *)newTitle Body:(NSString *)newBody{
if(self = [super init]){
    index = newIndex;
    self.title = newTitle;
    self.body = newBody;
}
return self;
}

- (int)getIndex{
return index;
}

- (void)dealloc {
[title release];
[body release];
[super dealloc];
}

@end

创建DAO（Data Access Objects）

这里将 FMDB 的函数调用封装为 DAO 的方式。

//BaseDao.h
#import <Foundation/Foundation.h>

@class FMDatabase;

@interface BaseDao : NSObject {
FMDatabase *db;
}

@property (nonatomic, retain) FMDatabase *db;

-(NSString *)setTable:(NSString *)sql;

@end

//BaseDao.m
#import "SqlSampleAppDelegate.h"
#import "FMDatabase.h"
#import "FMDatabaseAdditions.h"
#import "BaseDao.h"

@implementation BaseDao
@synthesize db;

- (id)init{
if(self = [super init]){
    // 由 AppDelegate 取得打开的数据库
    SqlSampleAppDelegate *appDelegate = (SqlSampleAppDelegate *)[[UIApplication sharedApplication] delegate];
    db = [[appDelegate db] retain];
}
return self;
}
// 子类中实现
-(NSString *)setTable:(NSString *)sql{
return NULL;
}

- (void)dealloc {
[db release];
[super dealloc];
}

@end

下面是访问 TbNote 表格的类。

//TbNoteDao.h
#import <Foundation/Foundation.h>
#import "BaseDao.h"

@interface TbNoteDao : BaseDao {
}

-(NSMutableArray *)select;
-(void)insertWithTitle:(NSString *)title Body:(NSString *)body;
-(BOOL)updateAt:(int)index Title:(NSString *)title Body:(NSString *)body;
-(BOOL)deleteAt:(int)index;

@end

//TbNoteDao.m
#import "FMDatabase.h"
#import "FMDatabaseAdditions.h"
#import "TbNoteDao.h"
#import "TbNote.h"

@implementation TbNoteDao

-(NSString *)setTable:(NSString *)sql{
return [NSString stringWithFormat:sql, @"TbNote"];
}
// SELECT
-(NSMutableArray *)select{
NSMutableArray *result = [[[NSMutableArray alloc] initWithCapacity:0] autorelease];
FMResultSet *rs = [db executeQuery:[self setTable:@"SELECT * FROM %@"]];
while ([rs next]) {
    TbNote *tr = [[TbNote alloc]
              initWithIndex:[rs intForColumn:@"id"]
              Title:[rs stringForColumn:@"title"]
              Body:[rs stringForColumn:@"body"]
              ];
    [result addObject:tr];
    [tr release];
}
[rs close];
return result;
}
// INSERT
-(void)insertWithTitle:(NSString *)title Body:(NSString *)body{
[db executeUpdate:[self setTable:@"INSERT INTO %@ (title, body) VALUES (?,?)"], title, body];
if ([db hadError]) {
    NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
}
}
// UPDATE
-(BOOL)updateAt:(int)index Title:(NSString *)title Body:(NSString *)body{
BOOL success = YES;
[db executeUpdate:[self setTable:@"UPDATE %@ SET title=?, body=? WHERE id=?"], title, body, [NSNumber numberWithInt:index]];
if ([db hadError]) {
    NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    success = NO;
}
return success;
}
// DELETE
- (BOOL)deleteAt:(int)index{
BOOL success = YES;
[db executeUpdate:[self setTable:@"DELETE FROM %@ WHERE id = ?"], [NSNumber numberWithInt:index]];
if ([db hadError]) {
    NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    success = NO;
}
return success;
}

- (void)dealloc {
[super dealloc];
}

@end

为了确认程序正确，我们添加一个 UITableView。使用 initWithNibName 测试 DAO。

//NoteController.h
#import <UIKit/UIKit.h>

@class TbNoteDao;

@interface NoteController : UIViewController <UITableViewDataSource, UITableViewDelegate>{
UITableView *myTableView;
TbNoteDao *tbNoteDao;
NSMutableArray *record;
}

@property (nonatomic, retain) UITableView *myTableView;
@property (nonatomic, retain) TbNoteDao *tbNoteDao;
@property (nonatomic, retain) NSMutableArray *record;

@end

//NoteController.m
#import "NoteController.h"
#import "TbNoteDao.h"
#import "TbNote.h"

@implementation NoteController
@synthesize myTableView, tbNoteDao, record;

- (id)initWithNibName:(NSString *)nibNameOrNil bundle:(NSBundle *)nibBundleOrNil {
if (self = [super initWithNibName:nibNameOrNil bundle:nibBundleOrNil]) {
    tbNoteDao = [[TbNoteDao alloc] init];
    [tbNoteDao insertWithTitle:@"TEST TITLE" Body:@"TEST BODY"];
//    [tbNoteDao updateAt:1 Title:@"UPDATE TEST" Body:@"UPDATE BODY"];
//    [tbNoteDao deleteAt:1];
    record = [[tbNoteDao select] retain];
}
return self;
}

- (void)viewDidLoad {
[super viewDidLoad];
myTableView = [[UITableView alloc] initWithFrame:[[UIScreen mainScreen] applicationFrame]];
myTableView.delegate = self;
myTableView.dataSource = self;
self.view = myTableView;
}

- (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView {
return 1;
}

- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section {
return [record count];
}

- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {
static NSString *CellIdentifier = @"Cell";

UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];
if (cell == nil) {
    cell = [[[UITableViewCell alloc] initWithFrame:CGRectZero reuseIdentifier:CellIdentifier] autorelease];
}
TbNote *tr = (TbNote *)[record objectAtIndex:indexPath.row];
cell.text = [NSString stringWithFormat:@"%i %@", [tr getIndex], tr.title];
return cell;
}

- (void)didReceiveMemoryWarning {
[super didReceiveMemoryWarning];
}

- (void)dealloc {
[super dealloc];
}

@end

最后我们开看看连接DB，和添加 ViewController 的处理。这一同样不使用 Interface Builder。

//SqlSampleAppDelegate.h
#import <UIKit/UIKit.h>

@class FMDatabase;

@interface SqlSampleAppDelegate : NSObject <UIApplicationDelegate> {
UIWindow *window;
FMDatabase *db;
}

@property (nonatomic, retain) IBOutlet UIWindow *window;
@property (nonatomic, retain) FMDatabase *db;

- (BOOL)initDatabase;
- (void)closeDatabase;

@end

//SqlSampleAppDelegate.m
#import "SqlSampleAppDelegate.h"
#import "FMDatabase.h"
#import "FMDatabaseAdditions.h"
#import "NoteController.h"

@implementation SqlSampleAppDelegate

@synthesize window;
@synthesize db;

- (void)applicationDidFinishLaunching:(UIApplication *)application {
if (![self initDatabase]){
    NSLog(@"Failed to init Database.");
}
NoteController *ctrl = [[NoteController alloc] initWithNibName:nil bundle:nil];
[window addSubview:ctrl.view];
[window makeKeyAndVisible];
}

- (BOOL)initDatabase{
BOOL success;
NSError *error;
NSFileManager *fm = [NSFileManager defaultManager];
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"sample.db"];

success = [fm fileExistsAtPath:writableDBPath];
if(!success){
    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"sample.db"];
    success = [fm copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
    if(!success){
      NSLog([error localizedDescription]);
    }
    success = NO;
}
if(success){
    db = [[FMDatabase databaseWithPath:writableDBPath] retain];
    if ([db open]) {
      [db setShouldCacheStatements:YES];
    }else{
      NSLog(@"Failed to open database.");
      success = NO;
    }
}
return success;
}

- (void) closeDatabase{
[db close];
}

- (void)dealloc {
[db release];
[window release];
[super dealloc];
}

@end