数据库

ViewController.m
#import "ViewController.h"
#import "DataBaseHandle.h"
#import "Student.h"
@interface ViewController ()
- (IBAction)openDB:(id)sender;
- (IBAction)createTable:(id)sender;
- (IBAction)insert:(id)sender;
- (IBAction)delete:(id)sender;
- (IBAction)change:(id)sender;
- (IBAction)selectAll:(id)sender;
- (IBAction)closeDB:(id)sender;

@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    // Do any additional setup after loading the view, typically from a nib.
}

- (void)didReceiveMemoryWarning {
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}

- (IBAction)openDB:(id)sender {
    // 用单例找到数据库的对象,然后去调用方法.
    [[DataBaseHandle shareDataBaseHandle] openDB];
}

- (IBAction)createTable:(id)sender {
    [[DataBaseHandle shareDataBaseHandle] creatTable];
}

- (IBAction)insert:(id)sender {
    Student *stu = [[Student alloc] init];
    stu.name = @"哈哈";
    stu.sex = @"女";
    stu.age = 20;
    [[DataBaseHandle shareDataBaseHandle] insertStu:stu];
}

- (IBAction)delete:(id)sender {
    Student *stu = [[Student alloc] init];
    stu.name = @"老师";
    [[DataBaseHandle shareDataBaseHandle] deleteStu:stu];
}

- (IBAction)change:(id)sender {
    Student *stu = [[Student alloc] init];
    stu.name = @"男男女女";
    stu.age = 15;
    stu.sex = @"男";
    [[DataBaseHandle shareDataBaseHandle] updateStu:stu];
}

- (IBAction)selectAll:(id)sender {
    NSMutableArray *stuArr = [[DataBaseHandle shareDataBaseHandle] selectAllStu];
    for (Student *stu in stuArr) {
        NSLog(@"%@", stu.name);
    }

}

- (IBAction)closeDB:(id)sender {
    [[DataBaseHandle shareDataBaseHandle] closeDB];
}
@end

#import <Foundation/Foundation.h>
#import <sqlite3.h>
#import "Student.h"
@interface DataBaseHandle : NSObject
{
    sqlite3 *dbPoint;
}
// 数据库对象的创建为了保证数据库唯一,使用单例模式进行创建.
+ (instancetype)shareDataBaseHandle;

// 打开数据库
- (void)openDB;

// 创建数据库表;
- (void)creatTable;

// 添加一个学生到数据库;
- (void)insertStu:(Student *)stu;

// 删除数据
- (void)deleteStu:(Student *)stu;

// 更新一条学生信息.
- (void)updateStu:(Student *)stu;

// 查找所有的学生,放到数组里,并且返回
- (NSMutableArray *)selectAllStu;

// 关闭数据库.
- (void)closeDB;
@end

DataBaseHandle.m
#import "DataBaseHandle.h"

@implementation DataBaseHandle

+ (instancetype)shareDataBaseHandle {
    static DataBaseHandle *handle;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        handle = [[DataBaseHandle alloc] init];
    });
    return handle;
}

- (void)openDB {
    // 数据库的文件保存在本地的沙盒路径,所以打开数据库得根据路径寻找数据库文件.
    NSString *sandBoxPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];

    // 拼接一个数据文件路径,如果路径下没有这个数据库文件则会创建一个,如果有的话,不重复创建,直接打开数据库.
    NSString *dbPath = [sandBoxPath stringByAppendingPathComponent:@"Student.sqlite"];
    // 为了让数据库工具能打开数据库的文件,一般文件的扩展是sqlite或者db,其他的打不开.

    // 打开数据库.
    // 参数1:文件的路径,需要把OC的字符串转换成C语言的字符串.
    // 调用的语句会返回执行结果,每个数字代表了不同的情况,如果发现错误,可以打印result,对照错误,修改
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"开启成功");
    } else {
        NSLog(@"开启失败");
    }
    NSLog(@"%@", dbPath);
}

- (void)creatTable {
    // 数据库问题80%是sql语句写错了,所以先用工具测试sql语句写的是否正确,无误再粘贴到工程里.
    NSString *sqlStr = @"create table if not exists stu(number integer primary key autoincrement, sex text, name text, age integer)";
    // 在数据库打开的情况下,直接操作sql语句的执行.
    // 数据库打开就是对dbpoint的指针进行赋值.
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"表创建成功");
    } else {
        NSLog(@"表创建失败");
    }
}

- (void)insertStu:(Student *)stu {
    NSString *sqlStr = [NSString stringWithFormat:@"insert into stu(name, sex, age) values ('%@', '%@', '%ld')", stu.name, stu.sex, stu.age];
    // 执行sql语句
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"添加成功");
    } else {
        NSLog(@"添加失败");
    }
}

- (void)deleteStu:(Student *)stu {
    // 删除分为两种,一种是物理删除,就是使用delete语句把数据全部删掉,另一种是update,这种是通过更改某条件,在查找过程中找不到,但是数据内容还在.
    NSString *sqlStr = [NSString stringWithFormat:@"delete from stu where name = '%@'", stu.name];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
}

- (void)updateStu:(Student *)stu {
    NSString *sqlStr = [NSString stringWithFormat:@"update stu set name = '%@', age = '%ld' where sex = '%@'", stu.name, stu.age, stu.sex];
    // 主键不能更新.
    // 执行sql语句.
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"更新成功");
    } else {
        NSLog(@"跟新失败");
    }
}

- (NSMutableArray *)selectAllStu {
    // 整个查询的逻辑.
    // 1.先从本地的数据库里读取出来所有的要的数据.
    // 2.然后把读出来的数据逐条给model的属性进行赋值.
    // 3.最后把赋值好的model放到数组里返回.
    NSString *sqlStr = @"select * from stu";
    // *是通配符的意思,它代表了查询所有的字段内容,如果想查询特定字段,把*换成字段名.

    // 接下来需要创建一个跟随指针,用来遍历数据库里查出来的每一条数据
    sqlite3_stmt *stmt = nil;

    // 进行查询.
    // 第三个参数:查询语句字数的限制,-1是没有限制
    int result = sqlite3_prepare_v2(dbPoint, [sqlStr UTF8String], -1, &stmt, nil);
    // 整个方法相当于把数据库,跟随指针,sql关联在一起,共同执行.
    NSMutableArray *strArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        // 逐行进行遍历
        // 如果当前没有数据的,循环结束,整个遍历过程结束.
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 满足循环条件,开始逐渐个model进行赋值.
            const unsigned char *sex = sqlite3_column_text(stmt, 1);
            const unsigned char *name = sqlite3_column_text(stmt, 2);
            int age = sqlite3_column_int(stmt, 3);
            // 把数据的类型进行转换
            Student *stu = [[Student alloc] init];
            stu.name = [NSString stringWithUTF8String:(const char *)name];
            stu.sex = [NSString stringWithUTF8String:(const char *)sex];
            stu.age = age;

            [strArr addObject:stu];
        }
    }
    // 把跟随指针注销掉,如果不注销,当多次查找之后会返回5,数据库繁忙,所以每次查询之后要注销
    sqlite3_finalize(stmt);
    return strArr;
}

- (void)closeDB {
    int result = sqlite3_close(dbPoint);
    if (result == SQLITE_OK) {
        dbPoint = nil;
        NSLog(@"关闭数据库");
    }
}
@end

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

@property(nonatomic, copy)NSString *name;
@property(nonatomic, copy)NSString *sex;
@property(nonatomic, assign)NSInteger age;
@end