在 Swift 中访问 SQLite 数据库 [英] Accessing an SQLite Database in Swift

问题描述

我正在寻找一种使用 Swift 代码在我的应用中访问 SQLite 数据库的方法.

I'm looking for a way to access an SQLite database in my app with Swift code.

我知道我可以在 Objective C 中使用 SQLite Wrapper 并使用桥接头，但我宁愿能够完全在 Swift 中完成这个项目.有没有办法做到这一点，如果是这样，有人可以给我指出一个参考，显示如何提交查询、检索行等?

I know that I can use an SQLite Wrapper in Objective C and use the bridging header, but I'd rather be able to do this project entirely in Swift. Is there a way to do this, if so, can someone point me to a reference that shows how to submit a query, retrieve rows, etc?

推荐答案

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

While you should probably use one of the many SQLite wrappers, if you wanted to know how to call the SQLite library yourself, you would:

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

1. Configure your Swift project to handle SQLite C calls. If using Xcode 9 or later, you can simply do:

import SQLite3

创建/打开数据库.

Create/open database.

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 文档 明确指出我们必须这样做以避免内存泄漏:

Note, I know it seems weird to close the database upon failure to open, but the sqlite3_open documentation makes it explicit that we must do so to avoid leaking memory:

无论打开时是否发生错误，与数据库连接句柄相关的资源应该通过将其传递给 sqlite3_close() 来释放，当它不是需要更长的时间.

Whether or not an error occurs when it is opened, resources associated with the database connection handle should be released by passing it to sqlite3_close() when it is no longer required.

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

Use sqlite3_exec to perform SQL (e.g. create table).

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)")
}

使用 sqlite3_prepare_v2 准备带有 ? 占位符的 SQL，我们将绑定值到该占位符.

Use sqlite3_prepare_v2 to prepare SQL with ? placeholder to which we'll bind value.

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 常量 可以实现如下:

Note, that uses the SQLITE_TRANSIENT constant which can be implemented as follows:

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

重置 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)")
}

完成准备好的语句以恢复与该准备好的语句相关的内存:

Finalize prepared statement to recover memory associated with that prepared statement:

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

statement = nil

准备用于从表中选择值并循环检索值的新语句:

Prepare new statement for selecting values from table and loop through retrieving the values:

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

关闭数据库:

Close database:

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

db = nil

对于 Swift 2 及更早版本的 Xcode，请参阅此答案的先前修订版.

For Swift 2 and older versions of Xcode, see previous revisions of this answer.

这篇关于在 Swift 中访问 SQLite 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！