SQLite.swift 建表

    private let Users = Table("Users")
    private let id = Expression<Int>("id")
    private let name = Expression<String>("name")
    private let age = Expression<Int>("age")

    lazy var database :Connection = {
        //測試方便每次都刪除舊的
        try? FileManager.default.removeItem(at: URL(fileURLWithPath: "\(DocumentsPath)/Swift.sqlite"))
        var connection :Connection!
        do {
            connection = try Connection.init("\(DocumentsPath)/Swift.sqlite")
            try connection.run( Users.create{ t in
                t.column(id, primaryKey: true)
                t.column(name, defaultValue: 0)
                t.column(age, defaultValue: 0)
                
            })
              //創(chuàng)建索引
            try connection.run(Users.createIndex(id,name))
        } catch {
            print(error)
        }
        return connection
    }()    

FMDB 建表

    lazy var database :FMDatabase? = {
        try? FileManager.default.removeItem(at: URL(fileURLWithPath: "\(DocumentsPath)/FM.sqlite"))
        let db = FMDatabase(path: "\(DocumentsPath)/FM.sqlite")
        guard db.open() else {return nil}
        do {
        //模仿SQLite.swift寫法餐曼，看起來順眼
            var s = "CREATE TABLE Users "
            s += "(id integer  PRIMARY KEY,"
            s += "name text default 0,"
            s += "age integer default 0)"
 
            try db.executeUpdate(s, values: nil)
            try db.executeUpdate("CREATE INDEX name_index ON Users (name);", values: nil)
        }catch{
            print(error)
        }
        return db
    }()   

SQLite.swift 批量插入

    func update(list :[User]){
        guard let db = database else {return}
        do{
            try db.transaction {
                for u in list {
                    try db.run(Users.insert(or: .replace,
                                              id <- u.id,
                                              name <- u.name,
                                              age <- u.age))
                }

            }
        } catch {
            print(error)
        }
    }  

FMDB 批量插入

    func update(list :[User]){
        guard let db = database else {return}
        for u in list {
            do {
                try db.executeUpdate("REPLACE INTO Users VALUES (?,?,?)",
                                     values: [u.id,
                                              u.name,
                                              u.age])
            }catch{
                print(error)
            }
        }
    } 
    

在主線程中批量插入同等數(shù)據(jù)的耗時(shí)

FMDB 提供了一個(gè)安全的線程組驹尼，看了下源碼其實(shí)就是一個(gè)同步執(zhí)行的串行隊(duì)列

    func update(list :[User]){
        guard let db = database else {return}
        let queue = FMDatabaseQueue(path: "\(DocumentsPath)/FM.sqlite")
        queue.inTransaction { db, rollback in
          for u in list {
            do {
                try db.executeUpdate("REPLACE INTO Users VALUES (?,?,?)",
                                     values: [u.id,
                                              u.name,
                                              u.age])
            }catch{
                print(error)
            }
        }
     }
    //SQLite.Swift   
    let queue = DispatchQueue(label: "queue")
    queue.sync {
        guard let db = database else {return}
        for u in list {
            do {
                try db.executeUpdate("REPLACE INTO Users VALUES (?,?,?)",
                                     values: [u.id,
                                              u.name,
                                              u.age])
            }catch{
                print(error)
            }
        }
      }

SQLite.swift 讀取數(shù)據(jù)

// 數(shù)據(jù)模型用的是 ObjectMapper
 func query() -> [User]{
        guard let db = database else {return []}
        //可以鏈?zhǔn)秸{(diào)用
        //let table = Users.filter(name.like("%\("小明")%")).order(age.asc)
        let table = Users.order(age.asc)
        do {
            let list :[User] = try db.prepare(table).map({ v -> User in
                let u = User(JSON: ["id":v[id],
                                           "name":v[name],
                                           "age":v[age]])
                return u!
            })
            return list
        } catch  {
            print(error)
        }
        return []
    }

FMDB 讀取數(shù)據(jù)

    func query() -> [User]{
        guard let db = database else {return []}
        var list :[User] = []
        do {
            let s = try db.executeQuery("SELECT * FROM User ORDER BY age ASC", values: nil)
            while (s.next()){
                if let json = s.resultDictionary as? [String:Any]{
                    if let u = User(JSON: json){
                        list.append(u)
                    }
                }
            }
        }catch{
            print(error)
        }
 
        return list

    
    }

讀取相同數(shù)據(jù)耗時(shí)