iOS SQLite.swift使用说明

SQLite.swift使用说明

import UIKit
import SQLite

struct YXSQLiteManagerSearchTag {

    private var db: Connection!
    private let searchTag = Table("searchTag")
    let id = Expression<Int64>("id")
    let title = Expression<String?>("title")

    init() {
        connectSQLite3()  
    }
    // 与数据库建立连接
    mutating func connectSQLite3(filePath: String = "/Documents") -> Void {
        let sqlFilePath = NSHomeDirectory() + filePath + "/db.sqlite3"
        YXLog("sqlFilePath = \(sqlFilePath)")
        do { // 与数据库建立连接
            db = try Connection(sqlFilePath)
            YXLog("与数据库建立连接 成功")
        } catch {
            YXLog("与数据库建立连接 失败：\(error)")
        }
    }
    }

1、建表

func createTable() -> Bool {
        do { // 创建表
            try db.run(searchTag.create { st in
                st.column(id, primaryKey: true)
                st.column(title)
            })
            YXLog("创建表 searchTag 成功")
            return true
        } catch {
            YXLog("创建表 searchTag 失败：\(error)")
            return false
        }
    }

var  sqLite3 = YXSQLiteManagerSearchTag()    

等价于执行SQL语句

CREATE TABLE IF NOT EXISTS "searchTag" (
     "id" INTEGER PRIMARY KEY NOT NULL,
     "title" TEXT,
)

2、插入数据

func insert(_title: String){

        if delete(_title: _title) {

            let insert = searchTag.insert(title <- _title)
            do {
                let rowid = try db.run(insert)
                YXLog("插入数据成功 id: \(rowid)")
            } catch {
                YXLog("插入数据失败: \(error)")
            }
        }

    }

self.sqLite3.insert(_title: "titt")

等价于执行SQL语句

insert into searchTag (title) values('titt')

4、删除

func delete(_title: String) -> Bool {
        let item = searchTag.filter(title == _title)
        do {
            if try db.run(item.delete()) > 0 {
                YXLog("\(_title) 删除成功")
                return true
            } else {
                YXLog("没有发现 条目 \(_title)")
                return true
            }
        } catch {
            YXLog("\(_title) 删除失败：\(error)")
            return false
        }
    }

func delete() {
        for st in try! db.prepare(searchTag) {
            let currCity = searchTag.filter(title == st[title])
            do {
                try db.run(currCity.delete())
                YXLog("删除成功: \(st[id])")
            } catch {
                YXLog("删除失败: \(error)")
            }
        }
    }

sqLite3. delete(_title: "ttt")

self.sqLite3.delete()

等价于执行SQL语句

delete from searchTag where title = ttt
delete * FROM searchTag

5、读取

func readTable() -> [(id: String, title: String)] {
        var model = (id: "", title: "")
        var modelArr = [model]
        for st in try! db.prepare(searchTag) {
            model.id = String(st[id])
            model.title = st[title]!
            modelArr.append(model)
        }
        modelArr.remove(at: 0)
        return modelArr
    }

let queue = DispatchQueue.global()
                queue.sync {

                    for st in sqLite3.readTable() {
                        biggerTagListView.addTag(st.title)
                    }
                }
            }  

等价于执行SQL语句

select * from searchTag where title = ttt
select * from searchTag

6、修改数据

let update = users.filter(id == rowid)
try! db?.run(update.update(email <- email.replace("foxmail", with: "qq")))

for user in (try! db?.prepare(users.filter(name == "究极死胖兽")))! {
    print("Update:id: \(user[id]), name: \(user[name]), email: \(user[email])")
}

等价于执行SQL语句

update users set email=replace(email,'foxmail','qq') where id == 1
SELECT * FROM users where name='究极死胖兽'