Xcode 操作数据库

#define kDBName @"SHS150711.sqlite"//数据库表名
//数据库指针(对象)
static sqlite3 *db = nil;

+ (sqlite3 *)open{
    @synchronized(self){
        //打开过了直接返回
        if (db != nil) {
            return db;
        }else{
            //第一个参数: 数据库路径
            //第二个参数: 数据库对象地址, 通过地址改值, 返回一个打开的数据库
            //1. 数据库路径
            //注意: 数据库是备份的数据, 需要备份的数据都是存放在沙盒的 documents 里面
           
            NSString *documents = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)objectAtIndex:0];
           
            NSString *dbPath = [documents stringByAppendingPathComponent:kDBName];
           
            //2. 创建并且打开数据库, 如果没有该数据库会自动创建并且打开
           int result = sqlite3_open(dbPath.UTF8String, &db);
            //该函数的作用是打开路径数据库, 并且给db 赋值.
           
            //3. 判断数据库是否打开, 如果打开, 则创建数据库表
            if (result == SQLITE_OK) {
               
                //4. sql 语句
                NSString *sqlString = @"CREATE TABLE 'studentInfo' ('uid' INTEGER PRIMARY KEY NOT NULL, 'username' text, 'pwd' text)";
               
                //5. 执行sql语句
                //sqlite3 执行函数
                //db : 数据库对象
                //sql sql 语句 C字符串
                //回调函数, 查询时使用
                //作用: 数据库db 执行sqlstring 语句.并返回执行结果, int 型
               
                int result2 = sqlite3_exec(db, sqlString.UTF8String,nil, nil, nil);
               
                if (result2 == SQLITE_OK) {
                    NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"表创建成功");
                }else{
                    NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"表创建失败, 或者已经创建过");
                }
               
            }//if 结束
           
        }//else 结束
        return db;
    }//sync 结束
}

+ (void)close{
    int result = sqlite3_close(db);
   
    if (result == SQLITE_OK) {
         NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"数据库关闭");
      db = nil;
    }
}

//添加数据
+ (BOOL)insertStudent:(Student *)student{
   
    BOOL isSuccess = NO;
    //1. 数据库对象
    sqlite3 *db = [self open];
   
    //2. sql语句
    NSString *sqlString = [NSString stringWithFormat:@"INSERT INTO 'studentInfo' values(%ld, '%@', '%@')", student.uid, student.username, student.pwd];
   
    //3. 执行exe
    int result = sqlite3_exec(db, sqlString.UTF8String, nil, nil, nil);
   
    if (result == SQLITE_OK) {
        NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"数据库插入成功");
        isSuccess = YES;
    }else{
        NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"数据库插入失败");
    }
    return isSuccess;
}

//删除数据
+ (BOOL)deleteStudent:(Student *)student{
    BOOL isSuccess = NO;
    //1. 数据库对象
    sqlite3 *db = [self open];
   
    //2. sql 语句
    NSString *sqlString = [NSString stringWithFormat:@"DELETE FROM 'studentInfo' WHERE uid = %ld", student.uid];
   
    //3. 执行
    int result = sqlite3_exec(db, sqlString.UTF8String, nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"数据删除成功");
        isSuccess = YES;
    }
   
    return isSuccess;
}

//更改数据
+ (BOOL)updateStudent:(Student *)student pwd:(NSString *)pwd{
    BOOL isSuccess = NO;
    //1. 数据库对象
    sqlite3 *db = [self open];
   
    //2. sql语句
    NSString *sqlString = [NSString stringWithFormat:@"UPDATE 'studentInfo' set 'pwd' = '%@' WHERE uid = %ld;", pwd, student.uid];
   
    //3. 执行
    int result = sqlite3_exec(db, sqlString.UTF8String, nil, nil, nil);
   
    if (result == SQLITE_OK) {
        NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"数据更新成功");
        isSuccess = YES;
    }else{
        NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"数据更新失败");
    }
    return isSuccess;
}

//查询某一数据
+ (Student *)findStudentByUID:(NSInteger)uid{
   
    Student *student = nil;//创建返回对象
    //1. 数据库对象
    sqlite3 *db = [self open];
   
    //2. sql 字符串
    NSString *sqlString = [NSString stringWithFormat:@"SELECT * FROM 'studentInfo' WHERE uid = %ld", uid];
   
    //3. 创建 stmt statement 语句对象
    sqlite3_stmt *stmt = nil;//作用 执行sql语句
   
    //4. 预执行, 查看有无语法错误, 给stmt 赋值 sql 语句
    //sqlite3_prepare_v2()
    //db 数据库对象
    //sql 语句
    //-1 语句执行长度 -1 为全部长度
    //&stmt 语句对象地址, 如果预执行正确则赋值给stmt sql语句
    //nil 没有执行的sql 语句, 一般给nil
    int result = sqlite3_prepare_v2(db, sqlString.UTF8String, -1, &stmt, nil);
   
    //5. 判断 如果预 执行成功, 执行相关操作
    if (result == SQLITE_OK) {
       
        //6.执行 sqlite3_step(stmt) ,执行函数有返回值, 如果是插入, 删除, 更新, 返回值为 SQLITE_DOWN,
        //如果是查找操作, 则会返回SQLITE_ROW, 当前行并且指向下一行, 直到没有值, 返回 SQLITE_DOWN
        if (sqlite3_step(stmt) == SQLITE_ROW) {
            //7. 如果有值, 则逐列取值  sqlite3_column_类型(stmt, 列下标, 从0 开始)
            NSInteger uid = sqlite3_column_int(stmt, 0);
            const  unsigned  char *cName = sqlite3_column_text(stmt, 1);
            //username
            NSString *username = [NSString stringWithUTF8String:(const char *)cName];
            //pwd
            NSString *pwd = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
           
            student = [[Student alloc] initWithUID:uid username:username pwd:pwd];
        }//if  step 结束
    }//if result 结束
   
    //8. 释放stmt 语句对象 , 无论执行是否正确, 都需要释放.
    sqlite3_finalize(stmt);
   
    if (student == nil) {
        NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"查找失败");
    }else{
        NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"查找成功");
    }
    return student;
}

//查询所有数据
+ (NSArray *)findAllStudents{
   
    //返回值 可变数组
    NSMutableArray *stuArray = [NSMutableArray array];
   
    //1. 数据库对象
    sqlite3 *db = [self open];
   
    //2. sql 语句
    NSString *sqlString = @"SELECT * FROM 'studentInfo';";
   
    //3. stmt 语句对象
    sqlite3_stmt *stmt = nil;
   
    //4. prepare_v2 预执行
    int result = sqlite3_prepare_v2(db, sqlString.UTF8String, - 1, &stmt, nil);
   
    //5. 判断
    if (result == SQLITE_OK) {
       
        //6. 执行 查询多条记录, 需要用while 语句
        // == row 说明有值, 则取值
        while (sqlite3_step(stmt) == SQLITE_ROW) {
           
            //7. 逐列取值
            //uid
            NSInteger uid = sqlite3_column_int(stmt, 0);
           
            //username
            NSString *username = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            //pwd
            NSString *pwd = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
           
            Student *student = [[Student alloc] initWithUID:uid username:username pwd:pwd];
           
            [stuArray addObject:student];
        }//while 查询结束
    }//if 预执行结束
   
    //8. 释放
    sqlite3_finalize(stmt);
   
    if (stuArray.count == 0) {
        NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"没有数据或查找失败");
    }else{
        NSLog(@"%s %d %@", __FUNCTION__, __LINE__ , @"查找成功");
    }
    return stuArray;
}