用SQLite管理數據庫

http://www.yifeiyang.net/iphone-developer-advanced-9-management-database-using-sqlite/ iPhone開發進階（9）--- 用SQLite管理數據庫今天我們來看看 iPhone 中數據庫的使用方法。iPhone 中使用名為 SQLite 的數據庫管理系統。它是一款輕型的數據庫，是遵守ACID的關聯式數據庫管理系統，它的設計目標是嵌入式的，而且目前已經在很多嵌入式產品中使用了它，它占用資源非常的低，在嵌入式設備中，可能只需要幾百K的內存就夠了。它能夠支持Windows/Linux/Unix等等主流的操作系統，同時能夠跟很多程序語言相結合，比如Tcl、PHP、Java等，還有ODBC接口，同樣比起Mysql、PostgreSQL這兩款開源世界著名的數據庫管理系統來講，它的處理速度比他們都快。其使用步驟大致分為以下幾步：1 創建DB文件和表格2 添加必須的庫文件(FMDB for iPhone, libsqlite3.0.dylib)3 通過 FMDB 的方法使用 SQLite創建DB文件和表格12345$ sqlite3 sample.dbsqlite> 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。1svn co http://flycode.googlecode.com/svn/trunk/fmdb fmdb如上下載該庫，并將以下文件添加到工程文件中：FMDatabase.hFMDatabase.mFMDatabaseAdditions.hFMDatabaseAdditions.mFMResultSet.hFMResultSet.m通過 FMDB 的方法使用 SQLite使用 SQL 操作數據庫的代碼在程序庫的 fmdb.m 文件中大部分都列出了、只是連接數據庫文件的時候需要注意 — 執行的時候，參照的數據庫路徑位于 Document 目錄下，之前把剛才的 sample.db 文件拷貝過去就好了。位置如下/Users/xxxx/Library/Application Support/iPhone Simulator/User/Applications/xxxx/Documents/sample.db以下為鏈接數據庫時的代碼：1234567891011121314151617181920212223242526272829303132333435363738394041BOOL 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]); }}//連接DBFMDatabase* 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 的形式來訪問數據庫的使用方法，代碼整體構造如下。首先創建如下格式的數據庫文件：123456$ sqlite3 sample.dbsqlite> CREATE TABLE TbNote( ...> id INTEGER PRIMARY KEY, ...> title VARCHAR(255), ...> body VARCHAR(255) ...> );創建DTO（Data Transfer Object）12345678910111213141516171819202122232425262728293031323334353637383940414243//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 [email protected] 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 的方式。12345678910111213141516171819202122232425262728293031323334353637383940414243//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 [email protected] 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 表格的類。1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374//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。12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273//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 [email protected] 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。123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778//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 [email protected] 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