如何在swift iOS中插入SQLite数据？

41ik7eoe  于 2023-01-08  发布在  Swift
关注(0)|答案(2)|浏览(115)

我对SQLite和Swift非常陌生。我已经学习了这个教程https://medium.com/@imbilalhassan/saving-data-in-sqlite-db-in-ios-using-swift-4-76b743d3ce0e
但我希望ID通过SQLite自动递增。

// Model

struct PersonModel {
    
    let firstName: String?
    let lastName: String?
    let phone: String?
    let address: String?
    
}

// DBManager

import Foundation
import UIKit
import SQLite3

class DBManager
{
    init()
    {
        db = openDatabase()
        createTable()
    }

    let dbPath: String = "myDb.sqlite"
    var db:OpaquePointer?
    
    // MARK: - Open DataBase

    func openDatabase() -> OpaquePointer?
    {
        let fileURL = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
            .appendingPathComponent(dbPath)
        var db: OpaquePointer? = nil
        if sqlite3_open(fileURL.path, &db) != SQLITE_OK
        {
            print("error opening database")
            return nil
        }
        else
        {
            print("Successfully opened connection to database at \(dbPath)")
            return db
        }
    }
    
    // MARK: - Create Table
    
    func createTable() {
        let createTableString = "CREATE TABLE IF NOT EXISTS person(id INTEGER PRIMARY KEY AUTOINCREMENT, firstName TEXT, lastName TEXT, phone TEXT, address TEXT);"
        var createTableStatement: OpaquePointer? = nil
        if sqlite3_prepare_v2(db, createTableString, -1, &createTableStatement, nil) == SQLITE_OK
        {
            if sqlite3_step(createTableStatement) == SQLITE_DONE
            {
                print("person table created.")
            } else {
                print("person table could not be created.")
            }
        } else {
            print("CREATE TABLE statement could not be prepared.")
        }
        sqlite3_finalize(createTableStatement)
    }
    
    // MARK: - Insert
    
    func insert(firstName: String, lastName: String, phone: String, address: String)
    {
        
        let insertStatementString = "INSERT INTO person (id, firstName, lastName, phone, address) VALUES (?, ?, ?, ?, ?);"
        var insertStatement: OpaquePointer? = nil
        if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {
            sqlite3_bind_text(insertStatement, 1, (firstName as NSString).utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 2, (lastName as NSString).utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 3, (phone as NSString).utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 4, (address as NSString).utf8String, -1, nil)
            
            if sqlite3_step(insertStatement) == SQLITE_DONE {
                print("Successfully inserted row.")
            } else {
                print("Could not insert row.")
            }
        } else {
            print("INSERT statement could not be prepared.")
        }
        sqlite3_finalize(insertStatement)
    }
    
    // MARK: - Read
    
    func read() -> [PersonModel] {
        let queryStatementString = "SELECT * FROM person;"
        var queryStatement: OpaquePointer? = nil
        var psns : [PersonModel] = []
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                let id = sqlite3_column_int(queryStatement, 0)
                let firstName = String(describing: String(cString: sqlite3_column_text(queryStatement, 1)))
                let lastName = String(describing: String(cString: sqlite3_column_text(queryStatement, 2)))
                let phone = String(describing: String(cString: sqlite3_column_text(queryStatement, 3)))
                let address = String(describing: String(cString: sqlite3_column_text(queryStatement, 4))) // this is where i'm getting error: Fatal error: Unexpectedly found nil while implicitly unwrapping an Optional value
                
                psns.append(PersonModel(firstName: firstName, lastName: lastName, phone: phone, address: address))
                print("Query Result:")
                print("\(id) | \(firstName) | \(lastName) | \(phone) | \(address)")
            }
        } else {
            print("SELECT statement could not be prepared")
        }
        sqlite3_finalize(queryStatement)
        return psns
    }
    
    // MARK: - Delete
    
    func deleteByID(id:Int) {
        let deleteStatementStirng = "DELETE FROM person WHERE Id = ?;"
        var deleteStatement: OpaquePointer? = nil
        if sqlite3_prepare_v2(db, deleteStatementStirng, -1, &deleteStatement, nil) == SQLITE_OK {
            sqlite3_bind_int(deleteStatement, 1, Int32(id))
            if sqlite3_step(deleteStatement) == SQLITE_DONE {
                print("Successfully deleted row.")
            } else {
                print("Could not delete row.")
            }
        } else {
            print("DELETE statement could not be prepared")
        }
        sqlite3_finalize(deleteStatement)
    }
    
}

// ViewController

class ViewController: UIViewController {
    
    @IBOutlet weak var txtFirstName: UITextField!
    @IBOutlet weak var txtLastName: UITextField!
    @IBOutlet weak var txtPhoneNumber: UITextField!
    @IBOutlet weak var txtAddress: UITextField!
    @IBOutlet weak var btnSave: UIButton!
    
    var db: DBManager = DBManager()
    var persons: [PersonModel] = []

    override func viewDidLoad() {
        super.viewDidLoad()
        
    }
    
    func setUpDataBase() {
        
        db.insert(firstName: txtFirstName.text ?? "", lastName: txtLastName.text ?? "", phone: txtPhoneNumber.text ?? "", address: txtAddress.text ?? "")
        
        persons = db.read()
        
    }
    
    // MARK: - Button Save Event

    @IBAction func btnSave_Event(_ sender: UIButton) {
        
        setUpDataBase()
    }
}

当我在SQLite中插入数据时，在func read()中，行：

let address = String(describing: String(cString: sqlite3_column_text(queryStatement, 4)))

正在引发错误：
致命错误：隐式展开可选值时意外找到nil。
我在地址textField中添加值，仍然得到这个错误，我也不知道这是否是自动递增ID的正确方法。有人能帮我吗？

    • 更新**

根据@Joakim Danielson的建议，我解决了这个问题：

// MARK: - Insert
    
    func insert(firstName: String, lastName: String, phone: String, address: String)
    {
        
        let insertStatementString = "INSERT INTO person (firstName, lastName, phone, address) VALUES (?, ?, ?, ?);"
        var insertStatement: OpaquePointer? = nil
        if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {
            sqlite3_bind_text(insertStatement, 1, (firstName as NSString).utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 2, (lastName as NSString).utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 3, (phone as NSString).utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 4, (address as NSString).utf8String, -1, nil)
            
            if sqlite3_step(insertStatement) == SQLITE_DONE {
                print("Successfully inserted row.")
            } else {
                print("Could not insert row.")
            }
        } else {
            print("INSERT statement could not be prepared.")
        }
        sqlite3_finalize(insertStatement)
    }
    
    // MARK: - Read
    
    func read() -> [PersonModel] {
        let queryStatementString = "SELECT * FROM person;"
        var queryStatement: OpaquePointer? = nil
        var psns : [PersonModel] = []
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                let id = sqlite3_column_int(queryStatement, 0)
                let firstName = String(describing: String(cString: sqlite3_column_text(queryStatement, 1)))
                let lastName = String(describing: String(cString: sqlite3_column_text(queryStatement, 2)))
                let phone = String(describing: String(cString: sqlite3_column_text(queryStatement, 3)))
                let address = String(describing: String(cString: sqlite3_column_text(queryStatement, 4)))
                
                psns.append(PersonModel(id: Int(id), firstName: firstName, lastName: lastName, phone: phone, address: address))
                print("Query Result:")
                print("\(id) | \(firstName) | \(lastName) | \(phone) | \(address)")
            }
        } else {
            print("SELECT statement could not be prepared")
        }
        sqlite3_finalize(queryStatement)
        return psns
    }
swift

2条答案

按热度按时间
g6ll5ycj

g6ll5ycj1#

您不应该在SQL插入语句中包含id属性，因为它将由数据库引擎自动处理。

let insertStatementString = "INSERT INTO person (firstName, lastName, phone, address) VALUES (?, ?, ?, ?);"

当我这样做的时候，我可以在一个操场上运行你的数据库相关的代码，并成功地阅读值，而没有任何运行时错误。如果你仍然得到 * Underwantly found nil while implicit unwrap a Optional value* 错误，那么还有其他不正确的地方，可能与你的视图控制器有关。

赞(0）分享  回复(0）举报  2023-01-08
irlmq6kh

irlmq6kh2#

我建议使用Swift软件包管理器安装SQlite3模块，方法是将以下内容写入您的软件包文件：

// Package.swift 
dependencies: [
    .package(url: "https://github.com/stephencelis/SQLite.swift.git", from: "0.14.1")
]

构建您的项目：第一个月
使用第三方库来管理数据库是访问存储在数据库中的数据的常用方法，它允许您执行任何操作（插入、读取、删除），而不使用字符串语句，这通过不同的方法，例如**Table**、prepare、insert、filter、etc ...其中一个关键特性是，您可以根据您的数据（字符串、数字、日期时间、数组）设置不同类型的 * 字段 *，并创建任何所需的模型结构。

struct PersonModel {
    
    let firstName: String?
    let lastName: String?
    let phone: String?
    let address: String?
}

import Foundation
import UIKit
import SQLite3

class DBManager
{
    init()
    {
        db = openDatabase()
        createTable()
    }

    let dbPath: String = "myDb.sqlite"
    var db:OpaquePointer?

    func openDatabase() -> OpaquePointer?
    {
        let fileURL = NSSearchPathForDirectoriesInDomains(.DocumentDirectory, .UserDomainMask, true)[0]
        var db = try? Connection(dbPath)
    }
    func createTable() {
        let createTableString = Table("person")
        let id = Expression<Int64>("id")
        let firstName = Expression<String?>("firstname")
        let lastName = Expression<String>("lastname")
        let phone = Expression<String>("phone")
        let address = Expression<String>("address")

        try! db?.run(users.create(ifNotExists: true, block: { (table) in
            table.column(id, primaryKey: true)
            table.column(firstName)
            table.column(lastName)
            table.column(phone, unique: true)
            table.column(address)
        }))
    }

    func insert(firstName: String, lastName: String, phone: String, address: String, person: Table)
    {
        let insert = person.insert("firstname" <- firstName, "lastname" <- lastName, "phone" <- phone, "address" <- address)
        let rowid = (try! db?.run(insert))!
    }
    
    func read(person: Table) -> [PersonModel] {
        var psns : [PersonModel] = []
        for user in (try! db?.prepare(person))! {
            print("Query :id: \(user[id]), name: \(user[firstname]), second name: \(user[lastname]), phone: \(user[phone]), address: \(user[address])")
        }
        return psns
    }
    
    func deleteByID(id:Int, person: Table) {
        try! db?.run(person.filter(id).delete())
    }
    
}

注：创建数据库表createTable的方法相当于以下语句

CREATE TABLE IF NOT EXISTS "users" (
    "id" INTEGER PRIMARY KEY NOT NULL,
    "firstname" TEXT,
    "lastname" TEXT
    "phone" TEXT NOT NULL UNIQUE
    "address" TEXT
)
赞(0）分享  回复(0）举报  2023-01-08
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com