iOS学习 数据库 FMDB框架使用 UISearchBar搜索框 线程安全，事务

UISearchBar应用：

http://blog.csdn.net/sanpintian/article/details/7379996

#import "ViewController.h"

#import "FMDB.h"

@interface ViewController ()<UITableViewDataSource,UITableViewDelegate,UISearchBarDelegate>

@property (weak,nonatomic) IBOutletUITextField *name;

@property (weak,nonatomic) IBOutletUITextField *price;

@property (weak,nonatomic) IBOutletUITableView *tableView;

@property (nonatomic,retain)FMDatabase *db;

@property (nonatomic,retain)NSMutableArray *dataArray;

@end

@implementation ViewController

- (void)viewDidLoad {

    [superviewDidLoad];

    //打开数据库（如果不存在，直接新建数据库）

    NSString *path = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES)lastObject]stringByAppendingPathComponent:@"shop.sqlite"];//沙盒路径拼接

    NSLog(@"%@",path);

    self.db = [FMDatabasedatabaseWithPath:path];

    

    //打开数据库

    [self.dbopen];

    

    //创表

    [self.dbexecuteUpdate:@"CREATE TABLE IF NOT EXISTS t_shop(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, price TEXT DEFAULT 100);"];

    

    _dataArray  = [NSMutableArrayarray];

    self.tableView.dataSource =self;

    self.tableView.delegate =self;

    self.tableView.backgroundColor = [UIColor lightGrayColor];

    UISearchBar *searchbar = [[UISearchBaralloc]initWithFrame:CGRectMake(0,0, self.tableView.frame.size.width,40)];

    searchbar.delegate =self;

    self.tableView.tableHeaderView = searchbar;

}

//监听搜索框内容变化

-(void)searchBar:(UISearchBar *)searchBar textDidChange:(NSString *)searchText{

    [_dataArrayremoveAllObjects];

    NSString *sql = [NSStringstringWithFormat:@"SELECT name,price FROM t_shop WHERE name LIKE '%%%@%%' OR price LIKE '%%%@%%'",searchText,searchText];

    FMResultSet *set = [self.dbexecuteQuery:sql];

    

    //不断往下取数据

    while (set.next) {

        //获取当前数据

        NSMutableDictionary *dict = [NSMutableDictionarydictionary];

        NSString *name = [setstringForColumn:@"name"];

        NSString *price = [setstringForColumn:@"price"];

        [dict setValue:nameforKey:@"name"];

        [dict setValue:priceforKey:@"price"];

        [_dataArrayaddObject: dict];

    }

    [self.tableViewreloadData];

}

//往数据库表中加入记录

- (IBAction)addRow:(id)sender {

    //添加数据

    [self.dbexecuteUpdateWithFormat:@"INSERT INTO t_shop(name, price) VALUES (%@,%@);",self.name.text,self.price.text];

    

}

//删除数据库表中特定条件记录

- (IBAction)deleteRow:(id)sender {

    //删除

//    [self.db executeUpdateWithFormat:@"DELETE FROM t_shop WHERE price = ''"];

    

    //修改

    [self.dbexecuteUpdateWithFormat:@"UPDATE t_shop SET name = 'iPhone5' WHERE name = '手机';"];

}

//刷新，将数据库表内容显示在tableView上

- (IBAction)refresh:(id)sender {

    //得到结果集

    FMResultSet *set = [self.dbexecuteQuery:@"SELECT * FROM t_shop;"];

    

    //不断往下取数据

    while (set.next) {

        //获取当前数据

        NSMutableDictionary *dict = [NSMutableDictionarydictionary];

        NSString *name = [setstringForColumn:@"name"];

        NSString *price = [setstringForColumn:@"price"];

        NSLog(@"name %@",name);

        [dict setValue:nameforKey:@"name"];

        [dict setValue:priceforKey:@"price"];

        [_dataArrayaddObject: dict];

    }

    NSLog(@"_dataArray:%@",_dataArray);

    [self.tableViewreloadData];

}

- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section{

    return_dataArray.count;

}

- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath{

    staticNSString *ID = @"shop";

    UITableViewCell *cell = [tableViewdequeueReusableCellWithIdentifier:ID];

    if (!cell) {

        cell = [[UITableViewCellalloc]initWithStyle:UITableViewCellStyleValue1reuseIdentifier:ID];

    }

 

    NSDictionary *dict = [NSDictionarydictionary];

    dict = _dataArray[indexPath.row];

    cell.textLabel.text = dict[@"name"];

    cell.detailTextLabel.text = dict[@"price"];

    return cell;

}

#import "ViewController.h"

#import <sqlite3.h>

#import "FMDB.h"

@interface ViewController ()

@property (nonatomic, strong) FMDatabaseQueue *queue;

@end

@implementation ViewController

- (void)viewDidLoad {

    [super viewDidLoad];

    //1.线程安全

    NSString *path = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject] stringByAppendingPathComponent:@"data.sqlite"];

    FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:path];

    self.queue = queue; //保证队列不消除

    [self.queue inDatabase:^(FMDatabase *db) { //在block 内部的就是线程安全

        

        BOOL success =  [db open];

        if (success) {

            NSLog(@"创建数据库成功!");

            //非查询语句都是用executeUpdate

            BOOL successT=  [db executeUpdate:@"CREATE   TABLE IF NOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT ,name TEXT NOT NULL , score REAL);"];

            if (successT) {

                NSLog(@"创建表成功!");

            }else{

                NSLog(@"创建表失败!!");

            }

            

        }else{

            NSLog(@"创建失败!");

        }

    }];

}

- (IBAction)selctClick:(id)sender {

   [self.queue inDatabase:^(FMDatabase *db) {       

       NSString *sql = @"SELECT  id,name,score FROM t_student WHERE score > 60 AND score < 75;";

             

       FMResultSet *result = [db executeQuery:sql];

       while ([result next]) {

           

           //name TEXT

           

           NSString *name =   [result stringForColumnIndex:1];

           //score DOUBLE

           double score =  [result doubleForColumnIndex:2];

           

           NSLog(@"name = %@ score =  %f",name,score);

           

       }

   }];    

}

- (void)touchesBegan:(NSSet<UITouch *> *)touches withEvent:(UIEvent *)event

{

    //修改

    //UPDATE  t_money SET MONEY = 8000 – 800   WHERE  name =小明;

    //UPDATE t_money SET MONEY =  8000 – 1800 WHERE name =小明;

    [self.queue inDatabase:^(FMDatabase *db) {

       

        //开启事务  在这条语句之后的都是一个事务

        [db beginTransaction];

        //UPDATE  t_money SET MONEY = 8000 – 800   WHERE  name =小明;

        //UPDATE t_money SET MONEY =  8000 – 1800 WHERE name =小明;

    

        [db rollback];  //自动回滚

        

        [db commit];

        //判断是否在事务中

        if ([db inTransaction]) {

            

        }

    }];

}

- (IBAction)addClick:(id)sender {

    

    [self.queue inDatabase:^(FMDatabase *db) {

 

        for (int  i = 0; i < 100; i++) {

            

            NSString *name = [NSString stringWithFormat:@"xiaoli-%d",i];

            

            NSString *sql = [NSString stringWithFormat:@"INSERT INTO t_student (name,score) VALUES ('%@',%f)",name,arc4random_uniform(8000)/100.0 + 20];

            BOOL success =  [db executeUpdate:sql];

            if (success) {

                NSLog(@"添加数据成功!");

            }else{

                NSLog(@"添加数据失败!!");

            }

        }

    }];    

}