数据库

数据库

1.创建一个数据库对象,为了保证数据库对象在整个工程里是唯一的,通过单例模式进行创建;单例必须是+号方法,对方法名也有要求,比如要以share,main,standard,default;常见的单例UIMenuController, NSUserDefaults,NSFileManager等

+ (DataBaseHandle *)shareDataBaseHandle;

+ (DataBaseHandle *)shareDataBaseHandle{
    
    // 第一种写法
//    static DataBaseHandle *dataBase;
//    if (!dataBase) {
//        dataBase = [[DataBaseHandle alloc] init];
//    }
//    return dataBase;
    
    // 第二种写法!!!!!!!!!!!!用这种方式(安全)
    static DataBaseHandle *dataBase = nil;
    static dispatch_once_t onceToken;
    // 创建之后,只会被执行一次
    dispatch_once(&onceToken, ^{
        dataBase = [[DataBaseHandle alloc] init];
    });
    return dataBase;
}

创建对象有三种类型id,instancetype,DataBaseHandle *;用id点不出属性,instancetype只能用在遍历构造器和初始化方法里使用;id可以使用在任何位置,相当于void *;instancetype相当于实例类型

2.打开数据库

- (void)openDB{
    // 数据库保存在沙盒的document文件夹
    // 找沙盒路径
    NSString *sandBoxPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, 1, YES) lastObject];
    NSLog(@"%@", sandBoxPath);
    // 拼接文件路径,如果数据库的文件没有的话,系统会自动创建一个
    NSString *dbPath = [sandBoxPath stringByAppendingPathComponent:@"Student.sqlite"];
    // 扩展名也可以是.db
    
    // 根据路径去打开数据库
    //                         oc的字符串转换成c
    int result = sqlite3_open([dbPath UTF8String], &dataBasePoint);
    if (result == SQLITE_OK) {
        NSLog(@"打开成功");
    } else {
        NSLog(@"%d", result);
    }
}

3.创建表(create table if not exists student(number integer primary key autoincrement, name text, sex text, hobby text)

- (void)createTable{
    NSString *sqlStr = @"create table if not exists student(number integer primary key autoincrement, name text, sex text, hobby text)";
    // 执行sql语句
    int result = sqlite3_exec(dataBasePoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    } else {
        NSLog(@"创建表失败");
    }
}

4.插入[NSString stringWithFormat:@"insert into student(name,sex,hobby) values ('%@','%@','%@')", stu.name, stu.sex, stu.hobby];

- (void)insertStu:(Student *)stu{
    
    NSString *sqlStr = [NSString stringWithFormat:@"insert into student(name,sex,hobby) values ('%@','%@','%@')", stu.name, stu.sex, stu.hobby];
    // 需要做一个拼接
    int result = sqlite3_exec(dataBasePoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"添加学生成功");
    } else {
        NSLog(@"失败");
    }
}

5.删除(NSString stringWithFormat:@"delete from student where name = '%@'", stu.name)

- (void)deleteStu:(Student *)stu{
    NSString *sqlStr = [NSString stringWithFormat:@"delete from student where name = '%@'", stu.name];
    int result = sqlite3_exec(dataBasePoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"失败");
    }
}

6.更新(NSString stringWithFormat:@"update student set name = '%@' where number = '12'", stu.name)

- (void)updateStu:(Student *)stu{
    NSString *sqlStr = [NSString stringWithFormat:@"update student set name = '%@' where number = '12'", stu.name];
    int result = sqlite3_exec(dataBasePoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"更新成功");
    } else {
        NSLog(@"失败");
    }
}

7.查询(select *from student, *代表是通配符,代表了所有的字段名)需要创建一个跟随指针,用来遍历查询数据库中表的每行数据

记得注销跟随指针 sqlite3_finalize(stmt);

- (NSMutableArray *)selectAllStu{
    // *代表是通配符,代表了所有的字段名
    NSString *sqlStr = @"select *from student";
    // 需要创建一个跟随指针,用来遍历查询数据库中表的每行数据
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dataBasePoint, [sqlStr UTF8String], -1, &stmt, nil);
    NSMutableArray *arr = [NSMutableArray array];
    
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            int number = sqlite3_column_int(stmt, 0);
            Student *stu = [[Student alloc] init];
            // 把表里的值付给属性
            stu.number = number;
            // 表里取文本内容
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            stu.name = [NSString stringWithUTF8String:(const char *)name];
            [arr addObject:stu];
        }
    }
    // 注销跟随指针
    sqlite3_finalize(stmt);
    return  arr;
}

查询一个学生 select *from student  where number = '12'

- (Student *)selectOneStudent{
    NSString *sqlStr = @"select *from student  where number = '12'";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dataBasePoint, [sqlStr UTF8String], -1, &stmt, nil);
    Student *stu = [[Student alloc] init];
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            int number = sqlite3_column_int(stmt, 0);
            stu.number = number;
            
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            stu.name = [NSString stringWithUTF8String:(const char *)name];

        }
        
        
    }
    return  stu;
}

查询几个人select *  from student where name = '李四'

<span style="font-size:14px;">- (NSInteger)selectByName{
    NSString *sqlStr = @"select *  from student where name = '李四'";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(dataBasePoint, [sqlStr UTF8String], -1, &stmt, nil);
    NSInteger i = 0;
    Student *stu = [[Student alloc] init];
    if (result == SQLITE_OK) {
        NSLog(@"查询成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            stu.name = [NSString stringWithUTF8String:(const char *)name];
            i++;
        }
    }
    return i;
}</span><span style="color: rgb(209, 47, 27); font-size: 18px;">
</span>