FMDB
使用框架
官網(wǎng)地址
直接拖拽
- 將 fmdb 文件夾拖入項(xiàng)目
- 建立橋接文件
- 將
Swift extensions拖入項(xiàng)目 - 添加
libsqlite3.tdb
Podfile
- 不推薦
use_frameworks!
pod 'FMDB', :git => 'https://github.com/robertmryan/fmdb.git'
FMDB 架構(gòu)圖
Snip20160819_40.png
代碼演練
單例
- 單例代碼
/// 數(shù)據(jù)庫(kù)名稱
private let dbName = "readme.db"
/// 數(shù)據(jù)庫(kù)管理工具
class SQLiteManager {
static let sharedManager = SQLiteManager()
// 全局?jǐn)?shù)據(jù)庫(kù)隊(duì)列 - 支持線程操作
var queue: FMDatabaseQueue
private init() {
var path = NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true).last!
path = (path as NSString).stringByAppendingPathComponent(dbName)
print(path)
queue = FMDatabaseQueue(path: path)
createTable()
}
/// 創(chuàng)建數(shù)據(jù)表
private func createTable() {
let path = NSBundle.mainBundle().pathForResource("db.sql", ofType: nil)!
let sql = try! String(contentsOfFile: path)
queue.inTransaction { (db, _) in
if db.executeStatements(sql) {
print("創(chuàng)表成功")
} else {
print("創(chuàng)表失敗")
}
}
}
}
代碼小結(jié)
- 將數(shù)據(jù)庫(kù)名稱定義成常量，可以避免在
AppDelegate中打開數(shù)據(jù)庫(kù) -
init創(chuàng)建隊(duì)列并指定路徑- 如果數(shù)據(jù)庫(kù)不存在，會(huì)建立數(shù)據(jù)庫(kù)，然后打開
- 如果數(shù)據(jù)庫(kù)已經(jīng)存在，會(huì)直接打開
- 創(chuàng)表函數(shù)，可以把所有數(shù)據(jù)表的 SQL 保存在
db.sql中，直接通過(guò)executeStatements一次性將所有數(shù)據(jù)表建立 -
queue是 FMDB 提供的一個(gè)核心類，封裝了一個(gè)串行隊(duì)列，能夠?qū)崿F(xiàn)多線程數(shù)據(jù)操作，而且是線程安全的
插入數(shù)據(jù)
- 傳統(tǒng)方法
func demoInsert1() {
// 模擬 SQL 注入
let name = "王五"
let sql = "INSERT INTO T_Person (name, age, height) VALUES ('\(name)', 18, 1.8);"
print(sql)
SQLiteManager.sharedManager.queue.inDatabase { (db) -> Void in
if db.executeStatements(sql) {
print("插入成功")
}
}
}
存在的問(wèn)題：SQL 注入
- 修改 name 數(shù)值，演示 SQL 注入
let name = "王五', 18, 1.8); DELETE FROM T_Person; --"
- 使用預(yù)編譯指令插入數(shù)據(jù) - 綁定參數(shù)
func demoInsert2() {
let sql = "INSERT INTO T_Person (name, age, height) VALUES (?, ?, ?);"
SQLiteManager.sharedManager.queue.inDatabase { (db) -> Void in
if db.executeUpdate(sql, "張三1", 18, 1.7) {
print("插入成功 \(db.lastInsertRowId())")
} else {
print("插入失敗")
}
}
}
- 使用字典更新 SQL － FMDB 特有語(yǔ)法
func demoInsert3(dict: [String: AnyObject]) {
let sql = "INSERT INTO T_Person (name, age, height) VALUES (:name, :age, :height);"
SQLiteManager.sharedManager.queue.inDatabase { (db) -> Void in
if db.executeUpdate(sql, withParameterDictionary: dict) {
print("插入成功 \(db.lastInsertRowId())")
} else {
print("插入失敗")
}
}
}
刪除數(shù)據(jù)
func demoDelete(id: Int) {
let sql = "DELETE FROM T_Person WHERE id = ?;"
SQLiteManager.sharedManager.queue.inDatabase { (db) -> Void in
if db.executeUpdate(sql, id) {
print("刪除成功 \(db.changes())")
} else {
print("刪除失敗")
}
}
}
更新數(shù)據(jù)
func demoUpdate(dict: [String: AnyObject]) {
let sql = "UPDATE T_Person set name = :name, age = :age, height = :height WHERE id = :id;"
SQLiteManager.sharedManager.queue.inDatabase { (db) -> Void in
if db.executeUpdate(sql, withParameterDictionary: dict) {
print("更新成功 \(db.changes())")
} else {
print("更新失敗")
}
}
}
查詢數(shù)據(jù)
- 固定 SQL 的查詢方式
func persons1() {
let sql = "SELECT id, name, age, height FROM T_Person;"
SQLiteManager.sharedManager.queue.inDatabase { (db) -> Void in
guard let rs = db.executeQuery(sql) else {
return
}
while rs.next() {
let id = rs.intForColumn("id")
let name = rs.stringForColumn("name")
let age = rs.stringForColumn("age")
let height = rs.doubleForColumn("height")
print("\(id) \(name) \(age) \(height)")
}
}
}
- 動(dòng)態(tài) SQL 的查詢方式
func persons2() {
let sql = "SELECT id, name, age, height FROM T_Person;"
var result = [[String: AnyObject]]()
SQLiteManager.sharedManager.queue.inDatabase { (db) -> Void in
print(NSThread.currentThread())
guard let rs = db.executeQuery(sql) else {
return
}
while rs.next() {
// 0. 字典
var dict = [String: AnyObject]()
// 1. 列數(shù)
let colCount = rs.columnCount()
for col in 0..<colCount {
// 2. 列名
let colName = rs.columnNameForIndex(col)
// 3. 值
dict[colName] = rs.objectForColumnIndex(col)
}
result.append(dict)
}
}
print(result)
}
- 在
SQLiteManager中封裝查詢方法
/// 執(zhí)行 SQL 返回結(jié)果集合
///
/// - parameter sql: sql
func execRecordset(sql: String, finished: ([[String: AnyObject]]) -> ()) {
dispatch_async(dispatch_get_global_queue(0, 0)) { () -> Void in
var result = [[String: AnyObject]]()
self.queue.inDatabase { (db) -> Void in
guard let rs = db.executeQuery(sql) else {
dispatch_async(dispatch_get_main_queue()) { finished(result) }
return
}
while rs.next() {
// 0. 字典
var dict = [String: AnyObject]()
// 1. 列數(shù)
let colCount = rs.columnCount()
for col in 0..<colCount {
// 2. 列名
let colName = rs.columnNameForIndex(col)
// 3. 值
dict[colName] = rs.objectForColumnIndex(col)
}
result.append(dict)
}
}
dispatch_async(dispatch_get_main_queue()) { finished(result) }
}
}
批量插入數(shù)據(jù)
func manyPersons() {
let sql = "INSERT INTO T_Person (name, age, height) VALUES (:name, :age, :height);"
let start = CACurrentMediaTime()
print("開始")
SQLiteManager.sharedManager.queue.inTransaction { (db, rollback) -> Void in
for i in 0..<10000 {
let age = 10 + random() % 10
let height = 1.5 + Double(random() % 5) / 10
let dict = ["name": "zhangsan - \(i)", "age": age, "height": height]
if !db.executeUpdate(sql, withParameterDictionary: dict as [NSObject : AnyObject]) {
rollback.memory = true
break
}
// 模擬失敗
if i == 1000 {
rollback.memory = true
break
}
}
}
print("完成 \(CACurrentMediaTime() - start)")
}
FMDB 主要函數(shù)
- 執(zhí)行單條語(yǔ)句
executeUpdate - 查詢
executeQuery - 執(zhí)行多條語(yǔ)句
executeStatements，創(chuàng)表時(shí)使用
要設(shè)置 rollBack 可以使用
rollBack.memory = true
