面试题：SQLite中如何在iOS应用iSeinfeld里实现数据的批量插入

Question

假设在iSeinfeld iOS SQLite应用中，有一个包含用户信息（姓名、年龄、邮箱）的表，现在需要一次性插入100条模拟数据，描述使用SQLite在iOS环境下实现该批量插入操作的具体步骤和代码示例（可使用Objective - C或Swift语言）。

Answer 1

使用Objective - C实现

1. 导入SQLite库：在项目中导入libsqlite3.dylib库，并在需要使用的文件中引入头文件#import <sqlite3.h>。
2. 创建SQLite数据库连接：

sqlite3 *database;
NSString *documentsPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
NSString *databasePath = [documentsPath stringByAppendingPathComponent:@"iSeinfeld.db"];
if (sqlite3_open([databasePath UTF8String], &database) != SQLITE_OK) {
    NSLog(@"Failed to open database");
    return;
}

3. 创建表（如果表不存在）：

const char *createTableSQL = "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, email TEXT)";
if (sqlite3_exec(database, createTableSQL, NULL, NULL, NULL) != SQLITE_OK) {
    NSLog(@"Failed to create table");
    sqlite3_close(database);
    return;
}

4. 批量插入数据：

const char *insertSQL = "INSERT INTO users (name, age, email) VALUES (?,?,?)";
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(database, insertSQL, -1, &statement, NULL) != SQLITE_OK) {
    NSLog(@"Failed to prepare statement");
    sqlite3_close(database);
    return;
}
for (int i = 0; i < 100; i++) {
    NSString *name = [NSString stringWithFormat:@"User%d", i];
    int age = arc4random_uniform(100);
    NSString *email = [NSString stringWithFormat:@"[e-mail]", i];
    sqlite3_bind_text(statement, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(statement, 2, age);
    sqlite3_bind_text(statement, 3, [email UTF8String], -1, SQLITE_TRANSIENT);
    if (sqlite3_step(statement) != SQLITE_DONE) {
        NSLog(@"Failed to insert data");
    }
    sqlite3_reset(statement);
}
sqlite3_finalize(statement);

5. 关闭数据库连接：

sqlite3_close(database);

使用Swift实现

1. 导入SQLite库：在项目中导入libsqlite3.dylib库，并在需要使用的文件中引入import SQLite3。
2. 创建SQLite数据库连接：

let fileURL = try! FileManager.default.url(for:.documentDirectory, in:.userDomainMask, appropriateFor: nil, create: false).appendingPathComponent("iSeinfeld.db")
var database: OpaquePointer?
if sqlite3_open(fileURL.path, &database) != SQLITE_OK {
    print("Failed to open database")
    return
}

3. 创建表（如果表不存在）：

let createTableSQL = "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, email TEXT)"
if sqlite3_exec(database, createTableSQL, nil, nil, nil) != SQLITE_OK {
    print("Failed to create table")
    sqlite3_close(database)
    return
}

4. 批量插入数据：

let insertSQL = "INSERT INTO users (name, age, email) VALUES (?,?,?)"
var statement: OpaquePointer?
if sqlite3_prepare_v2(database, insertSQL, -1, &statement, nil) != SQLITE_OK {
    print("Failed to prepare statement")
    sqlite3_close(database)
    return
}
for i in 0..<100 {
    let name = "User\(i)"
    let age = Int.random(in: 0...99)
    let email = "user\(i)@example.com"
    sqlite3_bind_text(statement, 1, name, -1, nil)
    sqlite3_bind_int(statement, 2, Int32(age))
    sqlite3_bind_text(statement, 3, email, -1, nil)
    if sqlite3_step(statement) != SQLITE_DONE {
        print("Failed to insert data")
    }
    sqlite3_reset(statement)
}
sqlite3_finalize(statement)

5. 关闭数据库连接：

sqlite3_close(database)