首页 > 程序开发 > 移动开发 > IOS >

iPhone开发进阶(9)--- 用SQLite管理数据库

2011-10-19

今天我们来看看iPhone 中数据库的使用方法。iPhone 中使用名为SQLite 的数据库管理系统。它是一款轻型的数据库,是遵守ACID的关联式数据库管理系统,它的设计目标是嵌入式的,而且目前已经在很多嵌入式产品中...

今天我们来看看iPhone 中数据库的使用方法。iPhone 中使用名为SQLite 的数据库管理系统。它是一款轻型的数据库,是遵守ACID的关联式数据库管理系统,它的设计目标是嵌入式的,而且目前已经在很多嵌入式产品中使用了它,它占用资源非常的低,在嵌入式设备中,可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统,同时能够跟很多程序语言相结合,比如Tcl、PHPJava等,还有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

相关文章
最新文章
热点推荐