Objective-c通过FMDB操作SQLite数据库的步骤:
1.安裝FMDB
引入相关文件
2.打开数据库
NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES)objectAtIndex:0];
NSString *dbPath = [docPathstringByAppendingPathComponent:@"user.db"];
3.操作table
//创建表(FMDB中只有update和query操作,出了查询其他都是update操作)
[database executeUpdate:@"create table user (name text,gender text,age integer)"];
//插入数据
BOOL insert = [databaseexecuteUpdate:@"insert into user values (?,?,?)", self.nameTextField.text,self.genderTextField.text,self.ageTextField.text];
//不需要像Android中那样关闭Cursor关闭FMResultSet,因为相关的数据库关闭时,FMResultSet也会被自动关闭
FMResultSet *resultSet = [databaseexecuteQuery:@"select * from user"];
//参数必须是NSObject的子类,int,double,bool这种基本类型,需要封装成对应的包装类才可以
BOOL update = [databaseexecuteUpdate:@"update user set name = ? where age = ?",@"liu",[NSNumbernumberWithInt:24]];
//删除数据
BOOL delete = [databaseexecuteUpdate:@"delete from user where name = ?",@"liuyinghui"];
4.关闭数据库
[databaseQueueclose];
项目实战
1.在Xcode6.2下新建一个Single View Application工程,命名为SaliteModel。
引入FMDB,添加支持库libsqlite3.dylib
2. 在界面上添加控件并关联属性
关联属性
@property (strong,nonatomic) IBOutletUITextField *nameTextField;
@property (strong,nonatomic) IBOutletUITextField *genderTextField;
@property (strong,nonatomic) IBOutletUITextField *ageTextField;
添加按钮响应方法
//点击按钮后执行保存到数据库的操作
- (IBAction)saveButtonClicked:(id)sender {
//获取Document文件夹下的数据库文件,没有则创建
NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES)objectAtIndex:0];
NSString *dbPath = [docPathstringByAppendingPathComponent:@"user.db"];
//获取数据库并打开
FMDatabase *database = [FMDatabasedatabaseWithPath:dbPath];
if (![databaseopen]) {
NSLog(@"Open database failed");
return;
}
//创建表(FMDB中只有update和query操作,出了查询其他都是update操作)
[database executeUpdate:@"create table user (name text,gender text,age integer)"];
//插入数据
BOOL insert = [databaseexecuteUpdate:@"insert into user values (?,?,?)",self.nameTextField.text,self.genderTextField.text,self.ageTextField.text];
if (insert) {
UIAlertView *alert = [[UIAlertViewalloc] initWithTitle:@"提示"message:@"保存成功"delegate:selfcancelButtonTitle:@"确定"otherButtonTitles:nil,nil];
[alertshow];
}
[databaseclose];
}
//点击按钮后执行查询操作
- (IBAction)queryButtonTapped:(id)sender {
NSArray *paths =NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES);
NSString *documentPath = [pathsobjectAtIndex:0];
NSString *dbPath = [documentPathstringByAppendingPathComponent:@"user.db"];
FMDatabase *database = [FMDatabasedatabaseWithPath:dbPath];
if (![databaseopen]) {
return;
}
//不需要像Android中那样关闭Cursor关闭FMResultSet,因为相关的数据库关闭时,FMResultSet也会被自动关闭
FMResultSet *resultSet = [databaseexecuteQuery:@"select * from user"];
while ([resultSetnext]) {
NSString *name = [resultSetstringForColumn:@"name"];
NSString *gender = [resultSetstringForColumn:@"gender"];
int age = [resultSetintForColumn:@"age"];
NSLog(@"Name:%@,Gender:%@,Age:%d",name,gender,age);
}
[databaseclose];
//这里也不需要release
// [database release];
}
//执行条件查询操作
- (IBAction)queryByConditionBtnTapped:(id)sender {
NSArray *paths =NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES);
NSString *docPath = [pathsobjectAtIndex:0];
NSString *dbPath = [docPathstringByAppendingPathComponent:@"user.db"];
FMDatabase *database = [FMDatabasedatabaseWithPath:dbPath];
if (![databaseopen]) {
return;
}
FMResultSet *resultSet = [databaseexecuteQuery:@"select * from user where name = ?",@"Ryan"];
while ([resultSetnext]) {
NSString *name = [resultSetstringForColumn:@"name"];
NSString *gender = [resultSetstringForColumn:@"gender"];
int age = [resultSetintForColumn:@"age"];
NSLog(@"Name:%@,Gender:%@,Age:%d",name,gender,age);
}
[databaseclose];
}
//执行更新操作
- (IBAction)updateBtnTapped:(id)sender {
NSArray *paths =NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES);
NSString *docPath = [pathsobjectAtIndex:0];
NSString *dbPath = [docPathstringByAppendingPathComponent:@"user.db"];
FMDatabase *database = [FMDatabasedatabaseWithPath:dbPath];
if (![databaseopen]) {
return;
}
//参数必须是NSObject的子类,int,double,bool这种基本类型,需要封装成对应的包装类才可以
BOOL update = [databaseexecuteUpdate:@"update user set name = ? where age = ?",@"liuyinghui",[NSNumbernumberWithInt:24]];
if(update){
UIAlertView *alert = [[UIAlertViewalloc] initWithTitle:@"提示"message:@"更新成功"delegate:selfcancelButtonTitle:@"确定"otherButtonTitles:nil,nil];
[alertshow];
}
[databaseclose];
}
- (IBAction)deleteBtnTapped:(id)sender {
NSArray *paths =NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES);
NSString *docPath = [pathsobjectAtIndex:0];
NSString *dbPath = [docPathstringByAppendingPathComponent:@"user.db"];
FMDatabase *database = [FMDatabasedatabaseWithPath:dbPath];
if (![databaseopen]) {
return;
}
BOOL delete = [databaseexecuteUpdate:@"delete from user where name = ?",@"liuyinghui"];
if (delete) {
UIAlertView *alert = [[UIAlertViewalloc] initWithTitle:@"提示"message:@"删除成功"delegate:selfcancelButtonTitle:@"确定"otherButtonTitles:nil,nil];
[alertshow];
}
[databaseclose];
}
-(void)executeDBOperation
{
//获取Document文件夹下的数据库文件,没有则创建
NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES)objectAtIndex:0];
NSString *dbPath = [docPathstringByAppendingPathComponent:@"user.db"];
FMDatabaseQueue *databaseQueue = [FMDatabaseQueuedatabaseQueueWithPath:dbPath];
[databaseQueueinDatabase:^(FMDatabase *db){
[db executeUpdate:@"insert into user values (?,?,?)",@"Ren",@"Male",[NSNumbernumberWithInt:20]];
}];
[databaseQueueclose];
}