IOS FMDB操作SQLite3

// 通过借鉴个别大牛的写法，自己也尝试做了个DemoTest 

// 代码粗糙 只希望有帮助吧

// 首先导入FMDB的所需库，gitHub 百度下都可以找到 全部导入即可

#define PATH_OF_DOCUMENT    [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0]

1. appDelegate.h 

-(NSString *)dbPath ;

appdelegate.m

-(NSString *)dbPath

{

    NSString *path = [PATH_OF_DOCUMENTstringByAppendingPathComponent:@"db.sqlite"];

    return path;

}

// ==================================

//

//  WWCSqlite3ViewController.h

//  TestCAOrUIViewAnimationApp7-30

//

//  Created by Whitney.c on 15/8/10.

//  Copyright (c) 2015年 ZhongShan Sun union Medical Technology Co. Ltd. All rights reserved.

//

#import <UIKit/UIKit.h>

#import "WWCSqlite3Utils.h"

@interface WWCSqlite3ViewController :UIViewController

{

    UITextField *textUserName;

    UITextField *textUserAge;

    UITextField *textUserDesc;

    UILabel *labelShowData;

    

    WWCSqlite3Utils *sqlite3Utils;

    

   

}

@end

//

//  WWCSqlite3ViewController.m

//  TestCAOrUIViewAnimationApp7-30

//

//  Created by Whitney.c on 15/8/10.

//  Copyright (c) 2015年 ZhongShan Sun union Medical Technology Co. Ltd. All rights reserved.

//

#import "WWCSqlite3ViewController.h"

#import "GeneralMarro.h"

#import "WWCSystemConfig.h"

#import "WWCSqlite3Utils.h"

@interface WWCSqlite3ViewController ()

@end

@implementation WWCSqlite3ViewController

- (void)viewDidLoad {

    [superviewDidLoad];

    // Do any additional setup after loading the view.

    

    

    self.title =@"SQLite3";

    

    sqlite3Utils = [WWCSqlite3UtilssharedInstance];

    

    [selfloadSelfLayoutSubvies];

    

    

}

-(void)loadSelfLayoutSubvies

{

    

    NSString *sqlite3Version = [WWCSystemConfiggetCurrentSQLite3Version];

    NSLog(@"currentSqlite3Version:%@",sqlite3Version);

    

    

    staticdispatch_once_t onceToken;

    dispatch_once(&onceToken , ^{

        

        [selfcreateTable];

        

    });

    

    

    [selfinitUi];

    

    

    [selfperformSelector:@selector(tapGestureRecognizer)withObject:nilafterDelay:0];

    

}

-(void)tapGestureRecognizer

{

    UITapGestureRecognizer *TAP = [[UITapGestureRecognizeralloc] initWithTarget:selfaction:@selector(tapAction:)];

    [self.viewaddGestureRecognizer:TAP];

}

-(void)tapAction:(UITapGestureRecognizer *)sender

{

    

    // editing =  no

    [textUserAgeresignFirstResponder];

    [textUserDescresignFirstResponder];

    [textUserNameresignFirstResponder];

}

#pragma mark - init UI

-(void)initUi

{

    CGRect rect = [UIScreenmainScreen].applicationFrame;

    

    

    textUserName = [[UITextFieldalloc] init];

    textUserName.frame =CGRectMake(10,10, rect.size.width-20,35);

    textUserName.backgroundColor = [UIColorgrayColor];

    textUserName.leftView =nil;

    [textUserNamesetPlaceholder:@"昵称"];

    

    textUserAge = [[UITextFieldalloc] init];

    textUserAge.frame =CGRectMake(10,10+35*1+5, rect.size.width-20,35);

    textUserAge.backgroundColor = [UIColorgrayColor];

    [textUserAgesetPlaceholder:@"年龄"];

    

    textUserDesc = [[UITextFieldalloc] init];

    textUserDesc.frame =CGRectMake(10,10+35*2+10, rect.size.width-20,35);

    textUserDesc.backgroundColor = [UIColorgrayColor];

    [textUserDescsetPlaceholder:@"描述"];

    

    [self.viewaddSubview:textUserName];

    [self.viewaddSubview:textUserAge];

    [self.viewaddSubview:textUserDesc];

    

    

    

    UIButton *button = [UIButtonbuttonWithType:UIButtonTypeCustom];

    button.frame =CGRectMake(10,10+35*3+50, rect.size.width-20,35);

    button.backgroundColor = [UIColorblueColor];

    [button setTitle:@"保存数据"forState:UIControlStateNormal];

    [button addTarget:selfaction:@selector(saveUserInfo:)forControlEvents:UIControlEventTouchUpInside];

    button.showsTouchWhenHighlighted =YES;

    

    [self.viewaddSubview:button];

    

    

    UIButton *buttonQuery = [UIButtonbuttonWithType:UIButtonTypeCustom];

    buttonQuery.frame =CGRectMake(10,15+35*4+50, rect.size.width-20,35);

    buttonQuery.backgroundColor = [UIColorblueColor];

    [buttonQuery setTitle:@"查询数据"forState:UIControlStateNormal];

    [buttonQuery addTarget:selfaction:@selector(queryUserInfo)forControlEvents:UIControlEventTouchUpInside];

    buttonQuery.showsTouchWhenHighlighted =YES;

    

    [self.viewaddSubview:buttonQuery];

    

    

    UIButton *buttonDelete = [UIButtonbuttonWithType:UIButtonTypeCustom];

    buttonDelete.frame =CGRectMake(10,20+35*5+50, rect.size.width-20,35);

    buttonDelete.backgroundColor = [UIColorblueColor];

    [buttonDelete setTitle:@"删除数据"forState:UIControlStateNormal];

    [buttonDelete addTarget:selfaction:@selector(deleteUserInfo)forControlEvents:UIControlEventTouchUpInside];

    buttonDelete.showsTouchWhenHighlighted =YES;

    

    [self.viewaddSubview:buttonDelete];

    

    

    labelShowData = [[UILabel alloc] init];

    labelShowData.frame = CGRectMake(10,25+35*6+50, rect.size.width-20,100);

    labelShowData.font = [UIFont systemFontOfSize:12];

    labelShowData.backgroundColor = [UIColor grayColor];

    labelShowData.alpha = 0 ;

    labelShowData.lineBreakMode = NSLineBreakByCharWrapping;

    labelShowData.numberOfLines = 0;

    [self.view addSubview:labelShowData];

    

    

}

#pragma mark - 保存数据

-(void)saveUserInfo:(UIButton *)sender

{

    NSString *userName = textUserName.text;

    NSString *userAge = textUserAge.text;

    NSString *userDesc = textUserDesc.text;

    

    

    if ( [userName isEqualToString:@""] || [userAge isEqualToString:@""] || [userDesc isEqualToString:@""] ) {

        NSLog(@" 请输入参数才能保存 ! ");

    }else {

        

        BOOL BOOL_ = [sqlite3Utils saveUserInfoToSqliteForUserName:userName forUserAge:userAge forUserDesc:userDesc];

        if (BOOL_) {

            NSLog(@"数据插入成功 !");

        }else{

            NSLog(@"数据插入失败 !");

        }

        

    }

    

}

#pragma mark - 查询数据

-(void)queryUserInfo

{

    NSArray *array = [sqlite3Utils queryUserInfoForWhereUserName:@""];

    

    if (array.count>0) {

        NSLog(@" 查询到数据 %ld条", [array count] );

        NSString *string = @"";

        for (int i=0; i<array.count; i++) {

            

            NSDictionary *dic = [array objectAtIndex:i];

            

            NSLog(@"%d -> userName:%@ ,userAge:%@ , userDesc:%@", i,

                  [dic objectForKey:@"uName"],

                  [dic objectForKey:@"uAge"],

                  [dic objectForKey:@"uDesc"]);

            string = [string stringByAppendingString:

                      [NSString stringWithFormat:@"%d -> userName:%@ ,userAge:%@ , userDesc:%@\n", i,

                                  [dic objectForKey:@"uName"],

                                  [dic objectForKey:@"uAge"],

                                  [dic objectForKey:@"uDesc"] ]];

            

        }

        

        if (![string isEqualToString:@""]) {

            labelShowData.text = string ;

            labelShowData.alpha = 1;

        }

        

    }else{

        NSLog(@" 没有数据可查询");

        labelShowData.text = @"没有数据可查询" ;

        labelShowData.alpha = 1;

    }

    

    

}

#pragma mark - 删除数据

-(void)deleteUserInfo

{

    BOOL bool_ = [sqlite3Utils deleteUserInfoWhereUserName:@""];

    

    if (bool_) {

        NSLog(@" 删除数据成功 !");

    }else{

        NSLog(@" 删除数据失败 !");

    }

}

#pragma mark - 创建User表

-(void)createTable

{

    

    BOOL bool_ = [sqlite3Utils createUserTable:@"user_table"];

    

    if (bool_) {

        

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

        

    }else{

        

    }

    

}

-(void)viewDidDisappear:(BOOL)animated

{

    [super viewDidDisappear:animated];

    

    // 宏定义一个数据库版本

    // 比较存储在本地的版本

    // 若是有改动的话，根据要求变更数据库结构或者是其它什么

    

    [WWCSystemConfig setCurrentSQLite3Version:@"3.5.5"];

}

- (void)didReceiveMemoryWarning {

    [super didReceiveMemoryWarning];

    // Dispose of any resources that can be recreated.

}

/*

#pragma mark - Navigation

// In a storyboard-based application, you will often want to do a little preparation before navigation

- (void)prepareForSegue:(UIStoryboardSegue *)segue sender:(id)sender {

    // Get the new view controller using [segue destinationViewController].

    // Pass the selected object to the new view controller.

}

*/

@end

// =＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

//

//  WWCSqlite3Utils.h

//  TestCAOrUIViewAnimationApp7-30

//

//  Created by Whitney.c on 15/8/10.

//  Copyright (c) 2015年 ZhongShan Sun union Medical Technology Co. Ltd. All rights reserved.

//

#import <Foundation/Foundation.h>

#import "FMDatabase.h"

@interface WWCSqlite3Utils :NSObject

// 单例模式

+(instancetype)sharedInstance;

// 创建用户表 (不存在才创建-> .. if not exists .. )

-(BOOL)createUserTable:(NSString *)tableName;

// 插入数据到用户表中

-(BOOL)saveUserInfoToSqliteForUserName:(NSString *)userName forUserAge:(NSString *)userAge forUserDesc:(NSString *)userDesc;

// 插入数据到用户表中(队列方式/多线程)

-(BOOL)saveUserInfoToSqliteForUserNameByQueue:(NSString *)userName forUserAge:(NSString *)userAge forUserDesc:(NSString *)userDesc;

// 查询数据

-(NSArray *)queryUserInfoForWhereUserName:(NSString *)userName;

// 删除数据

-(BOOL)deleteUserInfoWhereUserName:(NSString *)userName;

// 修改数据

-(BOOL)updateUserInfoWherUserName:(NSString *)userName;

@end

//

//  WWCSqlite3Utils.m

//  TestCAOrUIViewAnimationApp7-30

//

//  Created by Whitney.c on 15/8/10.

//  Copyright (c) 2015年 ZhongShan Sun union Medical Technology Co. Ltd. All rights reserved.

//

#import "WWCSqlite3Utils.h"

#import "WWCAppDelegate.h"

#import "FMDatabaseQueue.h"

@implementation WWCSqlite3Utils

NSString *path ;

-(id)init

{

    self = [super init];

    if (self) {

        NSLog(@" init 。。。。");

        WWCAppDelegate *appdelegate = (WWCAppDelegate *)kMyAppDelegate;

        path = [appdelegate dbPath];

        

    }

    returnself;

}

// 单例模式

+(instancetype)sharedInstance

{

    static WWCSqlite3Utils *sqlite3Utils =nil;

    static dispatch_once_t once_token ;

    dispatch_once(&once_token, ^{

        NSLog(@" once token ");

        sqlite3Utils = [[WWCSqlite3Utils alloc] init];

    });

    

    return sqlite3Utils;

}

-(BOOL)createUserTable:(NSString *)tableName

{

    NSFileManager * fileManager = [NSFileManager defaultManager];

    NSLog(@" path1 :%@", path);

    

    // 判断是否存在该文件

    if ( [fileManager fileExistsAtPath:path] ==NO ) {

        

        [fileManager createFileAtPath:path contents:nil attributes:nil];

    }

    // 创建数据库表

    

    FMDatabase *db = [FMDatabase databaseWithPath:path];

    

    if ( [db open] ) {

        NSString *sql = @" CREATE TABLE IF NOT EXISTS 'user' ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , 'user_name' VARCHAR(30), 'user_age' INTEGER , 'user_desc' VARCHAR(100) ) ";

        NSLog(@"Create table sql->%@", sql);

        return [db executeUpdate:sql];

    }

    

    [db close];

    

    returnNO;

    

}

// 插入数据到用户表中

-(BOOL)saveUserInfoToSqliteForUserName:(NSString *)userName forUserAge:(NSString *)userAge forUserDesc:(NSString *)userDesc

{

    NSLog(@" path2 :%@",path);

    

    FMDatabase *db  =[FMDatabasedatabaseWithPath:path];

    if ([dbopen]) {

         NSString *sql =@" insert into user (user_name,user_age,user_desc) values(?,?,?)";

        

        BOOL bool_ = [dbexecuteUpdate:sql, userName ,userAge, userAge];

        [db close];

        

        return bool_;

    }

   

    returnNO;

}

// 插入数据到用户表中(队列方式/多线程)

-(BOOL)saveUserInfoToSqliteForUserNameByQueue:(NSString *)userName forUserAge:(NSString *)userAge forUserDesc:(NSString *)userDesc

{

    

    FMDatabaseQueue *queue = [FMDatabaseQueuedatabaseQueueWithPath:path];

    

    // 创建队列  -队列标签 quene1 标签

    dispatch_queue_t q1 =dispatch_queue_create("quene1",NULL);

    dispatch_queue_t q2 =dispatch_queue_create("quene2",NULL);

    

    

    dispatch_async(q1, ^{

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

            //  开始队列插入数据

            [queue inDatabase:^(FMDatabase *db1){

               

                NSString *sql1 =@" insert into user (user_name, user_age,user_desc) values(?,?,?) ";

                BOOL bool_  = [db1executeUpdate:sql1,userName,userAge,userDesc];

                

                if (bool_) {

                    NSLog(@"插入数据成功 queue1 %d", i);

                }else{

                    NSLog(@"插入数据失败 queue1 %d", i);

                }

            }];

        }

        

    });

    

    

    dispatch_async(q2, ^{

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

            //  开始队列插入数据

            [queue inDatabase:^(FMDatabase *db2){

                

                NSString *sql2 =@" insert into user (user_name, user_age,user_desc) values(?,?,?) ";

                BOOL bool_  = [db2executeUpdate:sql2,userName,userAge,userDesc];

                

                if (bool_) {

                    NSLog(@"插入数据成功 queue2 %d", i);

                }else{

                    NSLog(@"插入数据失败 queue2 %d", i);

                }

            }];

        }

        

    });

    

    returnNO;

}

// 查询数据

-(NSArray *)queryUserInfoForWhereUserName:(NSString *)userName

{

    NSLog(@" PATH3 :%@",path);

    

    FMDatabase *db = [FMDatabasedatabaseWithPath:path];

    if ( [dbopen] ) {

        NSString *sql =@" select * from user ";

        FMResultSet *rs = [dbexecuteQuery:sql];

        

        NSMutableArray *array = [[NSMutableArrayalloc] initWithCapacity:10];

        

        while ([rsnext]) {

            NSMutableDictionary *dic = [[NSMutableDictionaryalloc] initWithCapacity:5];

            NSString *uName = [rsstringForColumn:@"user_name"];

            NSString *uAge = [rsstringForColumn:@"user_age"];

            NSString *uDesc = [rsstringForColumn:@"user_desc"];

            

            // 利用下标index的方式来获取字段  - FMResultSet中可查看

            //NSString *uName = [rs intForColumnIndex:0];

            //NSString *uAge = [rs intForColumnIndex:1];

            //NSString *uDesc = [rs intForColumnIndex:2];

            

            NSLog(@" uName:%@, uAge:%@ ,uDesc:%@" , uName, uAge, uDesc) ;

            

            // 这一步也可以用Model来实现

            [dic setObject:uNameforKey:@"uName"];

            [dic setObject:uAgeforKey:@"uAge"];

            [dic setObject:uDescforKey:@"uDesc"];

            

            [array addObject:dic];

            

        }

        

        [db close];

        

        return array;

        

    }

    returnnil;

    

}

// 删除数据

-(BOOL)deleteUserInfoWhereUserName:(NSString *)userName

{

    NSLog(@"path4 :%@",path);

    

    FMDatabase *db = [FMDatabasedatabaseWithPath:path];

    if ( [dbopen] ) {

        NSString *sql =@" delete from user "; // delete from user where user_name = ?

        BOOL bool_  = [dbexecuteUpdate:sql];

        

        [db close];

        

        

        return bool_;

        

    }

    returnNO;

}

// 修改数据

-(BOOL)updateUserInfoWherUserName:(NSString *)userName

{

    // sql = @" update user set user_desc = ?  where user_name = ? ";

    

    

    returnNO;

}

@end

// 代码结束   附图 队列代码写了，操作可以自己去尝试