导语
最近在写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)
}
文章就先写到这里了，分享给同样遇到坑的朋友。
