完成查询,删除,添加,插入等功能
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];
}
}