封装数据库查询学生信息(的增删查改)的方法

最新推荐文章于 2022-06-20 15:37:02 发布

原创最新推荐文章于 2022-06-20 15:37:02 发布 · 1.5k 阅读

1 ·

CC 4.0 BY-SA版权

本文介绍了一个使用SQLite进行学生信息管理的应用案例，包括查询所有学生信息、查询单个学生信息、添加学生信息、更新学生姓名及删除学生信息等功能的具体实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

紧接上一篇的文章内容(调用其打开和关闭数据库的方法)

//查询所有学生
+ (NSMutableArray *)getAllStudents;
//查询单个学生
+ (Student *)getStudentWithID:(NSInteger)aID;
//添加一个学生
+ (BOOL)insertStudent:(Student *)aStudent;
//修改一个学生Name
+ (BOOL)updateStudentWithName:(NSString *)aName byID:(NSInteger)aID;
//删除一个学生
+ (BOOL)deleteStudentWithID:(NSInteger)aID;

.m文件

//查询所有学生
+ (NSMutableArray *)getAllStudents
{
    sqlite3 *db = [DataBase openDB];//打开数据库

    //数据库的操作指针,stmt:statement
    sqlite3_stmt *stmt = nil;

    //验证SQL语句的正确性
    //参数1:数据库指针,参数2:SQL语句,参数3:SQL语句的长度(-1自动匹配长度,代表无限长),参数4:返回数据库操作指针,参数5:未来做准备.预留参数;一般写成NULL
    int result = sqlite3_prepare_v2(db, "select * from Student", -1, &stmt, NULL);
    NSMutableArray *studentArray = [NSMutableArray array];

    //判断SQL语句执行的结果(是否执行)
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW)//存在一行数据
        {
            int ID = sqlite3_column_int(stmt, 0);//列数从0开始
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *sex = sqlite3_column_text(stmt, 2);
            int age = sqlite3_column_int(stmt, 3);

            //blob类型的获取
            //1:获取长度
            int length = sqlite3_column_bytes(stmt, 4);
            //2:获取数据
            const void *photo = sqlite3_column_blob(stmt, 4);
            //3:转成NSData
            NSData *photoData = [NSData dataWithBytes:photo length:length];
            //4:转成UIImage
            UIImage *image = [UIImage imageWithData:photoData];

            //封装student模型
            Student *student = [[Student alloc] init];
            student.ID = ID;
            student.name = [NSString stringWithUTF8String:(const char *)name];
            student.sex = [NSString stringWithUTF8String:(const char *)sex];
            student.age = age;
            student.photo = image;

            //添加到数组中
            [studentArray addObject:student];
        }

    }
    //释放stmt指针
    sqlite3_finalize(stmt);
    //close数据库
    [DataBase closeDB];
    return studentArray;
}
//查询单个学生
+ (Student *)getStudentWithID:(NSInteger)aID
{
    sqlite3 *db = [DataBase openDB];
    sqlite3_stmt *stmt = nil;
    NSString *sqlString = [NSString stringWithFormat:@"select * from Student where id = %d",aID];
    int result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmt, NULL);

    Student *student = nil;

    if (result == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_ROW) {
            int ID = sqlite3_column_int(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *sex = sqlite3_column_text(stmt, 2);
            int age = sqlite3_column_int(stmt, 3);

            int length = sqlite3_column_bytes(stmt, 4);
            const void *photo = sqlite3_column_blob(stmt, 4);
            NSData *photoData = [NSData dataWithBytes:photo length:length];
            UIImage *image = [UIImage imageWithData:photoData];

            student = [[Student alloc] init];
            student.ID = ID;
            student.name = [NSString stringWithUTF8String:(const char *)name];
            student.sex = [NSString stringWithUTF8String:(const char *)sex];
            student.age = age;
            student.photo = image;

        }
    }
    sqlite3_finalize(stmt);
    [DataBase closeDB];
    return student;
}
//添加一个学生
+ (BOOL)insertStudent:(Student *)aStudent
{
    sqlite3 *db =[DataBase openDB];
    sqlite3_stmt *stmt = nil;
    NSString *sqlString = [NSString stringWithFormat:@"insert into Student (name, sex, age)values('%@','%@',%d)",aStudent.name,aStudent.sex,aStudent.age];
    int result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmt, nil);
    if (result == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_DONE)//判断语句是否执行完毕
        {
            sqlite3_finalize(stmt);
            [DataBase closeDB];
            return YES;
        }
    }
    sqlite3_finalize(stmt);
    [DataBase closeDB];
    return NO;
}
//修改一个学生Name
+ (BOOL)updateStudentWithName:(NSString *)aName byID:(NSInteger)aID
{
    sqlite3 *db = [DataBase openDB];
    sqlite3_stmt *stmt = nil;
    NSString *sqlString = [NSString stringWithFormat:@"update Student set name ='%@' where id = %d",aName,aID];
    int result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            sqlite3_finalize(stmt);
            [DataBase closeDB];
            return YES;
        }
    }
    sqlite3_finalize(stmt);
    [DataBase closeDB];
    return NO;
}
//删除一个学生
+ (BOOL)deleteStudentWithID:(NSInteger)aID
{
    sqlite3 *db = [DataBase openDB];
    sqlite3_stmt *stmt = nil;
    NSString *sqlString = [NSString stringWithFormat:@"delete from Student where id = %d",aID];
    int result = sqlite3_prepare_v2(db, [sqlString UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            sqlite3_finalize(stmt);
            [DataBase closeDB];
            return YES;
        }
    }
    sqlite3_finalize(stmt);
    [DataBase closeDB];
    return NO;
}