sqlite#

#import "DataBase.h"
#import <sqlite3.h>
#import "Student.h"
@implementation DataBase
static DataBase *dataBase = nil;
+(instancetype)shareDatabase{
    //加锁
    @synchronized(self) {
        if (nil == dataBase) {
            dataBase = [[DataBase alloc]init];
            //打开数据库
            [dataBase openDB];
        }
    }
    return dataBase;
}
//创建数据库对象
static sqlite3 *db = nil;
//打开数据库
-(void)openDB{
    //如果数据库已经打开,则不需要执行后面的操作
    if (db != nil) {
        return;
    }
    //创建保存数据库的路径
    NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)firstObject];
    documentPath = [documentPath stringByAppendingString:@"/蓝欧.sqlite"];
    //转码 打开数据库(如果该数据库存在 则直接打开 否则,自动创建一个再打开)
   int result = sqlite3_open([documentPath UTF8String], &db);
    NSLog(@"%@",documentPath);
    if (result == SQLITE_OK) {
        NSLog(@"数据库成功打开");
        //建表
        //准备sql语句
        NSString *sql = @"CREATE TABLE Class43 (Stu_ID INTEGER PRIMARY KEY NOT NULL UNIQUE, name TEXT NOT NULL, gender TEXT NOT NULL DEFAULT M, age INTEGER NOT NULL);";
        //执行sql语句
        sqlite3_exec(db, [sql UTF8String], NULL, NULL, NULL);
    }else{
        NSLog(@"数据库打开失败%d",result);
    }
}

//关闭数据库
-(void)closeDB{
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库关闭成功");
        //关闭数据库的时候,将db置为空,是因为打开数据库的时候,我们需要使用nil做判断
        db = nil;
    }else{
        NSLog(@"数据库关闭失败:%d",result);
    }
}

//添加
-(void)insertStudent:(Student *)stu{
    //1.打开数据库
    [self openDB];
    
    //2.创建跟随指针
    sqlite3_stmt *stmt = nil;
    //3.准备sql语句
    NSString *sql = @"INSERT INTO Class43 (Stu_ID,name,gender,age)VALUES (?,?,?,?)";
    //4.验证sql语句的正确性
    int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
    //5.
    if (result == SQLITE_OK) {
        NSLog(@"数据库添加成功");
        //一旦sql语句没有问题就要开始绑定数据,替换  ?
        //(1)跟随指针 (2)问号的顺序(从1开始) (3)要绑定的值
        
        sqlite3_bind_int(stmt, 1, stu.Stu_ID);
        sqlite3_bind_text(stmt, 2, [stu.name UTF8String], -1, NULL);
        sqlite3_bind_text(stmt, 3, [stu.gender UTF8String], -1, NULL);
        sqlite3_bind_int(stmt, 4, stu.age);
        //6.单步执行
        sqlite3_step(stmt);
        
    }else{
        NSLog(@"数据库添加失败:%d",result);
    }
    //7.释放跟随指针占用的内存
    sqlite3_finalize(stmt);
}

//删除
-(void)deleteStudent:(int)Stu_ID{
    //1.
    [self openDB];
    //2
    sqlite3_stmt *stmt = nil;
    //3.
    NSString *str = @"DELETE FROM Class43 WHERE Stu_ID = ?";
    //4.
    int result = sqlite3_prepare_v2(db, [str UTF8String], -1, &stmt, NULL);
    //5.
    if (result == SQLITE_OK){
        NSLog(@"数据删除成功");
        //开始绑定
        sqlite3_bind_int(stmt, 1, Stu_ID);
        //6.执行
        sqlite3_step(stmt);
        
    }else{
        NSLog(@"数据删除失败:%d",result);
    }
    //7.
    sqlite3_finalize(stmt);
}
//修改
-(void)upDataStudentWithGender:(NSString *)gender
                        Stu_ID:(int)Stu_ID{
    [self openDB];
    sqlite3_stmt *stmt = nil;
    NSString *str = @"UPDATE Class43 SET name = ?  WHERE Stu_ID = ?";
    
    int result = sqlite3_prepare_v2(db, [str UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"修改成功");
        //开始绑定
        sqlite3_bind_text(stmt, 1, [gender UTF8String], -1, NULL);
        sqlite3_bind_int(stmt, 2, Stu_ID);
        //执行
        sqlite3_step(stmt);
        
    }else{
        NSLog(@"修改失败");
    }
    //
    sqlite3_finalize(stmt);
}

//查询所有
-(NSArray *)selectAllowStudents{
    [self openDB];
    sqlite3_stmt *stmt = nil;
    NSString *str = @"SELECT *FROM Class43";
    
    int result = sqlite3_prepare_v2(db, [str UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        //创建可编数组,用来存放查询到的学生
        NSMutableArray *array = [NSMutableArray array];
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //根据sql语句,将搜索到的复合条件的值取出来
            //0代表数据库表的第一列
            int stu_id = sqlite3_column_int(stmt, 0);
            NSString *name = [NSString stringWithUTF8String:(const char*)sqlite3_column_text(stmt, 1)];
            NSString *gender = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            int age = sqlite3_column_int(stmt, 3);
            //将取出来的信息赋值给学生的模型
            Student *stu = [[Student alloc]initWithName:name age:age
                                                 gender:gender Stu_ID:stu_id];
            //将学生添加到可编数组里面
            [array addObject:stu];
            
        }
        //释放
        sqlite3_step(stmt);
        return array;
        
    }else{
        NSLog(@"查询失败%d",result);
    }
        //释放空间
    sqlite3_finalize(stmt);
    
    return nil;
}

//查询单个学生
-(Student *)selectStudentWithID:(int)Stu_ID{
    //1.打开数据库
    [self openDB];
    //2.跟随指针
    sqlite3_stmt *stmt = nil;
    //3准备sql语句
    NSString *str = @"SELECT *FROM Class43 WHERE Stu_ID = ?";
    //4验证
    int result = sqlite3_prepare_v2(db, [str UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        //5.绑定
        sqlite3_bind_int(stmt, 1, Stu_ID);
        //6.执行
        Student *stu = [Student new];
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            int stu_ID = sqlite3_column_int(stmt, 0);
            NSString *name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            NSString *gender = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            int age = sqlite3_column_int(stmt, 3);
            
            stu.Stu_ID = stu_ID;
            stu.name = name;
            stu.gender = gender;
            stu.age = age;
        }
        //释放
        sqlite3_step(stmt);
        return stu;
    }else{
        NSLog(@"数据库查询单个学生失败 %d",result);
    }
    //释放内存
    sqlite3_finalize(stmt);
    
    return nil;
}
@end

#import "ViewController.h"
#import "DataBase.h"
#import "Student.h"
@interface ViewController ()

@end

@implementation ViewController

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

- (IBAction)addStudent:(UIButton *)sender {
    NSLog(@"增加学生");
    Student *stu = [Student new];
    stu.Stu_ID = 1;
    stu.name = @"昭君";
    stu.gender = @"男";
    stu.age = 18;
    
    Student *stu1 = [[Student alloc]initWithName:@"大队长" age:25 gender:@"女" Stu_ID:2];
    Student *stu2 = [[Student alloc]initWithName:@"爱君" age:18 gender:@"女" Stu_ID:3];
    Student *stu3 = [[Student alloc]initWithName:@"饱饱" age:24 gender:@"男" Stu_ID:21];
    Student *stu4 = [[Student alloc]initWithName:@"金金" age:25 gender:@"女" Stu_ID:25];
    DataBase *db = [DataBase shareDatabase];
    [db insertStudent:stu];
    [db insertStudent:stu1];
    [db insertStudent:stu2];
    [db insertStudent:stu3];
    [db insertStudent:stu4];
}

- (IBAction)deletStudent:(UIButton *)sender {
    NSLog(@"删除学生");
    
    
//    Student *stu = [Student new];
//    stu.Stu_ID = 1;
    DataBase *db = [DataBase shareDatabase];
    [db deleteStudent:1];
}
- (IBAction)findStudent:(UIButton *)sender {
    NSLog(@"查询学生");
    DataBase *db = [DataBase shareDatabase];
   NSArray *array = [db selectAllowStudents];
    NSLog(@"%@",array);
}
- (IBAction)findOneStudent:(UIButton *)sender {
    NSLog(@"查询单个学生");
    DataBase *db = [DataBase shareDatabase];
   Student *stu = [db selectStudentWithID:2];
    NSLog(@"%@",stu);
}
- (IBAction)UpDataStudent:(UIButton *)sender {
    NSLog(@"修改学生");
    DataBase *db = [DataBase shareDatabase];
    [db upDataStudentWithGender:@"齐天大圣" Stu_ID:2];
    
}

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

@end