IOS编程 之 数据库

数据库：

选择一款数据库软件  创建一个数据库 创建表 创建列 设置主键  添加任意一条内容

本章中用的是 火狐浏览器工具组件SQLite Manager  数据结构如图所示：

创建一对 DB 类 

+ (sqlite3 *)openDB{
    
    if (dbPoint ) {//如果数据指针已经获得，就直接返回数据指针
        return dbPoint;
    }
    
    
    //判断目录中是否存在
    NSArray *array = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *path = [array lastObject];
    path = [NSString stringWithFormat:@"%@/db",path];
    NSLog(@"%@",path);
    if ( ![[NSFileManager defaultManager] fileExistsAtPath:path]) {  //判断文件是否存在，使用文件管理类
        //获得将要拷贝的文件的路径，bundle路径
        NSString *sourcePath = [[NSBundle mainBundle] pathForResource:@"DataBase" ofType:@"sqlite"];
        NSLog(@"source == %@",sourcePath);
        
        NSError *error = nil;
        //没有文件，就拷贝
        [[NSFileManager defaultManager] copyItemAtPath:sourcePath toPath:path error:&error];
        if (error) {
            NSLog(@"error===%@",error);
        }
    }
    
    //数据库代码，创建数据库的指针对象
    sqlite3_open([path UTF8String], &dbPoint);
    return dbPoint;
    
}

记得在.h文件中声明这个加号方法

创建一对NovelDataBase 类 名称随意自拟

#import <Foundation/Foundation.h>
@class Model;
@interface NovelDataBase : NSObject
+ (NSArray *)selectAllNovel;
+ (Model *)selectWithId:(NSInteger)num;
+ (void)insertWithModel : (Model *)model;
+ (void)updateWithId:(NSInteger)nid bookname:(NSString *)bookname;
+ (void)deleteWithId:(NSInteger)nid;
@end

.m文件中代码：

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

+ (NSArray *)selectAllNovel{
    NSMutableArray *array = [NSMutableArray array];
    
    //获得数据库指针
    sqlite3 *db = [DB openDB];
    //创建数据库替身
    sqlite3_stmt *stmt = nil;
    //替身里放数据库语句所得到的结果
    //数据库查询语句
    NSString *sql = [NSString stringWithFormat:@"select nv_id,nv_author,nv_bookname,nv_score from Novel"];
    
    //通过sql语句进行查询 并且将查询结果赋值给替身
    int result = sqlite3_prepare(db, [sql UTF8String], -1, &stmt, NULL);
    //检验你得sql语句是否正确  如果正确就会再替身里面赋值  如果不正确 替身中就得不到值
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) ==SQLITE_ROW) {
            int nid = sqlite3_column_int(stmt, 0);//第二个参数代表的时取得是前面sql语句中对应的列名的顺序  不是表顺序 起始位置从0开始
            const unsigned char *author =sqlite3_column_text(stmt, 1);
            //无符号char类型
            const unsigned char *bookname = sqlite3_column_text(stmt, 2);
            float score = sqlite3_column_double(stmt, 3);
            //放在字典里或者放在model里  不能放在数组里  数组里是由顺序的
            
            //转换  后面是强转类型
            NSString *nauthor = [NSString stringWithUTF8String:(const char *)author];
            NSString *nbookname = [NSString stringWithUTF8String:(const char *)bookname];
            
            Model *model = [Model ModelWithNid:nid author:nauthor bookname:nbookname score:score];
            [array addObject:model];
            
        }
    }
    
    sqlite3_finalize(stmt);
    return array;
}
+ (Model *)selectWithId:(NSInteger)num{
    //获得数据库指针
    sqlite3 * db = [DB openDB];
    //创建替身
    sqlite3_stmt * stmt = nil;
    //数据库查询语句
    NSString *sql = [NSString stringWithFormat:@"select nv_id,nv_author,nv_bookname,nv_score from Novel where nv_id = %d",num];
    //通过sql语句进行查询
    int result = sqlite3_prepare(db,[sql UTF8String], -1, &stmt, NULL);
    Model *model = [[Model alloc]init];
    if (result == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_ROW) {
            //取内容
            int nid = sqlite3_column_int(stmt, 0);
            const unsigned char *author = sqlite3_column_text(stmt, 1);
            const unsigned char *bookname = sqlite3_column_text(stmt, 2);
            float score =sqlite3_column_double(stmt, 3);
            //转换  后面是强转类型
            NSString *nauthor = [NSString stringWithUTF8String:(const char *)author];
            NSString *nbookname = [NSString stringWithUTF8String:(const char *)bookname];

            model = [Model ModelWithNid:nid author:nauthor bookname:nbookname score:score];
            sqlite3_finalize(stmt);
        }
       
        
        
    }
    return model;
    
}

+ (void)insertWithModel : (Model *)model{
    //获取数据库指针
    sqlite3 * db =[DB openDB];
    //数据库插入语句
    NSString *sql = [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,[sql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"添加成功");
    }
    
}
//根据id修改书名
+ (void)updateWithId:(NSInteger)nid bookname:(NSString *)bookname{
    //
    sqlite3 *db = [DB openDB];
    
    
    NSString *sql = [NSString stringWithFormat:@"update Novel set nv_bookname='%@' where nv_id=%d",bookname,nid];
    
    //通过sql语句进行更新
    int result = sqlite3_exec(db,[sql UTF8String], NULL, NULL, NULL);
  
    if (result == SQLITE_OK) {
        NSLog(@"更新成功");
    }
   
}
//删除
+ (void)deleteWithId:(NSInteger)nid{
    sqlite3 *db = [DB openDB];
    
    NSString *sql = [NSString stringWithFormat:@"delete from Novel where nv_id=%d",nid];
    int result =sqlite3_exec(db,[sql UTF8String ] ,NULL,NULL , NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }
    
}



@end

main.m中的代码：

@implementation MainViewController
-(void)dealloc{
    [super dealloc];
}
- (id)initWithNibName:(NSString *)nibNameOrNil bundle:(NSBundle *)nibBundleOrNil
{
    self = [super initWithNibName:nibNameOrNil bundle:nibBundleOrNil];
    if (self) {
        // Custom initialization
    }
    return self;
}

- (void)viewDidLoad
{
    [super viewDidLoad];
    // Do any additional setup after loading the view.
    self.navigationController.navigationBar.translucent =NO;
    
//    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
//    NSString *string = [paths firstObject];
//    string = [NSString stringWithFormat:@"%@/db",paths];
    
    [DB openDB];
    
    NSArray *array = [NovelDataBase selectAllNovel];
    NSLog(@"array ============%@",array); 
    
    
    Model * m = [NovelDataBase selectWithId:1];
    NSLog(@"%@",m);
    
    
    //插入数据
    Model *insertModel = [Model ModelWithNid:4 author:@"A" bookname:@"B" score:90];
    [NovelDataBase insertWithModel:insertModel];
    
    //更新
    //[NovelDataBase updateWithId:5 bookname:@"lala"];
    //删除
    [NovelDataBase deleteWithId:4];
    
}

- (void)didReceiveMemoryWarning
{
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}

/*
#pragma mark - Navigation

// In a storyboard-based application, you will often want to do a little preparation before navigation
- (void)prepareForSegue:(UIStoryboardSegue *)segue sender:(id)sender
{
    // Get the new view controller using [segue destinationViewController].
    // Pass the selected object to the new view controller.
}
*/

@end