swift使用SQLite本地?cái)?shù)據(jù)庫(kù)

SQLite是一個(gè)輕量級(jí)的本地?cái)?shù)據(jù)，使用簡(jiǎn)單。但是操作的代碼全都是自己手動(dòng)輸入，容易出錯(cuò)。
封裝一個(gè)類來操作數(shù)據(jù)庫(kù)

class EISSQLiteManager: NSObject {
    private static let manager: EISSQLiteManager = EISSQLiteManager()
    //單例
    class func shareManager() -> EISSQLiteManager{
        return manager
    }
    //數(shù)據(jù)庫(kù)對(duì)象
    private var db:OpaquePointer? = nil
    func openDB(sqliteName:String){
        //0.拿到數(shù)據(jù)庫(kù)的路徑
        let path = sqliteName.docDir()
//        print(path)
        let cPath = path.cString(using: String.Encoding.utf8)
        //1.需要代開的數(shù)據(jù)庫(kù)的路徑 c語言的字符串
        //2.打開之后的數(shù)據(jù)庫(kù)對(duì)象（指針）,以后所有的數(shù)據(jù)庫(kù)操作，都必須拿到這個(gè)指針才能進(jìn)行相關(guān)操作
        if sqlite3_open(cPath, &db) != SQLITE_OK{
            print("數(shù)據(jù)庫(kù)打開失敗")
            return
        }
        
    }
    
    func closeDB() -> Void {
        sqlite3_close(db)
    }
    func creatTable(sql:String) -> Bool
    {
        // 1.編寫SQL語句
        // 建議: 在開發(fā)中編寫SQL語句, 如果語句過長(zhǎng), 不要寫在一行
        // 開發(fā)技巧: 在做數(shù)據(jù)庫(kù)開發(fā)時(shí), 如果遇到錯(cuò)誤, 可以先將SQL打印出來, 拷貝到PC工具中驗(yàn)證之后再進(jìn)行調(diào)試
        // print(sql)
        // 2.執(zhí)行SQL語句
        return execSQL(sql: sql)
    }
    func execSQL(sql: String) -> Bool
    {
        // 0.將Swift字符串轉(zhuǎn)換為C語言字符串
        let cSQL = sql.cString(using: String.Encoding.utf8)!
        
        // 在SQLite3中, 除了查詢以外(創(chuàng)建/刪除/新增/更新)都使用同一個(gè)函數(shù)
        /*
         1. 已經(jīng)打開的數(shù)據(jù)庫(kù)對(duì)象
         2. 需要執(zhí)行的SQL語句, C語言字符串
         3. 執(zhí)行SQL語句之后的回調(diào), 一般傳nil
         4. 是第三個(gè)參數(shù)的第一個(gè)參數(shù), 一般傳nil 
         5. 錯(cuò)誤信息, 一般傳nil 
         */
        if sqlite3_exec(db, cSQL, nil, nil, nil) != SQLITE_OK 
        {
            
            return false
        } 
        return true
    }
    
    /// 執(zhí)行 SQL 返回查詢結(jié)果集
    ///
    /// - parameter sql: 任意給定的 SELETE 查詢 SQL
    func execRecordSet(sql: String) -> [[String: AnyObject]]? {
        
        // 1. 預(yù)編譯 SQL
        /**
         參數(shù)
         
         1. 已經(jīng)打開的數(shù)據(jù)庫(kù)句柄
         2. 要執(zhí)行的 SQL
         3. 以字節(jié)為單位的 SQL 最大長(zhǎng)度，傳入 -1 會(huì)自動(dòng)計(jì)算
         4. SQL 語句句柄
         - 后續(xù)針對(duì)當(dāng)前查詢結(jié)果的操作全部基于此句柄
         - 需要調(diào)用 sqlite3_finalize 釋放
         5. 未使用的指針地址，通常傳入 nil
         */
        var stmt: OpaquePointer? = nil
        if sqlite3_prepare_v2(db, sql, -1, &stmt, nil) != SQLITE_OK {
            print("SQL 錯(cuò)誤\n")
            sqlite3_finalize(stmt)
            if (sqlite3_errmsg(self.db)) != nil {
                let msg = "SQLiteDB - failed to prepare SQL:\(sql)"
                print(msg)
            }
            return nil
        }
        
        // 創(chuàng)建結(jié)果數(shù)組
        var rows = [[String: AnyObject]]()
        
        // 2. 遍歷集合
        while sqlite3_step(stmt) == SQLITE_ROW {
            // 將單條記錄字典添加到結(jié)果數(shù)組中
            rows.append(record(stmt: stmt!))
        }
        
        // 3. 釋放語句句柄 - 很重要，否則會(huì)內(nèi)容泄漏
        sqlite3_finalize(stmt)
        
        // 4. 返回結(jié)果數(shù)組
        return rows
    }
    
    /// 從 stmt 中獲取當(dāng)前記錄的完整內(nèi)容
    ///
    /// - parameter stmt: stmt 句柄
    private func record(stmt: OpaquePointer) -> [String: AnyObject] {
        
        // 1. 獲取查詢結(jié)果列數(shù)
        let colCount = sqlite3_column_count(stmt)
        
        // 單條記錄字典
        var row = [String: AnyObject]()
        
        // 2. 遍歷所有列，獲取每一列的信息
        for col in 0..<colCount {
            // 1> 獲取列名
            let cName = sqlite3_column_name(stmt, col)
            let name = String(cString: cName!, encoding: String.Encoding.utf8)
            
            
            // 2> 獲取每列數(shù)據(jù)類型
            let type = sqlite3_column_type(stmt, col)
            
            // 3> 根據(jù)數(shù)據(jù)類型獲取對(duì)應(yīng)結(jié)果
            var value: AnyObject?
            switch(type) {
            case SQLITE_FLOAT:
                value = sqlite3_column_double(stmt, col) as AnyObject
            case SQLITE_INTEGER:
                value = Int(sqlite3_column_int64(stmt, col)) as AnyObject
            case SQLITE3_TEXT:

//                let cText = UnsafePointer<Int8>(sqlite3_column_text(stmt, col))
                let cText = sqlite3_column_text(stmt, col).withMemoryRebound(to: Int8.self, capacity: 1, { ( ptr:UnsafePointer<Int8>) -> UnsafePointer<Int8> in
                    return ptr
                })
                
                
                value = String(cString: cText, encoding: String.Encoding.utf8) as AnyObject
            case SQLITE_NULL:
                value = NSNull()
            default:
                print("不支持的數(shù)據(jù)類型")
            }
            
            //            print("列名 \(name) 值 \(value)")
            row[name!] = value ?? NSNull()
        }
        
        return row
    }
    
    //刪除數(shù)據(jù)
    func deleteUser(sql: String) -> Bool {
        
        //刪除sql語句
//        let sql = "delete from UserTable where username = '\(username)'";
        
        //sqlite3_stmt指針
        var stmt:OpaquePointer? = nil
        let cSql = sql.cString(using: .utf8)
        
        //編譯sql
        let prepare_result = sqlite3_prepare_v2(self.db, cSql!, -1, &stmt, nil)
        
        //判斷如果失敗，獲取失敗信息
        if prepare_result != SQLITE_OK {
            sqlite3_finalize(stmt)
            if (sqlite3_errmsg(self.db)) != nil {
                let msg = "SQLiteDB - failed to prepare SQL:\(sql)"
                print(msg)
            }
            return false
        }
        
        //step執(zhí)行
        let step_result = sqlite3_step(stmt)
        
        //判斷執(zhí)行結(jié)果，如果失敗，獲取失敗信息
        if step_result != SQLITE_OK && step_result != SQLITE_DONE {
            sqlite3_finalize(stmt)
            if (sqlite3_errmsg(self.db)) != nil {
                let msg = "SQLiteDB - failed to execute SQL:\(sql)"
                print(msg)
            }
            return false
        }
        
        //finalize
        sqlite3_finalize(stmt)
        
        return true
    }
    
    //更新數(shù)據(jù)
    func updateUser(sql: String) -> Bool {
        
        //更新sql語句
//        let sql = "update UserTable set username = '\(toName)' where username = '\(name)'";
        
        //sqlite3_stmt指針
        var stmt:OpaquePointer? = nil
        let cSql = sql.cString(using: .utf8)
        
        //編譯sql
        let prepare_result = sqlite3_prepare_v2(self.db, cSql!, -1, &stmt, nil)
        
        //判斷如果失敗，獲取失敗信息
        if prepare_result != SQLITE_OK {
            sqlite3_finalize(stmt)
            if (sqlite3_errmsg(self.db)) != nil {
                let msg = "SQLiteDB - failed to prepare SQL:\(sql)"
                print(msg)
            }
            return false
        }
        
        //step執(zhí)行
        let step_result = sqlite3_step(stmt)
        
        //判斷執(zhí)行結(jié)果，如果失敗，獲取失敗信息
        if step_result != SQLITE_OK && step_result != SQLITE_DONE {
            sqlite3_finalize(stmt)
            if (sqlite3_errmsg(self.db)) != nil {
                let msg = "SQLiteDB - failed to execute SQL:\(sql)"
                print(msg)
            }
            return false
        }
        
        //finalize
        sqlite3_finalize(stmt)
        
        return true
    }
}

使用代碼：創(chuàng)建數(shù)據(jù)表

func creatDB() {
        EISSQLiteManager.shareManager().openDB(sqliteName: "device.sqlite")
        //設(shè)備表
        let creatSql = "CREATE TABLE IF NOT EXISTS T_Device(id INTEGER PRIMARY KEY AUTOINCREMENT," +
            "CheckTaskGuid TEXT," +
            "FireDeviceGuid TEXT," +
            "GroupGuid TEXT," +
            "DeviceName TEXT, " +
            "Code TEXT," +
            "LocationDescription TEXT, " +
            "FireDeviceStatusName TEXT," +
            "QRCode TEXT," +
            "Description TEXT," +
            "CheckRsult INTEGER," +
            "FireDeviceCheckStatus INTEGER," +
            "Longitude Double," +
            "Latitude Double," +
            "SignTime TEXT" +
        "); \n"
        //文件表
        let creatFileSql = "CREATE TABLE IF NOT EXISTS T_RecordFile(id INTEGER PRIMARY KEY AUTOINCREMENT," +
            "CheckTaskGuid TEXT," +
            "FireDeviceGuid TEXT," +
            "Type INTEGER," +
            "FilePath TEXT," +
            "URL TEXT" +
        "); \n"
        
        if EISSQLiteManager.shareManager().creatTable(sql: creatSql) {
            print("創(chuàng)建設(shè)備表成功?。。。。?)
            
        }else{
            print("創(chuàng)建設(shè)備表失?。。。。。?！")
        }
        
        if EISSQLiteManager.shareManager().creatTable(sql: creatFileSql) {
            print("創(chuàng)建文件表成功?。。。。?)
            
        }else{
            print("創(chuàng)建文件表失?。。。。。?！")
        }
        EISSQLiteManager.shareManager().closeDB()
    }

添加數(shù)據(jù)

EISSQLiteManager.shareManager().openDB(sqliteName: "device.sqlite")

        let insertSql = "INSERT into T_Device(" + "FireDeviceGuid," +
            "CheckTaskGuid," +
            "GroupGuid," +
            "DeviceName," +
            "Code," +
            "LocationDescription," +
            "FireDeviceStatusName," +
            "Description," +
            "CheckRsult," +
            "FireDeviceCheckStatus," +
            "Longitude," +
            "Latitude," +
            "SignTime) " +
            "VALUES ('\(Model.FireDeviceGuid ?? "" )'," +
            "'\(self.taskModel?.CheckTaskGuid ?? "")'," +
            "'\(self.GroupGuid ?? "")'," +
            "'\(Model.DeviceName ?? "")'," +
            "'\(Model.Code ?? "")'," +
            "'\(Model.LocationDescription ?? "")'," +
            "'\(Model.FireDeviceStatusName ?? "")'," +
            "'\(Description)'," +
            "\(CheckRsult)," +
            "\(CheckRsult)," +
            "\(self.recordLocation?.coordinate.longitude ?? 0)," +
            "\(self.recordLocation?.coordinate.latitude ?? 0)," +
            "'\(signTime)')"
        if EISSQLiteManager.shareManager().execSQL(sql: insertSql) {
            print("添加設(shè)備數(shù)據(jù)表成功！?。。?！")
        }else{
            print("添加設(shè)備數(shù)據(jù)表失敗？？？？？")
        }
        EISSQLiteManager.shareManager().closeDB()

刪除數(shù)據(jù)

EISSQLiteManager.shareManager().openDB(sqliteName: "device.sqlite")
        let deleteRecordSql = "delete from T_RecordFile where FireDeviceGuid = '\(self.valueString ?? "")' and CheckTaskGuid = '\(self.taskModel?.CheckTaskGuid ?? "")'"
        if EISSQLiteManager.shareManager().execSQL(sql: deleteRecordSql) {
            print("刪除T_RecordFile數(shù)據(jù)表成功?。。。?！")
        }else{
            print("刪除T_RecordFile數(shù)據(jù)表失?。?？？？？")
        }
        let deleteDeviceSql = "delete from T_Device where FireDeviceGuid = '\(self.valueString ?? "")' and CheckTaskGuid = '\(self.taskModel?.CheckTaskGuid ?? "")'"
        if EISSQLiteManager.shareManager().execSQL(sql: deleteDeviceSql) {
            print("刪除T_Device數(shù)據(jù)表成功?。。。。?)
        }else{
            print("刪除T_Device數(shù)據(jù)表失??？？？？？")
        }
        
        EISSQLiteManager.shareManager().closeDB()

修改數(shù)據(jù)

//修改數(shù)據(jù)
        EISSQLiteManager.shareManager().openDB(sqliteName: "device.sqlite")
        let uptateSql = "UPDATE T_Device set Description = '\(Description)'," +
            "CheckRsult = \(CheckRsult)," +
            "FireDeviceCheckStatus = \(CheckRsult)," +
            "Longitude = \(self.recordLocation?.coordinate.longitude ?? 0)," +
            "Latitude = \(self.recordLocation?.coordinate.latitude ?? 0)," +
            "SignTime = '\(signTime)," +
        " where FireDeviceGuid = '\(self.valueString!)' and CheckTaskGuid = '\(self.taskModel?.CheckTaskGuid! ?? "")';"
        if EISSQLiteManager.shareManager().execSQL(sql: uptateSql) {
            print("修改數(shù)據(jù)表成功！?。。。?)
        }else{
            print("修改數(shù)據(jù)表失?。?？？？？")
        }
        EISSQLiteManager.shareManager().closeDB()
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

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

  • Swift1> Swift和OC的區(qū)別1.1> Swift沒有地址/指針的概念1.2> 泛型1.3> 類型嚴(yán)謹(jǐn) 對(duì)...
    cosWriter閱讀 11,665評(píng)論 1贊 32
  • 發(fā)現(xiàn) 關(guān)注 消息 iOS 第三方庫(kù)、插件、知名博客總結(jié) 作者大灰狼的小綿羊哥哥關(guān)注 2017.06.26 09:4...
    肇東周閱讀 15,379評(píng)論 4贊 61
  • 在這里小小推薦下我的個(gè)人博客csdn：雷園的csdn博客個(gè)人博客：雷園的個(gè)人博客簡(jiǎn)書：雷園的簡(jiǎn)書 ? 10月3...
    雷園LY閱讀 5,096評(píng)論 2贊 2
  • vue-resource特點(diǎn): 體積小vue-resource非常小巧，在壓縮以后只有大約12KB，服務(wù)端啟用gz...
    寒梁沐月閱讀 1,103評(píng)論 0贊 7
  • 今天的知識(shí)付費(fèi)給我們帶來極大便利，因?yàn)榕ψ兒檬侨祟愄煨浴Ｆ疵缡志褪遣幌肼淙松砗?，今天一個(gè)穿衣搭配、明天一個(gè)寫作...
    呆萌葉子閱讀 535評(píng)論 0贊 3

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