DOC

sqllite

By Douglas Griffin,2014-09-16 03:54
15 views 0
sqllite

; $ 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${V

    ER}.sdk/usr/lib/libsqlite3.0.dylib

    这样一来就可以访问数据库了,但是为了更加方便的操作数据库,这里使用 FMDB for iPhone 1 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; 1

    NSError *error; 2

    NSFileManager *fm = [NSFileManager defaultManager]; 3

    NSArray *paths = 4

    5 NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,

    6 NSUserDomainMask, YES);

    NSString *documentsDirectory = [paths objectAtIndex:0]; 7

    NSString *writableDBPath = [documentsDirectory 8

    9 stringByAppendingPathComponent:@"sample.db"]; 10 success = [fm fileExistsAtPath:writableDBPath];

    if(!success){ 11

     NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] 12

    13 stringByAppendingPathComponent:@"sample.db"]; 14 success = [fm copyItemAtPath:defaultDBPath toPath:writableDBPath

    15 error:&error];

     if(!success){ 16

     NSLog([error localizedDescription]); 17

    18 }

    19 }

    20

    21 // 连接DB

    FMDatabase* db = [FMDatabase databaseWithPath:writableDBPath]; 22

    if ([db open]) { 23

    24 [db setShouldCacheStatements:YES]; 25

    26 // INSERT

    27 [db beginTransaction];

     int i = 0; 28

     while (i++ < 20) { 29

    30 [db executeUpdate:@"INSERT INTO TEST (name) values (?)" ,

    [NSString stringWithFormat:@"number %d", i]]; 31

     if ([db hadError]) { 32

    33 NSLog(@"Err %d: %@", [db lastErrorCode], [db

    34 lastErrorMessage]);

    35 }

    36 }

    37 [db commit];

38

    39 // SELECT

     FMResultSet *rs = [db executeQuery:@"SELECT * FROM TEST"]; 40

     while ([rs next]) { 41

     NSLog(@"%d %@", [rs intForColumn:@"id"], [rs

    stringForColumn:@"name"]);

     }

     [rs close];

     [db close];

    }else{

     NSLog(@"Could not open db.");

    }

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

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

    1 $ sqlite3 sample.db

    2 sqlite> CREATE TABLE TbNote( 3 ...> id INTEGER PRIMARY KEY, 4 ...> title VARCHAR(255), 5 ...> body VARCHAR(255)

    6 ...> ); 创建DTOData Transfer Object

    1 //TbNote.h 2 #import

    3

    @interface TbNote : NSObject { 4

     int index; 5

     NSString *title; 6

     NSString *body; 7

    8 }

    9

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

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

    12

    - (id)initWithIndex:(int)newIndex Title:(NSString *)newTitle 13

    Body:(NSString *)newBody; 14

    - (int)getIndex; 15

    16

    @end 17

    18

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

    21

    @implementation TbNote 22

    @synthesize title, body; 23

    24

    - (id)initWithIndex:(int)newIndex Title:(NSString *)newTitle 25

    Body:(NSString *)newBody{ 26

     if(self = [super init]){ 27

    28 index = newIndex;

     self.title = newTitle; 29

     self.body = newBody; 30

    31 }

     return self; 32

    33 }

34

    - (int)getIndex{ 35

     return index; 36

    37 }

    38

    - (void)dealloc { 39

    40 [title release]; 41 [body release];

     [super dealloc]; 42

    43 }

    @end

    创建DAOData Access Objects 这里将 FMDB 的函数调用封装为 DAO 的方式。

    1 //BaseDao.h

    2 #import

    3

    @class FMDatabase; 4

    5

    @interface BaseDao : NSObject { 6

     FMDatabase *db; 7

    8 }

    9

    @property (nonatomic, retain) FMDatabase *db; 10

    11

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

    13

    @end 14

    15

    16 //BaseDao.m

    17 #import "SqlSampleAppDelegate.h"

    18 #import "FMDatabase.h" 19 #import "FMDatabaseAdditions.h"

    20 #import "BaseDao.h" 21

    @implementation BaseDao 22

    @synthesize db; 23

    24

    - (id)init{ 25

     if(self = [super init]){ 26

    27 // AppDelegate 取得打开的数据库

     SqlSampleAppDelegate *appDelegate = (SqlSampleAppDelegate 28

    *)[[UIApplication sharedApplication] delegate]; 29

    30 db = [[appDelegate db] retain];

    31 }

     return self; 32

    33 }

    34 // 子类中实现

    -(NSString *)setTable:(NSString *)sql{ 35

     return NULL; 36

    37 }

    38

    - (void)dealloc { 39

    40 [db release];

     [super dealloc]; 41

    42 }

    43

    @end

    下面是访问 TbNote 表格的类。

    1 //TbNoteDao.h 2 #import

    3 #import "BaseDao.h"

    4

    @interface TbNoteDao : BaseDao { 5

    6 }

    7

    -(NSMutableArray *)select; 8

    -(void)insertWithTitle:(NSString *)title Body:(NSString *)body; 9

    -(BOOL)updateAt:(int)index Title:(NSString *)title Body:(NSString 10

    *)body; 11

    -(BOOL)deleteAt:(int)index; 12

    13

    @end 14

    15

    16 //TbNoteDao.m

    17 #import "FMDatabase.h"

    18 #import "FMDatabaseAdditions.h" 19 #import "TbNoteDao.h"

    20 #import "TbNote.h"

    21

    @implementation TbNoteDao 22

    23

    -(NSString *)setTable:(NSString *)sql{ 24

     return [NSString stringWithFormat:sql, @"TbNote"]; 25

    26 }

    27 // SELECT

    -(NSMutableArray *)select{ 28

     NSMutableArray *result = [[[NSMutableArray alloc] 29

    30 initWithCapacity:0] autorelease];

     FMResultSet *rs = [db executeQuery:[self setTable:@"SELECT * 31

    32 FROM %@"]];

     while ([rs next]) { 33

     TbNote *tr = [[TbNote alloc] 34

    35 initWithIndex:[rs intForColumn:@"id"]

    36 Title:[rs stringForColumn:@"title"]

    37 Body:[rs stringForColumn:@"body"]

    38 ];

    39 [result addObject:tr]; 40 [tr release];

    41 }

    42 [rs close];

     return result; 43

    44 }

    45 // INSERT

    -(void)insertWithTitle:(NSString *)title Body:(NSString *)body{ 46

     [db executeUpdate:[self setTable:@"INSERT INTO %@ (title, body) 47

    48 VALUES (?,?)"], title, body];

     if ([db hadError]) { 49

    50 NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);

51 }

    52 }

    53 // UPDATE

    -(BOOL)updateAt:(int)index Title:(NSString *)title Body:(NSString 54

    *)body{ 55

     BOOL success = YES; 56

     [db executeUpdate:[self setTable:@"UPDATE %@ SET title=?, body=? 57

    WHERE id=?"], title, body, [NSNumber numberWithInt:index]]; 58

     if ([db hadError]) { 59

    60 NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);

    61 success = NO; 62 }

     return success; 63

    64 }

    65 // DELETE

    - (BOOL)deleteAt:(int)index{ 66

     BOOL success = YES; 67

     [db executeUpdate:[self setTable:@"DELETE FROM %@ WHERE id = ?"], 68

    [NSNumber numberWithInt:index]]; 69

     if ([db hadError]) { 70

    71 NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);

    72 success = NO; 73 }

     return success; 74

    }

    - (void)dealloc {

     [super dealloc];

    }

    @end

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

    1 //NoteController.h 2 #import

    3

    @class TbNoteDao; 4

5

    @interface NoteController : UIViewController 6

    <UITableViewDataSource, UITableViewDelegate>{ 7

     UITableView *myTableView; 8

     TbNoteDao *tbNoteDao; 9

     NSMutableArray *record; 10

    11 }

    12

    @property (nonatomic, retain) UITableView *myTableView; 13

    @property (nonatomic, retain) TbNoteDao *tbNoteDao; 14

    @property (nonatomic, retain) NSMutableArray *record; 15

    16

    @end 17

    18

    19 //NoteController.m 20 #import "NoteController.h" 21 #import "TbNoteDao.h" 22 #import "TbNote.h" 23

    @implementation NoteController 24

    @synthesize myTableView, tbNoteDao, record; 25

    26

    - (id)initWithNibName:(NSString *)nibNameOrNil bundle:(NSBundle 27

    *)nibBundleOrNil { 28

     if (self = [super initWithNibName:nibNameOrNil 29

    30 bundle:nibBundleOrNil]) {

     tbNoteDao = [[TbNoteDao alloc] init]; 31

    32 [tbNoteDao insertWithTitle:@"TEST TITLE" Body:@"TEST BODY"];

    33 // [tbNoteDao updateAt:1 Title:@"UPDATE TEST" Body:@"UPDATE

    34 BODY"];

    35 // [tbNoteDao deleteAt:1]; 36 record = [[tbNoteDao select] retain];

    37 }

     return self; 38

    39 }

    40

    - (void)viewDidLoad { 41

     [super viewDidLoad]; 42

     myTableView = [[UITableView alloc] initWithFrame:[[UIScreen 43

    44 mainScreen] applicationFrame]];

     myTableView.delegate = self; 45

     myTableView.dataSource = self; 46

     self.view = myTableView; 47

    48 }

    49

    - (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView 50

    51 {

     return 1; 52

    53 }

    54

    - (NSInteger)tableView:(UITableView *)tableView 55

    numberOfRowsInSection:(NSInteger)section { 56

     return [record count]; 57

    58 }

    59

    - (UITableViewCell *)tableView:(UITableView *)tableView 60

    cellForRowAtIndexPath:(NSIndexPath *)indexPath { 61

     static NSString *CellIdentifier = @"Cell"; 62

    63

     UITableViewCell *cell = [tableView 64

    65 dequeueReusableCellWithIdentifier:CellIdentifier];

     if (cell == nil) { 66

     cell = [[[UITableViewCell alloc] initWithFrame:CGRectZero 67

    68 reuseIdentifier:CellIdentifier] autorelease];

    69 }

     TbNote *tr = (TbNote *)[record objectAtIndex:indexPath.row]; 70

     cell.text = [NSString stringWithFormat:@"%i %@", [tr getIndex], 71

    72 tr.title];

     return cell; 73

    }

    - (void)didReceiveMemoryWarning {

     [super didReceiveMemoryWarning];

    }

Report this document

For any questions or suggestions please email
cust-service@docsford.com