首先需要引入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);
}
}];
}
});
}