12-(掌握)代碼實(shí)現(xiàn)SQLite-DDL
- 創(chuàng)建一個Swift項(xiàng)目
- 導(dǎo)入系統(tǒng)框架
sqlite3.tbd(sqlite3.dylib)
在Xcode->build Phases->Link Binary With Libraries - 建立橋接文件, 導(dǎo)入頭文件
sqlite3.h- 新建一個.h 頭文件
- 設(shè)置為橋接文件
- 4.代碼實(shí)現(xiàn)
1. 打開數(shù)據(jù)庫
// 1. 打開數(shù)據(jù)庫
let docDir: String! = NSSearchPathForDirectoriesInDomains(NSSearchPathDirectory.DocumentDirectory, NSSearchPathDomainMask.UserDomainMask, true).first
// SQlite3數(shù)據(jù)庫文件的擴(kuò)展名沒有一個標(biāo)準(zhǔn)定義涕癣，比較流行的選擇是.sqlite3唱逢、.db哈误、.db3
let fileName: String = docDir + "/demo.sqlite"
/**
* sqlite3_open 使用這個函數(shù)打開一個數(shù)據(jù)庫
* 參數(shù)一: 需要打開的數(shù)據(jù)庫文件路徑
* 參數(shù)二: 一個指向SQlite3數(shù)據(jù)結(jié)構(gòu)的指針, 到時候操作數(shù)據(jù)庫都需要使用這個對象
* 功能作用: 如果需要打開數(shù)據(jù)庫文件路徑不存在, 就會創(chuàng)建該文件;如果存在, 就直接打開; 可通過返回值, 查看是否打開成功
*/
if sqlite3_open(fileName, &db) != SQLITE_OK {
print("打開數(shù)據(jù)庫失敗")
}else {
print("打開數(shù)據(jù)庫成功")
}
- 使用打開的數(shù)據(jù)庫, 執(zhí)行DDL語句, 創(chuàng)建一個數(shù)據(jù)庫表
// 創(chuàng)建SQL語句
let sql = "CREATE TABLE IF NOT EXISTS t_student (name TEXT, age INTEGER, score text, id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT)"
// 執(zhí)行SQL語句
// 參數(shù)一: 數(shù)據(jù)庫
// 參數(shù)二: 需要執(zhí)行的SQL語句
// 參數(shù)三: 回調(diào)結(jié)果, 執(zhí)行完畢之后的回調(diào)函數(shù), 如果不需要置為NULL
// 參數(shù)四: 參數(shù)三的第一個參數(shù), 刻意通過這個傳值給回調(diào)函數(shù) 如果不需要置為NULL
// 參數(shù)五: 錯誤信息, 通過傳遞一個地址, 賦值給外界, 如果不需要置為NULL
if sqlite3_exec(db, sql, nil, nil, nil) != SQLITE_OK
{
print("創(chuàng)建表失敗")
}else
{
print("創(chuàng)建表成功")
}
4.3. 使用打開的數(shù)據(jù)庫, 執(zhí)行DDL語句, 刪除一個數(shù)據(jù)庫表
let sql = "DROP TABLE IF EXISTS t_student2"
if sqlite3_exec(db, sql, nil, nil, nil) != SQLITE_OK
{
print("刪除表失敗")
}else
{
print("刪除表成功")
}
4.4. 將數(shù)據(jù)庫操作封裝成一個工具類
class SqliteTool: NSObject {
static let shareTool = SqliteTool()
var db : OpaquePointer? = nil
override init() {
super.init()
let path = "/Users/luosu/Downloads/11資料/09實(shí)用技術(shù)學(xué)習(xí)/練習(xí)/sql/demo.sqlite"
if sqlite3_open(path, &db) == SQLITE_OK
{
print("執(zhí)行成功")
}
else
{
print("執(zhí)行失敗")
}
}
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)"
if execute(sql: sql) {
print("yes")
}
}
func dropTable()
{
let sql = "drop table if exists t_stu"
if execute(sql: sql) {
print("yes")
}
}
func execute(sql:String) -> Bool {
return (sqlite3_exec(db, sql, nil, nil, nil) == SQLITE_OK)
}
}
13-(掌握)代碼實(shí)現(xiàn)DML語句-Insert
- 13.1. 創(chuàng)建一個Student類
屬性
name
age
構(gòu)造方法
init(name: String, age: Int)
class Student: NSObject {
var name : String = ""
var age :Int = 0
var score: Float = 0.0
init(name:String, age: Int, score:Float) {
super.init()
self.name = name
self.age = age
self.score = score
}
///添加學(xué)生
class func inertStudent(stu: Student) {
let sql = "insert into t_stu(name, age, score) values('\(stu.name)', \(stu.age), \(stu.score))"
if SqliteTool.shareTool.execute(sql: sql)
{
print("添加學(xué)生成功")
}
}
///刪除學(xué)生
static func deleteStudent(name:String)
{
let sql = "delete from t_stu where name = '\(name)'"
if SqliteTool.shareTool.execute(sql: sql)
{
print("刪除學(xué)生成功")
}
}
class func alterStudent(newStu:Student)
{
let sql = "update t_stu set name= '\(newStu.name)' , age= \(newStu.age), score = \(newStu.score) where name = '\(newStu.name)'"
if SqliteTool.shareTool.execute(sql: sql)
{
print("修改學(xué)生成功")
}
}
}
- 13.2. 創(chuàng)建數(shù)據(jù)庫操作方法
數(shù)據(jù)庫中, 對Student對象的操作封裝
insertStudent()
14-(了解)代碼實(shí)現(xiàn)DML語句-Insert綁定參數(shù)
- 準(zhǔn)備語句（prepared statement）對象
準(zhǔn)備語句（prepared statement）對象一個代表一個簡單SQL語句對象的實(shí)例烤镐，這個對象通常被稱為“準(zhǔn)備語句”或者“編譯好的SQL語句”或者就直接稱為“語句”洁闰。 - 操作歷程
- 使用
sqlite3_prepare_v2或相關(guān)的函數(shù)創(chuàng)建這個對象
如果執(zhí)行成功筐付，則返回SQLITE_OK辩尊，否則返回一個錯誤碼 - 使用
sqlite3_bind_*()給宿主參數(shù)（host parameters）綁定值
sqlite3_bind_text()
參數(shù)1:準(zhǔn)備語句
參數(shù)2:綁定的參數(shù)索引 (從1開始)
參數(shù)3:綁定的參數(shù)內(nèi)容
參數(shù)4:綁定的參數(shù)長度 (-1代表自動計(jì)算長度)
參數(shù)5:參數(shù)的處理方式
SQLITE_TRANSIENT 會對字符串做一個 copy只锻，SQLite 選擇合適的機(jī)會釋放
SQLITE_STATIC / nil 把它當(dāng)做全局靜態(tài)變量, 不會字符串做任何處理，如果字符串被釋放樱溉，保存到數(shù)據(jù)庫的內(nèi)容可能不正確挣输！
注意: swift中沒有宏的概念
// 替換 sqlite3.h 中的宏
private let SQLITE_TRANSIENT = unsafeBitCast(-1, sqlite3_destructor_type.self) - 通過調(diào)用
sqlite3_step()一次或多次來執(zhí)行這個sql
對于DML語句, 如果執(zhí)行成功, 返回SQLITE_DONE
對于DQL語句, 通過多次執(zhí)行獲取結(jié)果集, 繼續(xù)執(zhí)行的條件是返回值 SQLITE_ROW - 使用
sqlite3_reset()重置這個語句，然后回到第2步福贞，這個過程做0次或多次 - 使用
sqlite3_finalize()銷毀這個對象, 防止內(nèi)存泄露
class func binInserStudent(){
let sql = "insert into t_stu(name, age, score) values(?, ?, ?)"
//1.使用sqlite3_prepare_v2或相關(guān)的函數(shù)創(chuàng)建這個對象
//參數(shù)3去除字符串的長度
//參數(shù)4 預(yù)處理的語句
//參數(shù)5撩嚼，參數(shù)3取出之后剩余的參數(shù)
var stmt :OpaquePointer? = nil
if sqlite3_prepare_v2(SqliteTool.shareTool.db, sql, -1, &stmt, nil) != SQLITE_OK
{
print("創(chuàng)建準(zhǔn)備語句失敗")
}
//2.使用sqlite3_bind_*()給宿主參數(shù)（host parameters）綁定值
//參數(shù)1 準(zhǔn)備語句
//參數(shù)2 綁定值的索引
//參數(shù)3，需要綁定的值
sqlite3_bind_int(stmt, 2, 33)
sqlite3_bind_double(stmt, 3, 67.5)
let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
sqlite3_bind_text(stmt, 1, "lilei", -1, SQLITE_TRANSIENT)
//3.通過調(diào)用`sqlite3_step() `一次或多次來執(zhí)行這個sql
if sqlite3_step(stmt) == SQLITE_DONE {
print("執(zhí)行成功")
}
//4.使用sqlite3_reset()重置這個語句挖帘，然后回到第2步
sqlite3_reset(stmt)
//5.使用sqlite3_finalize()銷毀這個對象, 防止內(nèi)存泄露
sqlite3_finalize(stmt)
}
上面這種方式比原始的效率更高完丽，但是把第一步和第五步分解出來，循環(huán)中間的3步
優(yōu)化方案拇舀，如果使用的是sqlite3_exec或者是sqlite3——step這種方式執(zhí)行語句逻族，他會自動開啟一個事務(wù)，然后自動提交這個事務(wù)
解決方案：我們要手動開始和關(guān)閉事務(wù)骄崩，這時候函數(shù)內(nèi)部就不會自動開始和提交事務(wù)
經(jīng)過計(jì)算可得 普通的插入1000條數(shù)據(jù)大概16是聘鳞，bind虛幻2、3要拂、4步大概4秒抠璃，而手動開啟和關(guān)閉事務(wù)之需要0.02秒
class func binInserStudent(){
let sql = "insert into t_stu(name, age, score) values(?, ?, ?)"
//1.使用sqlite3_prepare_v2或相關(guān)的函數(shù)創(chuàng)建這個對象
//參數(shù)3去除字符串的長度
//參數(shù)4 預(yù)處理的語句
//參數(shù)5，參數(shù)3取出之后剩余的參數(shù)
var stmt :OpaquePointer? = nil
if sqlite3_prepare_v2(SqliteTool.shareTool.db, sql, -1, &stmt, nil) != SQLITE_OK
{
print("創(chuàng)建準(zhǔn)備語句失敗")
}
SqliteTool.shareTool.beginTransaction()
for _ in 0..<1000
{
//2.使用sqlite3_bind_*()給宿主參數(shù)（host parameters）綁定值
//參數(shù)1 準(zhǔn)備語句
//參數(shù)2 綁定值的索引
//參數(shù)3脱惰，需要綁定的值
sqlite3_bind_int(stmt, 2, 33)
sqlite3_bind_double(stmt, 3, 67.5)
let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
sqlite3_bind_text(stmt, 1, "lilei", -1, SQLITE_TRANSIENT)
//3.通過調(diào)用sqlite3_step() 一次或多次來執(zhí)行這個sql
if sqlite3_step(stmt) == SQLITE_DONE {
print("執(zhí)行成功")
}
//4.使用sqlite3_reset()重置這個語句搏嗡，然后回到第2步
sqlite3_reset(stmt)
}
SqliteTool.shareTool.commitTransaction()
//5.使用sqlite3_finalize()銷毀這個對象, 防止內(nèi)存泄露
sqlite3_finalize(stmt)
}
15-(掌握代碼實(shí)現(xiàn))DML語句-Insert插入數(shù)據(jù)優(yōu)化
- 測試方式
循環(huán)插入10000條數(shù)據(jù), 查看耗時 - 測試步驟
sqlite_exec直接執(zhí)行
未拆解"準(zhǔn)備語句" 執(zhí)行
拆解后的"準(zhǔn)備語句" 執(zhí)行 - 手動開啟事務(wù)后:
sqlite_exec 直接執(zhí)行
拆解后的"準(zhǔn)備語句" 執(zhí)行
CFAbsoluteTimeGetCurrent()獲取當(dāng)前時間對應(yīng)的秒數(shù) - 測試結(jié)果
1. 插入10000條數(shù)據(jù), sqlite_exec 直接執(zhí)行, 未拆解"準(zhǔn)備語句" 執(zhí)行, 拆解后的"準(zhǔn)備語句" 執(zhí)行, 分別耗時
sqlite_exec直接執(zhí)行:5.8080689907074
未拆解"準(zhǔn)備語句" 執(zhí)行:5.93309998512268
拆解后的"準(zhǔn)備語句" 執(zhí)行:4.91254101991653 - 結(jié)果分析
sqlite_exec直接執(zhí)行 和 未拆解"準(zhǔn)備語句" 執(zhí)行 執(zhí)行時間平均差不多
sqlite_exec函數(shù)是對"準(zhǔn)備語句"的封裝 (預(yù)處理語句->綁定參數(shù)->執(zhí)行語句->重置語句->釋放語句)
拆解后的"準(zhǔn)備語句" 執(zhí)行, 效率明顯高了一些
主要原因是真正遵循了"準(zhǔn)備語句"的操作流程
雖然按步驟使用"準(zhǔn)備語句", 但是執(zhí)行效率,依然非常低, 達(dá)到了5秒左右, 不可原諒 -
原因分析
每當(dāng)SQL調(diào)用執(zhí)行方法執(zhí)行一個語句時, 都會開啟一個叫做"事務(wù)"的東西, 執(zhí)行完畢之后再提交"事務(wù)";
也就是說, 如果執(zhí)行了10000次SQL語句, 就打開和提交了10000次"事務(wù)", 所以造成耗時嚴(yán)重 - 解決方案
只要在執(zhí)行多個SQL語句之前, 手動開啟事務(wù), 在執(zhí)行完畢之后, 手動提交事務(wù), 這樣 再調(diào)用SQL方法執(zhí)行語句時, 就不會再自動開啟和提交事務(wù) - 優(yōu)化后結(jié)果
插入10000條數(shù)據(jù), 大概耗時0.07秒 - 得出結(jié)論
- 如果插入大量數(shù)據(jù), 請務(wù)必手動開啟/提交事務(wù)
- 根據(jù)不同情況, 選擇使用
sqlite3_exec或者 "準(zhǔn)備語句"
單條語句, 前者, 因?yàn)槭褂煤唵?br> 大批量插入, 選擇后者
16-(掌握)代碼實(shí)現(xiàn)-事務(wù)
- 概念
事務(wù)（Transaction）是并發(fā)控制的單位，是用戶定義的一個操作序列。這些操作要么都做采盒，要么都不做旧乞，是一個不可分割的工作單位。通過事務(wù)纽甘，可以將邏輯相關(guān)的一組操作綁定在一起良蛮，保持?jǐn)?shù)據(jù)的完整性。
事務(wù)通常是以BEGIN TRANSACTION開始悍赢，以COMMIT TRANSACTION或ROLLBACK TRANSACTION結(jié)束决瞳。
COMMIT表示提交，即提交事務(wù)的所有操作左权。具體地說就是將事務(wù)中所有對數(shù)據(jù)庫的更新寫回到磁盤上的物理數(shù)據(jù)庫中去皮胡，事務(wù)正常結(jié)束。
ROLLBACK表示回滾赏迟，即在事務(wù)運(yùn)行的過程中發(fā)生了某種故障屡贺，事務(wù)不能繼續(xù)進(jìn)行，系統(tǒng)將事務(wù)中對數(shù)據(jù)庫的所有以完成的操作全部撤消锌杀，滾回到事務(wù)開始的狀態(tài)甩栈。 - 測試
修改兩條記錄, 一個成功, 一個失敗測試
XMGSQLTool.shareInstance.beginTransaction()
let result1 = Student.updateStudent("score = score - 10", condition: "name = 'zs'")
let result2 = Student.updateStudent("score1 = score + 10", condition: "name = 'wex'")
// 如果都執(zhí)行成功再提交, 如果都不成功, 那就回滾
if result1 && result2
{
XMGSQLTool.shareInstance.commitTransaction()
}else
{
XMGSQLTool.shareInstance.rollBackTransaction()
}
//tool
func beginTransaction()
{
let sql = "begin transaction"
if execute(sql: sql) {
print("事務(wù)開起成功")
}
}
func commitTransaction()
{
let sql = "commit transaction"
if execute(sql: sql) {
print("事務(wù)提交成功")
}
}
func rollBackTransaction()
{
let sql = "rollback transaction"
if execute(sql: sql) {
print("事務(wù)回轉(zhuǎn)")
}
}
17-(掌握)代碼實(shí)現(xiàn)DQL語句
sql返回結(jié)果會產(chǎn)生一個集合，所有他會執(zhí)行返回代碼塊參數(shù)
- 方式1: sqlite3_exec
作用: 可以通過回調(diào)來獲取結(jié)果, 步驟相對來說簡單, 結(jié)果數(shù)據(jù)類型沒有特定類型(id)
這里返回的數(shù)據(jù)沒有類型糕再，都是按照字符串來進(jìn)行輸出的
參數(shù)說明
// 參數(shù)1: 一個打開的數(shù)據(jù)庫
// 參數(shù)2: 需要執(zhí)行的SQL語句
// 參數(shù)3: 查詢結(jié)果回調(diào)(執(zhí)行0次或多次)
參數(shù)1: 參數(shù)4的值
參數(shù)2: 列的個數(shù)
參數(shù)3: 結(jié)果值的數(shù)組
參數(shù)4: 所有列的名稱數(shù)組
返回值: 0代表繼續(xù)執(zhí)行一致到結(jié)束, 1代表執(zhí)行一次
// 參數(shù)4: 回調(diào)函數(shù)的第一個值
// 參數(shù)5: 錯誤信息
demo
class func queryAll()
{
let sql = "select * from t_stu"
let db = SqliteTool.shareTool.db
//參數(shù)1 要打開的數(shù)據(jù)庫
//參數(shù)2 要執(zhí)行的語句
//參數(shù)3量没，callback
//參數(shù)1:傳遞過來的
//參數(shù)2 ：列的個數(shù)
//參數(shù)3:值的數(shù)組
//參數(shù)4:列名稱數(shù)組
let result = sqlite3_exec(db, sql,{(firstValue, columnCount, values, columnNames)->Int32 in
let count = Int(columnCount)
for i in 0..<count {
//列的名稱
let columnName = columnNames![i]
let columnNameStr = String(cString: columnName!)
let columnStr = String(cString: columnName!, encoding: String.Encoding.utf8)
let value = values![i]
let valueStr = String(cString: value!)
print(columnNameStr, valueStr)
}
return 0
}, nil, nil)
print(result)
if result == SQLITE_OK {
print("查詢學(xué)生信息成功")
}
else
{
print("查詢學(xué)生信息失敗")
}
}
- 方式2: 通過"準(zhǔn)備語句"
作用: 可以處理不同特定類型, 步驟相對來說復(fù)雜 - 步驟:
2.1. 預(yù)處理函數(shù) 獲取"準(zhǔn)備語句"
sqlite3_prepare
2.2. 不斷執(zhí)行"準(zhǔn)備語句", 直到無結(jié)果集
while sqlite3_step(stmt) == SQLITE_ROW
2.3. 獲取列的類型
sqlite3_column_type
2.4. 根據(jù)每列的類型取出不同的值
sqlite3_column_int64
SQLITE_INTEGER
sqlite3_column_double
SQLITE_FLOAT
sqlite3_column_text
SQLITE3_TEXT
需要轉(zhuǎn)換下字符串
let cText = UnsafePointer<Int8>(sqlite3_column_text(stmt, col))
let text = String(CString: cText, encoding: NSUTF8StringEncoding)
NSNull()
SQLITE_NULL
2.5. 釋放資源
sqlite3_finalize`
demo
class func queryAllStmt(){
//1.創(chuàng)建stmt語句
let sql = "select * from t_stu"
//參數(shù)1：查詢的數(shù)據(jù)哭
//參數(shù)2:執(zhí)行的sql語句
//參數(shù)3:對參數(shù)的處理-1默認(rèn)自定哦
//參數(shù)4:生成stmt語句地址
//參數(shù)5:參數(shù)3 設(shè)置完參數(shù)的剩余參數(shù)
var stmt : OpaquePointer? = nil
if sqlite3_prepare_v2(SqliteTool.shareTool.db, sql, -1, &stmt, nil) != SQLITE_OK
{
print("準(zhǔn)備語句失敗")
}
//2.綁定類型(如果用不到，可以不寫)
//3. 執(zhí)行step
while sqlite3_step(stmt) == SQLITE_ROW // 表示還有其他的行
{
//1.計(jì)算預(yù)處理語句里面獲取的結(jié)果一共列的數(shù)量
let columnCount = sqlite3_column_count(stmt)
for i in 0..<columnCount {
// 2.取出列的名稱
let columnName = sqlite3_column_name(stmt, i)
let columnNameStr = String(cString: columnName!, encoding: .utf8)
print(columnNameStr!)
//3.取出列的值
//不同的數(shù)據(jù)類型突想，需要執(zhí)行不同函數(shù)來獲取
//3.1獲取每列的數(shù)據(jù)類型
let columnType = sqlite3_column_type(stmt, i)
//3.2 根據(jù)不同的數(shù)據(jù)類型殴蹄，使用不同的函數(shù)，獲取結(jié)果
if columnType == SQLITE_INTEGER
{
let value = sqlite3_column_int(stmt, i)
print(value)
}
if columnType == SQLITE_FLOAT {
let value = sqlite3_column_double(stmt, i)
print(value)
}
if columnType == SQLITE_TEXT
{
let value = sqlite3_column_text(stmt, i)
//let valueInt8 = UnsafePointer<CChar>(value)
let valueStr = String(cString: value!)
print(valueStr)
}
}
}
//4.重置語句
sqlite3_reset(stmt)
//5.釋放資源
sqlite3_finalize(stmt)
}
使用預(yù)處理語句來操作可以方便拿到對應(yīng)列的類型猾担，比如插入的時候袭灯，我們可以綁定二進(jìn)制的數(shù)據(jù)，但是如果是sql語句直接執(zhí)行绑嘹，無法綁定二進(jìn)制數(shù)據(jù)稽荧，因?yàn)樗J(rèn)會當(dāng)成字符串來處理。
