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

关于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