FMDB(用SQLite存数据)

完成查询,删除,添加,插入等功能

1.先写一个单例

+ (instancetype)shareDataBase;

+ (instancetype)shareDataBase{
    static DataBaseManager *manager = nil;
    static dispatch_once_t once;
    dispatch_once(&once, ^{
        manager = [[DataBaseManager alloc]init];

    });
    return manager;
}

2.打开

- (void)openDB;

- (void)openDB{
    NSString *sandBoxPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, 1, YES)lastObject];
    self.filePath = [sandBoxPath stringByAppendingPathComponent:@"Student.sqlite"];
    NSLog(@"%@",self.filePath);
///创建一个FMDB的对象
    self.fmdb = [[FMDatabase alloc]initWithPath:self.filePath];
    BOOL result = [self.fmdb open];
    if (result) {
        NSLog(@"打开成功");
    }else{
        NSLog(@"打开失败");
    }
}

3.创建

- (void)creatTable;

- (void)creatTable{
    NSString *sqlStr = @"create table if not exists student1135(number integer primary key autoincrement,  name text,sex text) ";
///执行sql语句
    [self executeSql:sqlStr message:@"创建表"];
}

4.判断是否创建成功

- (void)executeSql:(NSString *)sqlStr message:(NSString *)message{
    BOOL result = [self.fmdb executeUpdate:sqlStr];
    if (result) {
        NSLog(@"%@",[NSString stringWithFormat:@"%@成功",message]);
    }else{
        NSLog(@"%@",[NSString stringWithFormat:@"%@失败",message]);
    }
}

5.插入 @”insert into student1135(name,sex) values(‘%@’,’%@’)”,stu.name,stu.sex

- (void)insertStu:(Student *)stu;

- (void)insertStu:(Student *)stu{
    NSString *sqlStr = [NSString stringWithFormat:@"insert into student1135(name,sex) values('%@','%@')",stu.name,stu.sex];
    [self executeSql:sqlStr message:@"添加一个学生"];
}

6.更新 @”update student1135 set sex = ‘女’where sex=’男’”

- (void)updateStu:(Student *)stu;

- (void)updateStu:(Student *)stu{
    NSString *sqlStr = @"update student1135 set sex = '女'where sex='男'";
    [self executeSql:sqlStr message:@"更新学生性别"];
}

7.删除 @”delete from student1135 where name = ‘J’”

- (void)deleteStu:(Student *)stu;

- (void)deleteStu:(Student *)stu{
    NSString *sqlStr = @"delete  from student1135 where name = 'J'";
    [self executeSql:sqlStr message:@"删除学生"];
}

8.遍历 @”select * from student1135”

- (void)selectAllStu;

- (void)selectAllStu{
    NSString *sqlStr = @"select * from student1135";
    ///执行查询方法
    FMResultSet *resultSet = [self.fmdb executeQuery:sqlStr];
    ///系统会把查询之后的结果赋值给resultSet
    ///进行遍历
    while ([resultSet next]) {
        NSString *name = [resultSet stringForColumn:@"name"];
        NSLog(@"%@",name);
        NSInteger number = [resultSet intForColumn:@"number"];
        NSLog(@"%ld",number);
    }
}

9.删除表 @”drop table student1135”

- (void)dropTable

- (void)dropTable{
    NSString *sqlStr = @"drop table student1135";
    [self executeSql:sqlStr message:@"删除表"];
}

10.一次插入多个学生 @”insert into student1135(name,sex) values(‘%@’,’%@’)”,stu.name,stu.sex

- (void)insertMoreStu:(Student *)stu;

- (void)insertMoreStu:(Student *)stu{
    ///创建FMDB多线程对象,实现将多个插入操作放到子线程中,并且完成添加操作
    FMDatabaseQueue *queue = [[FMDatabaseQueue alloc]initWithPath:self.filePath];
    ///需要创建一个事物,然后把多个操作放到事物中去执行
    ///Transaction就是事物的意思
    [queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
         NSString *sqlStr = [NSString stringWithFormat:@"insert into student1135(name,sex) values('%@','%@')",stu.name,stu.sex];
        BOOL result = [db executeUpdate:sqlStr];
        if (result) {
            NSLog(@"插入多个成功");
        }else{
            NSLog(@"插入多个失败");
        }
    }];
}

注 :调用方法没写,在下面的例子中会有具体应用(在ViewController.m中)

例:

Student.h

#import <Foundation/Foundation.h>

@interface Student : NSObject
@property(nonatomic,copy)NSString *name;
@property(nonatomic,copy)NSString *sex;
@end

DataBaseManager.h

#import <Foundation/Foundation.h>
#import "Student.h"
@interface DataBaseManager : NSObject

+ (instancetype)shareDataBase;
- (void)openDB;
- (void)creatTable;
- (void)insertStu:(Student *)stu;
- (void)updateStu:(Student *)stu;
- (void)deleteStu:(Student *)stu;
- (void)selectAllStu;
- (void)dropTable;
///一次插入多个学生
- (void)insertMoreStu:(Student *)stu;

@end

DataBaseManager.m

#import "DataBaseManager.h"
#import "FMDatabase.h"
#import "FMDatabaseQueue.h"
#import "FMDB.h"
@interface DataBaseManager ()
@property(nonatomic,copy)NSString *filePath;
@property(nonatomic,retain)FMDatabase *fmdb;
@end

@implementation DataBaseManager

+ (instancetype)shareDataBase{
    static DataBaseManager *manager = nil;
    static dispatch_once_t once;
    dispatch_once(&once, ^{
        manager = [[DataBaseManager alloc]init];

    });
    return manager;
}

- (void)openDB{
    NSString *sandBoxPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, 1, YES)lastObject];
    self.filePath = [sandBoxPath stringByAppendingPathComponent:@"Student.sqlite"];
    NSLog(@"%@",self.filePath);
///创建一个FMDB的对象
    self.fmdb = [[FMDatabase alloc]initWithPath:self.filePath];
    BOOL result = [self.fmdb open];
    if (result) {
        NSLog(@"打开成功");
    }else{
        NSLog(@"打开失败");
    }
}
- (void)executeSql:(NSString *)sqlStr message:(NSString *)message{
    BOOL result = [self.fmdb executeUpdate:sqlStr];
    if (result) {
        NSLog(@"%@",[NSString stringWithFormat:@"%@成功",message]);
    }else{
        NSLog(@"%@",[NSString stringWithFormat:@"%@失败",message]);
    }
}

- (void)creatTable{
    NSString *sqlStr = @"create table if not exists student1135(number integer primary key autoincrement,  name text,sex text) ";
///执行sql语句
    [self executeSql:sqlStr message:@"创建表"];
}
- (void)insertStu:(Student *)stu{
    NSString *sqlStr = [NSString stringWithFormat:@"insert into student1135(name,sex) values('%@','%@')",stu.name,stu.sex];
    [self executeSql:sqlStr message:@"添加一个学生"];
}
- (void)updateStu:(Student *)stu{
    NSString *sqlStr = @"update student1135 set sex = '女'where sex='男'";
    [self executeSql:sqlStr message:@"更新学生性别"];
}
- (void)deleteStu:(Student *)stu{
    NSString *sqlStr = @"delete  from student1135 where name = 'J'";
    [self executeSql:sqlStr message:@"删除学生"];
}
- (void)selectAllStu{
    NSString *sqlStr = @"select * from student1135";
    ///执行查询方法
    FMResultSet *resultSet = [self.fmdb executeQuery:sqlStr];
    ///系统会把查询之后的结果赋值给resultSet
    ///进行遍历
    while ([resultSet next]) {
        NSString *name = [resultSet stringForColumn:@"name"];
        NSLog(@"%@",name);
        NSInteger number = [resultSet intForColumn:@"number"];
        NSLog(@"%ld",number);
    }
}
- (void)dropTable{
    NSString *sqlStr = @"drop table student1135";
    [self executeSql:sqlStr message:@"删除表"];
}
- (void)insertMoreStu:(Student *)stu{
    ///创建FMDB多线程对象,实现将多个插入操作放到子线程中,并且完成添加操作
    FMDatabaseQueue *queue = [[FMDatabaseQueue alloc]initWithPath:self.filePath];
    ///需要创建一个事物,然后把多个操作放到事物中去执行
    ///Transaction就是事物的意思
    [queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
         NSString *sqlStr = [NSString stringWithFormat:@"insert into student1135(name,sex) values('%@','%@')",stu.name,stu.sex];
        BOOL result = [db executeUpdate:sqlStr];
        if (result) {
            NSLog(@"插入多个成功");
        }else{
            NSLog(@"插入多个失败");
        }
    }];
}
@end

ViewController.m ( 调用)

#import "ViewController.h"
#import "DataBaseManager.h"
#import "Student.h"
#import "Main.h"
@interface ViewController ()
- (IBAction)createTable:(id)sender;
- (IBAction)insert:(id)sender;
- (IBAction)update:(id)sender;
- (IBAction)delete:(id)sender;
- (IBAction)selete:(id)sender;
- (IBAction)dropTable:(id)sender;
- (IBAction)insertLines:(id)sender;

@property (weak, nonatomic) IBOutlet UIButton *insertLines;

@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    [[DataBaseManager shareDataBase]openDB];
}


- (IBAction)createTable:(id)sender {
    [[DataBaseManager shareDataBase] creatTable];
}

- (IBAction)insert:(id)sender {
    Student *stu = [[Student alloc]init];
    stu.name = @"张三";
    stu.sex = @"男";
    [[DataBaseManager shareDataBase] insertStu:stu];
}

- (IBAction)update:(id)sender {
    Student *stu = [[Student alloc]init];
    [[DataBaseManager shareDataBase] updateStu:stu];
}

- (IBAction)delete:(id)sender {
     Student *stu = [[Student alloc]init];

    [[DataBaseManager shareDataBase] deleteStu:stu];
}

- (IBAction)selete:(id)sender {
    [[DataBaseManager shareDataBase] selectAllStu];
}

- (IBAction)dropTable:(id)sender {
    [[DataBaseManager shareDataBase] dropTable];
}

- (IBAction)insertLines:(id)sender {
    for (NSInteger i = 0; i < 100; i ++) {
        Student *stu = [[Student alloc]init];
        stu.name = [NSString stringWithFormat:@"张%ld",i];
        stu.sex = @"男";
        [[DataBaseManager shareDataBase]insertMoreStu:stu];
    }
}