介绍
一、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类型的数据；
三、使用SQLiteStudio创建数据库，然后导出到桌面，再拖到项目中，最后通过代码拷贝到Documens下进行操作，并获取数据库路径
Person.swift
// Person.swift
// swiftDemo
//
// Created by 夏远全 on 2017/2/20.
// Copyright © 2017年 夏远全. All rights reserved.
// import UIKit class Person: NSObject { var name:String?
var password:String?
var email:String?
var age:Int?
}
DatabaseOperation.swift
(1)打开数据库
//不透明指针，对应C语言中的void *,这里指sqlit3指针
private var db:OpaquePointer? = nil //初始化方法打开数据库
required init(dbPath:String) {
print("db path:" + dbPath) //String类的路径，转换成cString
let cpath = dbPath.cString(using: .utf8) //打开数据库
let error = sqlite3_open(cpath!,&db) //数据库打开失败
if error != SQLITE_OK {
sqlite3_close(db)
}
}
(2)关闭数据库
//关闭数据库
deinit {
self.closeDB()
}
func closeDB() -> Void {
sqlite3_close(db)
}
(3)创建表
//创建表
func creatTable() -> Bool {
//sql语句
let sql = "CREATE TABLE UserTable(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,username TEXT NOT NULL, password TEXT NOT NULL,email TEXT NOT NULL,age INTEGER)" //执行sql语句
let excuResult = sqlite3_exec(db, sql.cString(using: .utf8), nil, nil, nil) //判断是否执行成功
if excuResult != SQLITE_OK {
return false
}
return true
}
(4)插入数据
//插入数据
func addUser(user:Person) -> Bool { //sql语句
let sql = "INSERT INTO UserTable(username,password,email,age) VALUES(?,?,?,?);"; //sql语句转换成cString类型
let cSql = sql.cString(using: .utf8) //sqlite3_stmt指针
var stmt:OpaquePointer? = nil //编译
let prepare_result = sqlite3_prepare_v2(self.db, cSql!, -1, &stmt, nil) //判断如果失败，获取失败信息
if prepare_result != SQLITE_OK {
sqlite3_finalize(stmt)
if (sqlite3_errmsg(self.db)) != nil {
let msg = "SQLiteDB - failed to prepare SQL:\(sql)"
print(msg)
}
return false
} //绑定参数
//第二个参数，索引从1开始
//最后一个参数为函数指针
sqlite3_bind_text(stmt, 1, user.name!.cString(using: .utf8), -1, nil)
sqlite3_bind_text(stmt, 2, user.password!.cString(using: .utf8), -1, nil)
sqlite3_bind_text(stmt, 3, user.email!.cString(using: .utf8), -1, nil)
sqlite3_bind_int(stmt, 4, Int32(Int(user.age!))) //step执行
let step_result = sqlite3_step(stmt) //判断执行结果
if step_result != SQLITE_OK && step_result != SQLITE_DONE {
sqlite3_finalize(stmt)
if (sqlite3_errmsg(self.db)) != nil {
let msg = "SQLiteDB - failed to execute SQL:\(sql)"
print(msg)
}
return false
} //finalize
sqlite3_finalize(stmt) return true
}
(5)查询数据
//查询数据
func readAllUsers() -> [Person] { //声明一个Person对象数组（查询的信息会添加到数组中）
var userArr = [Person]() //查询sql语句
let sql = "SELECT * FROM UserTable;"; //sqlite3_stmt指针
var stmt:OpaquePointer? = nil
let cSql = sql.cString(using: .utf8) //编译
let prepare_result = sqlite3_prepare_v2(self.db, cSql!, -1, &stmt, nil)
if prepare_result != SQLITE_OK {
sqlite3_finalize(stmt)
if (sqlite3_errmsg(self.db)) != nil {
let msg = "SQLiteDB - failed to prepare SQL:\(sql)"
print(msg)
}
} //step
while (sqlite3_step(stmt) == SQLITE_ROW) {
let user = Person() //循环从数据库数据，添加到数组
let cName = UnsafePointer(sqlite3_column_text(stmt, 0))
let cPwd = UnsafePointer(sqlite3_column_text(stmt, 1))
let cEmail = UnsafePointer(sqlite3_column_text(stmt, 2))
let cAge = sqlite3_column_int(stmt, 3) user.name = String.init(cString: cName!)
user.password = String.init(cString: cPwd!)
user.email = String.init(cString: cEmail!)
user.age = Int(cAge) userArr += [user]
} //finalize
sqlite3_finalize(stmt) return userArr
}
(6)更新数据
//更新数据
func updateUser(name:String,toName:String) -> Bool { //更新sql语句
let sql = "update UserTable set username = '\(toName)' where username = '\(name)'"; //sqlite3_stmt指针
var stmt:OpaquePointer? = nil
let cSql = sql.cString(using: .utf8) //编译sql
let prepare_result = sqlite3_prepare_v2(self.db, cSql!, -1, &stmt, nil) //判断如果失败，获取失败信息
if prepare_result != SQLITE_OK {
sqlite3_finalize(stmt)
if (sqlite3_errmsg(self.db)) != nil {
let msg = "SQLiteDB - failed to prepare SQL:\(sql)"
print(msg)
}
return false
} //step执行
let step_result = sqlite3_step(stmt) //判断执行结果，如果失败，获取失败信息
if step_result != SQLITE_OK && step_result != SQLITE_DONE {
sqlite3_finalize(stmt)
if (sqlite3_errmsg(self.db)) != nil {
let msg = "SQLiteDB - failed to execute SQL:\(sql)"
print(msg)
}
return false
} //finalize
sqlite3_finalize(stmt) return true
}
(7)删除数据
//删除数据
func deleteUser(username:String) -> Bool { //删除sql语句
let sql = "delete from UserTable where username = '\(username)'"; //sqlite3_stmt指针
var stmt:OpaquePointer? = nil
let cSql = sql.cString(using: .utf8) //编译sql
let prepare_result = sqlite3_prepare_v2(self.db, cSql!, -1, &stmt, nil) //判断如果失败，获取失败信息
if prepare_result != SQLITE_OK {
sqlite3_finalize(stmt)
if (sqlite3_errmsg(self.db)) != nil {
let msg = "SQLiteDB - failed to prepare SQL:\(sql)"
print(msg)
}
return false
} //step执行
let step_result = sqlite3_step(stmt) //判断执行结果，如果失败，获取失败信息
if step_result != SQLITE_OK && step_result != SQLITE_DONE {
sqlite3_finalize(stmt)
if (sqlite3_errmsg(self.db)) != nil {
let msg = "SQLiteDB - failed to execute SQL:\(sql)"
print(msg)
}
return false
} //finalize
sqlite3_finalize(stmt) return true
}
(8)复制数据库路径
//将Bundle.main路径下的数据库文件复制到Documents下
class func loadDBPath() -> String { //声明一个Documents下的路径
let dbPath = NSHomeDirectory() + "/Documents/RWDataTest.db" //判断数据库文件是否存在
if !FileManager.default.fileExists(atPath: dbPath) { //获取安装包内是否存在
let bundleDBPath = Bundle.main.path(forResource: "RWDataTest", ofType:"db")! //将安装包内的数据库到Documents目录下
do {
try FileManager.default.copyItem(atPath: bundleDBPath, toPath: dbPath)
} catch let error as NSError {
print(error)
}
} return dbPath
}
ViewController.swift测试
// Created by 夏远全 on 2017/1/13.
// Copyright © 2017年 夏远全. All rights reserved.
// import UIKit class ViewController: UIViewController { override func viewDidLoad() {
super.viewDidLoad() //打开数据库
let path = DatabaseOperations.loadDBPath()
let dbOpearion = DatabaseOperations.init(dbPath:path)
print(path) //添加一张表
let person:Person = Person()
person.name = "张三"
person.password = "123566"
person.email = "zhangsan@163.com"
person.age = 30 //插入一条信息,通过Person对象来传值
let addBool = dbOpearion.addUser(user: person)
print(addBool) //查询
let personArray:[Person] = dbOpearion.readAllUsers()
print("共搜索到:\(personArray.count) 条数据") //更新
let updateBool = dbOpearion.updateUser(name: "张三", toName: "李四")
print(updateBool) //删除
let deleteBool = dbOpearion.deleteUser(username: "李四")
print(deleteBool) //关闭数据库
dbOpearion.closeDB()
}
}