SQLite的使用

2017-02-06  本文已影响16人  为零sowill

第一步:建立模型.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;
    }
}
上一篇下一篇

猜你喜欢

热点阅读