SQLite3是嵌入到ios中的关系型数据库。对存储大规模的数据非常实用，使得不必将每个对象加到内存中。
支持NULL、INTEGER、REAL（浮点数字）、TEXT（字符串和文本）、BLOB（二进制对象）数据类型。
1、通过Firefox中的SQLite Manager组件创建一个数据库文件，并在其中创建创建一个Table，添加相关的参数。
2、将创建好的文件推到Xcode项目中，并在Link Binary With Libraries中添加libsqlite3.dylib依赖库。
3、新建一个C语言类型文件，对话框会自动添加桥接文件，在文件中添加语句#import<sqlite3.h>。
4、然后创建一个DataBaseOperations.swift文件来管理数据库。
在DataBaseOperation.swift文件中，申明一个数据库指针。
//不透明指针，对应C语言里面的void *，这里指sqlite3指针
private var db:COpaquePointer = nil
创建一个初始化方法，在初始化方法中执行打开数据库操作。
//初始化方法打开数据库
required init(dbPath:String)
{
print("db path:" + dbPath) //String类的路径，转换成cString
let cpath = dbPath.cStringUsingEncoding(NSUTF8StringEncoding) //打开数据库
let error = sqlite3_open(cpath!, &db) //数据库打开失败处理
if error != SQLITE_OK {
sqlite3_close(db)
}
}
deinit{
self.colseDb()
}
//关闭数据库
func colseDb(){
sqlite3_close(db)
}
//代码创建表
func createTable() -> Bool{ //sql语句
let sql = "CREATE TABLE UserTable(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, username TEXT NOT NULL, password TEXT NOT NULL, email TEXT, age INTEGER)" //执行sql语句
let execResult = sqlite3_exec(db, sql.cStringUsingEncoding(NSUTF8StringEncoding)!, nil, nil, nil); //判断是否执行成功
if (execResult != SQLITE_OK) {
return false
} return true
}
//插入一条信息
func addUser(user: Person) -> Bool
{
//sql语句
let sql = "INSERT INTO UserTable (username, password, email, age) VALUES (?, ?, ?, ?);";
//sql语句转换成cString类型 let cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding) //sqlite3_stmt 指针
var stmt:COpaquePointer = nil //1.编译sql
let prepare_result = sqlite3_prepare_v2(self.db, cSql!, -, &stmt, nil) //判断如果失败，获取失败信息
if prepare_result != SQLITE_OK {
sqlite3_finalize(stmt)
if let error = String.fromCString(sqlite3_errmsg(self.db)) {
let msg = "SQLiteDB - failed to prepare SQL: \(sql), Error: \(error)"
print(msg)
self.alert(msg)
}
return false
} //2.bind 绑定参数
//第2个参数：索引从1开始
//最后一个参数为函数指针
sqlite3_bind_text(stmt, , user.name!.cStringUsingEncoding(NSUTF8StringEncoding)!, -, nil);
sqlite3_bind_text(stmt, , user.password!.cStringUsingEncoding(NSUTF8StringEncoding)!, -, nil);
sqlite3_bind_text(stmt, , user.email!.cStringUsingEncoding(NSUTF8StringEncoding)!, -, nil);
sqlite3_bind_int(stmt, , CInt(user.age!)); //3.step执行
let step_result = sqlite3_step(stmt) //判断执行结果，如果失败，获取失败信息
if step_result != SQLITE_OK && step_result != SQLITE_DONE {
sqlite3_finalize(stmt)
if let err = String.fromCString(sqlite3_errmsg(self.db)) {
let msg = "SQLiteDB - failed to execute SQL: \(sql), Error: \(err)"
print(msg)
self.alert(msg)
}
return false
} //4.finalize
sqlite3_finalize(stmt); return true
}
//查询
func readAllUsers() -> [Person]{ //声明一个Person对象数组（查询的信息会添加到该数组）
var usersArr = [Person]() //查询sql语句
let sql = "SELECT * FROM UserTable;"; //sqlite3_stmt 指针
var stmt:COpaquePointer = nil
let cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding) //1.编译sql
let prepare_result = sqlite3_prepare_v2(self.db, cSql!, -, &stmt, nil)
if prepare_result != SQLITE_OK {
sqlite3_finalize(stmt)
if let error = String.fromCString(sqlite3_errmsg(self.db)) {
let msg = "SQLiteDB - failed to prepare SQL: \(sql), Error: \(error)"
print(msg)
self.alert(msg)
}
return usersArr
} //2.step
while (sqlite3_step(stmt) == SQLITE_ROW) {
let user = Person() //循环 从数据库获取数据，添加到数组中
let cName = UnsafePointer<CChar>(sqlite3_column_text(stmt, ))
let cPwd = UnsafePointer<CChar>(sqlite3_column_text(stmt, ))
let cEmail = UnsafePointer<CChar>(sqlite3_column_text(stmt, ))
let cAge = sqlite3_column_int(stmt, ) user.name = String.fromCString(cName)
user.password = String.fromCString(cPwd)
user.email = String.fromCString(cEmail)
user.age = Int(cAge) usersArr += [user]
} //3.finalize
sqlite3_finalize(stmt); return usersArr
}
//更新一条信息
func updateUser(name: String , toName:String) -> Bool
{
//更新sql语句
let sql = "update UserTable set username = '\(toName)' where username = '\(name)'"; //sqlite3_stmt 指针
var stmt:COpaquePointer = nil
let cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding) //1.编译sql
let prepare_result = sqlite3_prepare_v2(self.db, cSql!, -, &stmt, nil) //判断如果失败，获取失败信息
if prepare_result != SQLITE_OK {
sqlite3_finalize(stmt)
if let error = String.fromCString(sqlite3_errmsg(self.db)) {
let msg = "SQLiteDB - failed to prepare SQL: \(sql), Error: \(error)"
print(msg)
self.alert(msg)
}
return false
} //2.step执行
let step_result = sqlite3_step(stmt) //判断执行结果，如果失败，获取失败信息
if step_result != SQLITE_OK && step_result != SQLITE_DONE {
sqlite3_finalize(stmt)
if let err = String.fromCString(sqlite3_errmsg(self.db)) {
let msg = "SQLiteDB - failed to execute SQL: \(sql), Error: \(err)"
print(msg)
self.alert(msg)
}
return false
} //4.finalize
sqlite3_finalize(stmt); return true
}
//删除一条信息
func deleteUser(username: String) -> Bool
{
//删除sql语句
let sql = "delete from UserTable where username = '\(username)'"; //sqlite3_stmt 指针
var stmt:COpaquePointer = nil
let cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding) //1.编译sql
let prepare_result = sqlite3_prepare_v2(self.db, cSql!, -, &stmt, nil) //判断如果失败，获取失败信息
if prepare_result != SQLITE_OK {
sqlite3_finalize(stmt)
if let error = String.fromCString(sqlite3_errmsg(self.db)) {
let msg = "SQLiteDB - failed to prepare SQL: \(sql), Error: \(error)"
print(msg)
self.alert(msg)
}
return false
} //3.step执行
let step_result = sqlite3_step(stmt) //判断执行结果，如果失败，获取失败信息
if step_result != SQLITE_OK && step_result != SQLITE_DONE {
sqlite3_finalize(stmt)
if let err = String.fromCString(sqlite3_errmsg(self.db)) {
let msg = "SQLiteDB - failed to execute SQL: \(sql), Error: \(err)"
print(msg)
self.alert(msg)
}
return false
} //4.finalize
sqlite3_finalize(stmt); return true
}
//定义一个报警器
func alert(msg:String) {
dispatch_async(dispatch_get_main_queue()) {
let alert = UIAlertView(title: "SQLiteDB", message:msg, delegate: nil, cancelButtonTitle: "OK")
alert.show()
}
}
因为工程数据库文件打包之后，会在NSBundle.mainBundle()路径下，该路径是只读的，不允许修改，所以必须把该路径下的数据库拷贝一份到Documents路径下，以后整个工程都将操作Documents路径下的数据库。
在ViewController中，添加一下代码。
//声明一个Documents下的路径
let dbPath = NSHomeDirectory() + "/Documents/RWDataTest.sqlite" //判断数据库文件是否存在
if !NSFileManager.defaultManager().fileExistsAtPath(dbPath)
{
//获取安装包内数据库路径
let bundleDBPath = NSBundle.mainBundle().pathForResource("RWDataTest", ofType: "sqlite")! //将安装包内数据库拷贝到Documents目录下
do
{
try NSFileManager.defaultManager().copyItemAtPath(bundleDBPath, toPath: dbPath)
}
catch let error as NSError {
print(error)//如果创建失败，error 会返回错误信息
}
}
//打开数据库
let dbOperation = DatabaseOperations(dbPath: dbPath) //添加一张表
dbOperation.createTable(); //插入一条信息， 通过Person对象来传值
let person:Person = Person(name: "刘明洋", pwd: "liumingyang", email: "liumingyang@leadingdo.com", age: ) dbOperation.addUser(person) //查询
let personArray:[Person] = dbOperation.readAllUsers()
print("共搜索到：\(personArray.count) 条数据" ) //更新
dbOperation.updateUser("刘明洋", toName: "刘蕙通") //删除
dbOperation.deleteUser("刘蕙通") //关闭数据库
dbOperation.colseDb()