iOS使用sqlite3使用

1.在XCode工程中,打开targets,在Build Phases下导入Libsqlite.tbd,在需要使用sqlite3的位置导入头文件即可.

#import <sqlite3.h>

static sqlite3 *_db;

2. 生成路径 并打开数据库

NSString *path = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"auro_main.sqlite"];
// 将OC字符串 转为 c字符串
const char *cPath = path.UTF8String;
    
// 打开数据库文件
int result = sqlite3_open(cPath, &_db);
if (result == SQLITE_OK) {
   [self showAlert:@"打开数据库成功"];
} else {
   [self showAlert:@"打开数据库失败"];
}

因为方便记忆，把执行sqlite封转成自己的方法

// 执行sqlite
- (int)executSqlite:(NSString *)sql err:(char *)errMsg {
    const char *cSql = sql.UTF8String;
    return sqlite3_exec(_db, cSql, NULL, NULL, &errMsg);
}

 

3.创建表Table

- (void)creatTableWith:(NSString *)sql {
    char *errMsg = NULL;
    int result = [self executSqlite:sql err:errMsg];
    if (result == SQLITE_OK) {
        [self showAlert:@"创建表成功"];
    } else {
        [self showAlert:[NSString stringWithFormat:@"创建表失败-%s", errMsg]];
    }
}

 4.删除表Table

- (void)deleteTableWith:(NSString *)sql {
    char *errMsg = NULL;
    int result = [self executSqlite:sql err:errMsg];
    if (result == SQLITE_OK) {
        [self showAlert:@"删除表成功"];
    } else {
        [self showAlert:[NSString stringWithFormat:@"删除表失败-%s", errMsg]];
    }
}

5.增、删、改、查

5.1 增加数据

- (void)insertDataWith:(NSString *)sql {
    char *errMsg = NULL;
    int result = [self executSqlite:sql err:errMsg];
    if (result == SQLITE_OK) {
        [self showAlert:@"增加数据成功"];
    } else {
        [self showAlert:[NSString stringWithFormat:@"增加数据失败-%s", errMsg]];
    }
}

5.2 删除数据

- (void)deleteWith:(NSString *)sql {
    char *errMsg = NULL;
    int result = [self executSqlite:sql err:errMsg];
    if (result == SQLITE_OK) {
        [self showAlert:@"删除表成功"];
    } else {
        [self showAlert:[NSString stringWithFormat:@"删除表失败-%s", errMsg]];
    }
}

5.3 修改数据

- (void)updateWith:(NSString *)sql {
    char *errMsg = NULL;
    int result = [self executSqlite:sql err:errMsg];
    if (result == SQLITE_OK) {
        [self showAlert:@"修改成功"];
    } else {
        [self showAlert:[NSString stringWithFormat:@"修改失败-%s", errMsg]];
    }
}

5.4 查询数据

- (NSArray *)selectWith:(NSString *)sql {
    const char *zSql = sql.UTF8String;
    sqlite3_stmt *stmt = NULL;
    /*
     第一个参数:需要执行SQL语句的数据库
     第二个参数:需要执行的SQL语句
     第三个参数: 告诉系统SQL语句的长度, 如果传入一个小于0的数, 系统会自动计算
     第四个参数:结果集, 里面存放所有查询到的数据(不严谨)
     */
    sqlite3_prepare(_db, zSql, -1, &stmt, NULL);
    NSMutableArray *tempArr = [NSMutableArray array];
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        int count = sqlite3_column_count(stmt);
        NSMutableDictionary *tempDic = [NSMutableDictionary dictionary];
        for (int i = 0; i < count; i++) {
            const char *key = sqlite3_column_name(stmt, i);
            const char *type = sqlite3_column_decltype(stmt, i);
            NSString *keyStr = [NSString stringWithUTF8String:(const char *)key];
            NSString *typeStr = [NSString stringWithUTF8String:type];
            if ([typeStr isEqual:@"INTEGER"]) {     // 整数型
                int _int = sqlite3_column_int(stmt, i);
                [tempDic setValue:@(_int) forKey:keyStr];
            } else if ([typeStr isEqual:@"‌REAL"]) { // 浮点型
                CGFloat _float = sqlite3_column_double(stmt, i);
                [tempDic setValue:@(_float) forKey:keyStr];
            } else if ([typeStr isEqual:@"TEXT"]) { // 字符串文本
                const unsigned char *value = sqlite3_column_text(stmt, i);
                NSString *valueStr = [NSString stringWithUTF8String:(const char *)value];
                [tempDic setValue:valueStr forKey:keyStr];
            } else { // NULL 空值、 BLOB 二进制对象
                [tempDic setValue:@"" forKey:keyStr];
            }
        }
        [tempArr addObject:tempDic];
    }
    return tempArr;
}

 6.常用SQL语句

#define CREATE_TABLE(tableName, kvalues)       [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ (%@);", tableName, kvalues]
#define INSERT_DATA(tableName, keys, values)   [NSString stringWithFormat:@"INSERT INTO %@ (%@) VALUES (%@);", tableName, keys, values]
#define DELETE_TABLE(tableName)                [NSString stringWithFormat:@"DROP TABLE %@;", tableName]
#define DELETE_ALL_DATA(tableName)             [NSString stringWithFormat:@"DELETE FROM %@;", tableName]
#define DELETE_DATA(tableName, major)          [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@;", tableName, major]
#define UPDATE_DATA(tableName, kvalues, major) [NSString stringWithFormat:@"UPDATE %@ SET %@ WHERE %@;", tableName, kvalues, major]
#define SELECT_ALL_DATA(tableName)             [NSString stringWithFormat:@"SELECT * FROM %@;", tableName]
#define SELECT_DATA(tableName, keys)           [NSString stringWithFormat:@"SELECT (%@) FROM %@;", keys, tableName]
#define ADD_NEW_AREA(tableName, area)          [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ varchar", tableName, area]