这里将数据库的封装为一个工具类.将数据库设计为一个单例,封装一些打开,创建表格,增,删,改,查的方法.在收藏功能中主要用到,查询,增添,和删除的方法.
查询,返回的数据,是你存入的数据内容.可以返回数据库的全部内容,也可以根据某个属性,返回符合该属性的内容.
代码区别:
根据title来查询数据库中内容,返回与title一样的数据.
主要思路:
1.查询数据库的内容,遍历返回的数组来判断数据库中是否有想要收藏的数据(model),以此来判断收藏提示界面的状态.
2.收藏界面状态的更换,来判断什么时候往数据库中插入某条数据,什么时候删除数据库中的某条数据.
3.查询数据库中的内容,,将数据库中的内容在另外一个界面将数据库中的model展现出来.
将要收藏的数据(model)插入导数据库当中
实现该功能中遇到的问题:
1.什么时候插入数据,什么时候删除数据,不能写在 遍历查询返回的数组当中 ,会导致多条数据同时插入,同时删除.
2.判断起初的收藏状态,必须在该界面初始化之后,界面出现之前,多注意程序走方法的先后顺序.
3.数据库建立的 表格,若是同名,后建的表格不会替换之前的表格,若是表格添加了新的一列,属性,必须在文件夹中,将原始建立的表格删除.
4.数据库存数据的小技巧,先考虑自己想要存的内容,根据想要存的内容建立model类型,
在收藏界面,用该model的属性接收来自不同界面来的model类型的属性(一般为字符串属性,分开接收,这样避免直接接收整个model时需要判断不同的model类型)
避免界面一样,model的属性不同 ,导致数据中存储一些(null)的数据.
#import .h
@class ModelForListen;
@interface DataBaseHandler : NSObject
+ (instancetype)shareDataBaseHandler;
- (void)open;
- (void)createTable;
- (void)insertModel:(ModelForListen *) model;
- (void)updataModel:(ModelForListen *)model ForNumber:(NSInteger)number;
- (void)deleteWithTitle:(NSString *)title;
- (void)deleteWithModel:(ModelForListen *)model;
- (NSArray *)selectForTitle:(NSString *)title;
- (NSArray *)selectForModel;
- (void)dropTable;
- (void)closeDB;
.m
//// DataBaseHandler.m// UI21_SQLite//// Created by dllo on 16/1/11.// Copyright © 2016年 dllo. All rights reserved.//#import "DataBaseHandler.h"#import#import "ModelForListen.h"
@implementation DataBaseHandler
#pragma mark - 知识点 1 单例
+ (instancetype)shareDataBaseHandler {
//在静态区,只初始化一次 创建数据库对象
static DataBaseHandler *dataBase = nil;
if (dataBase == nil) {
dataBase = [[DataBaseHandler alloc] init];
}
return dataBase;
}
/** 定义一个 sqlite 结构体 (对象) */
sqlite3 *db;
#pragma mark - 知识点 2 :打开数据库
- (void)open{
// API: sqlite3_open
if (db != nil) {
NSLog(@"数据库已经开启");
return;
}
NSString *file = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"model.sqlite"];
NSLog(@"%@",file);
/**
* @praram filename 数据库文件的路径(注意!类型为 c 字符串)
*
* @param ppDb 数据库对象
*
**/
int result = sqlite3_open(file.UTF8String, &db);
if (result == SQLITE_OK) {
NSLog(@"数据库打开成功");
}else{
NSLog(@"数据打开失败, code = %d", result);
}
}
#pragma mark - 知识点 3 创建表格
- (void)createTable{
// API: sqlite3_exec
// 创建 SQL 语句.
//////////////
NSString *createTableSQL = @"CREATE TABLE IF NOT EXISTS lanou1130(number INTEGER PRIMARY KEY AUTOINCREMENT, title text, lastUptrackTitle text, coverSmall text, albumId text, nickname text, coverLarge text, trackTitle text)";
int result = sqlite3_exec(db, createTableSQL.UTF8String, NULL, NULL, nil);
if (result == SQLITE_OK) {
NSLog(@"创建表格成功");
}else{
NSLog(@"创建表格失败. code :%d", result);
}
}
#pragma mark - 知识点 4 插入数据
- (void)insertModel:(ModelForListen *)model {
// API :sqlite3_exec
/////////////
NSString *insertSQL = [NSString stringWithFormat:@"INSERT into lanou1130(title, lastUptrackTitle, coverSmall, albumId, nickname, coverLarge, trackTitle) VALUES('%@', '%@', '%@', '%@', '%@', '%@', '%@')", model.title, model.lastUptrackTitle, model.coverSmall,model.albumId,model.nickname,model.coverLarge,model.trackTitle];
int result = sqlite3_exec(db, insertSQL.UTF8String, nil, nil, nil);
if (result == SQLITE_OK) {
NSLog(@"添加数据完成");
}else{
NSLog(@"添加数据失败.code:%d", result);
}
}
#pragma mark - 知识点 5 更新数据
- (void)updataModel:(ModelForListen *)model ForNumber:(NSInteger)number{
//API :sqlite2_exec
NSString *updataSQL = [NSString stringWithFormat:@"UPDATE lanou1130 SET title = '%@', lastUptrackTitle = '%@', coverSmall = '%@' albumId = '%@' nickname = '%@' coverLarge = '%@' trackTitle = '%@' WHERE number = '%ld'",model.title, model.lastUptrackTitle, model.coverSmall, model.albumId,model.nickname,model.coverLarge,model.trackTitle, number];
int result = sqlite3_exec(db, updataSQL.UTF8String, nil, nil, nil);
if (result == SQLITE_OK) {
NSLog(@"更新成功");
}else {
NSLog(@"更新失败, code:%d", result);
}
}
#pragma mark -- 知识点 6 :删除数据
- (void)deleteWithTitle:(NSString *)title {
//API sqlite3_exec
NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM lanou1130 WHERE title = '%@'",title];
int result = sqlite3_exec(db, deleteSQL.UTF8String, nil, nil, nil);
if (result == SQLITE_OK) {
NSLog(@"删除成功");
}else{
NSLog(@"删除失败 code:%d", result);
}
}
- (void)deleteWithModel:(ModelForListen *)model {
NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM lanou1130 WHERE title = '%@'",model.title];
int result = sqlite3_exec(db, deleteSQL.UTF8String, nil, nil, nil);
if (result == SQLITE_OK) {
NSLog(@"删除成功");
}else{
NSLog(@"删除失败 code:%d", result);
}
}
#pragma mark --知识点 7 查询数据
- (NSArray *)selectForTitle:(NSString *)title{
/** 存放符合条件的查询数据 (元素是 model 对象)*/
NSMutableArray *arr = [NSMutableArray array];
// sql 语句
NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM lanou1130 WHERE title = '%@'", title];
//API : stmt 结构体, sqlite3_prepareV2,sqlite3_step, sqlite_column
// 声明一个结构体 stmt 对象 (结构体)
sqlite3_stmt *stmt = nil;
//(准备好的语句对象)
int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
if (result == SQLITE_OK) {
// 逐行查询 每行都执行查询语句
while (sqlite3_step(stmt) == SQLITE_ROW) {
// 如果查询条件匹配, 通过 sqlite3_column 函数蔟 取出值;
const unsigned char *title = sqlite3_column_text(stmt, 1);// 取出哪一列的数据值
const unsigned char *lastUptrackTitle = sqlite3_column_text(stmt, 2);
const unsigned char *coverSmall = sqlite3_column_text(stmt, 3);
const unsigned char *albumId = sqlite3_column_text(stmt, 4);
const unsigned char *nickname = sqlite3_column_text(stmt, 5);
const unsigned char *coverLarge = sqlite3_column_text(stmt, 6);
const unsigned char *trackTitle = sqlite3_column_text(stmt, 7);
// int age = sqlite3_column_int(stmt, 3);
// 创建 model 对象, 赋值之后放入数组中
ModelForListen *model = [[ModelForListen alloc] init];
model.title = [NSString stringWithUTF8String:(const char *) title];
model.lastUptrackTitle = [NSString stringWithUTF8String:(const char*) lastUptrackTitle];
model.coverSmall = [NSString stringWithUTF8String:(const char*) coverSmall];
model.albumId = [NSString stringWithUTF8String:(const char*)albumId];
model.nickname = [NSString stringWithUTF8String:(const char*)nickname];
model.coverLarge = [NSString stringWithUTF8String:(const char*)coverLarge];
model.trackTitle = [NSString stringWithUTF8String:(const char*)trackTitle];
[arr addObject:model];
}
NSLog(@"准备就绪,查询中...");
// 销毁对象
sqlite3_finalize(stmt);
}else {
NSLog(@"不能正常查询, code:%d", result);
// 销毁对象
sqlite3_finalize(stmt);
}
return arr;
}
- (NSArray *)selectForModel {
/** 存放符合条件的查询数据 (元素是 model 对象)*/
NSMutableArray *arr = [NSMutableArray array];
// sql 语句
NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM lanou1130"];
//API : stmt 结构体, sqlite3_prepareV2,sqlite3_step, sqlite_column
// 声明一个结构体 stmt 对象 (结构体)
sqlite3_stmt *stmt = nil;
//(准备好的语句对象) -1 代表查询全部对象.
int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
if (result == SQLITE_OK) {
// 逐行查询 每行都执行查询语句
while (sqlite3_step(stmt) == SQLITE_ROW) {
// 如果查询条件匹配, 通过 sqlite3_column 函数蔟 取出值;
const unsigned char *title = sqlite3_column_text(stmt, 1);// 取出哪一列的数据值
const unsigned char *lastUptrackTitle = sqlite3_column_text(stmt, 2);
const unsigned char *coverSmall = sqlite3_column_text(stmt, 3);
const unsigned char *albumId = sqlite3_column_text(stmt, 4);
const unsigned char *nickname = sqlite3_column_text(stmt, 5);
const unsigned char *coverLarge = sqlite3_column_text(stmt, 6);
const unsigned char *trackTitle = sqlite3_column_text(stmt, 7);
// int age = sqlite3_column_int(stmt, 3);
// 创建 model 对象, 赋值之后放入数组中
ModelForListen *model = [[ModelForListen alloc] init];
model.title = [NSString stringWithUTF8String:(const char *) title];
model.lastUptrackTitle = [NSString stringWithUTF8String:(const char*) lastUptrackTitle];
model.coverSmall = [NSString stringWithUTF8String:(const char*) coverSmall];
model.albumId = [NSString stringWithUTF8String:(const char*)albumId];
model.nickname = [NSString stringWithUTF8String:(const char*)nickname];
model.coverLarge = [NSString stringWithUTF8String:(const char*)coverLarge];
model.trackTitle = [NSString stringWithUTF8String:(const char*)trackTitle];
[arr addObject:model];
}
NSLog(@"准备就绪,查询中...");
// 销毁对象
sqlite3_finalize(stmt);
}else {
NSLog(@"不能正常查询, code:%d", result);
// 销毁对象
sqlite3_finalize(stmt);
}
return arr;
}
#pragma mark --知识点 8 删除表单
-(void)dropTable{
NSString *dropSQL = @"DROP TABLE lanou1130";
int result = sqlite3_exec(db, dropSQL.UTF8String, nil, nil, nil);
if (result == SQLITE_OK) {
NSLog(@"删除表格成功!!!!");
}else{
NSLog(@"删除失败");
}
}
#pragma mark --关掉数据库
- (void)closeDB {
// API:sqlite3_close
int result = sqlite3_close(db);
if (result == SQLITE_OK) {
NSLog(@"数据库关闭成功");
db = nil;
}else{
NSLog(@"数据库关闭失败. code :%d", result);
}
}
@end
网友评论