基于Swift使用FMDB实现(增删改查+搜索+聊天置顶+分页读取)

Pod引入FMDB框架(记得加use_frameworks!),引入头文件import FMDB

写一个DBManager类

class DBManager: NSObject {    static let shareManager = DBManager()    var dbQueue: FMDatabaseQueue?    func openDB() {        let documentDirectory = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first        let userPath = documentDirectory?.stringByAppendingPathComponent(path: "data10001")        if !FileManager.default.fileExists(atPath: userPath!) {            guard ((try? FileManager.default.createDirectory(atPath: userPath!, withIntermediateDirectories: false, attributes: nil)) != nil) else {                return            }        }        let fileName = userPath?.stringByAppendingPathComponent(path: "person.db")        dbQueue = FMDatabaseQueue(path: fileName)    }}extension String {    //给String扩展一个stringByAppendingPathComponent方法     func stringByAppendingPathComponent(path: String) -> String {        let nsString = self as NSString        return nsString.appendingPathComponent(path)    }}

每一张表写一个DAO(Data Access Object),写sql语句操作数据库,暴露接口,例如TABLE_LIST表的ListDAO的部分代码

/// 单例static let shareDAO = ListDAO()/// 创建表func creatTable() {    let sqlString = "CREATE TABLE IF NOT EXISTS TABLE_LIST('personId' Integer Integer PRIMARY KEY NOT NULL, 'name' Text, 'isTop' Integer)"  //字段的单引号可有可没有,添加主键防止重复添加    DBManager.shareManager.dbQueue?.inDatabase({ (db) in        guard (db?.executeUpdate(sqlString, withArgumentsIn: [])) != nil else {            return        }    })}/// 删除表func dropTable() {    let sqlString = "DROP TABLE TABLE_LIST"    DBManager.shareManager.dbQueue?.inDatabase({ (db) in        guard (db?.executeUpdate(sqlString, withArgumentsIn: [])) != nil else {            return        }    })}/// 插入数据func insertData(model: ListModel) {    let sqlString = "INSERT OR REPLACE INTO TABLE_LIST(personId, name, isTop) values (?,?,?)"    DBManager.shareManager.dbQueue?.inDatabase({ (db) in        guard (db?.executeUpdate(sqlString, withArgumentsIn: [model.personId, model.name, model.isTop]))! else {            return        }    })}/// 获取数据(已经置顶)func getDataList() -> Array<ListModel> {    var resultArray = Array<Any>()    DBManager.shareManager.dbQueue?.inDatabase({ (db) in        //无条件全量查找        //let sqlString = "SELECT * FROM TABLE_LIST";        //根据isTop字段降序输出        let sqlString = "SELECT * FROM TABLE_LIST ORDER BY isTop DESC";        guard let set = try? db?.executeQuery(sqlString, values: []) else {            return        }        while (set?.next())! {            let model = ListModel()            model.personId = Int((set?.int(forColumn: "personId"))!)            model.name = set?.string(forColumn: "name")            model.isTop = (set?.bool(forColumn: "isTop"))!            resultArray.append(model)        }        set?.close()    })    return resultArray as! Array<ListModel>}/// 删除单条数据func deleteDataOfDataList(personId: Int) -> Bool {    var result: Bool = false    DBManager.shareManager.dbQueue?.inDatabase({ (db) in        let sqlString = "DELETE FROM TABLE_LIST WHERE personId = ?"        result = (db?.executeUpdate(sqlString, withArgumentsIn: [personId]))!    })    return result}/// 置顶操作func setTopWithPersonId(personId: Int, isTop: Bool) -> Bool {    var result: Bool = false    DBManager.shareManager.dbQueue?.inDatabase({ (db) in        let sqlString = "UPDATE TABLE_LIST SET isTop = ? WHERE personId = ?"        result = (db?.executeUpdate(sqlString, withArgumentsIn: [isTop,personId]))!    })    return result}

TABLE_DETAIL表的DetailDAO的部分代码

/// 查询数据(全量获取)func getDataListWithString(string: String) -> Array<Any> {    var resultArray = Array<Any>()    DBManager.shareManager.dbQueue?.inDatabase({ (db) in        // LIKE '%Java%'查询des字段中包含Java的, LIKE 'Java*' 查询以Java开头的, '*Java查询以Java结尾的'        // 查询 TABLE_DETAIL 表中包含string的model        let sqlString = "SELECT * FROM TABLE_DETAIL WHERE des LIKE '%" + string + "%'"        guard let set = try? db?.executeQuery(sqlString, values: []) else {            return        }        while (set?.next())! {            let model = DetailModel()            model.desId = Int((set?.int(forColumn: "desId"))!)            model.des = set?.string(forColumn: "des")            resultArray.append(model)        }        set?.close()    })    return resultArray}/// 分页获取数据(分页读取,每次20条)func pageReadDataListWith(page: Int) -> Array<Any> {    var resultArray = Array<Any>()    DBManager.shareManager.dbQueue?.inDatabase({ (db) in        var sqlString = String()        if page != 1 {            // TABLE_DETAIL AS TD: 为TABLE_DETAIL起个别名TD            // TD.desId>\(page * 20): 查询TD.desId大于当前的数据            // ORDER BY TD.desId ASC: 根据TD.desId升序            // LIMIT 0,20: 每次获取20条数据            sqlString = "SELECT * FROM TABLE_DETAIL AS TD WHERE TD.desId>\(page * 20) ORDER BY TD.desId ASC LIMIT 0,20"        } else {            // ASC 升序(默认)  DESC 降序            sqlString = "SELECT * FROM TABLE_DETAIL AS TD ORDER BY TD.desId ASC LIMIT 1,20"        }        guard let set = try? db?.executeQuery(sqlString, values: []) else {            return        }        while (set?.next())! {            let model = DetailModel()            model.desId = Int((set?.int(forColumn: "desId"))!)            model.des = set?.string(forColumn: "des")            resultArray.append(model)        }        set?.close()    })    return resultArray}

DEMO效果

DEMO地址