Xcode 操作数据库

来源：互联网发布：ettercap linux 安装编辑：程序博客网时间：2024/06/01 10:52
#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;}
0 0