iOS-sqlite3&FMDB使用代码示范

数据库操作是我们使用十分频繁的一份操作，在iOS中如何使用数据库，使用什么数据库，是我们不得不考虑的一个问题。

小型数据我们可以使用plist文件，或者NSUserDefaults存储。数据量比较多得情况下，我们可以使用sqlite或者Core Data.

在此先介绍一下sqlite的系统API,然后介绍一下第三方库FMDB,使用第三方库比使用系统的sqlite简单方便。

对数据库的操作，我们可以简单理解为增删改查，下面的具体直接使用代码实现增删改查，不一具体介绍。

  #import <UIKit/UIKit.h> #import <sqlite3.h> #import "Person.h" @interface ViewController : UIViewController {     NSMutableArray *_arrayData; } - (IBAction)addButtonClick:(id)sender; - (IBAction)deleteButtonClick:(id)sender; - (IBAction)updateButtonClick:(id)sender; - (IBAction)selectButtonClick:(id)sender; @end  #import "ViewController.h"  @interface ViewController ()  @end sqlite3 *_database; @implementation ViewController  - (void)viewDidLoad {     [super viewDidLoad];     // Do any additional setup after loading the view, typically from a nib.     _arrayData = [[NSMutableArray alloc]init];     for(int i = 0;i < 5;i ++)     {         Person *p = [[Person alloc]init];         p.name = [NSString stringWithFormat:@"wyg%d",i];         p.age = 20 + i;         [_arrayData addObject:p];     }     [self createTable]; }  - (void)didReceiveMemoryWarning {     [super didReceiveMemoryWarning];     // Dispose of any resources that can be recreated. }  - (IBAction)addButtonClick:(id)sender {     [self insertData]; }  - (IBAction)deleteButtonClick:(id)sender {     [self deleteData]; }  - (IBAction)updateButtonClick:(id)sender {     [self updateData]; }  - (IBAction)selectButtonClick:(id)sender {     [self selectData]; }  -(void)openDatabase {     if (sqlite3_open([[self getFilePath] UTF8String], &_database) == SQLITE_OK)     {         NSLog(@"open success");     }     else     {         NSLog(@"open failed");     } } -(void)createTable {     [self openDatabase];     NSString *sql = @"create table if not exists students(name text ,age integer)";     sqlite3_stmt *stmt = nil;     if (sqlite3_prepare_v2(_database, [sql UTF8String], -1, &stmt, nil) == SQLITE_OK)     {         if (sqlite3_step(stmt) == SQLITE_DONE)         {             NSLog(@"create table success");         }         else         {             NSLog(@"create table failed");         }     }     sqlite3_finalize(stmt);     sqlite3_close(_database); } -(void)insertData {     [self openDatabase];     NSString *sql = @"insert into students(name,age)values(?,?)";     sqlite3_stmt *stmt = nil;     for (int i = 0; i < _arrayData.count; i ++)     {         Person *p = _arrayData[i];         if (sqlite3_prepare_v2(_database, [sql UTF8String], -1, &stmt, nil) == SQLITE_OK)         {                 sqlite3_bind_text(stmt, 1, [p.name UTF8String], strlen([p.name UTF8String]), nil);                 sqlite3_bind_int(stmt, 2, p.age);                 if (sqlite3_step(stmt) == SQLITE_DONE)                 {                     NSLog(@"insert success");                 }             else             {                 NSLog(@"insert failed");             }         }     }     sqlite3_finalize(stmt);     sqlite3_close(_database); } -(void)deleteData {     [self openDatabase];     sqlite3_stmt *stmt = nil;     NSString *sql = @"delete from students where age > 23";     if (sqlite3_prepare_v2(_database, [sql UTF8String], -1, &stmt, nil) == SQLITE_OK)     {         if (sqlite3_step(stmt) == SQLITE_DONE)         {             NSLog(@"delete success");         }         else         {             NSLog(@"delete failed");         }     }     sqlite3_finalize(stmt);     sqlite3_close(_database); } -(void)updateData {     [self openDatabase];     sqlite3_stmt *stmt = nil;     NSString *sql = @"update students set name = 'www' where age > 22";     if (sqlite3_prepare_v2(_database, [sql UTF8String], -1, &stmt, nil) == SQLITE_OK)     {         if (sqlite3_step(stmt) == SQLITE_DONE)         {             NSLog(@"update success");         }         else         {             NSLog(@"update failed");         }     }     sqlite3_finalize(stmt);     sqlite3_close(_database); } -(void)selectData {     [self openDatabase];     NSString *sql = @"select *from students";     sqlite3_stmt *stmt = nil;     NSMutableArray *array = [[NSMutableArray alloc]init];     if (sqlite3_prepare_v2(_database, [sql UTF8String], -1, &stmt, nil) == SQLITE_OK)     {         while (sqlite3_step(stmt) == SQLITE_ROW)         {             char *name = (char *)sqlite3_column_text(stmt, 0);             int age = sqlite3_column_int(stmt, 1);             Person *p = [[Person alloc]init];             p.name = [NSString stringWithUTF8String:name];             p.age = age;             [array addObject:p];         }     }     NSLog(@"---%@",array);     sqlite3_finalize(stmt);     sqlite3_close(_database); } -(NSString *)getFilePath {     NSString *docuPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];     NSString *filePath = [docuPath stringByAppendingPathComponent:@"database.db"];     return filePath; } @end  sqlite

关于FMDB，是对系统sqlite的封装，使用起来更加方便，我们可以从github上下载。

下面代码是对FMDB的基本封装。

其中包括对数据库打开关闭的操作，和数据库的基本操作。

对数据库创建，打开，关闭等操作我们封装在DatabaseTool类里面。

对数据库的基本操作，例如创建表，增删改查等操作，我们封装在ContactDAO类里面。

操作的数据我们封装在Contact里面。

  ********************************************************* **  DatabaseTool **  #import <Foundation/Foundation.h> #import "FMDB.h" @interface DatabaseTool : NSObject +(FMDatabase *)shareDatabase; +(BOOL)close; @end  #import "DatabaseTool.h" static FMDatabase *_db = nil; @implementation DatabaseTool +(FMDatabase *)shareDatabase {     if (_db == nil)     {         _db = [[FMDatabase alloc]initWithPath:[self getFilePath]];     }     [self open];     return _db; } +(NSString *)getFilePath {     NSString *documentPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];     NSString *path = [documentPath stringByAppendingPathComponent:@"file.db"];     return path; } +(BOOL)open {     if ([_db open] == NO)     {         [_db close];         NSAssert(NO, @"数据库打开失败");     }     //设置数据库缓存机制     [_db setShouldCacheStatements:YES];     return YES; } +(BOOL)close {     if ([_db close] == NO)     {         NSAssert(NO, @"数据库关闭失败");     }     return YES; } @end ************************************************************* **   Contact ** #import <Foundation/Foundation.h> //modal 模型 @interface Contact : NSObject {     int _cid;     NSString *_name;     NSString *_phone; } @property(nonatomic,assign) int cid; @property(nonatomic,copy) NSString *name; @property(nonatomic,copy) NSString *phone; //自定义初始化方法 -(id)initWithID:(int)ID name:(NSString *)aName phone:(NSString *)aPhone; @end  #import "Contact.h"  @implementation Contact @synthesize cid = _cid; @synthesize name = _name; @synthesize phone = _phone; -(id)initWithID:(int)ID name:(NSString *)aName phone:(NSString *)aPhone {     if (self = [super init])     {         self.cid = ID;         self.name = aName;         self.phone = aPhone;     }     return self; } -(void)dealloc {     [_name release];     [_phone release];     [super dealloc]; } @end ************************************************************* **  ContactDAO ** #import <Foundation/Foundation.h> #import "DatabaseTool.h" //连接  数据模型和数据库对象 主要完成表的创建，增删改查得功能 @interface ContactDAO : NSObject +(void)createContactTable; +(void)insertData; +(NSMutableArray *)queryData; +(void)updateDataWithID:(int)cid; +(void)deleteDataWithID:(int)cid; @end   #import "ContactDAO.h" #import "Contact.h" @implementation ContactDAO +(void)createContactTable {     FMDatabase *database = [DatabaseTool shareDatabase];     if ([database tableExists:@"contact"] == NO)     {         [database executeUpdate:@"create table contact(id integer primary key autoincrement not null,name text,phone text)"];     }     [DatabaseTool close]; } +(void)insertData {     FMDatabase *database = [DatabaseTool shareDatabase];     [database executeUpdate:@"insert into contact(name,phone)values(?,?)",@"wyg",@"1992"];     [DatabaseTool close]; } +(NSMutableArray *)queryData {     NSMutableArray *array = [[NSMutableArray alloc]init];     FMDatabase *database = [DatabaseTool shareDatabase];     FMResultSet *set = [database executeQuery:@"select *from contact"];     while ([set next])     {         int cid = [set intForColumn:@"id"];         NSString *name = [set stringForColumn:@"name"];         NSString *phone = [set stringForColumn:@"phone"];         Contact *c = [[Contact alloc]initWithID:cid name:name phone:phone];         [array addObject:c];         [c release];     }     [set close];     [DatabaseTool close];     return array; } +(void)updateDataWithID:(int)cid {     NSNumber *num = [NSNumber numberWithInt:cid];     FMDatabase *database = [DatabaseTool shareDatabase];     [database executeUpdate:@"update contact set name = 'www' where id = ?",num];     [DatabaseTool close]; } +(void)deleteDataWithID:(int)cid {     NSNumber *num = [NSNumber numberWithInt:cid];     FMDatabase *database = [DatabaseTool shareDatabase];     [database executeUpdate:@"delete from contact where id = ?",num];     [DatabaseTool close]; } @end  FMDB