在 Swift 中访问 SQLite 数据库

虽然您可能应该使用许多SQLite包装器中的一个，但如果您想知道如何自己调用SQLite库，您可以:

1. 配置Swift项目以处理SQLite C调用.如果使用Xcode 9或更高版本，只需执行以下操作:

   import SQLite3
   

2. 创建/打开数据库.

   let fileURL = try! FileManager.default
       .url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
       .appendingPathComponent("test.sqlite")
   
   // open database
   
   var db: OpaquePointer?
   guard sqlite3_open(fileURL.path, &db) == SQLITE_OK else {
       print("error opening database")
       sqlite3_close(db)
       db = nil
       return
   }
   

   注意，我知道在无法打开时关闭数据库似乎很奇怪，但sqlite3_open documentation明确表示，我们必须这样做以避免内存泄漏:

   无论打开时是否发生错误，当不再需要database connection句柄时，应该通过将其传递给sqlite3_close()来释放与database connection句柄相关的资源.

3. 使用sqlite3_exec执行SQL(例如创建表).

   if sqlite3_exec(db, "create table if not exists test (id integer primary key autoincrement, name text)", nil, nil, nil) != SQLITE_OK {
       let errmsg = String(cString: sqlite3_errmsg(db)!)
       print("error creating table: \(errmsg)")
   }
   

4. 使用sqlite3_prepare_v2准备SQL和?占位符，我们将向其绑定值.

   var statement: OpaquePointer?
   
   if sqlite3_prepare_v2(db, "insert into test (name) values (?)", -1, &statement, nil) != SQLITE_OK {
       let errmsg = String(cString: sqlite3_errmsg(db)!)
       print("error preparing insert: \(errmsg)")
   }
   
   if sqlite3_bind_text(statement, 1, "foo", -1, SQLITE_TRANSIENT) != SQLITE_OK {
       let errmsg = String(cString: sqlite3_errmsg(db)!)
       print("failure binding foo: \(errmsg)")
   }
   
   if sqlite3_step(statement) != SQLITE_DONE {
       let errmsg = String(cString: sqlite3_errmsg(db)!)
       print("failure inserting foo: \(errmsg)")
   }
   

   注意，它使用SQLITE_TRANSIENT常数which can be implemented，如下所示:

   internal let SQLITE_STATIC = unsafeBitCast(0, to: sqlite3_destructor_type.self)
   internal let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
   

5. 重置SQL以插入另一个值.在本例中，我将插入一个NULL值:

   if sqlite3_reset(statement) != SQLITE_OK {
       let errmsg = String(cString: sqlite3_errmsg(db)!)
       print("error resetting prepared statement: \(errmsg)")
   }
   
   if sqlite3_bind_null(statement, 1) != SQLITE_OK {
       let errmsg = String(cString: sqlite3_errmsg(db)!)
       print("failure binding null: \(errmsg)")
   }
   
   if sqlite3_step(statement) != SQLITE_DONE {
       let errmsg = String(cString: sqlite3_errmsg(db)!)
       print("failure inserting null: \(errmsg)")
   }
   

6. Finalize prepared statement可恢复与该prepared statement关联的内存:

   if sqlite3_finalize(statement) != SQLITE_OK {
       let errmsg = String(cString: sqlite3_errmsg(db)!)
       print("error finalizing prepared statement: \(errmsg)")
   }
   
   statement = nil
   

7. 准备从表中 Select 值的新语句，并通过检索值进行循环:

   if sqlite3_prepare_v2(db, "select id, name from test", -1, &statement, nil) != SQLITE_OK {
       let errmsg = String(cString: sqlite3_errmsg(db)!)
       print("error preparing select: \(errmsg)")
   }
   
   while sqlite3_step(statement) == SQLITE_ROW {
       let id = sqlite3_column_int64(statement, 0)
       print("id = \(id); ", terminator: "")
   
       if let cString = sqlite3_column_text(statement, 1) {
           let name = String(cString: cString)
           print("name = \(name)")
       } else {
           print("name not found")
       }
   }
   
   if sqlite3_finalize(statement) != SQLITE_OK {
       let errmsg = String(cString: sqlite3_errmsg(db)!)
       print("error finalizing prepared statement: \(errmsg)")
   }
   
   statement = nil
   

8. 关闭数据库:

   if sqlite3_close(db) != SQLITE_OK {
       print("error closing database")
   }
   
   db = nil
   

有关Swift 2及更早版本的Xcode，请参阅previous revisions of this answer.