SQLite集成FMDB

使用前先导入libsqlite3.0.tbd

1.什么是FMDB?

FMDB是iOS平台的SQLite数据库框架

FMDB以OC的方式封装了SQLite的C语言API

2.FMDB有什么优势?

使用起来更加面向对象，省去了很多麻烦、冗余的C语言代码

提供了多线程安全的数据库操作方法，有效地防止数据混乱

3.核心类

FMDatabase
    一个FMDatabase对象就代表一个单独的SQLite数据库
    用来执行SQL语句

FMResultSet
    使用FMDatabase执行查询后的结果集

FMDatabaseQueue
    用于在多线程中执行多个查询或更新，它是线程安全的

FMDB的基本使用

import Foundation

class SQLiteTool: NSObject {

    static let shareInstance = SQLiteTool()

    var db: FMDatabase?
    override init() {
        super.init()

        // 打开, 创建一个数据库
        db = FMDatabase(path: "/Users/xiaomage/Desktop/db/demo.sqlite")

        // 打开数据库
        if db!.open() {
            print("数据库打开成功")
            createTable()
        }else {
            print("数据库打开失败")
        }
    }

    func createTable() -> () {
        let sql = "create table if not exists t_stu(id integer primary key autoincrement, name text not null, age integer, score real default 60)"

        let result = db?.executeUpdate(sql, withArgumentsInArray: nil)
        if result! {
            print("yes")
        }else {
            print("No")
        }
    }

    func insertRecord() -> () {
        let sql = "insert into t_stu(name, age, score) values('zhangsan', 15, 88)"
        let result = db?.executeUpdate(sql, withArgumentsInArray: nil)
        if result! {
            print("yes")
        }else {
            print("No")
        }
    }

    func queryAll() -> () {
        let sql = "select * from t_stu"
        guard let resultSet = db?.executeQuery(sql, withArgumentsInArray: nil) else {return}

        while resultSet.next() {
            let name = resultSet.stringForColumn("name")
            let age = resultSet.intForColumn("age")
            let score = resultSet.doubleForColumn("score")
            print(name, age, score)
        }

    }

    func executeStaments() -> () {

        let sql = "insert into t_stu(name, age, score) values('zhangsan', 15, 88);insert into t_stu(name, age, score) values('zhangsan2', 152, 881)"
        let result = db?.executeStatements(sql)
        if result! {
            print("多条语句执行成功")
        }else {
            print("失败")
        }
    }
}

FMDB的高级使用

FMDatabase这个类是线程不安全的，如果在多个线程中同时使用一个FMDatabase实例，会造成数据混乱等问题

为了保证线程安全，FMDB提供方便快捷的FMDatabaseQueue类

import Foundation

class SQLiteTool: NSObject {

    static let shareInstance = SQLiteTool()

    var dbQueue: FMDatabaseQueue = FMDatabaseQueue(path: "/Users/xiaomage/Desktop/db/demo.sqlite")

    func createTable() -> () {
        let sql = "create table if not exists t_stu(id integer primary key autoincrement, name text not null, age integer, score real default 60)"
        dbQueue.inDatabase { (db: FMDatabase!) in
            let result = db.executeUpdate(sql, withArgumentsInArray: nil)
            if result {
                print("yes")
            }else {
                print("No")
            }

        }
   }

    //事务
    func transaction() -> () {
        let sql = "insert into t_stu(name, age, score) values('lisi', 5, 8)"
        let sql2 = "insert into t_stu2(name, age, score) values('lisi2', 52, 82)"

        dbQueue.inTransaction { (db: FMDatabase!, rollback) in

            let result1 = db.executeUpdate(sql, withArgumentsInArray: nil)
            let result2 = db.executeUpdate(sql2, withArgumentsInArray: nil)

            if result1 && result2 {

            }else {
                rollback.memory = true
            }
        }
    }
}