一次向 SQLite 插入 250 多条记录

Question

我需要在现有的SQLite数据库中插入超过1,000条记录。 为此，我有一个包含一些静态函数的类，如下所示。

class Application {
    static func openDatabase(path: String) -> OpaquePointer? {
        var db: OpaquePointer? = nil
        if sqlite3_open(path, &db) == SQLITE_OK {
            return db
        } else {
            return nil
        }
    }
    static func createTable(path: String) {
        let open = openDatabase(path: path)
        var createTableStatement: OpaquePointer? = nil
        let sqlCreate = "CREATE TABLE IF NOT EXISTS data (ID INTEGER PRIMARY KEY AUTOINCREMENT, year text, month text, day text, x1 text, x2 text, x3 text)"
        if sqlite3_prepare_v2(open, sqlCreate, -1, &createTableStatement, nil) == SQLITE_OK {
            if sqlite3_step(createTableStatement) == SQLITE_DONE {
                print("Table created.")
            } else {
                print("Table could not be created.")
            }
        } else {
            print("CREATE TABLE statement could not be prepared.")
        }
        sqlite3_finalize(createTableStatement)
    }
    static func insertRecord(path: String, year: String, month: String, day: String) {
        let open = openDatabase(path: path)
        var insertStatement: OpaquePointer? = nil
        let sqlInsert = "INSERT INTO data (year, month, day) VALUES (?, ?, ?)"
        if sqlite3_prepare_v2(open, sqlInsert, -1, &insertStatement, nil) == SQLITE_OK {
            let yearStr = year as NSString
            let monthStr = month as NSString
            let dayStr = day as NSString
            sqlite3_bind_text(insertStatement, 1, yearStr.utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 2, monthStr.utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 3, dayStr.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.")
        }
        // 5
        sqlite3_finalize(insertStatement)
    }
}

我插入AppDelegate的记录. 如果我第二次运行它以便将记录插入数据库，如下所示，

class AppDelegate: UIResponder, UIApplicationDelegate {
    var appFile = String()
    func application(_ application: UIApplication, didFinishLaunchingWithOptions launchOptions: [UIApplicationLaunchOptionsKey: Any]?) -> Bool {
        appFile = folderPath(s: 0, name: "Application")
        if !pathExists(path: appFile) {
            Application.createTable(path: appFile)
        } else {
            for i in 0..<10 {
                for j in 0..<12 {
                    for k in 0..<31 {
                        let yearStr = String(i + 2018)
                        let monthStr = String(j + 1)
                        let dayStr = String(k + 1)
                        Application.insertRecord(path: appFile, year: yearStr, month: monthStr, day: dayStr)
                    }
                }
            }
        }
        return true
    }
}

将

250 条记录插入数据库后，应用将崩溃。 调试器说"NSInternalInconsistencyException" 我相信它会崩溃，因为该应用程序一次打开 250 个文件。 几年前，这不是问题，因为我们可以像

sqlite3_close(statement)

每次应用打开语句时。 现在，我们不能。 那么我们可以做些什么来一次插入多个记录呢？ 谢谢。

Answer 1

使用 SQLite 时，正确清理所有资源非常重要。您的主要问题是您在每次调用insertRecord时都打开数据库，但从不关闭数据库。这很糟糕。

对于每个成功的sqlite_open调用，必须有一个相应的调用sqlite_close。

对于每个成功的sqlite3_prepare_v2调用，必须有一个对sqlite3_finalize的相应调用，以及对sqlite3_reset的零个或多个调用。

您的方法应该是：

static func insertRecord(path: String, year: String, month: String, day: String) {
    if let open = openDatabase(path: path) {
        var insertStatement: OpaquePointer? = nil
        let sqlInsert = "INSERT INTO data (year, month, day) VALUES (?, ?, ?)"
        if sqlite3_prepare_v2(open, sqlInsert, -1, &insertStatement, nil) == SQLITE_OK {
            let yearStr = year as NSString
            let monthStr = month as NSString
            let dayStr = day as NSString
            sqlite3_bind_text(insertStatement, 1, yearStr.utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 2, monthStr.utf8String, -1, nil)
            sqlite3_bind_text(insertStatement, 3, dayStr.utf8String, -1, nil)
            if sqlite3_step(insertStatement) == SQLITE_DONE {
                print("Successfully inserted row.")
            } else {
                print("Could not insert row.")
            }
            sqlite3_finalize(insertStatement)
        } else {
            print("INSERT statement could not be prepared.")
        }
        sqlite3_close(open)
    } else {
        print("Database couldn't be open.")
    }
}