ViewController.m#
//
// ViewController.m
// 數(shù)據(jù)庫(kù)補(bǔ)充
//
// 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];
//收藏學(xué)生
[db favoriteStudent:stu1];
[db favoriteStudent:stu2];
//根據(jù)學(xué)號(hào)獲取學(xué)生對(duì)象
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數(shù)據(jù)庫(kù)
//
//
#import <Foundation/Foundation.h>
@class Student;
@interface DataBase : NSObject
//創(chuàng)建單例
+ (instancetype)shareDatabase;
//打開(kāi)數(shù)據(jù)庫(kù)
- (void)openDB;
//收藏學(xué)生
- (void)favoriteStudent:(Student *)student;
//根據(jù)學(xué)號(hào)獲取學(xué)生對(duì)象
- (Student *)selectStudentForNumber:(NSInteger)number;
@end
DataBase.m#
//
// DataBase.m
// UI19_SQLite數(shù)據(jù)庫(kù)
//
// 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];
//打開(kāi)數(shù)據(jù)庫(kù)
[dataBase openDB];
}
}
return dataBase;
}
//創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象
static sqlite3 *db = nil;
//打開(kāi)數(shù)據(jù)庫(kù)
- (void)openDB{
//如果數(shù)據(jù)庫(kù)已經(jīng)打開(kāi),則不需要執(zhí)行后面的操作
if (db != nil) {
return;
}
//創(chuàng)建保存數(shù)據(jù)庫(kù)的路徑
NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
documentPath = [documentPath stringByAppendingString:@"/LOClass.sqlite"];
//打開(kāi)數(shù)據(jù)庫(kù)(如果該數(shù)據(jù)庫(kù)存在,則直接打開(kāi),否則,自動(dòng)創(chuàng)建一個(gè)再打開(kāi))
int result = sqlite3_open([documentPath UTF8String], &db);
if (result == SQLITE_OK) {
NSLog(@"數(shù)據(jù)庫(kù)成功打開(kāi)");
//建表
//準(zhǔn)備sql語(yǔ)句
NSString *sql = @"CREATE TABLE StudentFavorite (number INTEGER PRIMARY KEY NOT NULL UNIQUE, name TEXT NOT NULL, data BLOB NOT NULL);";
//執(zhí)行sql語(yǔ)句
sqlite3_exec(db, [sql UTF8String], NULL, NULL, NULL);
}else{
NSLog(@"%d",result);
}
}
//關(guān)閉數(shù)據(jù)庫(kù)
- (void)closeDB{
int result = sqlite3_close(db);
if (result == SQLITE_OK) {
NSLog(@"數(shù)據(jù)庫(kù)關(guān)閉成功");
//關(guān)閉數(shù)據(jù)庫(kù)的時(shí)候,將db置為空,是因?yàn)榇蜷_(kāi)數(shù)據(jù)庫(kù)的時(shí)候,我們需要使用nil做判斷
db = nil;
}else{
NSLog(@"數(shù)據(jù)庫(kù)關(guān)閉失敗:%d",result);
}
}
//收藏學(xué)生
- (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存儲(chǔ)到數(shù)據(jù)庫(kù)中
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);
}
//根據(jù)學(xué)號(hào)獲取學(xué)生對(duì)象
- (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：內(nèi)容、大小
//內(nèi)容
//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數(shù)據(jù)庫(kù)
//
//
#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數(shù)據(jù)庫(kù)
//
// Created by sherry on 15/12/17.
// Copyright ? 2015年 藍(lán)鷗科技. 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
