UIday2001:FMDB使用

首先需要引入libsqlite3.dylib  引入方法：Build Phases ---- Link Binary With Libraries ---- ' + '

然后将FMDB包拖入工程

ViewController.h

#import <UIKit/UIKit.h>
#import "FMDatabase.h"
#import "FMDatabaseQueue.h"


@interface ViewController : UIViewController{
    FMDatabase * db;
    NSString * database_path;
}

@end

ViewController.m

#import "ViewController.h"
#define NAME @"c_name"
#define TEL @"c_tel"
#define ID @"c_id"
#define PCOUNT @"c_personCount"
#define ADDRESS @"c_address"

@interface ViewController ()

@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    // Do any additional setup after loading the view, typically from a nib.
    //创建数据库文件
    NSString * s = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
    database_path = [s stringByAppendingPathComponent:@"fmdbDemo"];
    NSLog(@"filePath = %@",database_path);
    db = [FMDatabase databaseWithPath:database_path];
}

//创建表
- (IBAction)createTabel:(id)sender {
    if ([db open]) {
        //准备sql语句
        NSString * createSql = [NSString stringWithFormat:@"create table if not exists company(c_id integer primary key autoincrement,c_name text,c_tel integer,c_personCount integer,c_address text)"];
        //执行语句并返回结果
        BOOL result = [db executeUpdate:createSql];
        //根据返回结果打印
        if (result) {
            NSLog(@"创建表成功");
            //            NSLog(@"创建表失败");
        }else{
            NSLog(@"创建表失败");
            //            NSLog(@"创建表成功");
        }
        [db close];
    }
}

//插入数据
- (IBAction)insert:(id)sender {
    if ([db open]) {
        //准备sql语句
        NSString * sqlInsert = [NSString stringWithFormat:@"insert into company(c_name,c_tel,c_personCount,c_address)values('tengxun',110,100,'北京'),('alibaba',112,200,'杭州'),('baidu',119,150,'山西'),('jingdong',120,110,'上海'),('苏宁',108,201,'河北'),('guomei',210,220,'天津'),('蓝鸥',180,220,'清河'),('东软',210,880,'河南'),('中国中铁',199,330,'湖北'),('中国核电',155,760,'四川')"];
        
        //执行语句并返回结果
        BOOL result = [db executeUpdate:sqlInsert];
        if (result) {
            NSLog(@"数据插入成功");
        }else{
            NSLog(@"数据插入失败");
        }
        
        //        [sender ifResult:result sqlStr:sqlInsert];
        
        [db close];
    }
    
}

修改数据
- (IBAction)update:(id)sender {
    if ([db open]) {
        //准备sql语句
        NSString * sqlUpdate = [NSString stringWithFormat:@"update company set c_tel = '110' where c_personCount < 110"];
        
        //执行语句并返回结果
        BOOL result = [db executeUpdate:sqlUpdate];
        if (result) {
            NSLog(@"数据修改成功");
        }else{
            NSLog(@"数据修改失败");
        }
        [db close];
    }
    
}

//删除数据
- (IBAction)delete:(id)sender {
    if ([db open]) {
        //准备sql语句
        NSString * sqlDelete = [NSString stringWithFormat:@"delete from company where c_id in (3,6,9)"];
        
        BOOL result = [db executeUpdate:sqlDelete];
        if (result) {
            NSLog(@"数据删除成功");
        }else{
            NSLog(@"数据删除失败");
        }
        [db close];
    }
}

//查询数据
- (IBAction)select:(id)sender {
    
    if ([db open]) {
        
        //1 准备好sql语句
        NSString * sqlSelect = [NSString stringWithFormat:@"select * from company where c_name like '%%中国%%'"];
        
        //2 执行sql语句 并返回结果集
        FMResultSet * resultSet = [db executeQuery:sqlSelect];
        
        //3 遍历
        while ([resultSet next]) {
            int  cId = [resultSet intForColumn:ID];
            NSString * cName = [resultSet stringForColumn:NAME];
            int  ctel = [resultSet intForColumn:TEL];
            int  pcount = [resultSet intForColumn:PCOUNT];
            NSString * cAddress = [resultSet stringForColumn:ADDRESS];
            
            NSLog(@"cId = %d, cName = %@, cTel = %d, pcount = %d, cAddress = %@",cId,cName,ctel,pcount,cAddress);
        }
        
        [db close];
    }
    
}




- (void)didReceiveMemoryWarning {
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}

@end

多线程中FMDB的使用：

-(void) multithread{
    FMDatabaseQueue * queue = [FMDatabaseQueue databaseQueueWithPath:database_path];
    dispatch_queue_t q1 = dispatch_queue_create("queue1", NULL);
    dispatch_queue_t q2 = dispatch_queue_create("queue2", NULL);
    
    dispatch_async(q1, ^{
        for (int i = 0; i < 50; ++i) {
            [queue inDatabase:^(FMDatabase *db2) {
                
                NSString *insertSql1= [NSString stringWithFormat:
                                       @"INSERT INTO '%@' ('%@', '%@', '%@') VALUES (?, ?, ?)",
                                       TABLENAME, NAME, AGE, ADDRESS];
                
                NSString * name = [NSString stringWithFormat:@"jack %d", i];
                NSString * age = [NSString stringWithFormat:@"%d", 10+i];
                
                
                BOOL res = [db2 executeUpdate:insertSql1, name, age,@"济南"];
                if (!res) {
                    NSLog(@"error to inster data: %@", name);
                } else {
                    NSLog(@"succ to inster data: %@", name);
                }
            }];
        }
    });
    
    dispatch_async(q2, ^{
        for (int i = 0; i < 50; ++i) {
            [queue inDatabase:^(FMDatabase *db2) {
                NSString *insertSql2= [NSString stringWithFormat:
                                       @"INSERT INTO '%@' ('%@', '%@', '%@') VALUES (?, ?, ?)",
                                       TABLENAME, NAME, AGE, ADDRESS];
                
                NSString * name = [NSString stringWithFormat:@"lilei %d", i];
                NSString * age = [NSString stringWithFormat:@"%d", 10+i];
                
                BOOL res = [db2 executeUpdate:insertSql2, name, age,@"北京"];
                if (!res) {
                    NSLog(@"error to inster data: %@", name);
                } else {
                    NSLog(@"succ to inster data: %@", name);
                }
            }];
        }
    });

}