swift3.1 SQlite3 使用

导语

最近在写swift网络请求使用二级缓存的时候，用到了SQLite3来保存缓存数据，以前都是在oc中使用，这次在swift3.1中使用，遇到了不少坑，分享给大家。

1、打开数据库，创建表

    mutating func openSQLiteDataBase(path: String) -> Bool {
        if let _ = db {
            return true
        }
       //获取cache路径
        guard let basePath = NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.cachesDirectory,FileManager.SearchPathDomainMask.userDomainMask, true).last else { return false }
        let finalPath = basePath + "/\(path)"
        //将string转换成cString
        let cStr = finalPath.cString(using: .utf8)
        //返回openDB是否成功
        return sqlite3_open(cStr, &db) == SQLITE_OK
    }
    
    func creatTable() -> Bool {
        let creatTb = "create table if not exists netWorkCache (key text, size integer, inline_data blob,primary key(key));"
        //执行SQL语句
        return execSQL(SQL:creatTb)
    }

func execSQL(SQL: String?) -> Bool {
        guard let sqlstr = SQL else { return false }
        let cSQL = sqlstr.cString(using: .utf8)
        let error :UnsafeMutablePointer<UnsafeMutablePointer<Int8>?>? = nil
        let result = sqlite3_exec(db, cSQL, nil, nil, error)
        if let err = error {
            print("sqlite exec error \(result),\(err)")
        }
        return result == SQLITE_OK
    }

2、插入数据（开始又坑了）

首先是这个key String 类型,转换为cString来绑定数据时，经常写不进去，db表里面key这个值有时有数据，有时是空的，有时只有不全的key，到处找资料，修改SQL语句，也不行啊。最后在http://stackoverflow.com上找到了解决方法（还是国外牛人多啊），解决方式：把String先转换成NSString，再转换成cString 这样就写进去了，我勒个去，完全不知道为什么这样改了就能成功绑定了（有知道原因的大神请给我说说原因啊）。Data类型数据也是同理。

  func insertDataToDist(key: String, size: Int, data: Data) -> Bool {
        let insetSql = "insert or replace into netWorkCache (key, size, inline_data) values (?1, ?2, ?3)"
        guard let stmt = dbPrepareStmt(sql: insetSql) else { return false }
        let nsdata = data as NSData
        let nskey = key as NSString
        sqlite3_bind_text(stmt, 1, nskey.utf8String, -1, nil)
        sqlite3_bind_int(stmt, 2, Int32(size))
        sqlite3_bind_blob(stmt, 3, nsdata.bytes, Int32(nsdata.length), nil)
        return sqlite3_step(stmt) == SQLITE_DONE
    }

3、查找数据(同样需要先转成NSString，再转换成cString)

    func getDbItem(key: String) -> NetCacheModel? {
        let searchSql = "select key, size, inline_data from netWorkCache where key = ?1"
        guard let stmt = dbPrepareStmt(sql: searchSql) else { return nil }
        let nskey = key as NSString
        sqlite3_bind_text(stmt, 1, nskey.utf8String, -1, nil)
        if sqlite3_step(stmt) == SQLITE_ROW {
           return dbGetItemFormStmt(stmt: stmt)
        }else {
            return nil
        }
    }
    
    func dbPrepareStmt(sql: String?) -> OpaquePointer? {
        guard let sqlStr = sql else { return nil }
        var stmt: OpaquePointer? = nil
        if sqlite3_prepare_v2(db, sqlStr.cString(using: .utf8), -1, &stmt, nil) == SQLITE_OK {
            return stmt
        }else {
            return nil
        }
    }
    
    func dbGetItemFormStmt(stmt:OpaquePointer) -> NetCacheModel? {
        let size = sqlite3_column_int(stmt, 1)
        let databytes = sqlite3_column_bytes(stmt, 2)
        guard let key = sqlite3_column_text(stmt, 0),
        let data = sqlite3_column_blob(stmt, 2) else { return nil }
        //取到data数据bytes，然后获取length，初始化data
        let result = Data.init(bytes: data, count: Int(databytes))
        return NetCacheModel(key: String.init(cString: key), size: Int(size), data: result)
    }

文章就先写到这里了，分享给同样遇到坑的朋友。