IOS FMDB 使用记录

Sqlite ：

import UIKit
import FMDB

let DEFAULTS_KEY_IS_CREATETABLE = "DEFAULTS_KEY_IS_CREATETABLE"

class DBManager: NSObject {
    static let shareInstance = DBManager()
    var db : FMDatabase?
    
    var createTableDone : Bool {
        get {
            return UserDefaults.standard.bool(forKey: DEFAULTS_KEY_IS_CREATETABLE)
        }
        set {
            UserDefaults.standard.set(newValue, forKey: DEFAULTS_KEY_IS_CREATETABLE)
            UserDefaults.standard.synchronize()
        }
    }
    
    func openDB(dbName : String){
        ///给路径后面加一个!
        let path = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).last!
        print(path)
        ///创建一个dbBase的数据库的对象
        db = FMDatabase(path:"\(path)/\(dbName)")
        ///判断是否创建成功,需要强制解包
        if db!.open() {
            print("数据库创建成功")
        }else{
            print("数据库创建失败")
        }
    }
    
    func initDataToDB() {
        let path = Bundle.main.path(forResource: "3000", ofType: "json")
        do
        {
            let jsonString = try String(contentsOfFile: path!, encoding: String.Encoding.utf8)
            let jsonData = jsonString.data(using: String.Encoding.utf8)
            
            if  jsonData == nil {
                return
            }
            
            let json = try JSONSerialization.jsonObject(with: jsonData!, options: JSONSerialization.ReadingOptions.mutableLeaves) as! NSArray
            
            for item in json {
                if let tmpStr = item as? String {
                    insertTable(word: tmpStr)
                }
            }
        }
        catch let aError as NSError
        {
            print(aError.description)
        }
    }
    
    func createTable() {
        if let tmpDb = db {
            if tmpDb.open() {
                let createTableStr = "create table if not exists my_vocabulary(word varchar primary key,initial char,know bool,repeat_count int)"
                let t = tmpDb.executeUpdate(createTableStr, withArgumentsIn: [])
                if t {
                    print("createTable成功")
                } else {
                    print("createTable失败")
                }
            }
        }
    }
    
    func insertTable(word: String) {
        let initial = String(word.first!)
        let insertSql = "insert into my_vocabulary(word,initial,know,repeat_count) values(?,?,?,?)"
        
        if let tmpDb = db {
            if tmpDb.open() {
                let t = tmpDb.executeUpdate(insertSql, withArgumentsIn: [word,initial,true,1])
                if t {
                    print("insertSql成功")
                } else {
                    print("insertSql失败")
                }
            }
        }
    }
    
    func updateTableWordCount(word: String) {
        let initial = String(word.first!)
        let insertSql = "insert into my_vocabulary(word,initial,know,repeat_count) values(?,?,?,?)"
        if let tmpDb = db {
            if tmpDb.open() {
                let t = tmpDb.executeUpdate(insertSql, withArgumentsIn: [word,initial,true,1])
                if t {
                    print("\(word) 插入成功")
                } else {
                    print("\(word) 重复 更新")
                    let updateSql = "UPDATE my_vocabulary SET repeat_count = repeat_count + 1 WHERE word LIKE \'\(word)\'"
                    let update = tmpDb.executeUpdate(updateSql, withArgumentsIn: [word,initial,true,1])
                    if update {
                        print("\(word) 更新成功")
                    } else {
                        print("\(word) 更新失败")
                    }
                }
            }
        }
    }
    
    func queryTable(page: Int) -> [WordItem]? {
        var resultArray : [WordItem] = []
        var sqlString = String()
        sqlString = "SELECT * FROM my_vocabulary AS TD ORDER BY TD.repeat_count DESC LIMIT \(page * 100),100"  // "SELECT * FROM my_vocabulary AS TD ORDER BY TD.word ASC LIMIT 1,20"
        guard let set = try? db?.executeQuery(sqlString, values: []) else {
            return nil
        }
        while (set?.next())! {
            let model = WordItem()
            model.word = set?.string(forColumn: "word")
            model.isKnown = set?.bool(forColumn: "know")
            model.repeatCount = Int((set?.int(forColumn: "repeat_count"))!)
            resultArray.append(model)
        }
        set?.close()
        return resultArray
    }
}