ios swift sqlite3 简单使用

一.创建Person类

  1. import UIKit
  2. class Person: NSObject {
  3. var name:String? = nil
  4. var sex:String? = nil
  5. var age:String? = nil
  6. override func setValue(_ value: Any?, forUndefinedKey key: String) {
  7. }
  8. }

二.在使用的地方导入sqlite.3头文件,例如方法都在viewcontroller中使用

  1. import UIKit
  2. import SQLite3

三.viewcontroller数据sqlite的增,删,查,等方法

  1. //打开数据库表
  2. func opneDB() -> Bool {
  3. let docDir:String! = NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory, FileManager.SearchPathDomainMask.userDomainMask, true).first
  4. let fileName:String! = docDir + "/demo1.sqlite"
  5. if sqlite3_open(fileName, &db) != SQLITE_OK {
  6. print("打开数据库失败")
  7. return false
  8. } else {
  9. print("打开数据库成功")
  10. return true
  11. }
  12. }
  13. //创建数据库表
  14. func creatTable() ->Bool {
  15. let sql = "CREATE TABLE persontable(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,name TEXT NOT NULL, age TEXT NOT NULL,sex TEXT NOT NULL);"
  16. // 第1个参数是 数据库db 第2个是要执行的sql 第3,4个是回调 第5个是错误信息
  17. let result = sqlite3_exec(db, sql, nil, nil, nil)
  18. return result == SQLITE_OK
  19. }
  20. // 插入数据
  21. func insert(nameText:String, ageText:String, sexText:String) -> Bool{
  22. let sql = "insert into persontable(name,age,sex) VALUES(?,?,?);"
  23. var stmt: OpaquePointer?
  24. let prepareResult = sqlite3_prepare_v2(db, sql, -1, &stmt, nil)
  25. if prepareResult != SQLITE_OK {
  26. sqlite3_finalize(stmt)
  27. if let msg = sqlite3_errmsg(db) {
  28. print("insert prepare 发生错误: \(msg)")
  29. }
  30. return false
  31. }
  32. let name = (nameText as NSString).utf8String
  33. let age = (ageText as NSString).utf8String
  34. let sex = (sexText as NSString).utf8String
  35. sqlite3_bind_text(stmt, 1, name, -1, nil)
  36. sqlite3_bind_text(stmt, 2, age, -1, nil)
  37. sqlite3_bind_text(stmt, 3, sex, -1, nil)
  38. let stepResult = sqlite3_step(stmt)
  39. if stepResult != SQLITE_OK && stepResult != SQLITE_DONE {
  40. sqlite3_finalize(stmt)
  41. if let msg = sqlite3_errmsg(db) {
  42. print("insert step 发生错误: \(msg)")
  43. }
  44. return false
  45. }
  46. sqlite3_finalize(stmt)
  47. return true
  48. }
  49. // 查询数据
  50. func queryAll()-> [Any]? {
  51. var persons = [Person]()
  52. //根据查询条件查询数据
  53. //let namestr:String = "张三"
  54. //let sql = "SELECT * FROM persontable WHERE name LIKE '%\(namestr)%';"
  55. let sql = "SELECT * FROM persontable ;"
  56. var stmt: OpaquePointer?
  57. // 编译
  58. let prepareResult = sqlite3_prepare_v2(db, sql, -1, &stmt, nil)
  59. if prepareResult != SQLITE_OK {
  60. // 关闭语句句柄
  61. sqlite3_finalize(stmt)
  62. if let msg = sqlite3_errmsg(db) {
  63. print("insert prepare 发生错误: \(msg)")
  64. }
  65. return []
  66. }
  67. while sqlite3_step(stmt) == SQLITE_ROW {
  68. let person = Person()
  69. //let index = String.init(cString: sqlite3_column_text(stmt, 0)!)
  70. let name = String.init(cString: sqlite3_column_text(stmt, 1)!)
  71. let age = String.init(cString: sqlite3_column_text(stmt, 2)!)
  72. let sex = String.init(cString: sqlite3_column_text(stmt, 3)!)
  73. print("查询数据----\(name)--\(age)--\(sex)")
  74. person.name = name
  75. person.age = age
  76. person.sex = sex
  77. persons.append(person)
  78. }
  79. sqlite3_finalize(stmt)
  80. print("查询数据库成功")
  81. return persons
  82. }
  83. //更新列表
  84. func update(name: String, toName: String) -> Bool {
  85. //根据条件删除
  86. let sql = "update persontable set tradename = '\(toName)' where tradename = '\(name)'";
  87. return self.exec(sql: sql)
  88. }
  89. // 删除数据
  90. func delete(name: String) -> Bool{
  91. //删除sql语句
  92. //let sql = "delete from qingmeitable where tradename = '\(name)'"
  93. let sql = "delete from persontable "
  94. return self.exec(sql: sql)
  95. }
  96. // 执行编译sql
  97. private func exec(sql: String) -> Bool{
  98. // sqlite3_stmt指针
  99. var stmt: OpaquePointer?
  100. // 编译
  101. let prepareResult = sqlite3_prepare_v2(self.db, sql, -1, &stmt, nil)
  102. if prepareResult != SQLITE_OK {
  103. // 关闭语句句柄
  104. sqlite3_finalize(stmt)
  105. if let msg = sqlite3_errmsg(db) {
  106. print("delete prepare 发生错误: \(msg)")
  107. }
  108. return false
  109. }
  110. let stepResult = sqlite3_step(stmt)
  111. if stepResult != SQLITE_OK && stepResult != SQLITE_DONE {
  112. sqlite3_finalize(stmt)
  113. if let msg = sqlite3_errmsg(db) {
  114. print("delete step 发生错误: \(msg)")
  115. }
  116. return false
  117. }
  118. sqlite3_finalize(stmt)
  119. return true
  120. }

参考链接