IOS----UI,SQL

最新推荐文章于 2024-08-14 23:11:00 发布

王云峰

最新推荐文章于 2024-08-14 23:11:00 发布

阅读量253

点赞数

分类专栏： IOS 文章标签： IOS UI 编程 SQL 数据库

本文链接：https://blog.csdn.net/wyf617467663/article/details/37606147

版权

IOS 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

创建一个类用于打开数据库，头文件如下：

#import <Foundation/Foundation.h>
#import <sqlite3.h>
@interface DB : NSObject 
+ (sqlite3 *)openDB;
@end

.m文件如下：

#import "DB.h"

//静态内存区域，存储数据库指针
static sqlite3 *dbPoint = nil;

@implementation DB

+ (sqlite3 *)openDB{
    //如果已经用过数据库，直接返回静态区的指针
    if(dbPoint){
        return dbPoint;
    }
    
    //获得Documents路径，用于存储bundle中的数据库文件
    NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    path = [NSString stringWithFormat:@"%@/DB",path];
    
    //判断文件是否存在，不存在则拷贝
    if(![[NSFileManager defaultManager] fileExistsAtPath:path]){
        //获得将要拷贝的文件的路径，bundle路径
        NSString *sourcePath = [[NSBundle mainBundle] pathForResource:@"DataBase" ofType:@"sqlite"];
        NSError *error = nil;
        [[NSFileManager defaultManager] copyItemAtPath:sourcePath toPath:path error:&error];
        if(error){
            NSLog(@"%@",error);
        }
    }
    //创建数据库的指针对象
    sqlite3_open([path UTF8String], &dbPoint);
    return dbPoint;
}

@end

创建类用于写增删改查方法，头文件如下：包括几个简单的增删改查方法:

#import <Foundation/Foundation.h>
#import "NovelModel.h"
@interface NovelDataBase : NSObject
+ (NSArray *)selectAllNovel;//查询所有信息
+ (NovelModel *)selectWithId:(NSInteger)num;//按ID查找信息
+ (void)insertWithModel:(NovelModel *)model;//添加信息
+ (void)updateBookname:(NSString *)bookname withId:(NSInteger)num;//根据ID修改书名
+ (void)deleteWithId:(NSInteger)num;//根据ID删除
@end

.m文件如下：

#import "NovelDataBase.h"
#import "DB.h"
@implementation NovelDataBase

+ (NSArray *)selectAllNovel{
    
    NSMutableArray *results = [NSMutableArray array];

    //获得数据库指针
    sqlite3 *db = [DB openDB];
    //创建一个数据库的替身
    sqlite3_stmt *stmt = nil;//用于存储数据库语句得到的结果
    
    //数据库查询语句
    NSString *sqlStr = [NSString stringWithFormat:@"select nv_id,bookname,author,type,nv_score from Novel"];
    
    //通过SQL语句进行查询并且将查询结果赋值给替身stmt(检验SQL语句是否正确，正确则向替身赋值，否则替身内无值)
    int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, nil);
    
    //SQLITE_OK==0,不是0进SDK看错误信息
    if(result == SQLITE_OK){
               while (sqlite3_step(stmt) == SQLITE_ROW/*下一行已准备好*/) {
            float nv_id = sqlite3_column_int(stmt, 0/*取sql语句对应列名的顺序*/);
            const unsigned char *nbookname = sqlite3_column_text(stmt, 1);
            const unsigned char *nauthor = sqlite3_column_text(stmt, 2);
            const unsigned char *ntype = sqlite3_column_text(stmt, 3);
            float nv_score = sqlite3_column_double(stmt, 4);
            
            NSString *author = [NSString stringWithUTF8String:(const char *)nauthor];
            NSString *bookname = [NSString stringWithUTF8String:(const char *)nbookname];
            NSString *type = [NSString stringWithUTF8String:(const char *)ntype];
            
            NovelModel *novel = [NovelModel NovelWithId:nv_id bookname:bookname author:author type:type score:nv_score];
            
            [results addObject:novel];
        }
    }
    //干掉替身
    sqlite3_finalize(stmt);
    return results;
}

+ (NovelModel *)selectWithId:(NSInteger)num{
    
    //获得数据库指针
    sqlite3 *db = [DB openDB];
    //创建一个数据库的替身
    sqlite3_stmt *stmt = nil;//用于存储数据库语句得到的结果
    
    //数据库查询语句
    NSString *sqlStr = [NSString stringWithFormat:@"select nv_id,bookname,author,type,nv_score from Novel where nv_id = %d",num];
    
    //通过SQL语句进行查询并且将查询结果赋值给替身stmt(检验SQL语句是否正确，正确则向替身赋值，否则替身内无值)
    int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, nil);
    
    //SQLITE_OK==0,不是0进SDK看错误信息
    if(result == SQLITE_OK){
        if(sqlite3_step(stmt) == SQLITE_ROW/*下一行已准备好*/) {
            int nv_id = sqlite3_column_int(stmt, 0/*取sql语句对应列名的顺序*/);
            const unsigned char *nbookname = sqlite3_column_text(stmt, 1);
            const unsigned char *nauthor = sqlite3_column_text(stmt, 2);
            const unsigned char *ntype = sqlite3_column_text(stmt, 3);
            float nv_score = sqlite3_column_double(stmt, 4);
            
            NSString *author = [NSString stringWithUTF8String:(const char *)nauthor];
            NSString *bookname = [NSString stringWithUTF8String:(const char *)nbookname];
            NSString *type = [NSString stringWithUTF8String:(const char *)ntype];
            
            NovelModel *novel = [NovelModel NovelWithId:nv_id bookname:bookname author:author type:type score:nv_score];
            //干掉替身
            sqlite3_finalize(stmt);
            return novel;
        }
    }
    return nil;
}

+ (void)insertWithModel:(NovelModel *)model{
    //获得数据库指针
    sqlite3 *db = [DB openDB];
    
    //数据库查询语句
    NSString *sqlStr = [NSString stringWithFormat:@"insert into Novel(nv_id,bookname,author,type,nv_score)values(%d,'%@','%@','%@',%f)",model.nv_id,model.bookname,model.author,model.type,model.nv_score];
    
    //通过SQL语句进行查询并且将查询结果赋值给替身stmt(检验SQL语句是否正确，正确则向替身赋值，否则替身内无值)
    int result = sqlite3_exec(db, [sqlStr UTF8String], NULL, NULL, NULL);
    
    //SQLITE_OK==0,不是0进SDK看错误信息
    if(result == SQLITE_OK){
        NSLog(@"添加成功");
    }
}


+ (void)updateBookname:(NSString *)bookname withId:(NSInteger)num{
    //获得数据库指针
    sqlite3 *db = [DB openDB];
    
    //数据库查询语句
    NSString *sqlStr = [NSString stringWithFormat:@"update Novel set bookname = '%@' where nv_id = %d",bookname,num];
    
    //通过SQL语句进行查询并且将查询结果赋值给替身stmt(检验SQL语句是否正确，正确则向替身赋值，否则替身内无值)
    int result = sqlite3_exec(db, [sqlStr UTF8String], NULL, NULL, NULL);
    
    //SQLITE_OK==0,不是0进SDK看错误信息
    if(result == SQLITE_OK){
        NSLog(@"更新成功");
    }
}

+ (void)deleteWithId:(NSInteger)num{
    //获得数据库指针
    sqlite3 *db = [DB openDB];
    
    //数据库查询语句
    NSString *sqlStr = [NSString stringWithFormat:@"delete from Novel where nv_id = %d",num];
    
    //通过SQL语句进行查询并且将查询结果赋值给替身stmt(检验SQL语句是否正确，正确则向替身赋值，否则替身内无值)
    int result = sqlite3_exec(db, [sqlStr UTF8String], NULL, NULL, NULL);
    
    //SQLITE_OK==0,不是0进SDK看错误信息
    if(result == SQLITE_OK){
        NSLog(@"删除成功");
    }

}

对于数据库的使用，现有一个小说类：

#import <Foundation/Foundation.h>

@interface NovelModel : NSObject

@property(nonatomic,assign) NSInteger nv_id;
@property(nonatomic,retain) NSString *bookname;
@property(nonatomic,retain) NSString *author;
@property(nonatomic,retain) NSString *type;
@property(nonatomic,assign) CGFloat nv_score;

- (id)initWithId:(NSInteger)nid bookname:(NSString *)bookname author:(NSString *)author type:(NSString *)type score:(CGFloat)score;
+ (id)NovelWithId:(NSInteger)nid bookname:(NSString *)bookname author:(NSString *)author type:(NSString *)type score:(CGFloat)score;


@end

#import "NovelModel.h"

@implementation NovelModel

- (void)dealloc{
    [_bookname release];_bookname = nil;
    [_author release];_author = nil;
    [_type release];_type = nil;
    [super dealloc];
}

- (id)initWithId:(NSInteger)nid bookname:(NSString *)bookname author:(NSString *)author type:(NSString *)type score:(CGFloat)score{
    self = [super init];
    if(self){
        self.nv_id = nid;
        self.bookname = bookname;
        self.author = author;
        self.type = type;
        self.nv_score = score;
    }
    return self;
}
+ (id)NovelWithId:(NSInteger)nid bookname:(NSString *)bookname author:(NSString *)author type:(NSString *)type score:(CGFloat)score{
    NovelModel *novel = [[NovelModel alloc]initWithId:nid bookname:bookname author:author type:type score:score];
    return [novel autorelease];
}

- (NSString *)description{
    return [NSString stringWithFormat:@"id==%d  bookname==%@  author==%@  type==%@  score==%f",self.nv_id,self.bookname,self.author,self.type,self.nv_score];
}

@end

函数的调用如下：

- (void)viewDidLoad
{
    [super viewDidLoad];
    self.navigationController.navigationBar.translucent = NO;
    
    NSArray *array = [NovelDataBase selectAllNovel];
    NSLog(@"%@",[array lastObject]);
    
    NovelModel *novel = [NovelDataBase selectWithId:1];
    NSLog(@"%@",novel);
    
    NovelModel *model = [NovelModel NovelWithId:2 bookname:@"金鳞岂是池中物" author:@"龟仙人" type:@"色情读物" score:100];
    [NovelDataBase insertWithModel:model];

    
    [NovelDataBase updateBookname:@"七龙珠" withId:2];
    
    novel = [NovelDataBase selectWithId:2];
    NSLog(@"%@",novel);
    
    
    [NovelDataBase deleteWithId:2];
    array = [NovelDataBase selectAllNovel];
    NSLog(@"%@",[array lastObject]);
    
}