创建数据库管理类SQLiteManager
设置类方法创建单例对象
Swift 中单例对象可以直接输出定义的自身类内的成员变量.
class SQLiteManager: NSObject { static let instance = SQLiteManager() class func shareInstance() -> SQLiteManager { return instance }}
操作数据库
首先需要在项目中导入libsqlite3.tbd框架
SQLite3 框架是一套 C 语言的框架,直接在swift中使用首先需要添加桥接文件
在swift中使用OC/C++/C等文件都需要这个桥接文件.
创建完桥接头文件还需要将桥接头文件配置到项目中
然后就可以在swift项目中愉快的使用C语言的各种接口方法了.
打开数据库
开启数据库
class SQLiteManager: NSObject { static let instance = SQLiteManager() class func shareInstance() -> SQLiteManager { return instance } var db : OpaquePointer? = nil func openDB() -> Bool { let dicumentPath = NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory, FileManager.SearchPathDomainMask.userDomainMask, true).last let DBPath = (dicumentPath! as NSString).appendingPathComponent("appDB.sqlite") let cDBPath = DBPath.cString(using: String.Encoding.utf8) if sqlite3_open(cDBPath, &db) != SQLITE_OK { print("数据库打开失败") } return creatTable(); } func creatTable() -> Bool { let creatUserTable = "CREATE TABLE IF NOT EXISTS 't_User' ( 'ID' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'name' TEXT,'age' INTEGER,'icon' TEXT);" let creatCarTable = "CREATE TABLE IF NOT EXISTS 't_Car' ('ID' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,'type' TEXT,'output' REAL,'master' TEXT);" return creatTableExecSQL(SQL_ARR: [creatUserTable,creatCarTable]) } func creatTableExecSQL(SQL_ARR : [String]) -> Bool { for item in SQL_ARR { if execSQL(SQL: item) == false { return false } } return true } func execSQL(SQL : String) -> Bool { let cSQL = SQL.cString(using: String.Encoding.utf8) let errmsg : UnsafeMutablePointer<UnsafeMutablePointer<Int8>?>? = nil if sqlite3_exec(db, cSQL, nil, nil, errmsg) == SQLITE_OK { return true }else{ print("SQL 语句执行出错 -> 错误信息: 一般是SQL语句写错了 \(errmsg)") return false } }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
一般在app启动开启数据库并建表
func application(_ application: UIApplication, didFinishLaunchingWithOptions launchOptions: [UIApplicationLaunchOptionsKey: Any]?) -> Bool { // Override point for customization after application launch. //一般在程序启动时开始数据库并建表 if SQLiteManager.shareInstance().openDB() { print("开启数据库成功!") } return true }
数据库内SQL操作
func execSQL(SQL : String) -> Bool { let cSQL = SQL.cString(using: String.Encoding.utf8) let errmsg : UnsafeMutablePointer<UnsafeMutablePointer<Int8>?>? = nil if sqlite3_exec(db, cSQL, nil, nil, errmsg) == SQLITE_OK { return true }else{ print("SQL 语句执行出错 -> 错误信息: 一般是SQL语句写错了 \(errmsg)") return false } }
项目中的Model自定义对象可以自定义一个将自身插入数据库的方法
//将自身插入数据库接口 func insertSelfToDB() -> Bool { //插入SQL语句 let insertSQL = "INSERT INTO 't_User' (name,age,icon) VALUES ('\(name!)',\(age),'\(icon!)');" if SQLiteManager.shareInstance().execSQL(SQL: insertSQL) { print("插入数据成功") return true }else{ return false } }
模拟插入若干用户
for i in 1...7 { let name = "name_\(i)" let age = arc4random_uniform(18).hashValue + i let icon = "http://qiuxuewei.com/icon\(i).png" let user : User = User(name: name, age: age, icon: icon) if user.insertSelfToDB() { print("第 \(i) 个用户插入成功!") } } }
如果需要更新数据库对应表中数据,直接调用SQL执行方法即可实现
func changeIcon(newIcon : String) { let changeIconSQL = "UPDATE 't_User' SET icon='\(newIcon)' WHERE name='name_6'" if SQLiteManager.shareInstance().execSQL(SQL: changeIconSQL) { print("name_6 头像修改成功!") } }
查询数据库中对应表中所有数据
在SQLiteManager中封装一个类方法,可以直接传入SQL语句输出数据库中存储的数据
其中swift3中对 UnsafePointer 转 String 做了改动
let s = String(cString: yourCharPointer)
参考:http://stackoverflow.com/questions/39533320/swift-3-convert-a-null-terminated-unsafepointeruint8-to-a-string
func queryDBData(querySQL : String) -> [[String : AnyObject]]? { var stmt : OpaquePointer? = nil if querySQL.lengthOfBytes(using: String.Encoding.utf8) > 0 { let cQuerySQL = (querySQL.cString(using: String.Encoding.utf8))! if sqlite3_prepare_v2(db, cQuerySQL, -1, &stmt, nil) == SQLITE_OK { var queryDataArrM = [[String : AnyObject]]() while sqlite3_step(stmt) == SQLITE_ROW { let columnCount = sqlite3_column_count(stmt) var dict = [String : AnyObject]() for i in 0..<columnCount { let cKey = sqlite3_column_name(stmt, i) let key : String = String(validatingUTF8: cKey!)! let cValue = sqlite3_column_text(stmt, i) let value = String(cString:cValue!) dict[key] = value as AnyObject } queryDataArrM.append(dict) } return queryDataArrM } } return nil }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
在自定义模型中有必要定义个工厂方法可将数据库对应表中所有数据取出,以模型数组的形式输出
class func allUserFromDB() -> [User]? { let querySQL = "SELECT name,age,icon FROM 't_User'" let allUserDictArr = SQLiteManager.shareInstance().queryDBData(querySQL: querySQL) print(allUserDictArr) if let tempUserDictM = allUserDictArr { var userModelArrM = [User]() for dict in tempUserDictM { userModelArrM.append(User(dict: dict)) } return userModelArrM } return nil }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
当然,github已经上传源代码:https://github.com/qxuewei/Swift-test/tree/master/SQLite%E6%95%B0%E6%8D%AE%E5%BA%93%E6%93%8D%E4%BD%9C-Swift