NO IMAGE

介紹

sqlite是純C語言中底層的資料庫,在OC和Swift中都是經常使用的資料庫,在開發中,可以使用程式碼建立資料庫,可以使用圖形化介面建立資料庫。例如SQLiteManager、SQLiteStudio等

常用方法

方法名稱描述
OpaquePointer: *db資料庫控制代碼,跟檔案控制代碼FIFL類似,這裡是sqlite3指標
sqlite3_stmt: *stmt相當於ODBC的Command物件,用於儲存編譯好的SQL語句
sqlite3_open()開啟資料庫,沒有資料庫時建立
sqlite3_exec()執行非查詢的SQL語句
sqlite3_step()在呼叫sqlite3_prepare後,使用這個函式在記錄集中移動
sqlite3_close()關閉資料庫檔案
sqlite3_column_text()取text型別的資料
sqlite3_column_blob()取blob型別的資料
sqlite3_column_int()取int型別的資料

引入Sqlite

引入Sqlite資料庫的方式,網上有很多,我這裡就不在贅述了

具體操作

資料庫連線操作(包括資料庫的建立,表的建立,表的查詢,修改,刪除等操作)

class SQLiteConnect {
var db :OpaquePointer? = nil
let sqlitePath :String
init?(path :String) {
sqlitePath = path
db = self.openDatabase(sqlitePath)
if db == nil {
return nil
}
}
// 連結資料庫 connect database
func openDatabase(_ path :String) -> OpaquePointer? {
var connectdb: OpaquePointer? = nil
if sqlite3_open(path, &connectdb) == SQLITE_OK {
print("Successfully opened database \(path)")
return connectdb!
} else {
print("Unable to open database.")
return nil
}
}
// 建立資料表 create table
func createTable(_ tableName :String, columnsInfo :[String]) -> Bool {
let sql = "create table if not exists \(tableName) "
"(\(columnsInfo.joined(separator: ",")))"
if sqlite3_exec(self.db, sql.cString(using: String.Encoding.utf8), nil, nil, nil) == SQLITE_OK{
return true
}
return false
}
// 新增資料
func insert(_ tableName :String, rowInfo :[String:String]) -> Bool {
var statement :OpaquePointer? = nil
let sql = "insert into \(tableName) "
"(\(rowInfo.keys.joined(separator: ","))) "
"values (\(rowInfo.values.joined(separator: ",")))"
if sqlite3_prepare_v2(self.db, sql.cString(using: String.Encoding.utf8), -1, &statement, nil) == SQLITE_OK {
if sqlite3_step(statement) == SQLITE_DONE {
return true
}
sqlite3_finalize(statement)
}
return false
}
// 讀取資料
func fetch(_ tableName :String, cond :String?, order :String?) -> OpaquePointer {
var statement :OpaquePointer? = nil
var sql = "select * from \(tableName)"
if let condition = cond {
sql  = " where \(condition)"
}
if let orderBy = order {
sql  = " order by \(orderBy)"
}
sqlite3_prepare_v2(self.db, sql.cString(using: String.Encoding.utf8), -1, &statement, nil)
return statement!
}
// 更新資料
func update(_ tableName :String, cond :String?, rowInfo :[String:String]) -> Bool {
var statement :OpaquePointer? = nil
var sql = "update \(tableName) set "
// row info
var info :[String] = []
for (k, v) in rowInfo {
info.append("\(k) = \(v)")
}
sql  = info.joined(separator: ",")
// condition
if let condition = cond {
sql  = " where \(condition)"
}
if sqlite3_prepare_v2(self.db, sql.cString(using: String.Encoding.utf8), -1, &statement, nil) == SQLITE_OK {
if sqlite3_step(statement) == SQLITE_DONE {
return true
}
sqlite3_finalize(statement)
}
return false
}
// 刪除資料
func delete(_ tableName :String, cond :String?) -> Bool {
var statement :OpaquePointer? = nil
var sql = "delete from \(tableName)"
// condition
if let condition = cond {
sql  = " where \(condition)"
}
if sqlite3_prepare_v2(self.db, sql.cString(using: String.Encoding.utf8), -1, &statement, nil) == SQLITE_OK {
if sqlite3_step(statement) == SQLITE_DONE {
return true
}
sqlite3_finalize(statement)
}
return false
}
}

使用

var db :SQLiteConnect?
override func viewDidLoad() {
super.viewDidLoad()
// 資料庫檔案的路徑
let urls = FileManager.default.urls(for: .documentDirectory, in: .userDomainMask)
let sqlitePath = urls[urls.count-1].absoluteString   "sqlite3.db"
// 印出儲存檔案的位置
print(sqlitePath)
// SQLite 資料庫
db = SQLiteConnect(path: sqlitePath)
if let mydb = db {
// create table
let _ = mydb.createTable("students", columnsInfo: [
"id integer primary key autoincrement",
"name text",
"height double"])
// insert
let _ = mydb.insert("students", rowInfo: ["name":"'大強'","height":"178.2"])
// select
let statement = mydb.fetch("students", cond: "1 == 1", order: nil)
while sqlite3_step(statement) == SQLITE_ROW{
let id = sqlite3_column_int(statement, 0)
let name = String(cString: sqlite3_column_text(statement, 1))
let height = sqlite3_column_double(statement, 2)
print("\(id). \(name) 身高: \(height)")
}
sqlite3_finalize(statement)
// update
let _ = mydb.update("students", cond: "id = 1", rowInfo: ["name":"'小強'","height":"176.8"])
// delete
let _ = mydb.delete("students", cond: "id = 5")
}
}

使用sqlite.swift框架

import UIKit
import SQLite
struct SQLiteManager {
private var db: Connection!
private let users = Table("users") //表名
private let id = Expression<Int64>("id")      //主鍵
private let name = Expression<String>("name")  //列表1
private let email = Expression<String>("email") //列表2
init() {
createdsqlite3()
}
//建立資料庫檔案
mutating func createdsqlite3(filePath: String = "/Documents")  {
let sqlFilePath = NSHomeDirectory()   filePath   "/db.sqlite3"
do {
db = try Connection(sqlFilePath)
try db.run(users.create { t in
t.column(id, primaryKey: true)
t.column(name)
t.column(email, unique: true)
})
} catch { print(error) }
}
//插入資料
func insertData(_name: String, _email: String){
do {
let insert = users.insert(name <- _name, email <- _email)
try db.run(insert)
} catch {
print(error)
}
}
//讀取資料
func readData() -> [(id: String, name: String, email: String)] {
var userData = (id: "", name: "", email: "")
var userDataArr = [userData]
for user in try! db.prepare(users) {
userData.id = String(user[id])
userData.name = user[name]
userData.email = user
userDataArr.append(userData)
}
return userDataArr
}
//更新資料
func updateData(userId: Int64, old_name: String, new_name: String) {
let currUser = users.filter(id == userId)
do {
try db.run(currUser.update(name <- name.replace(old_name, with: new_name)))
} catch {
print(error)
}
}
//刪除資料
func delData(userId: Int64) {
let currUser = users.filter(id == userId)
do {
try db.run(currUser.delete())
} catch {
print(error)
}
}
}

使用:

let sqliteContext = SQLiteManager() //如果沒有,預設建立資料庫及表格
//插入資料
sqliteContext.insertData("username", _email: "[email protected]")
//讀取全部資料
//要想讀取指定資料,可以自己自定義轉換成其他模型,比如字典 元組,可以根據id查詢
let dataM = sqliteContext.readData()
print(dataM) 
//更新資料 1 -> 使用者id
sqliteContext.updateData(1, old_name: "oldValue", new_name: "newValue")
//刪除資料
sqliteContext.delData(1) // 1 -> 使用者id

參考資料

ios sqlite3的使用
Swift起步走
swift第三方框架之sqlite3
github的sqlite.swift