Xcode中Swift读取iOS SQLite数据库的问题排查与可行方案

问题背景 #

在Xcode 16、Swift 6.0（SwiftUI、Foundation、SQLite3）、iOS 18.0环境下开发iOS应用，需要实现SQLite数据库连接、全量数据读取、数据传递至视图、搜索功能，仅需读取数据，后续可能扩展其他功能。以下是初始实现代码，存在运行问题，需修正。

初始代码 #

DataModel.swift

struct DataModel: Identifiable {
    let value1: Int
    let value2: Int
    let value3: String
    let value4: String
    let value5: Int
}

DBManager.swift

import Foundation
import SQLite3

class DBManager {
    var db: OpaquePointer?
    let dbName: String = "MyDB.sqlite"
    
    init() {
        openDatabase()
        closeDatabase()
        fetchAllData()
    }
    
    func openDatabase() {
        let dbFile = Bundle.main.url(forResource: dbName, withExtension: nil)!
        
        if sqlite3_open(dbFile.path, &db) != SQLITE_OK {
            print("Error opening database")
        } else {
            print("Database opened successfully at \(dbFile.path)")
        }
        
    }
    
    func fetchAllData() -> [DataModel] {
        var result: [DataModel] = []
        let queryStatementString = "SELECT * FROM tableOfMyDB;"
        var queryStatement: OpaquePointer?
        
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                let value1 = sqlite3_column_int(queryStatement, 0)
                let value2 = sqlite3_column_int(queryStatement, 1)
                let value3 = String(cString: sqlite3_column_text(queryStatement, 2))
                let value4 = String(cString: sqlite3_column_text(queryStatement, 3))
                let value5 = sqlite3_column_int(queryStatement, 4)
                
                result.append(DataModel(value1: Int(value1), value2: Int(value2), value3: value3, value4: value4, value5: Int(value5)))
            }
        }else {
            print("Error preparing query statement")
        }
        sqlite3_finalize(queryStatement)
        return result
    }
    
    func closeDatabase() {
        sqlite3_close(db)
    }
    
}

ViewModel.swift

import Foundation
import Combine

class DataViewModel: ObservableObject {
    @Published var data: [DataModel] = []
    @Published var searchText = ""

    private var dbManager = DBManager()

    init() {
        loadData()
    }

    func loadData() {
        data = dbManager.fetchAllData()
    }

    var filteredData: [DataModel] {
        if searchText.isEmpty {
            return data
        } else {
            return data.filter { $0.value4.contains(searchText) || $0.value3.contains(searchText) }
        }
    }
}

ContentView.swift

import SwiftUI

struct ContentView: View {
    @ObservedObject var viewModel = DataViewModel()

    var body: some View {
        VStack {
            TextField("Search...", text: $viewModel.searchText)
                .textFieldStyle(RoundedBorderTextFieldStyle())
                .padding()

            List(viewModel.filteredData) { item in
                VStack(alignment: .leading) {
                    Text("Name: \(item.value4)")
                    Text("Guide: \(item.value3)")
                }
            }
        }
        .navigationTitle("Data List")
    }
}

#Preview {
    ContentView()
}

修正后的可行方案 #

DataModel.swift #

struct DataModel: Identifiable {
    let value1: Int
    let value2: Int
    let value3: String
    let value4: String
    let value5: Int
}

DBManager.swift #

import Foundation
import SQLite3

class DBManager {
    var db: OpaquePointer?
    let dbName: String = "MyDB.sqlite"
    
    init() {
        openDatabase()
    }
    
    func openDatabase() {
        guard let dbFile = Bundle.main.url(forResource: dbName, withExtension: nil) else {
            print("Database file not found in bundle")
            return
        }
        
        if sqlite3_open(dbFile.path, &db) != SQLITE_OK {
            print("Error opening database")
        } else {
            print("Database opened successfully at \(dbFile.path)")
        }
    }
    
    @discardableResult
    func fetchAllData() -> [DataModel] {
        var result: [DataModel] = []
        let queryStatementString = "SELECT * FROM TableOfMyDB;"
        var queryStatement: OpaquePointer?
        
        guard let db = db else {
            print("Database not open")
            return result
        }
        
        if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
            while sqlite3_step(queryStatement) == SQLITE_ROW {
                let value1 = sqlite3_column_int(queryStatement, 0)
                let value2 = sqlite3_column_int(queryStatement, 1)
                let value3 = String(cString: sqlite3_column_text(queryStatement, 2))
                let value4 = String(cString: sqlite3_column_text(queryStatement, 3))
                let value5 = sqlite3_column_int(queryStatement, 4)
                
                result.append(DataModel(value1: Int(value1), value2: Int(value2), value3: value3, value4: value4, value5: Int(value5)))
            }
        } else {
            print("Error preparing query statement: \(String(cString: sqlite3_errmsg(db)))")
        }
        sqlite3_finalize(queryStatement)
        return result
    }
    
    func closeDatabase() {
        if let db = db {
            sqlite3_close(db)
            self.db = nil
        }
    }
    
    deinit {
        closeDatabase()
    }
}

ViewModel.swift #

import Foundation
import Combine

class DataViewModel: ObservableObject {
    @Published var data: [DataModel] = []
    @Published var searchText = ""
    
    private let dbManager = DBManager()
    
    init() {
        loadData()
    }
    
    func loadData() {
        data = dbManager.fetchAllData()
    }
    
    var filteredData: [DataModel] {
        if searchText.isEmpty {
            return data
        } else {
            let lowercasedSearch = searchText.lowercased()
            return data.filter { 
                $0.value4.lowercased().contains(lowercasedSearch) || 
                $0.value3.lowercased().contains(lowercasedSearch) 
            }
        }
    }
}

ContentView.swift #

import SwiftUI

struct ContentView: View {
    @ObservedObject var viewModel = DataViewModel()
    
    var body: some View {
        NavigationStack {
            List(viewModel.filteredData) { item in
                let vValue2 = String(item.value2)
                let vValue3 = item.value3
                let vValue4 = item.value4
                let vValue5 = item.value5
                
                HStack {
                    VStack(alignment: .leading) {
                        if item.value1 > 10 {
                            Text(vValue2)
                                .font(.headline)
                        }
                        Text(vValue3)
                            .font(.subheadline)
                    }
                    
                    Spacer()
                    
                    HStack {
                        if vValue5 == 1 {
                            Text(vValue4)
                                .fontWeight(.heavy)
                                .fontDesign(.monospaced)
                                .foregroundStyle(.blue)
                                .frame(width: 50, height: 40, alignment: .leading)
                                .padding(.leading, 10)
                        } else {
                            Text(vValue4)
                                .fontWeight(.heavy)
                                .fontDesign(.monospaced)
                                .foregroundStyle(.primary)
                                .frame(width: 50, height: 40, alignment: .leading)
                                .padding(.leading, 20)
                        }
                    }
                }
                .listRowBackground(vValue5 == 1 ? .blue.opacity(0.5) : Color(UIColor.systemBackground))
            }
            .navigationTitle("Data List")
            .searchable(text: $viewModel.searchText, prompt: "搜索...")
        }
    }
}

#Preview {
    ContentView()
}

内容的提问来源于stack exchange，提问作者Miroslav