ViewController.m#

//
//  ViewController.m
//  数据库补充
//
//  Created by sherry on 15/12/17.

//

#import "ViewController.h"
#import "DataBase.h"
#import "Student.h"
@interface ViewController ()

@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    
    DataBase *db = [DataBase shareDatabase];
    
    Student *stu1 = [[Student alloc] initWithName:@"Devil" andNumber:1];
    Student *stu2 = [[Student alloc] initWithName:@"Lee" andNumber:2];
    
    //收藏学生
    [db favoriteStudent:stu1];
    [db favoriteStudent:stu2];
    
 
    //根据学号获取学生对象
    Student *stu = [db selectStudentForNumber:2];
    NSLog(@"%@",stu);

}

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

@end

DataBase.h#

//
//  DataBase.h
//  SQLite数据库
//

//

#import <Foundation/Foundation.h>
@class Student;

@interface DataBase : NSObject

//创建单例
+ (instancetype)shareDatabase;


//打开数据库
- (void)openDB;

//收藏学生
- (void)favoriteStudent:(Student *)student;

//根据学号获取学生对象
- (Student *)selectStudentForNumber:(NSInteger)number;

@end

DataBase.m#

//
//  DataBase.m
//  UI19_SQLite数据库
//
//  Created by sherry on 15/12/17.

//

#import "DataBase.h"
#import <sqlite3.h>
#import "Student.h"


@implementation DataBase
static DataBase *dataBase = nil;
+ (instancetype)shareDatabase{
    //加锁
    @synchronized(self) {
        if (nil == dataBase) {
            dataBase = [[DataBase alloc] init];
            //打开数据库
            [dataBase openDB];
        }
    }
    return dataBase;
}

//创建数据库对象
static sqlite3 *db = nil;

//打开数据库
- (void)openDB{
    //如果数据库已经打开,则不需要执行后面的操作
    if (db != nil) {
        return;
    }
    //创建保存数据库的路径
    NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
    documentPath = [documentPath stringByAppendingString:@"/LOClass.sqlite"];
    
    //打开数据库(如果该数据库存在,则直接打开,否则,自动创建一个再打开)
    int result = sqlite3_open([documentPath UTF8String], &db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库成功打开");
        //建表
        //准备sql语句
        NSString *sql = @"CREATE TABLE StudentFavorite (number INTEGER PRIMARY KEY NOT NULL UNIQUE, name TEXT NOT NULL, data BLOB NOT NULL);";
        //执行sql语句
        sqlite3_exec(db, [sql UTF8String], NULL, NULL, NULL);
    }else{
        NSLog(@"%d",result);
    }
}

//关闭数据库
- (void)closeDB{
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库关闭成功");
        //关闭数据库的时候,将db置为空,是因为打开数据库的时候,我们需要使用nil做判断
        db = nil;
    }else{
        NSLog(@"数据库关闭失败:%d",result);
    }
}

//收藏学生
- (void)favoriteStudent:(Student *)student
{
    
    [self openDB];
    sqlite3_stmt * stmt = nil;
    NSString * sql = @"insert into StudentFavorite (number,name,data) values (?,?,?)";
    int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        sqlite3_bind_int(stmt, 1, (int)student.number);
        sqlite3_bind_text(stmt, 2, [student.name UTF8String], -1, NULL);
        
        //归档student得到data，将data存储到数据库中
        NSMutableData * data = [NSMutableData dataWithCapacity:40];
        NSKeyedArchiver * archiver = [[NSKeyedArchiver alloc] initForWritingWithMutableData:data];
        [archiver encodeObject:student forKey:[NSString stringWithFormat:@"%ld",student.number]];
        [archiver finishEncoding];
        sqlite3_bind_blob(stmt, 3, [data bytes], (int)[data length], NULL);
        sqlite3_step(stmt);
    }
    sqlite3_finalize(stmt);
}

//根据学号获取学生对象
- (Student *)selectStudentForNumber:(NSInteger)number{
    [self openDB];
    
    sqlite3_stmt * stmt = nil;
    
    NSString * sql = @"select data from StudentFavorite where number = ?";
    
    int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, NULL);
    
    if (result == SQLITE_OK) {
        
        sqlite3_bind_int(stmt, 1, (int)number);
        if (sqlite3_step(stmt) == SQLITE_ROW) {
            
            //取data：内容、大小
            //内容
            //sqlite3_column_blob(stmt, 0);
            //大小
            //sqlite3_column_bytes(stmt, 0);
            
            NSData * data = [NSData dataWithBytes:sqlite3_column_blob(stmt, 0) length:sqlite3_column_bytes(stmt, 0)];
            //反归档
            NSKeyedUnarchiver * unarchiver = [[NSKeyedUnarchiver alloc] initForReadingWithData:data];
            Student * stu = [unarchiver decodeObjectForKey:[NSString stringWithFormat:@"%ld",number]];
            [unarchiver finishDecoding];
            
            sqlite3_finalize(stmt);
            return stu;
        }
    }
    return nil;
}

@end

Student.h#

//
//  Student.h
// SQLite数据库
//
//

#import <Foundation/Foundation.h>

@interface Student : NSObject<NSCoding>

@property (nonatomic, strong)NSString *name;
@property (nonatomic, assign)NSInteger number;

-(instancetype)initWithName:(NSString *)name andNumber:(NSInteger)number;


@end

Student.m#

//
//  Student.m
//  SQLite数据库
//
//  Created by sherry on 15/12/17.
//  Copyright © 2015年 蓝鸥科技. All rights reserved.
//

#import "Student.h"

#define kNumber @"number"
#define kName @"name"


@implementation Student


-(void)encodeWithCoder:(NSCoder *)aCoder{
    [aCoder encodeObject:_name forKey:kName];
    [aCoder encodeInteger:_number forKey:kNumber];
}

-(instancetype)initWithCoder:(NSCoder *)aDecoder{
    if (self = [super init]) {
        _number = [aDecoder decodeIntegerForKey:kNumber];
        _name = [aDecoder decodeObjectForKey:kName];
    }
    return self;
}

-(void)setValue:(id)value forUndefinedKey:(NSString *)key{

}

- (NSString *)description{
    return [NSString stringWithFormat:@"%@", _name];
}

//自定义初始化方法
-(instancetype)initWithName:(NSString *)name andNumber:(NSInteger)number{
    if (self = [super init]) {
        _number = number;
        _name = name;
    }
    return self;
}

@end