iOS数据库的使用方法（FMDB）

一、下面简单的介绍一下FMDB的相关信息：

1.FMDB是iOS平台SQLite数据库框架，以OC的方式封装了SQLite的C语言的API

2.在诸多的数据库框架中，为什么FMDB能够如此的受开发者喜爱呢？

主要是因为它使用起来更加面向对象，省去了很多的麻烦、冗余的代码；

比ios自带的Core Data框架更加的轻量级和灵活；

并且提供了或线程安装的数据库操作方法，有效地防止数据混乱。

3.FMDB的下载，到giihub上搜索下载

https://github.com

二、FMDB的三个主要类

1.FMDatabase

一个FMDatabase对象就是一个Sqlite数据库，用来执行SQL语言

2.FMResultSet

使用FMDatabase执行sql操作后的查询结果集

3.FMDatabaseQueue

用于多线程中执行多个查询或更新等操作

三、FMDB的具体使用方法

1.打开数据库

FMDatabase *db = [FMDatabase databaseWithPath:dbPath];

if (![db open]) {

      DDLogError(@"Could not open db.");

    }else{

[db setUserVersion:1];//设置数据库的版本号，方便后期的数据库升级

}

2.执行更新----在FMDB中，除了查询之外所有的操作都称为“更新”

使用executeUpdate:方法执行更新    

    - (BOOL)executeUpdate:(NSString*)sql, ...

    - (BOOL)executeUpdateWithFormat:(NSString*)format, ...

    - (BOOL)executeUpdate:(NSString*)sql withArgumentsInArray:(NSArray *)arguments

- (BOOL)executeUpdate:(NSString*)sql withParameterDictionary:(NSDictionary *)arguments

示例：create创建新的表

包装一个更新的方法

- (BOOL)executeUpdate:(NSString*)sql {
    BOOL result = NO;
    @try {
        result = [self.db executeUpdate:sql];
    }
    @catch (NSException *exception) {
        result = NO;
        DDLogError(@"error:%@", exception);
    }
    @finally {
        return result;
    }
}           

- (BOOL)initTable {
    BOOL isSuccess = YES;
    [self.db beginTransaction];    <pre name="code" class="objc">    isSuccess = [self executeUpdate:@"CREATE TABLE IF NOT EXISTS banner ( banner_id INTEGER PRIMARY KEY AUTOINCREMENT, sequence INTEGER, photo_addr TEXT, photo_title TEXT, hyperlink TEXT );"];<span style="font-family: Arial, Helvetica, sans-serif;">    </span>           

if (isSuccess) { [self.db commit]; }else { [self.db rollback]; } return isSuccess;}

插入，修改保存           

<p class="p1"><span class="s1">sql = [</span><span class="s2">NSString</span><span class="s1"> </span><span class="s3">stringWithFormat</span><span class="s1">:</span><span class="s4">@"insert or replace into %@ (%@) values (%@) "</span><span class="s1">,tableName,keyString,valueString]；</span></p><p class="p1"><span class="s1" style="font-family: Arial, Helvetica, sans-serif;">[</span><span class="s2" style="font-family: Arial, Helvetica, sans-serif;">self</span><span class="s1" style="font-family: Arial, Helvetica, sans-serif;">.</span><span class="s3" style="font-family: Arial, Helvetica, sans-serif;">db</span><span class="s1" style="font-family: Arial, Helvetica, sans-serif;"> </span><span class="s4" style="font-family: Arial, Helvetica, sans-serif;">executeUpdate</span><span class="s1" style="font-family: Arial, Helvetica, sans-serif;">:sql </span><span class="s4" style="font-family: Arial, Helvetica, sans-serif;">withParameterDictionary</span><span class="s1" style="font-family: Arial, Helvetica, sans-serif;">:dict];</span></p><p class="p1"><span class="s1" style="font-family: Arial, Helvetica, sans-serif;"><span class="s1" style="white-space: pre; background-color: rgb(240, 240, 240);">或者[</span><span class="s2" style="white-space: pre; background-color: rgb(240, 240, 240);">self</span><span class="s1" style="white-space: pre; background-color: rgb(240, 240, 240);">.</span><span class="s3" style="white-space: pre; background-color: rgb(240, 240, 240);">db</span><span class="s1" style="white-space: pre; background-color: rgb(240, 240, 240);"> </span><span class="s4" style="white-space: pre; background-color: rgb(240, 240, 240);">executeUpdate</span><span class="s1" style="white-space: pre; background-color: rgb(240, 240, 240);">:sql withArgumentsInArray:array</span><span class="s1" style="white-space: pre; background-color: rgb(240, 240, 240);">];</span>
</span></p><p class="p1"><span class="s1" style="font-family: Arial, Helvetica, sans-serif;">delete from table//删除表格</span></p>           

3.执行查询

- (FMResultSet *)executeQuery:(NSString*)sql, ...

- (FMResultSet *)executeQueryWithFormat:(NSString*)format, ...

- (FMResultSet *)executeQuery:(NSString *)sql withArgumentsInArray:(NSArray *)arguments

示例

// 查询数据

FMResultSet *rs = [db executeQuery:@"SELECT * FROM t_student"];

// 遍历结果集

while ([rs next]) {

    NSString *name = [rs stringForColumn:@"name"];

    int age = [rs intForColumn:@"age"];

    double score = [rs doubleForColumn:@"score"];

}

四、示例代码

#import "DBViewController.h"
#import "FMDB.h"

@interface DBViewController ()
@property(nonatomic,strong)FMDatabase *db;
@end

@implementation DBViewController

- (void)viewDidLoad
{
    [super viewDidLoad];
    //1.获得数据库文件的路径
    NSString *doc=[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *fileName=[doc stringByAppendingPathComponent:@"20151104.db"];
    
    //2.获得数据库
    FMDatabase *db=[FMDatabase databaseWithPath:fileName];
    
    //3.打开数据库
    if ([db open]) {
        //4.创表
        BOOL result=[db executeUpdate:@"CREATE TABLE IF NOT EXISTS t_student (id integer PRIMARY KEY AUTOINCREMENT, name text NOT NULL, age integer NOT NULL);"];
        if (result) {
            NSLog(@"创表成功");
        }else
        {
            NSLog(@"创表失败");
        }
    }
    self.db=db;

}

-(void)touchesBegan:(NSSet *)touches withEvent:(UIEvent *)event
{
    [self delete];
    [self insert];
    [self query];
}

//插入数据
-(void)insert
{
    for (int i = 0; i<10; i++) {
        NSString *name = [NSString stringWithFormat:@"jack-%d", arc4random_uniform(100)];
       
        [self.db executeUpdate:@"INSERT INTO t_student (name, age) VALUES (?, ?);", name, @(arc4random_uniform(40))];
        //        [self.db executeUpdate:@"INSERT INTO t_student (name, age) VALUES (?, ?);" withArgumentsInArray:@[name, @(arc4random_uniform(40))]];
        
        // executeUpdateWithFormat : 不确定的参数用%@、%d等来占位
        //        [self.db executeUpdateWithFormat:@"INSERT INTO t_student (name, age) VALUES (%@, %d);", name, arc4random_uniform(40)];
    }
}

//删除数据
-(void)delete
{
    [self.db executeUpdate:@"DROP TABLE IF EXISTS t_student;"];
    [self.db executeUpdate:@"CREATE TABLE IF NOT EXISTS t_student (id integer PRIMARY KEY AUTOINCREMENT, name text NOT NULL, age integer NOT NULL);"];
}

//查询
- (void)query
{
    // 1.执行查询语句
    FMResultSet *resultSet = [self.db executeQuery:@"SELECT * FROM t_student"];
    
    // 2.遍历结果
    while ([resultSet next]) {
        int ID = [resultSet intForColumn:@"id"];
        NSString *name = [resultSet stringForColumn:@"name"];
        int age = [resultSet intForColumn:@"age"];
        NSLog(@"%d %@ %d", ID, name, age);
    }
}

@end