準(zhǔn)備步驟
- 在項目界面,進(jìn)入Build Phases面板,選擇Link Binary With Libraries --> + --> Add Other... -->
Command+Shift+g
-->輸入/usr/lib
--> 選擇libsqlite3.dylib - 添加一個頭文件xxx.h到創(chuàng)建的項目中,打開此文件,輸入
#import "sqlite3.h"
- 在項目界面,進(jìn)入Build Setting選項,找到Objective-C Bridging Header,將其設(shè)置為添加的頭文件xxx.h
示例demo
var db:OpaquePointer? = nil
@IBOutlet weak var name: UITextField!
@IBOutlet weak var address: UITextField!
@IBOutlet weak var age: UITextField!
Paste_Image.png
創(chuàng)建數(shù)據(jù)庫
@IBAction func createdatabse(_ sender: AnyObject) {
let dirPaths = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)
let docsDir = dirPaths[0] as NSString
let databasePath = NSString(string:docsDir.appendingPathComponent("Match.db"))
//判斷數(shù)據(jù)庫是否打開
if (sqlite3_open(databasePath.utf8String, &db) != SQLITE_OK){
print("數(shù)據(jù)庫打開失敗")
}
print("數(shù)據(jù)庫路徑是:'\(databasePath as String)'")
sqlite3_close(db)
}
- 使用sqlite3_open函數(shù)打開數(shù)據(jù)庫
- 使用sqlite3_exec函數(shù)執(zhí)行語句
- 使用sqlite3_close函數(shù)釋放資源
創(chuàng)建表
func execSql(_ sql:NSString) {
if sqlite3_exec(db, sql.utf8String, nil, nil, nil) != SQLITE_OK {
let errmsg = String(cString:sqlite3_errmsg(db))
print("error creating table :\(errmsg)")
}
}
@IBAction func createtable(_ sender: AnyObject) {
let dirPaths = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)
let docsDir = dirPaths[0] as NSString
let databasePath = NSString(string:docsDir.appendingPathComponent("Match.db"))
//判斷數(shù)據(jù)庫是否打開
if (sqlite3_open(databasePath.utf8String, &db) != SQLITE_OK){
print("數(shù)據(jù)庫打開失敗")
} else {
let sqlCreateTable:NSString = NSString(string:"CREATE TABLE IF NOT EXISTS match (name TEXT, address TEXT , age INTEGER)")
execSql(sqlCreateTable)
print("創(chuàng)建數(shù)據(jù)庫成功")
}
sqlite3_close(db)
}
插入數(shù)據(jù)
@IBAction func insertData(_ sender: AnyObject) {
let dirPaths = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)
let docsDir = dirPaths[0] as NSString
let databasePath = NSString(string:docsDir.appendingPathComponent("Match.db"))
if (sqlite3_open(databasePath.utf8String, &db) != SQLITE_OK){
print("數(shù)據(jù)庫打開失敗")
} else {
let namef = name.text!
let addressf = address.text!
let agef = Int(age.text!)!
let sql :NSString = NSString(string: "INSERT INTO match VALUES ('\(namef)','\(addressf)','\(agef)')")
self.execSql(sql)
print("插入數(shù)據(jù)成功")
}
sqlite3_close(db)
}
查詢數(shù)據(jù)
@IBAction func search(_ sender: AnyObject) {
let dirPaths = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)
let docsDir = dirPaths[0] as NSString
let databasePath = NSString(string:docsDir.appendingPathComponent("Match.db"))
var canshu = 33 //查詢條件里的整型變量,在本示例暫時固定
var canshustr = "ttt" //查詢條件里的字符串變量,在本示例暫時固定
var statement:OpaquePointer? = nil
if (sqlite3_open(databasePath.utf8String, &db) != SQLITE_OK){
print("數(shù)據(jù)庫打開失敗")
} else {
print("數(shù)據(jù)庫打開成功")
let sql = "SELECT * FROM match where name = ? and age = ?"
//let sql = "SELECT * FROM match where age = ?" //單個條件
//let sql = "SELECT * FROM match" //無查詢條件
let cSql = sql.cString(using: String.Encoding.utf8) //轉(zhuǎn)換為C接受的char*類型
//預(yù)處理過程
if sqlite3_prepare_v2(db, cSql!, -1, &statement, nil) == SQLITE_OK {
print("進(jìn)入預(yù)處理過程")
//綁定參數(shù)開始
sqlite3_bind_text(statement,1,canshustr,-1,nil) //字符串參數(shù)
sqlite3_bind_int(statement,2,Int32(canshu)) //整型參數(shù)
var strname = "a" //簡單初始化
var straddress = "b"
var strage = 54
//執(zhí)行查詢
while (sqlite3_step(statement) == SQLITE_ROW) {
print("開始查詢")
//提取字段數(shù)據(jù)
if let bufzhudui = UnsafeRawPointer.init(sqlite3_column_text(statement, 0)) {
let uptr1 = bufzhudui.bindMemory(to:CChar.self, capacity:0)
strname = String(validatingUTF8:uptr1)!
}
if let bufdate = UnsafeRawPointer.init(sqlite3_column_text(statement, 1)) {
let uptr2 = bufdate.bindMemory(to:CChar.self, capacity:0)
straddress = String(validatingUTF8:uptr2)!
}
strage = Int(sqlite3_column_int(statement,2))
print("name:'\(strname)'")
print("address is :'\(straddress)")
print("age is : " + String(strage) )
}
print("查詢結(jié)束")
sqlite3_finalize(statement) //釋放語句對象
}
}
sqlite3_close(db) //關(guān)閉數(shù)據(jù)庫
}
- 使用sqlite3_open函數(shù)打開數(shù)據(jù)庫
- sqlite3_prepare_v2預(yù)處理sql語句 (將sql編譯成二進(jìn)制代碼,提高sql語句的執(zhí)行速度.)
- sqlite3_bind_text綁定參數(shù)(前提:有查詢條件)
- sqlite3_step 執(zhí)行sql語句,遍歷結(jié)果集
- sqlite3_column_text 提取字段數(shù)據(jù)
- sqlite3_finalize和sqlite3_close釋放資源