swift使用SQLite本地数据库
2019-02-12 本文已影响0人
夏至樱花祭
SQLite是一个轻量级的本地数据，使用简单。但是操作的代码全都是自己手动输入，容易出错。
封装一个类来操作数据库
class EISSQLiteManager: NSObject {
private static let manager: EISSQLiteManager = EISSQLiteManager()
//单例
class func shareManager() -> EISSQLiteManager{
return manager
}
//数据库对象
private var db:OpaquePointer? = nil
func openDB(sqliteName:String){
//0.拿到数据库的路径
let path = sqliteName.docDir()
// print(path)
let cPath = path.cString(using: String.Encoding.utf8)
//1.需要代开的数据库的路径 c语言的字符串
//2.打开之后的数据库对象（指针）,以后所有的数据库操作，都必须拿到这个指针才能进行相关操作
if sqlite3_open(cPath, &db) != SQLITE_OK{
print("数据库打开失败")
return
}
}
func closeDB() -> Void {
sqlite3_close(db)
}
func creatTable(sql:String) -> Bool
{
// 1.编写SQL语句
// 建议: 在开发中编写SQL语句, 如果语句过长, 不要写在一行
// 开发技巧: 在做数据库开发时, 如果遇到错误, 可以先将SQL打印出来, 拷贝到PC工具中验证之后再进行调试
// print(sql)
// 2.执行SQL语句
return execSQL(sql: sql)
}
func execSQL(sql: String) -> Bool
{
// 0.将Swift字符串转换为C语言字符串
let cSQL = sql.cString(using: String.Encoding.utf8)!
// 在SQLite3中, 除了查询以外(创建/删除/新增/更新)都使用同一个函数
/*
1. 已经打开的数据库对象
2. 需要执行的SQL语句, C语言字符串
3. 执行SQL语句之后的回调, 一般传nil
4. 是第三个参数的第一个参数, 一般传nil
5. 错误信息, 一般传nil
*/
if sqlite3_exec(db, cSQL, nil, nil, nil) != SQLITE_OK
{
return false
}
return true
}
/// 执行 SQL 返回查询结果集
///
/// - parameter sql: 任意给定的 SELETE 查询 SQL
func execRecordSet(sql: String) -> [[String: AnyObject]]? {
// 1. 预编译 SQL
/**
参数
1. 已经打开的数据库句柄
2. 要执行的 SQL
3. 以字节为单位的 SQL 最大长度，传入 -1 会自动计算
4. SQL 语句句柄
- 后续针对当前查询结果的操作全部基于此句柄
- 需要调用 sqlite3_finalize 释放
5. 未使用的指针地址，通常传入 nil
*/
var stmt: OpaquePointer? = nil
if sqlite3_prepare_v2(db, sql, -1, &stmt, nil) != SQLITE_OK {
print("SQL 错误\n")
sqlite3_finalize(stmt)
if (sqlite3_errmsg(self.db)) != nil {
let msg = "SQLiteDB - failed to prepare SQL:\(sql)"
print(msg)
}
return nil
}
// 创建结果数组
var rows = [[String: AnyObject]]()
// 2. 遍历集合
while sqlite3_step(stmt) == SQLITE_ROW {
// 将单条记录字典添加到结果数组中
rows.append(record(stmt: stmt!))
}
// 3. 释放语句句柄 - 很重要，否则会内容泄漏
sqlite3_finalize(stmt)
// 4. 返回结果数组
return rows
}
/// 从 stmt 中获取当前记录的完整内容
///
/// - parameter stmt: stmt 句柄
private func record(stmt: OpaquePointer) -> [String: AnyObject] {
// 1. 获取查询结果列数
let colCount = sqlite3_column_count(stmt)
// 单条记录字典
var row = [String: AnyObject]()
// 2. 遍历所有列，获取每一列的信息
for col in 0..<colCount {
// 1> 获取列名
let cName = sqlite3_column_name(stmt, col)
let name = String(cString: cName!, encoding: String.Encoding.utf8)
// 2> 获取每列数据类型
let type = sqlite3_column_type(stmt, col)
// 3> 根据数据类型获取对应结果
var value: AnyObject?
switch(type) {
case SQLITE_FLOAT:
value = sqlite3_column_double(stmt, col) as AnyObject
case SQLITE_INTEGER:
value = Int(sqlite3_column_int64(stmt, col)) as AnyObject
case SQLITE3_TEXT:
// let cText = UnsafePointer<Int8>(sqlite3_column_text(stmt, col))
let cText = sqlite3_column_text(stmt, col).withMemoryRebound(to: Int8.self, capacity: 1, { ( ptr:UnsafePointer<Int8>) -> UnsafePointer<Int8> in
return ptr
})
value = String(cString: cText, encoding: String.Encoding.utf8) as AnyObject
case SQLITE_NULL:
value = NSNull()
default:
print("不支持的数据类型")
}
// print("列名 \(name) 值 \(value)")
row[name!] = value ?? NSNull()
}
return row
}
//删除数据
func deleteUser(sql: 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
}
//更新数据
func updateUser(sql: 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
}
}
使用代码：创建数据表
func creatDB() {
EISSQLiteManager.shareManager().openDB(sqliteName: "device.sqlite")
//设备表
let creatSql = "CREATE TABLE IF NOT EXISTS T_Device(id INTEGER PRIMARY KEY AUTOINCREMENT," +
"CheckTaskGuid TEXT," +
"FireDeviceGuid TEXT," +
"GroupGuid TEXT," +
"DeviceName TEXT, " +
"Code TEXT," +
"LocationDescription TEXT, " +
"FireDeviceStatusName TEXT," +
"QRCode TEXT," +
"Description TEXT," +
"CheckRsult INTEGER," +
"FireDeviceCheckStatus INTEGER," +
"Longitude Double," +
"Latitude Double," +
"SignTime TEXT" +
"); \n"
//文件表
let creatFileSql = "CREATE TABLE IF NOT EXISTS T_RecordFile(id INTEGER PRIMARY KEY AUTOINCREMENT," +
"CheckTaskGuid TEXT," +
"FireDeviceGuid TEXT," +
"Type INTEGER," +
"FilePath TEXT," +
"URL TEXT" +
"); \n"
if EISSQLiteManager.shareManager().creatTable(sql: creatSql) {
print("创建设备表成功！！！！！")
}else{
print("创建设备表失败！！！！！！")
}
if EISSQLiteManager.shareManager().creatTable(sql: creatFileSql) {
print("创建文件表成功！！！！！")
}else{
print("创建文件表失败！！！！！！")
}
EISSQLiteManager.shareManager().closeDB()
}
添加数据
EISSQLiteManager.shareManager().openDB(sqliteName: "device.sqlite")
let insertSql = "INSERT into T_Device(" + "FireDeviceGuid," +
"CheckTaskGuid," +
"GroupGuid," +
"DeviceName," +
"Code," +
"LocationDescription," +
"FireDeviceStatusName," +
"Description," +
"CheckRsult," +
"FireDeviceCheckStatus," +
"Longitude," +
"Latitude," +
"SignTime) " +
"VALUES ('\(Model.FireDeviceGuid ?? "" )'," +
"'\(self.taskModel?.CheckTaskGuid ?? "")'," +
"'\(self.GroupGuid ?? "")'," +
"'\(Model.DeviceName ?? "")'," +
"'\(Model.Code ?? "")'," +
"'\(Model.LocationDescription ?? "")'," +
"'\(Model.FireDeviceStatusName ?? "")'," +
"'\(Description)'," +
"\(CheckRsult)," +
"\(CheckRsult)," +
"\(self.recordLocation?.coordinate.longitude ?? 0)," +
"\(self.recordLocation?.coordinate.latitude ?? 0)," +
"'\(signTime)')"
if EISSQLiteManager.shareManager().execSQL(sql: insertSql) {
print("添加设备数据表成功！！！！！")
}else{
print("添加设备数据表失败？？？？？")
}
EISSQLiteManager.shareManager().closeDB()
删除数据
EISSQLiteManager.shareManager().openDB(sqliteName: "device.sqlite")
let deleteRecordSql = "delete from T_RecordFile where FireDeviceGuid = '\(self.valueString ?? "")' and CheckTaskGuid = '\(self.taskModel?.CheckTaskGuid ?? "")'"
if EISSQLiteManager.shareManager().execSQL(sql: deleteRecordSql) {
print("删除T_RecordFile数据表成功！！！！！")
}else{
print("删除T_RecordFile数据表失败？？？？？")
}
let deleteDeviceSql = "delete from T_Device where FireDeviceGuid = '\(self.valueString ?? "")' and CheckTaskGuid = '\(self.taskModel?.CheckTaskGuid ?? "")'"
if EISSQLiteManager.shareManager().execSQL(sql: deleteDeviceSql) {
print("删除T_Device数据表成功！！！！！")
}else{
print("删除T_Device数据表失败？？？？？")
}
EISSQLiteManager.shareManager().closeDB()
修改数据
//修改数据
EISSQLiteManager.shareManager().openDB(sqliteName: "device.sqlite")
let uptateSql = "UPDATE T_Device set Description = '\(Description)'," +
"CheckRsult = \(CheckRsult)," +
"FireDeviceCheckStatus = \(CheckRsult)," +
"Longitude = \(self.recordLocation?.coordinate.longitude ?? 0)," +
"Latitude = \(self.recordLocation?.coordinate.latitude ?? 0)," +
"SignTime = '\(signTime)," +
" where FireDeviceGuid = '\(self.valueString!)' and CheckTaskGuid = '\(self.taskModel?.CheckTaskGuid! ?? "")';"
if EISSQLiteManager.shareManager().execSQL(sql: uptateSql) {
print("修改数据表成功！！！！！")
}else{
print("修改数据表失败？？？？？")
}
EISSQLiteManager.shareManager().closeDB()
