第一步：建立模型.h文件如下：
/**
* qa
*/
@property (copy,nonatomic) NSString *qa;
/**
* qb
*/
@property (copy,nonatomic) NSString *qb;
/**
* qc
*/
@property (copy,nonatomic) NSString *qc;
/**
* qd
*/
@property (copy,nonatomic) NSString *qd;
/**
* 问题
*/
@property (copy,nonatomic) NSString *question_name;
/**
* 答案
*/
@property (copy,nonatomic) NSString *qkey;
/**
* question_id
*/
@property (copy,nonatomic) NSString *question_id;
-(instancetype)initWithQName:(NSString *)q_name
Qa:(NSString *)q_a
Qb:(NSString *)q_b
Qc:(NSString *)q_c
Qd:(NSString *)q_d
Qkey:(NSString *)q_key
Qid:(NSString *)q_id;
下面是模型的.m文件：
-(instancetype)initWithQName:(NSString *)q_name
Qa:(NSString *)q_a
Qb:(NSString *)q_b
Qc:(NSString *)q_c
Qd:(NSString *)q_d
Qkey:(NSString *)q_key
Qid:(NSString *)q_id
{
if (self = [super init]) {
_question_name = q_name;
_qa = q_a;
_qb = q_b;
_qc = q_c;
_qd = q_d;
_qkey = q_key;
_question_id = q_id;
}
return self;
}
第二步：创建一个DBManager的单例，并且暴露相关可操作的方法，增删改查
包含相关模型文件
@class QDXQuestionModel;
创建一个单例
+(instancetype)shareDataBase;
单例的实现
+(instancetype)shareDataBase
{
//使用GCD方法 使单例方法只创建一次
static dispatch_once_t onceToken;
dispatch_once(&onceToken, ^{
//初始化单例对象
dataBase = [[QDXOfflineDB alloc]init];
//打开数据库
[dataBase openOfflineDB];
});
return dataBase;
}
创建数据库对象
static sqlite3 *db = nil;
打开数据库建表
-(void)openOfflineDB;
-(void)openOfflineDB
{
//如果数据库已经打开,则不需要执行后面的操作 直接return
if (db != nil) {
return;
}
//存放数据库的路径
NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
path = [path stringByAppendingPathComponent:@"QDXOffine.sqlite"];
NSLog(@"%@",path);
//打开数据库(如果该数据库存在则直接打开,否则自动创建一个再打开)
int result = sqlite3_open([path UTF8String], &db);
if (result == SQLITE_OK) {
// NSLog(@"数据库打开成功");
//建表
const char *sql1 = "CREATE TABLE IF NOT EXISTS qdx_question (q_id integer PRIMARY KEY AUTOINCREMENT,question_name text NOT NULL,qa text,qb text,qc text,qd text,qkey text NOT NULL,question_id NOT NULL)";
char *errmsg = NULL;
sqlite3_exec(db, sql1, NULL, NULL, &errmsg);
}else
{
//如果失败,打印失败原因
// NSLog(@"%d",result);
}
}
关闭数据库删表
-(void)closeOfflineDB;
//关闭数据库
-(void)closeOfflineDB
{
int result = sqlite3_close(db);
if (result == SQLITE_OK) {
// NSLog(@"数据库关闭成功");
//当关闭数据库的时候将db置为空,是因为打开数据库的时候,我们需要使用nil作判断
db = nil;
const char *sql1 = "DROP TABLE qdx_point_question";
char *errmsg = NULL;
sqlite3_exec(db, sql1, NULL, NULL, &errmsg);
}else
{
//如果失败,打印失败原因
// NSLog(@"%d",result);
}
}
查询 所有问题
-(NSArray *)selectAllQuestion;
//查询所有 直接返回
-(NSArray *)selectAllQuestion
{
sqlite3_stmt *stmt = nil;
NSString *sql = @"SELECT *FROM qdx_question";
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, nil);
if (result == SQLITE_OK) {
NSMutableArray *array = [NSMutableArray array];
while (sqlite3_step(stmt) == SQLITE_ROW) {
NSString *question_name = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 1)];
NSString *qa = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 2)];
NSString *qb = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 3)];
NSString *qc = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 4)];
NSString *qd = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 5)];
NSString *qkey = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 6)];
NSString *question_id = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 7)];
QDXQuestionModel *qusetion = [[QDXQuestionModel alloc] initWithQName:question_name Qa:qa Qb:qb Qc:qc Qd:qd Qkey:qkey Qid:question_id];
[array addObject:qusetion];
}
sqlite3_finalize(stmt);
return array;
}else
{
// NSLog(@"查询失败");
sqlite3_finalize(stmt);
return nil;
}
}
上面是对qdx_question表创建，删除，和查询所有
以下是对表的其他操作：
修改当前线路状态
-(void)modifyMyline:(QDXGameModel *)myline;
-(void)modifyMyline:(QDXGameModel *)myline
{
NSString *sql = [NSString stringWithFormat:@"UPDATE qdx_myline SET mstatus_id = '%@',sdate = '%@',score = '%@',pointmap_id = '%@' WHERE myline_id = '%@'",myline.mstatus_id,myline.sdate,myline.score,myline.pointmap_id,myline.myline_id];
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, nil);
if (result == SQLITE_OK) {
sqlite3_bind_text(stmt, 3, [myline.mstatus_id UTF8String], -1, nil);
sqlite3_bind_text(stmt, 4, [myline.sdate UTF8String], -1, nil);
sqlite3_bind_text(stmt, 5, [myline.score UTF8String], -1, nil);
sqlite3_bind_text(stmt, 7, [myline.pointmap_id UTF8String], -1, nil);
sqlite3_bind_text(stmt, 2, [myline.myline_id UTF8String], -1, nil);
sqlite3_step(stmt);
}else
{
// NSLog(@"修改失败");
}
sqlite3_finalize(stmt);
}
添加问题表
-(void)insertQuestion:(QDXQuestionModel *)questions;
-(void)insertQuestion:(QDXQuestionModel *)questions
{
sqlite3_stmt *stmt = nil;
NSString *sql = @"INSERT INTO qdx_question (q_id,question_name,qa,qb,qc,qd,qkey,question_id)VALUES(?,?,?,?,?,?,?,?)";
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt,nil);
if (result == SQLITE_OK) {
sqlite3_bind_text(stmt, 2, [questions.question_name UTF8String], -1 , nil);
sqlite3_bind_text(stmt, 3, [questions.qa UTF8String], -1, nil);
sqlite3_bind_text(stmt, 4, [questions.qb UTF8String], -1, nil);
sqlite3_bind_text(stmt, 5, [questions.qc UTF8String], -1, nil);
sqlite3_bind_text(stmt, 6, [questions.qd UTF8String], -1, nil);
sqlite3_bind_text(stmt, 7, [questions.qkey UTF8String], -1, nil);
sqlite3_bind_text(stmt, 8, [questions.question_id UTF8String], -1, nil);
sqlite3_step(stmt);
}else
{
// NSLog(@"存入失败%d",result);
}
sqlite3_finalize(stmt);
}
条件查询：通过point_id查询对应点标
-(QDXPointModel *)selectPointWithPid:(NSString *)point_id;
-(QDXPointModel *)selectPointWithPid:(NSString *)point_id
{
NSString *sql = [NSString stringWithFormat:@"SELECT *FROM qdx_point WHERE point_id = '%@'",point_id];
sqlite3_stmt *stmt = nil;
int result = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, nil);
if (result == SQLITE_OK) {
sqlite3_bind_text(stmt, 1, [point_id UTF8String], -1, nil);
QDXPointModel *point = [QDXPointModel new];
while (sqlite3_step(stmt) == SQLITE_ROW) {
NSString *point_id = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 1)];
NSString *area_id = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 2)];
NSString *LAT = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 3)];
NSString *LON = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 4)];
NSString *label = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 5)];
NSString *point_name = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 6)];
NSString *rssi = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(stmt, 7)];
point = [[QDXPointModel alloc] initWithP_id:point_id A_id:area_id LAT:LAT LON:LON Label:label P_name:point_name Rssi:rssi];
}
sqlite3_finalize(stmt);
return point;
}else
{
sqlite3_finalize(stmt);
return nil;
}
}
删除重复记录
-(void)deleteTheSame;
-(void)deleteTheSame
{
NSString *sql1 = [NSString stringWithFormat:@"delete from qdx_point_question where p_q_id not in (select min(p_q_id) as p_q_id from qdx_point_question group by question_id,pointmap_id)"];
NSString *sql2 = [NSString stringWithFormat:@"delete from qdx_question where question_id in(select question_id from qdx_question group by question_id having count(question_id)>1) and q_id not in (select min(q_id) from qdx_question group by question_id having count(question_id)>1)"];
NSString *sql3 = [NSString stringWithFormat:@"delete from qdx_history where h_id not in (select min(h_id) as h_id from qdx_history group by point_id,myline_id)"];
NSString *sql4 = [NSString stringWithFormat:@"delete from qdx_line_point where l_p_id not in (select min(l_p_id) as l_p_id from qdx_line_point group by line_id,pointmap_id)"];
NSString *sql5 = [NSString stringWithFormat:@"delete from qdx_point where p_id not in (select min(p_id) as p_id from qdx_point group by point_id)"];
NSString *sql6 = [NSString stringWithFormat:@"delete from qdx_myline where m_l_id not in (select max(m_l_id) as m_l_id from qdx_myline group by myline_id)"];
sqlite3_exec(db, [sql1 UTF8String], nil, nil, nil);
sqlite3_exec(db, [sql2 UTF8String], nil, nil, nil);
sqlite3_exec(db, [sql3 UTF8String], nil, nil, nil);
sqlite3_exec(db, [sql4 UTF8String], nil, nil, nil);
sqlite3_exec(db, [sql5 UTF8String], nil, nil, nil);
sqlite3_exec(db, [sql6 UTF8String], nil, nil, nil);
}
在ViewController中的使用
/**
* 创建数据库模型
*/
@property (nonatomic, strong) QDXOfflineDB *offlineDB;
self.offlineDB = [QDXOfflineDB shareDataBase];
-(void)selectQuestion
{
NSArray *questionArray = [_offlineDB selectQuestionWithQid:pointmap_id];
for (int i=0; i<questionArray.count; i++) {
QDXQuestionModel *questions =[questionArray objectAtIndex:i];
qkey = questions.qkey;
question = questions.question_name;
qa = questions.qa;
qb = questions.qb;
qc = questions.qc;
qd = questions.qd;
}
}
