用SQLite管理数据库文档格式.docx
《用SQLite管理数据库文档格式.docx》由会员分享,可在线阅读,更多相关《用SQLite管理数据库文档格式.docx(16页珍藏版)》请在冰点文库上搜索。
FMDatabaseAdditions.m
FMResultSet.h
FMResultSet.m
通过FMDB的方法使用SQLite
使用SQL操作数据库的代码在程序库的fmdb.m文件中大部分都列出了、只是连接数据库文件的时候需要注意—执行的时候,参照的数据库路径位于Document目录下,之前把刚才的sample.db文件拷贝过去就好了。
/Users/xxxx/Library/ApplicationSupport/iPhoneSimulator/User/Applications/xxxx/Documents/sample.db
以下为链接数据库时的代码:
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
BOOLsuccess;
NSError*error;
NSFileManager*fm=[NSFileManagerdefaultManager];
NSArray*paths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES);
NSString*documentsDirectory=[pathsobjectAtIndex:
0];
NSString*writableDBPath=[documentsDirectorystringByAppendingPathComponent:
@"
sample.db"
];
success=[fmfileExistsAtPath:
writableDBPath];
if(!
success){
NSString*defaultDBPath=[[[NSBundlemainBundle]resourcePath]stringByAppendingPathComponent:
success=[fmcopyItemAtPath:
defaultDBPathtoPath:
writableDBPatherror:
&
error];
if(!
NSLog([errorlocalizedDescription]);
}
}
//连接DB
FMDatabase*db=[FMDatabasedatabaseWithPath:
if([dbopen]){
[dbsetShouldCacheStatements:
YES];
//INSERT
[dbbeginTransaction];
inti=0;
while(i++<
20){
[dbexecuteUpdate:
INSERTINTOTEST(name)values(?
)"
[NSStringstringWithFormat:
number%d"
i]];
if([dbhadError]){
NSLog(@"
Err%d:
%@"
[dblastErrorCode],[dblastErrorMessage]);
[dbcommit];
//SELECT
FMResultSet*rs=[dbexecuteQuery:
SELECT*FROMTEST"
while([rsnext]){
%d%@"
[rsintForColumn:
id"
],[rsstringForColumn:
name"
]);
[rsclose];
[dbclose];
}else{
Couldnotopendb."
);
接下来再看看用DAO的形式来访问数据库的使用方法,代码整体构造如下。
首先创建如下格式的数据库文件:
CREATETABLETbNote(
titleVARCHAR(255),
bodyVARCHAR(255)
创建DTO(DataTransferObject)
42
43
//TbNote.h
#import<
Foundation/Foundation.h>
@interfaceTbNote:
NSObject{
intindex;
NSString*title;
NSString*body;
@property(nonatomic,retain)NSString*title;
@property(nonatomic,retain)NSString*body;
-(id)initWithIndex:
(int)newIndexTitle:
(NSString*)newTitleBody:
(NSString*)newBody;
-(int)getIndex;
@end
//TbNote.m
#import"
TbNote.h"
@implementationTbNote
@synthesizetitle,body;
(NSString*)newBody{
if(self=[superinit]){
index=newIndex;
self.title=newTitle;
self.body=newBody;
returnself;
-(int)getIndex{
returnindex;
-(void)dealloc{
[titlerelease];
[bodyrelease];
[superdealloc];
创建DAO(DataAccessObjects)
这里将FMDB的函数调用封装为DAO的方式。
//BaseDao.h
@classFMDatabase;
@interfaceBaseDao:
FMDatabase*db;
@property(nonatomic,retain)FMDatabase*db;
-(NSString*)setTable:
(NSString*)sql;
//BaseDao.m
SqlSampleAppDelegate.h"
FMDatabase.h"
FMDatabaseAdditions.h"
BaseDao.h"
@implementationBaseDao
@synthesizedb;
-(id)init{
//由AppDelegate取得打开的数据库
SqlSampleAppDelegate*appDelegate=(SqlSampleAppDelegate*)[[UIApplicationsharedApplication]delegate];
db=[[appDelegatedb]retain];
//子类中实现
(NSString*)sql{
returnNULL;
[dbrelease];
下面是访问TbNote表格的类。
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
//TbNoteDao.h
@interfaceTbNoteDao:
BaseDao{
-(NSMutableArray*)select;
-(void)insertWithTitle:
(NSString*)titleBody:
(NSString*)body;
-(BOOL)updateAt:
(int)indexTitle:
-(BOOL)deleteAt:
(int)index;
//TbNoteDao.m
TbNoteDao.h"
@implementationTbNoteDao
return[NSStringstringWithFormat:
sql,@"
TbNote"
//SELECT
-(NSMutableArray*)select{
NSMutableArray*result=[[[NSMutableArrayalloc]initWithCapacity:
0]autorelease];
[selfsetTable:
SELECT*FROM%@"
]];
TbNote*tr=[[TbNotealloc]
initWithIndex:
[rsintForColumn:
]
Title:
[rsstringForColumn:
title"
Body:
body"
];
[resultaddObject:
tr];
[trrelease];
returnresult;
//INSERT
(NSString*)body{
INSERTINTO%@(title,body)VALUES(?
?
],title,body];
//UPDATE
BOOLsuccess=YES;
UPDATE%@SETtitle=?
body=?
WHEREid=?
"
],title,body,[NSNumbernumberWithInt:
index]];
success=NO;
returnsuccess;
//DELETE
-(BOOL)deleteAt:
(int)index{
DELETEFROM%@WHEREid=?
],[NSNumbernumberWithInt:
为了确认程序正确,我们添加一个UITableView。
使用initWithNibName测试DAO。
//NoteController.h
UIKit/UIKit.h>
@classTbNoteDao;
@interfaceNoteController:
UIViewController<
UITableViewDataSource,UITableViewDelegate>
{
UITableView*myTableView;
TbNoteDao*tbNoteDao;
NSMutableArray*record;
@property(nonatomic,retain)UITableView*myTableView;
@property(nonatomic,retain)TbNoteDao*tbNoteDao;
@property(nonatomic,retain)NSMutableArray*record;
//NoteController.m
NoteController.h"
@implementationNoteController
@synthesizemyTableView,tbNoteDao,record;
-(id)initWithNibName:
(NSString*)nibNameOrNilbundle:
(NSBundle*)nibBundleOrNil{
if(self=[superinitWithNibName:
nibNameOrNilbundle:
nibBundleOrNil]){
tbNoteDao=[[TbNoteDaoalloc]init];
[tbNoteDaoinsertWithTitle:
TESTTITLE"
TESTBODY"
//[tbNoteDaoupdateAt:
1Title:
UPDATETEST"
UPDATEBODY"
//[tbNoteDaodeleteAt:
1];
record=[[tbNoteDaoselect]retain];
-(void)viewDidLoad{
[superviewDidLoad];
myTableView=[[UITableViewalloc]initWithFrame:
[[UIScreenmainScreen]applicationFrame]];
myTableView.delegate=self;
myTableView.dataSource=self;
self.view=myTableView;
-(NSInteger)numberOfSectionsInTableView:
(UITableView*)tableView{
return1;
-(NSInteger)tableView:
(UITableView*)tableViewnumberOfRowsInSection:
(NSInteger)section{
return[recordcount];
-(UITableViewCell*)tableView:
(UITableView*)tableViewcellForRowAtIndexPath:
(NSIndexPath*)indexPath{
staticNSString*CellIdentifier=@"
Cell"
;
UITableViewCell*cell=[tableViewdequeueReusableCellWithIdentifier:
CellIdentifier];
if(cell==nil){
cell=[[[UITableViewCellalloc]initWithFrame:
CGRectZeroreuseIdentifier:
CellIdentifier]autorelease];
TbNote*tr=(TbNote*)[recordobjectAtIndex:
indexPath.row];
cell.text=[NSStringstringWithFormat:
%i%@"
[trgetIndex],tr.title];
returncell;
-(void)didReceiveMemoryWarning{
[superdidReceiveMemoryWarning];
最后我们开看看连接DB,和添加ViewController的处理。
这一同样不使用InterfaceBuilder。