swift之SQLite.swift的使用

集成

集成非常簡單，可以把SQLite.swift下載下來直接拖進(jìn)項目中，也可以用cocoaPods來繼承，為了省事我使用的cocoaPods。

source 'https://github.com/CocoaPods/Specs.git'
platform:ios,'10.0'
use_frameworks!

target 'SwiftStudy03' do

pod 'SQLite.swift'， '~> 0.12.2'

xcodeproj '/Users/adiqueen/Desktop/SwiftStudyDemos/SwiftStudy04/SwiftStudy03.xcodeproj'

end

另外，為了能能夠正常的使用SQLite.swift，需要在General->Frameworks,Libraries,and Embedded Content添加SystemConfigration.framework
添加SystemConfigration.framework

需要的地方直接：import SQLite 引入即可。
下面是一個簡單封裝的事例代碼，僅供個人學(xué)習(xí)于參考。

LGJDataStore

單例類，建表連庫

import Foundation
import SQLite

enum DataAccessError: Swift.Error {
    case datastoreConnectionError
    case insertError
    case deleteError
    case searchError
    case nilInData
    case nomoreData
}

class LGJDataStore {
    
    static let sharedInstance = LGJDataStore()
    let LGJDB: Connection?
    
    private init() {
        
        let dirs: [String] = NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory, FileManager.SearchPathDomainMask.allDomainsMask, true) as [String]
        let dir = dirs[0]
        let path = dir.appending("LGJ.sqlite")
        print("The DB Path:", path)
        
        do {
            LGJDB = try Connection.init(path)
            LGJDB?.busyTimeout = 5
            LGJDB?.busyHandler({ tries in
                if tries >= 3 {
                    return false
                }
                return true
            })
        }catch _ {
            LGJDB = nil
        }
    }
    
    func createTables() throws {
        do {
            try LGJDataHelper.createTable()
        } catch {
            throw DataAccessError.datastoreConnectionError
        }
        
    }
    
}
LGJDataHelperProtocol

協(xié)議，聲明一系列增刪改查的操作

import Foundation

protocol LGJDataHelperProtocol {
    
    associatedtype T
    
    static func createTable() throws -> Void
    
    static func insert(item: T) throws -> Int
    
    static func find(queryUserId: Int) throws -> [T]
    
}

//Optional implementation
extension LGJDataHelperProtocol {
    
    static func update(id: Int, value: T) throws -> T? {return nil}
    
    static func update(item: T) throws -> Bool {return false}
    
    static func delete(item: T) throws -> Bool {return false}
    
    static func findAll() throws -> [T]? {return nil}
    
    static func checkColimnExists(queryUserId: Int) throws -> Bool { return false }
    
}
LGJDataHelper

一個實現(xiàn)LGJDataHelperProtocol的class，完成具體的數(shù)據(jù)操作

import Foundation
import SQLite

class LGJDataHelper: LGJDataHelperProtocol {
    
    static let TABLE_NAME = "lgj_userinfo"
    
    static let userId = Expression<Int>("userId")
    static let name = Expression<String>("name")
    static let icon = Expression<UIImage>("icon")
    
    static let table = Table(TABLE_NAME)
    
    typealias T = LGJUserInfoModel
    
    static func createTable() throws {
        
        guard let DB = LGJDataStore.sharedInstance.LGJDB else {
            throw DataAccessError.datastoreConnectionError
        }
        do {
            _ = try DB.run(table.create(ifNotExists: true){t in
                t.column(userId)
                t.column(name)
                t.column(icon)
            })
        }catch _ {
            throw DataAccessError.datastoreConnectionError
        }
    }
    
    static func insert(item: T) throws -> Int {
        
        guard let DB = LGJDataStore.sharedInstance.LGJDB else {
            throw DataAccessError.datastoreConnectionError
        }
        
        let insert = table.insert(userId <- item.userId, name <- item.name, icon <- item.icon)
        do {
            let rowId = try DB.run(insert)
            guard rowId >= 0 else {
                throw DataAccessError.insertError
            }
            return Int(rowId)
        }catch _ {
            throw DataAccessError.insertError
        }
    }
    
    static func update(item: T) throws -> Bool {
        
        guard let DB = LGJDataStore.sharedInstance.LGJDB else {
            throw DataAccessError.datastoreConnectionError
        }
        
        let query = table.filter(item.userId == userId)
        
        if try DB.run(query.update(userId <- item.userId, name <- item.name, icon <- item.icon)) > 0 {
            return true
        }else{
            return false
        }
        
    }
    
    
    static func checkColimnExists(queryUserId: Int) throws -> Bool {
        
        guard let DB = LGJDataStore.sharedInstance.LGJDB else {
            throw DataAccessError.datastoreConnectionError
        }
        
        let query = table.filter(queryUserId == userId).exists

        let isExists = try DB.scalar(query)
        return isExists
        
    }
    
    static func delete(item: T) throws -> Bool {
        
        guard let DB = LGJDataStore.sharedInstance.LGJDB else {
            throw DataAccessError.datastoreConnectionError
        }
        
        let id = item.userId
        
        let query = table.filter(userId == id)
        do {
            let tmp = try DB.run(query.delete())
            
            guard tmp == 1 else {
                throw DataAccessError.deleteError
            }
        }catch _ {
            throw DataAccessError.deleteError
        }
        return true
    }
    
    static func find(queryUserId: Int) throws -> [LGJUserInfoModel] {
        
        guard let DB = LGJDataStore.sharedInstance.LGJDB else {
            throw DataAccessError.datastoreConnectionError
        }
        
        let query = table.filter(queryUserId == userId)
        let items = try DB.prepare(query)
        var retArray = [T]()
        for item in items {
            retArray.append(LGJUserInfoModel(userId: item[userId], name: item[name], icon: item[icon]))
        }
        return retArray
    }
    
    static func findAll() throws -> [LGJUserInfoModel]? {
        
        guard let DB = LGJDataStore.sharedInstance.LGJDB else {
            throw DataAccessError.datastoreConnectionError
        }
        var retArray = [T]()
        let items = try DB.prepare(table)
        for item in items {
            retArray.append(LGJUserInfoModel(userId: item[userId], name: item[name],icon: item[icon]))
        }
        return retArray
    }
    
    
}
LGJUserInfoModel

用戶數(shù)據(jù)模型，簡單定義了屬性、初始化方法及數(shù)據(jù)存儲實現(xiàn)。


import Foundation
import UIKit
import SQLite


// user info
struct LGJUserInfoModel {
    
    var userId: Int = 0
    var name: String = ""
    var icon: UIImage = UIImage.init(named: "default")!
    
    init() {}
    
    init(userId: Int, name: String, icon: UIImage) {
        self.userId = userId
        self.name = name
        self.icon = icon
    }
}


extension NSData: Value {

    //SQL Type
    public static var declaredDatatype: String {
        return Blob.declaredDatatype
    }

    //Decode
    public static func fromDatatypeValue(_ datatypeValue: Blob) -> NSData {
        return NSData(bytes: datatypeValue.bytes, length: datatypeValue.bytes.count)
    }
    
    //Encode
    public var datatypeValue: Blob {
        return Blob(bytes: self.bytes, length: self.length)
    }

}


extension UIImage: Value {
    
    public static var declaredDatatype: String{
        return Blob.declaredDatatype
    }

    public static func fromDatatypeValue(_ blobValue: Blob) -> UIImage {
        return UIImage(data: Data.fromDatatypeValue(blobValue))!
    }
    
    public var datatypeValue: Blob {
        return self.pngData()!.datatypeValue
    }
    
}

具體應(yīng)用起來就簡單的多了

//MARK
extension ViewController {
    
    func creatDB()  {
        do {
            try LGJDataStore.sharedInstance.createTables()
        }catch _ {
            print("create DB error")
        }
    }
    
    func insertUserModel()  {
        
        let userModel = LGJUserInfoModel(userId: 100, name: "liugaojian", icon: UIImage.init(named: "default")!)
        do {
           let torId = try LGJDataHelper.insert(item: userModel)
            print(torId)
        } catch _ {
            print("insert userModel error")
        }
        
    }
    
    func checkUserModel() {
        do {
            let exists =  try LGJDataHelper.checkColimnExists(queryUserId: 100)
            print(exists)
        } catch _ {
            print("check userModel error")
        }
    }
    
    func updateUserModel()  {
        
        let userModel = LGJUserInfoModel(userId: 100, name: "guhongjuan", icon: UIImage.init(named: "local")!)
        do {
            let isSuccess =  try LGJDataHelper.update(item: userModel)
            print(isSuccess)
        } catch _ {
                print("update userModel error")
        }
    }
    
    func getUserModel()  {
        do {
            let items = try LGJDataHelper.find(queryUserId: 100)
            
            if let item = items.first {
                print(item.userId, item.name, item.icon)
            }
            
        } catch _ {
            print("get userModel error")
        }
    }
    
    func getAllUserModel()  {
        
        do {
            let items =  try LGJDataHelper.findAll()!
            for item: LGJUserInfoModel in items {
                print(item.userId, item.name, item.icon)
            }
        } catch _ {
            print("find userModel error")
        }
    }
}

觸發(fā)以下方法

        creatDB()
        
        insertUserModel()
        
        checkUserModel()
        
        updateUserModel()
        
        getUserModel()
        
        getAllUserModel()

因為我們有些功能實現(xiàn)了簡單的打印操作，下面是打印結(jié)果：


打印
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容