SQLite.swift

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)
                
            })
              //创建索引
            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)
            }
        }
    } 
    

在主线程中批量插入同等数据的耗时

FMDB 提供了一个安全的线程组，看了下源码其实就是一个同步执行的串行队列

    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 读取数据

// 数据模型用的是 ObjectMapper
 func query() -> [User]{
        guard let db = database else {return []}
        //可以链式调用
        //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 读取数据

    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

    
    }

读取相同数据耗时