面试题：SQLite在iOS应用中如何进行数据库的配置与基本操作

Question

在iOS应用开发中，配置SQLite数据库后，如何创建一张包含姓名、年龄和邮箱字段的用户表，并插入一条数据？请用代码示例说明（语言不限，可使用你熟悉的iOS开发语言）。

Answer 1

以Objective-C为例：

1. 引入SQLite库： 在项目中添加libsqlite3.tbd库，并在文件头部引入头文件：

#import <sqlite3.h>

2. 创建表并插入数据：

- (void)createAndInsertUser {
    NSString *docsDir;
    NSArray *dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    docsDir = dirPaths[0];
    NSString *databasePath = [docsDir stringByAppendingPathComponent:@"test.db"];
    
    sqlite3 *contactDB;
    if (sqlite3_open([databasePath UTF8String], &contactDB) == SQLITE_OK) {
        const char *sqlStatement = "CREATE TABLE IF NOT EXISTS Users (ID INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Age INTEGER, Email TEXT)";
        char *errorMsg;
        if (sqlite3_exec(contactDB, sqlStatement, NULL, NULL, &errorMsg) != SQLITE_OK) {
            NSLog(@"Failed to create table: %s", errorMsg);
            sqlite3_free(errorMsg);
        } else {
            NSLog(@"Table created successfully.");
        }
        
        const char *insertStatement = "INSERT INTO Users (Name, Age, Email) VALUES (?,?,?)";
        sqlite3_stmt *compiledStatement;
        if (sqlite3_prepare_v2(contactDB, insertStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            NSString *name = @"John Doe";
            NSInteger age = 30;
            NSString *email = @"[e-mail]";
            sqlite3_bind_text(compiledStatement, 1, [name UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(compiledStatement, 2, age);
            sqlite3_bind_text(compiledStatement, 3, [email UTF8String], -1, SQLITE_TRANSIENT);
            
            if (sqlite3_step(compiledStatement) != SQLITE_DONE) {
                NSLog(@"Failed to insert data.");
            } else {
                NSLog(@"Data inserted successfully.");
            }
            sqlite3_finalize(compiledStatement);
        } else {
            NSLog(@"Failed to prepare insert statement.");
        }
        sqlite3_close(contactDB);
    } else {
        NSLog(@"Failed to open database.");
    }
}

调用createAndInsertUser方法即可完成创建表并插入数据的操作。

以Swift为例：

1. 引入SQLite库： 在桥接文件（如果是混编项目）或使用CocoaPods引入SQLite库。如果是纯Swift项目，可以通过#if canImport(sqlite3)导入。

import Foundation
#if canImport(sqlite3)
import sqlite3
#endif

2. 创建表并插入数据：

func createAndInsertUser() {
    let fileURL = try! FileManager.default.url(for:.documentDirectory, in:.userDomainMask, appropriateFor: nil, create: false).appendingPathComponent("test.db")
    
    var db: OpaquePointer?
    if sqlite3_open(fileURL.path, &db) == SQLITE_OK {
        let createTableString = "CREATE TABLE IF NOT EXISTS Users (ID INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Age INTEGER, Email TEXT)"
        var createTableStatement: OpaquePointer?
        if sqlite3_prepare_v2(db, createTableString, -1, &createTableStatement, nil) == SQLITE_OK {
            if sqlite3_step(createTableStatement) != SQLITE_DONE {
                print("Failed to create table")
            } else {
                print("Table created successfully")
            }
        } else {
            print("Failed to prepare create table statement")
        }
        sqlite3_finalize(createTableStatement)
        
        let insertString = "INSERT INTO Users (Name, Age, Email) VALUES (?,?,?)"
        var insertStatement: OpaquePointer?
        if sqlite3_prepare_v2(db, insertString, -1, &insertStatement, nil) == SQLITE_OK {
            let name = "Jane Smith"
            let age = 25
            let email = "[e-mail]"
            sqlite3_bind_text(insertStatement, 1, (name as NSString).utf8String, -1, nil)
            sqlite3_bind_int(insertStatement, 2, Int32(age))
            sqlite3_bind_text(insertStatement, 3, (email as NSString).utf8String, -1, nil)
            
            if sqlite3_step(insertStatement) != SQLITE_DONE {
                print("Failed to insert data")
            } else {
                print("Data inserted successfully")
            }
        } else {
            print("Failed to prepare insert statement")
        }
        sqlite3_finalize(insertStatement)
        sqlite3_close(db)
    } else {
        print("Failed to open database")
    }
}

调用createAndInsertUser方法来执行创建表和插入数据的操作。