iPhone开发进阶（9）--- 用SQLite管理数据库
From:http://www.2cto.com/kf/201110/108296.html
?
今天我们来看看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
