IOS 操作資料庫（FMDB）

標籤：ios   fmdb   sqlite   sdk   資料庫   

公司最近在做離線緩衝的東西，用的是sqlite資料庫儲存的。使用第三方類庫FMDataBase對資料庫進行相關操作，這是我寫的一個小demo，廢話不說，先上：

在操作資料庫的時候遇到了一點小問題，sqlite裡面string類型就用text來儲存，int型用integer,float用real,

需要注意的是：插入資料的時候如果string類型直接插入，但是要是float或者是integer類型就需要將插入值轉化成NSNumber資料類型如下：[NSNumber numberWithInt:24]

在調試資料庫操作的時候最好在模擬器上進行，然後列印出資料庫路徑，然後查看資料庫裡面的資料，是否存在，這裡推薦一個查看sqlite資料庫的軟體，很小，功能不算強大但是也勉強夠用了。（不要積分）

http://download.csdn.net/detail/zyzxrj/8203551

還需要注意一點，做資料庫增刪的時候不可查看資料庫，這時候資料庫會被鎖住，導致插入或者刪除失敗。但是查詢操作可以。

可參考下面語句

    NSString *createMessageTable = @"create table if not exists message(messageNo integer primary key,failldReason text,contract text,contractPhone text,isRead integer, orderStatus integer, messageType integer, acceptMessageTime text,appointedTime text,orderDate text,startStation text,arrivalStation text,transportLocation text,tradeNo text) ";

這個介面我為了省時間直接xib拖得，相信有一定ios開發基礎的人都可以用代碼寫出來，我也不廢話了，主要說一些資料庫操作的問題：

DataBaseViewController.h:

#import <UIKit/UIKit.h>@interface DataBaseViewController : UIViewController@property (weak, nonatomic) IBOutlet UITextField *nameTextField;@property (weak, nonatomic) IBOutlet UITextField *sexTextField;@property (weak, nonatomic) IBOutlet UITextField *ageTextField;- (IBAction)save:(id)sender;- (IBAction)query:(id)sender;- (IBAction)queryByCondition:(id)sender;- (IBAction)update:(id)sender;- (IBAction)deleteByCondition:(id)sender;@end

DataBaseViewController.m:

////  DataBaseViewController.m//  Location////  Created by admin on 14-11-17.//  Copyright (c) 2014年 admin. All rights reserved.//#import "DataBaseViewController.h"#import "FMDatabase.h"#import "FMDatabaseQueue.h"#define IOS7_OR_LATER   ([[[UIDevice currentDevice] systemVersion]floatValue] >= 7.0)@interface DataBaseViewController ()@end@implementation DataBaseViewController- (id)initWithNibName:(NSString *)nibNameOrNil bundle:(NSBundle *)nibBundleOrNil{    self = [super initWithNibName:nibNameOrNil bundle:nibBundleOrNil];    if (self) {        // Custom initialization    }    return self;}- (void)viewDidLoad{    [super viewDidLoad];    //控制器，點擊空白地方，隱藏鍵盤    CGRect cgrect = self.view.frame;    if (!IOS7_OR_LATER) {        cgrect.origin.y -= 20;    }    UIControl *clickControl = [[UIControl alloc] init];    clickControl.frame = cgrect;    [clickControl addTarget:self action:@selector(hideKeyboard) forControlEvents:UIControlEventTouchUpInside];    [self.view addSubview:clickControl];    [self.view sendSubviewToBack:clickControl];}- (void)didReceiveMemoryWarning{    [super didReceiveMemoryWarning];    // Dispose of any resources that can be recreated.}//隱藏軟鍵盤- (void)hideKeyboard{    [_nameTextField resignFirstResponder];    [_ageTextField resignFirstResponder];    [_sexTextField resignFirstResponder];}- (IBAction)save:(id)sender {    //擷取Document檔案夾下的資料庫檔案，沒有則建立    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];    NSString *dbPath = [docPath stringByAppendingPathComponent:@"user.db"];    NSLog(@"%@",dbPath);    //擷取資料庫並開啟    FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];    if (![dataBase open]) {        NSLog(@"開啟資料庫失敗");        return ;    }    //建立表（FMDB中只有update和query操作，出了查詢其他都是update操作）    [dataBase executeUpdate:@"create table if not exists user(name text,gender text,age integer) "];    //插入資料    BOOL inser = [dataBase executeUpdate:@"insert into user values(?,?,?)",_nameTextField.text,_sexTextField.text,_ageTextField.text];    if (inser) {        UIAlertView *alert = [[UIAlertView alloc]initWithTitle:@"提示" message:@"資訊儲存成功" delegate:self cancelButtonTitle:nil otherButtonTitles:@"確定", nil];        [alert show];    }    [dataBase close];}//查詢全部- (IBAction)query:(id)sender {    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];    NSString *dbPath = [docPath stringByAppendingPathComponent:@"user.db"];        FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];    if (![dataBase open]) {        NSLog(@"開啟資料庫失敗");        return ;    }    FMResultSet *resultSet = [dataBase executeQuery:@"select * from user"];    while ([resultSet next]) {        NSString *name = [resultSet stringForColumn:@"name"];        NSString *genter = [resultSet stringForColumn:@"gender"];        int age = [resultSet intForColumn:@"age"];        NSLog(@"Name:%@,Gender:%@,Age:%d",name,genter,age);    }        [dataBase close];}//條件查詢- (IBAction)queryByCondition:(id)sender {    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];    NSString *dbPath = [docPath stringByAppendingPathComponent:@"user.db"];    FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];    if (![dataBase open]) {        return ;    }//    FMResultSet *resultSet = [dataBase executeQuery:@"select *from user where name = ?",@"ZY"];    FMResultSet *resultSet = [dataBase executeQueryWithFormat:@"select * from user where name = %@",@"zy"];    while ([resultSet next]) {        NSString *name = [resultSet stringForColumnIndex:0];        NSString *gender = [resultSet stringForColumn:@"gender"];        int age = [resultSet intForColumn:@"age"];        NSLog(@"Name:%@,Gender:%@,Age:%d",name,gender,age);    }    [dataBase close];}- (IBAction)update:(id)sender {    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];    NSString *dbPath = [docPath stringByAppendingPathComponent:@"user.db"];    FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];    if (![dataBase open]) {        return ;    }    //參數必須是NSObject的子類，int,double,bool這種基本類型，需要封裝成對應的封裝類才可以    BOOL update = [dataBase executeUpdate:@"update user set age = ? where name = ?",[NSNumber numberWithInt:24],@"ZY"];    if (update) {        UIAlertView *alert = [[UIAlertView alloc]initWithTitle:@"提示" message:@"資訊更新成功" delegate:self cancelButtonTitle:nil otherButtonTitles:@"確定", nil];        [alert show];    }    [dataBase close];}- (IBAction)deleteByCondition:(id)sender{    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];    NSString *dbPath = [docPath stringByAppendingPathComponent:@"user.db"];    FMDatabase *dataBase = [FMDatabase databaseWithPath:dbPath];    if (![dataBase open]) {        return ;    }    BOOL delete = [dataBase executeUpdateWithFormat:@"delete from user where name = %@",@"zy"];    if (delete) {        UIAlertView *alert = [[UIAlertView alloc]initWithTitle:@"提示" message:@"資訊刪除成功" delegate:self cancelButtonTitle:nil otherButtonTitles:@"確定", nil];        [alert show];    }    [dataBase close];}@end

下面是整個demo的源檔案（不要積分的）：

http://download.csdn.net/detail/zyzxrj/8203583

大家要是有不解之處可以參考代碼

IOS 操作資料庫（FMDB）