数据库在IOS开发过程中的应用

数据库在IOS开发过程中的应用

+(NSArray *)selectAllNovel{

    NSMutableArray *results = [NSMutableArray array];

    

    //获得数据库指针

    sqlite3 *db = [DB openDB];

    //创建数据库的替身

    sqlite3_stmt *stmt = nil;

    //数据库查询语句

    NSString *sqlStr = [NSString  stringWithFormat:@"select nv_id,nv_author,nv_bookname,nv_score from Novel"];

    //[sqlStr UTF8String]转化为C语言类型   int nByte 最大长度限制

    //通过SQL语句进行查询,并且将查询结果赋值给替身

    int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);//检验SQL语句是否正确,如果正确就往替身赋值

    //如果得到的信息不是0 就进去查找

    if (result == SQLITE_OK) {

        while (sqlite3_step(stmt) == SQLITE_ROW) {

            //int iCol SQL语句对应列名的数据,,,,取第几列的东西

            int  nid = sqlite3_column_int(stmt, 0);

            const unsigned char *nauthor = sqlite3_column_text(stmt, 1);

            const unsigned char *bookName = sqlite3_column_text(stmt, 2);

            float nscore = sqlite3_column_double(stmt, 3);

            

            NSString *author = [NSString stringWithUTF8String:(const char *)nauthor];

            NSString *bookname = [NSString stringWithUTF8String:(const char *)bookName];

    

            DBModel *model = [DBModel moveKeepDataWithNid:nid author:author bookname:bookname score:nscore];

            

            [results addObject:model];

        }

    }

    sqlite3_finalize(stmt);

    return  results;

}

//通过id查询

+(DBModel *)selectWithId:(NSInteger)num{

    sqlite3 *db = [DB openDB];

    //创建替身

    sqlite3_stmt *stmt = nil;

    //数据库查询语句

    NSString *sqlStr = [NSString  stringWithFormat:@"select nv_id,nv_author,nv_bookname,nv_score from Novel where nv_id=%d",num];

    //通过SQL语句进行查询,并且将查询结果赋值给替身

    int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);//检验SQL语句是否正确,如果正确就往替身赋值

    //如果得到的信息不是0 就进去查找

    if (result == SQLITE_OK) {

        if (sqlite3_step(stmt) == SQLITE_ROW) {

            int  nid = sqlite3_column_int(stmt, 0);

            const unsigned char *nauthor = sqlite3_column_text(stmt, 1);

            const unsigned char *bookName = sqlite3_column_text(stmt, 2);

            float nscore = sqlite3_column_double(stmt, 3);

            

            NSString *author = [NSString stringWithUTF8String:(const char *)nauthor];

            NSString *bookname = [NSString stringWithUTF8String:(const char *)bookName];

            

            DBModel *model = [DBModel moveKeepDataWithNid:nid author:author bookname:bookname score:nscore];

            sqlite3_finalize(stmt);

            return model;

        }

    }

    return  nil;

}

//插入

+(void)insterWithModel:(DBModel*)model{

    //获取指针

    sqlite3 *db = [DB openDB];

    NSString *sqlStr = [NSString  stringWithFormat:@"insert into Novel (nv_id,nv_author,nv_bookname,nv_score)  values(%d,'%@','%@',%f)",model.nid,model.author,model.bookname,model.score];

    //通过SQL语句进行查询,并且将查询结果赋值给替身

    int result = sqlite3_exec(db, [sqlStr UTF8String], NULL, NULL, NULL);

    if (result == SQLITE_OK) {

        NSLog(@"添加成功");

    }

}

//根据id修改书名

+(void)updateNovelWith:(NSInteger)num name:(NSString *)name{

    sqlite3 *db = [DB openDB];

    NSString *sqlStr = [NSString  stringWithFormat:@"update Novel set nv_bookname ='%@' where nv_id = %d",name,num];

    //通过SQL语句进行查询,并且将查询结果赋值给替身

    int result = sqlite3_exec(db, [sqlStr UTF8String], NULL, NULL, NULL);

    if (result == SQLITE_OK) {

        NSLog(@"修改成功");

    }

}

+(void)delectNovelWith:(NSInteger)num{

    sqlite3 *db = [DB openDB];

    NSString *sqlStr = [NSString stringWithFormat:@"delete from Novel where nv_id = %d",num];

    int result = sqlite3_exec(db, [sqlStr UTF8String], NULL, NULL, NULL);

    if (result == SQLITE_OK) {

        NSLog(@"删除成功");

    }

}

@end

DBModel .m

@implementation DBModel

- (void)dealloc

{

    [_author release];

    [_bookname release];

    [super dealloc];

}

- (id)initWithNid:(NSInteger)nid author:(NSString *)author bookname:(NSString *)bookname score:(CGFloat)score

{

    self = [super init];

    if (self) {

        self.nid = nid;

        self.author = author;

        self.bookname = bookname;

        self.score = score;

    }

    return self;

}

+ (DBModel *)moveKeepDataWithNid:(NSInteger)nid author:(NSString *)author bookname:(NSString *)bookname score:(CGFloat)score{

    DBModel *data = [[DBModel alloc ]initWithNid:nid author:author bookname:bookname score:score];

    return data;

}@end