iOS sqlite3数据库使用复习

作者: Sweet丶 | 来源:发表于2019-06-10 14:35 被阅读0次

iOS对象存储框架,含单元测试Demo
python学习笔记|数据库sqlite3
iOS原生s
iOS sqlite3数据库使用复习
iOS数据库的基本使用
iOS数据存储之文件沙盒
iOS数据存储之NSCoder
数据库操作之FMDB
iOS数据库存储之SQL语句
sqlite3数据库操作

iOS APP的数据存储方式有很多，NSUserDefault、plist、归档存到文本文件、sqlite3数据库、Coredata数据库。对于数据量大一些的数据存在数据库里是最好的选择，因为只是一个APP的数据存储（不像后台数据库），所以常用操作很简单，这里复习一下sqlite3的基本功能使用。

下面是一个具体的使用，YSTSwipSQLUtils是负责存储的一个类，对照着自己敲一遍就学会了。

@interface YSTSwipSQLUtils (){
    sqlite3 *database;
}

@end

@implementation YSTSwipSQLUtils

static YSTSwipSQLUtils *sharedINstance;
+ (instancetype)shared{
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        sharedINstance = [[self alloc] init];
    });
    [sharedINstance openSQLiteDB];
    return sharedINstance;
}

- (void)openSQLiteDB{
    NSString *dbPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject stringByAppendingPathComponent:@"swipData.db"];
    
    sqlite3_open([dbPath UTF8String], &database);
    
    NSString *sql = @"CREATE TABLE IF NOT EXISTS TRADEUNUPLOADSIGNS(USERCODE TEXT,TRADESN TEXT,SIGNTDK TEXT);";
    [self createTableWithSQL:sql];
    //
    sql = @"CREATE TABLE IF NOT EXISTS PS008DATA(USERCODE TEXT,TRMNO TEXT,MERCID TEXT,ANOTHERNAME TEXT,USERMERCID TEXT);";
    [self createTableWithSQL:sql];
    
    sql = @"CREATE TABLE IF NOT EXISTS ICPARAMETERS(TRMNO TEXT,FLAG TEXT,RID1 TEXT,RID2 TEXT,RID3 TEXT,RID4 TEXT,RID5 TEXT,RID6 TEXT,RID7 TEXT,RID8 TEXT,RID9 TEXT,RID10 TEXT,RID11 TEXT,RID12 TEXT,AID1 TEXT,AID2 TEXT,AID3 TEXT,AID4 TEXT,AID5 TEXT,AID6 TEXT,AID7 TEXT,AID8 TEXT,AID9 TEXT,AID10 TEXT);";
    [self createTableWithSQL:sql];
    
    sql = @"create table if not exists mposlist (usercode text,mposname text)";
    [self createTableWithSQL:sql];
}

- (void)createTableWithSQL:(NSString *)sql{
    NSLog(@"创建表格%@",sql);
    sqlite3_exec(database, [sql UTF8String], NULL, NULL, NULL);
    
    int result = sqlite3_exec(database, [sql UTF8String], NULL, NULL, NULL);
    if (SQLITE_OK == result) {
        NSLog(@"sql:%@ success",sql);
    }else{
        NSLog(@"sql:%@ fail",sql);
    }
}

-(void)closeSQLiteDB{
    if (database) {
        sqlite3_close(database);
    }
}

// PS008数据保存
- (void)savePS008DataUserMercId:(NSString *)userMercId AndAnotherName:(NSString *)anotherName AndMercId:(NSString *)mercId  WithUserCode:(NSString *)userCode AndTrmNo:(NSString *)trmNo{
    
    //    sql = @"CREATE TABLE IF NOT EXISTS PS008DATA(USERCODE TEXT,TRMNO TEXT,MERCID TEXT,ANOTHERNAME TEXT,USERMERCID TEXT);";
    NSString *sql = @"SELECT * FROM WHERE USERCODE=? AND TRMNO=?;";
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, nil);
    sqlite3_bind_text(statement, 1, [userCode UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 2, [trmNo UTF8String], -1, SQLITE_TRANSIENT);
    if (sqlite3_step(statement) != SQLITE_ROW) {
        sql = @"INSERT INTO PS008DATA(USERCODE, TRMNO, MERCID, ANOTHERNAME, USERMERCID) VALUES(?,?,?,?,?);";
        sqlite3_stmt *statement2;
        sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement2, nil);
        sqlite3_bind_text(statement2, 1, [userCode UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement2, 2, [trmNo UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement2, 3, [mercId UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement2, 4, [anotherName UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement2, 5, [userMercId UTF8String], -1, SQLITE_TRANSIENT);
        if (sqlite3_step(statement2) == SQLITE_DONE) {
            NSLog(@"保存成功");
        }
        sqlite3_step(statement2);
        sqlite3_finalize(statement2);
        sqlite3_finalize(statement);
        
    }else{
        sql = @"UPDATE PS008DATA SET USERCODE=?, TRMNO=?, MERCID=?, ANOTHERNAME=?, USERMERCID=?; WHERE USERCODE=? AND TRMNO=?;";
        sqlite3_stmt *statement3;
        sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement3, nil);
        sqlite3_bind_text(statement3, 1, [userCode UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement3, 2, [trmNo UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement3, 3, [mercId UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement3, 4, [anotherName UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement3, 5, [userMercId UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement3, 6, [userCode UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement3, 7, [trmNo UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_step(statement3);
        sqlite3_finalize(statement3);
        sqlite3_finalize(statement);
    }
}

- (void)deletePS008DataWithUserCode:(NSString *)userCode AndTrmNo:(NSString *)trmNo{
    
    NSString *sql = @"DELETE FROM PS008DATA WHERE USERCODE=? AND TRMNO=?;";
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, nil);
    sqlite3_bind_text(statement, 1, [userCode UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 2, [trmNo UTF8String], -1, SQLITE_TRANSIENT);
    int result=sqlite3_step(statement);
    if (result==SQLITE_DONE){
        NSLog(@"删除PS008成功");
    }else{
        NSLog(@"删除PS008失败");
    }
    sqlite3_finalize(statement);
}

- (BOOL)deletePs008DataWithUserCode:(NSString *)userCode {
    NSString *sql=@"DELETE FROM PS008DATA WHERE USERCODE=?;";
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, nil);
    sqlite3_bind_text(statement, 1, [userCode UTF8String], -1, SQLITE_TRANSIENT);
    int result=sqlite3_step(statement);
    sqlite3_finalize(statement);
    return (result == SQLITE_DONE);
}

//    sql = @"CREATE TABLE IF NOT EXISTS PS008DATA(USERCODE TEXT,TRMNO TEXT,MERCID TEXT,ANOTHERNAME TEXT,USERMERCID TEXT);";
- (NSMutableDictionary *)getPS008DataInUserCode:(NSString *)userCode AndTrmNo:(NSString *)trmNo{
    NSString *sql = @"SELECT USERCODE, TRMNO, MERCID, ANOTHERNAME, USERMERCID FROM PS008DATA WHERE USERCODE=? AND TRMNO=?;";
    sqlite3_stmt *statement;
    sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, nil);
    sqlite3_bind_text(statement, 1, [userCode UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 2, [trmNo UTF8String], -1, SQLITE_TRANSIENT);
    
    NSMutableDictionary *result = [NSMutableDictionary dictionary];
    if (sqlite3_step(statement) == SQLITE_ROW) {
        result[@"userCode"] = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 0)?:""];
        result[@"trmNo"] = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)?:""];
        result[@"mercId"] = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)?:""];
        result[@"anotherName"] = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)?:""];
        result[@"userMercId"] = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)?:""];
    }
    sqlite3_finalize(statement);
    
    return result;
}

更多sqlite语句使用参考
sqlite3 基本使用