導(dǎo)語

最近在寫swift網(wǎng)絡(luò)請(qǐng)求使用二級(jí)緩存的時(shí)候，用到了SQLite3來保存緩存數(shù)據(jù)，以前都是在oc中使用，這次在swift3.1中使用，遇到了不少坑，分享給大家。

1、打開數(shù)據(jù)庫(kù)，創(chuàng)建表

    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轉(zhuǎn)換成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));"
        //執(zhí)行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、插入數(shù)據(jù)（開始又坑了）

首先是這個(gè)key String 類型,轉(zhuǎn)換為cString來綁定數(shù)據(jù)時(shí)，經(jīng)常寫不進(jìn)去，db表里面key這個(gè)值有時(shí)有數(shù)據(jù)，有時(shí)是空的，有時(shí)只有不全的key，到處找資料，修改SQL語句，也不行啊。最后在http://stackoverflow.com上找到了解決方法（還是國(guó)外牛人多?。?，解決方式：把String先轉(zhuǎn)換成NSString，再轉(zhuǎn)換成cString 這樣就寫進(jìn)去了，我勒個(gè)去，完全不知道為什么這樣改了就能成功綁定了（有知道原因的大神請(qǐng)給我說說原因啊）。Data類型數(shù)據(jù)也是同理。

  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、查找數(shù)據(jù)(同樣需要先轉(zhuǎn)成NSString，再轉(zhuǎn)換成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數(shù)據(jù)bytes，然后獲取length，初始化data
        let result = Data.init(bytes: data, count: Int(databytes))
        return NetCacheModel(key: String.init(cString: key), size: Int(size), data: result)
    }

文章就先寫到這里了，分享給同樣遇到坑的朋友。