Swift SQLite3语法和绑定

概述首先,这些是我的功能： >插入功能 func insert(book : Book) throws -> Bool { var insertPointer: OpaquePointer? = nil let query = "INSERT INTO BOOK (bookName, bookAuthor, bookDesc, bookDate, bookImg, createdBy) 首先,这些是我的功能：

>插入功能

func insert(book : Book) throws -> Bool {    var insertPointer: OpaquePointer? = nil    let query = "INSERT INTO BOOK (bookname,bookAuthor,bookDesc,bookDate,bookimg,createdBy) VALUES (?,?,?)"    defer{        sqlite3_finalize(insertPointer)    }    if sqlite3_prepare_v2(db,query,-1,&insertPointer,nil) == sqlITE_OK {        sqlite3_bind_text(insertPointer,1,book.bookTitle,nil)        sqlite3_bind_text(insertPointer,2,book.bookAuthor,3,book.bookDesc,nil)      //sqlite3_bind_date(insertPointer,4,book.bookDate,nil)      //sqlite3_bind_image(insertPointer,5,book.bookimg,6,book.createdBy,nil)        guard sqlite3_step(insertPointer) == sqlITE_DONE else {            throw sqliteError.Step(message: errorMessage)        }    } else {        throw sqliteError.Prepare(message: errorMessage)    }    return true}

>更新功能

func update(book : Book) throws -> Bool {    var updatePointer: OpaquePointer? = nil    var query = "UPDATE Book SET bookname = ?,bookAuthor = ?,bookDesc = ?,bookDate = ?,bookimg = ?,createdBy = ?,WHERE bookID = ?"    defer{        sqlite3_finalize(updatePointer)    }    if sqlite3_prepare_v2(db,&updatePointer,nil) == sqlITE_OK {        sqlite3_bind_text(updatePointer,nil)        sqlite3_bind_text(updatePointer,nil)        //sqlite3_bind_date(updatePointer,nil)        //sqlite3_bind_image(updatePointer,7,book.bookID,nil)        guard sqlite3_step(updatePointer) == sqlITE_DONE else {            throw sqliteError.Step(message: errorMessage)        }    } else {        throw sqliteError.Prepare(message: errorMessage)    }    return true}

>删除功能

func delete(book : Book) throws -> Bool {    var deletePointer: OpaquePointer? = nil    var query = "DELETE FROM Book WHERE bookID = ?"    defer{        sqlite3_finalize(deletePointer)    }    if sqlite3_prepare_v2(db,&deletePointer,nil) == sqlITE_OK {        sqlite3_bind_text(updatePointer,nil)        guard sqlite3_step(deletePointer) == sqlITE_DONE else {            throw sqliteError.Step(message: errorMessage)        }    } else {        throw sqliteError.Prepare(message: errorMessage)    }    return true}

我有一个像这样的Book类：

class Book{    var bookID : Int    var bookimg : Data    var bookTitle : String    var bookAuthor : String    var bookDesc : String    var bookDate : Date    var createdBy : String    init(bookID : Int,bookimg : Data,bookTitle : String,bookAuthor : String,bookDesc : String,bookDate : Date,createdBy : String){        self.bookID = bookID        self.bookimg = bookimg        self.bookTitle = bookTitle        self.bookAuthor = bookAuthor        self.bookDesc = bookDesc        self.bookDate = bookDate        self.createdBy = createdBy    }}

我是Swift和sqlite的新手.我的问题是：

>我是否正确使用参数绑定？
>如何将数据和日期类型绑定到sqlite查询中？ (上面代码中的注释行)

任何帮助将不胜感激！

解决方法 您询问：

Am I doing it right with the parameter binding?

大部分.

>绑定字符串时,使用sqlITE_TRANSIENT作​​为sqlite3_bind_text和sqlite3_bind_blob的最后一个参数可能是谨慎的,如下所述：

internal let sqlITE_STATIC = unsafeBitCast(0,to: sqlite3_destructor_type.self)internal let sqlITE_TRANSIENT = unsafeBitCast(-1,to: sqlite3_destructor_type.self)

>绑定bookID时,您想使用sqlite3_bind_int64.
>在删除中,您指的是updatePointer.将其更改为deletePointer.
>您应该检查这些sqlite3_bind_xxx返回代码并在它们不是sqlITE_OK时抛出错误.

然后你问：

How do I bind Data and Date type into sqlite query? (the commented line in code above)

重新日期类型,sqlite没有本机日期类型(请参阅http://sqlite.org/datatype3.html).或者：

>如果您不需要毫秒,请使用ISODateFormatter构建字符串,并绑定字符串;
>如果需要毫秒,请使用DateFormatter,其中dateFormat为yyyy-MM-dd’T’HH：mm：ss.SSSX,Locale的区域设置(标识符：“en_US_POSIX”)和timeZone的timeZone(secondsFromGMT：0),并再次存储和检索日期作为字符串并转换它;要么
>使用Date的timeIntervalSince1970,并将其作为sqlite3_bind_double插入.

以前的字符串替代方法最容易使用,并且在第三方工具中直观地检查数据库时非常容易. timeIntervalSince1970可以说有点效率,但它只是意味着你需要使用unixepoch将双精度转换为可理解的日期,如果查看第三方sqlite工具中的列,这可能有点麻烦.这是效率与可用性之间的权衡.

重新数据,使用sqlite3_bind_blob插入.

几个最后的小观察：

>您在sqlite3_prepare_v2之前推迟了sqlite3_finalize.你应该在sqlite3_prepare_v2之后推迟它.如果准备成功,您应该只是最终确定,而不是如果失败.
>在使用WHERE子句进行更新时,您可能需要检查sqlite3_changes以查看是否有任何记录更改.对于标识符的更新,如果没有更新/删除任何内容,我将函数更改为抛出错误.
>其中一些函数被定义为抛出错误以及返回布尔值.对于没有意义的更新/删除函数(因为我们使用错误来知道它是否成功,使布尔返回值变为冗余).所以我删除了Bool返回类型.对于其他函数(例如SELECT例程),返回值显然有意义,但不适用于这些通过/失败更新例程.
>对于Book属性,我删除了书籍前缀.在sql中使用该前缀是有意义的(它使连接查询更容易编写),但在Swift类型中它是多余的.您通常只使用消除歧义所需的那种前缀(例如bookDescription,以避免与customstringconvertible属性混淆,描述).

无论如何,把它拉到一起,你会得到类似的东西：

var dateFormatter: DateFormatter = {    let _formatter = DateFormatter()    _formatter.dateFormat = "yyyy-MM-dd'T'HH:mm:ss.SSSX"    _formatter.locale = Locale(IDentifIEr: "en_US_POSIX")    _formatter.timeZone = TimeZone(secondsFromGMT: 0)    return _formatter}()var errorMessage: String { return String(cString: sqlite3_errmsg(db)) }func insert(book: Book) throws {    var statement: OpaquePointer? = nil    let query = "INSERT INTO book (bookname,?)"    guard sqlite3_prepare_v2(db,&statement,nil) == sqlITE_OK else {        throw sqliteError.prepare(message: errorMessage)    }    defer { sqlite3_finalize(statement) }    guard sqlite3_bind_text(statement,book.Title,sqlITE_TRANSIENT) == sqlITE_OK else {        throw sqliteError.bind(message: errorMessage)    }    guard sqlite3_bind_text(statement,book.author,book.bookDescription,dateFormatter.string(from: book.createDate),sqlITE_TRANSIENT) == sqlITE_OK else {        throw sqliteError.bind(message: errorMessage)    }    guard book.image.withUnsafeBytes({ (bytes: UnsafePointer<UInt8>) -> Int32 in        sqlite3_bind_blob(statement,bytes,Int32(book.image.count),sqlITE_TRANSIENT)    }) == sqlITE_OK else {        throw sqliteError.bind(message: errorMessage)    }    guard sqlite3_bind_text(statement,sqlITE_TRANSIENT) == sqlITE_OK else {        throw sqliteError.bind(message: errorMessage)    }    guard sqlite3_step(statement) == sqlITE_DONE else {        throw sqliteError.step(message: errorMessage)    }}func update(book: Book) throws {    var statement: OpaquePointer? = nil    let query = "UPDATE Book SET bookname = ?,WHERE bookID = ?"    guard sqlite3_prepare_v2(db,sqlITE_TRANSIENT) == sqlITE_OK else {        throw sqliteError.bind(message: errorMessage)    }    guard sqlite3_bind_int64(statement,Int64(book.ID)) == sqlITE_OK else {        throw sqliteError.bind(message: errorMessage)    }    guard sqlite3_step(statement) == sqlITE_DONE else {        throw sqliteError.step(message: errorMessage)    }    guard sqlite3_changes(db) > 0 else {        throw sqliteError.noDataChanged    }}func delete(book: Book) throws {    var statement: OpaquePointer? = nil    let query = "DELETE FROM Book WHERE bookID = ?"    guard sqlite3_prepare_v2(db,nil) == sqlITE_OK else {        throw sqliteError.prepare(message: errorMessage)    }    defer { sqlite3_finalize(statement) }    guard sqlite3_bind_int64(statement,Int64(book.ID)) == sqlITE_OK else {        throw sqliteError.bind(message: errorMessage)    }    guard sqlite3_step(statement) == sqlITE_DONE else {        throw sqliteError.step(message: errorMessage)    }    guard sqlite3_changes(db) > 0 else {        throw sqliteError.noDataChanged    }}func select(bookID: Int) throws -> Book {    var statement: OpaquePointer? = nil    let query = "SELECT bookID,bookname,createdBy FROM Book WHERE bookID = ?"    guard sqlite3_prepare_v2(db,Int64(bookID)) == sqlITE_OK else {        throw sqliteError.bind(message: errorMessage)    }    guard sqlite3_step(statement) == sqlITE_ROW else {        throw sqliteError.step(message: errorMessage)    }    return try book(for: statement)}func selectAll() throws -> [Book] {    var statement: OpaquePointer? = nil    let query = "SELECT bookID,createdBy FROM Book"    guard sqlite3_prepare_v2(db,nil) == sqlITE_OK else {        throw sqliteError.prepare(message: errorMessage)    }    defer { sqlite3_finalize(statement) }    var books = [Book]()    var rc: Int32    repeat {        rc = sqlite3_step(statement)        guard rc == sqlITE_ROW else { break }        books.append(try book(for: statement))    } while rc == sqlITE_ROW    guard rc == sqlITE_DONE else {        throw sqliteError.step(message: errorMessage)    }    return books}func book(for statement: OpaquePointer?) throws -> Book {    let bookID = Int(sqlite3_column_int64(statement,0))    guard let booknameCString = sqlite3_column_text(statement,1) else {        throw sqliteError.column(message: errorMessage)    }    let bookname = String(cString: booknameCString)    guard let bookAuthorCString = sqlite3_column_text(statement,2) else {        throw sqliteError.column(message: errorMessage)    }    let bookAuthor = String(cString: bookAuthorCString)    guard let bookDescCString = sqlite3_column_text(statement,3) else {        throw sqliteError.column(message: errorMessage)    }    let bookDesc = String(cString: bookDescCString)    guard let bookDateCString = sqlite3_column_text(statement,4) else {        throw sqliteError.column(message: errorMessage)    }    guard let bookDate = dateFormatter.date(from: String(cString: bookDateCString)) else {        throw sqliteError.invalIDDate    }    let bookimgCount = Int(sqlite3_column_bytes(statement,5))    guard bookimgCount > 0 else {        throw sqliteError.missingData    }    guard let bookimgBlog = sqlite3_column_blob(statement,5) else {        throw sqliteError.column(message: errorMessage)    }    let bookimg = Data(bytes: bookimgBlog,count: bookimgCount)    guard let createdByCString = sqlite3_column_text(statement,6) else {        throw sqliteError.column(message: errorMessage)    }    let createdBy = String(cString: createdByCString)    return Book(ID: bookID,image: bookimg,Title: bookname,author: bookAuthor,bookDescription: bookDesc,createDate: bookDate,createdBy: createdBy)}

有了这些定义：

struct Book {    var ID: Int    var image: Data    var Title: String    var author: String    var bookDescription: String  // this is the only one where I kept the `book` prefix,simply because `description` is a reserved name    var createDate: Date    var createdBy: String}enum sqliteError: Error {    case open(result: Int32)    case exec(message: String)    case prepare(message: String)    case bind(message: String)    case step(message: String)    case column(message: String)    case invalIDDate    case missingData    case noDataChanged}

从Swift 3开始,我更喜欢小写的枚举值.

总结

以上是内存溢出为你收集整理的Swift SQLite3语法和绑定全部内容，希望文章能够帮你解决Swift SQLite3语法和绑定所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。