iPhone開發進階（9）--- 用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