准备步骤
- 在项目界面,进入Build Phases面板,选择Link Binary With Libraries --> + --> Add Other... -->
Command+Shift+g-->输入/usr/lib--> 选择libsqlite3.dylib - 添加一个头文件xxx.h到创建的项目中,打开此文件,输入
#import "sqlite3.h" - 在项目界面,进入Build Setting选项,找到Objective-C Bridging Header,将其设置为添加的头文件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
创建数据库
@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"))
//判断数据库是否打开
if (sqlite3_open(databasePath.utf8String, &db) != SQLITE_OK){
print("数据库打开失败")
}
print("数据库路径是:'\(databasePath as String)'")
sqlite3_close(db)
}
- 使用sqlite3_open函数打开数据库
- 使用sqlite3_exec函数执行语句
- 使用sqlite3_close函数释放资源
创建表
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"))
//判断数据库是否打开
if (sqlite3_open(databasePath.utf8String, &db) != SQLITE_OK){
print("数据库打开失败")
} else {
let sqlCreateTable:NSString = NSString(string:"CREATE TABLE IF NOT EXISTS match (name TEXT, address TEXT , age INTEGER)")
execSql(sqlCreateTable)
print("创建数据库成功")
}
sqlite3_close(db)
}
插入数据
@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("数据库打开失败")
} 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("插入数据成功")
}
sqlite3_close(db)
}
查询数据
@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("数据库打开失败")
} else {
print("数据库打开成功")
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) //转换为C接受的char*类型
//预处理过程
if sqlite3_prepare_v2(db, cSql!, -1, &statement, nil) == SQLITE_OK {
print("进入预处理过程")
//绑定参数开始
sqlite3_bind_text(statement,1,canshustr,-1,nil) //字符串参数
sqlite3_bind_int(statement,2,Int32(canshu)) //整型参数
var strname = "a" //简单初始化
var straddress = "b"
var strage = 54
//执行查询
while (sqlite3_step(statement) == SQLITE_ROW) {
print("开始查询")
//提取字段数据
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("查询结束")
sqlite3_finalize(statement) //释放语句对象
}
}
sqlite3_close(db) //关闭数据库
}
- 使用sqlite3_open函数打开数据库
- sqlite3_prepare_v2预处理sql语句 (将sql编译成二进制代码,提高sql语句的执行速度.)
- sqlite3_bind_text绑定参数(前提:有查询条件)
- sqlite3_step 执行sql语句,遍历结果集
- sqlite3_column_text 提取字段数据
- sqlite3_finalize和sqlite3_close释放资源
