萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> iOS 開發

Unity3D 實戰:使用 C# 建立跨平台 SQLite 本地資料庫(iOS/Android/Mac/Windows)

前置準備:必要 DLL

Unity 需要兩個 DLL 才能使用 SQLite:

  • Mono.Data.Sqlite.dll
  • System.Data.dll

Mac 版 Unity 找不到這兩個檔案,需從 Windows 版 Unity 安裝目錄複製,或下載附件 Unity資料庫文件.zip。放入專案 Assets/ 目錄:

核心工具類:DbAccess.cs

封裝 SQLite 連線、CRUD、查詢等常用操作,不需掛載在 GameObject 上,作為工具類直接實例化使用。

using UnityEngine;
using System;
using System.Collections;
using Mono.Data.Sqlite;

public class DbAccess {
    private SqliteConnection dbConnection;
    private SqliteCommand dbCommand;
    private SqliteDataReader reader;

    public DbAccess(string connectionString) { OpenDB(connectionString); }
    public DbAccess() {}

    public void OpenDB(string connectionString) {
        try {
            dbConnection = new SqliteConnection(connectionString);
            dbConnection.Open();
            Debug.Log("Connected to db");
        } catch (Exception e) {
            Debug.Log(e.ToString());
        }
    }

    public void CloseSqlConnection() {
        if (dbCommand != null) { dbCommand.Dispose(); dbCommand = null; }
        if (reader != null) { reader.Dispose(); reader = null; }
        if (dbConnection != null) { dbConnection.Close(); dbConnection = null; }
        Debug.Log("Disconnected from db.");
    }

    public SqliteDataReader ExecuteQuery(string sqlQuery) {
        dbCommand = dbConnection.CreateCommand();
        dbCommand.CommandText = sqlQuery;
        reader = dbCommand.ExecuteReader();
        return reader;
    }

    // === 便利方法 ===
    public SqliteDataReader ReadFullTable(string tableName) {
        return ExecuteQuery("SELECT * FROM " + tableName);
    }

    public SqliteDataReader InsertInto(string tableName, string[] values) {
        string query = "INSERT INTO " + tableName + " VALUES (" + values[0];
        for (int i = 1; i < values.Length; ++i) query += ", " + values[i];
        query += ")";
        return ExecuteQuery(query);
    }

    public SqliteDataReader UpdateInto(string tableName, string[] cols, string[] colsValues, string selectKey, string selectValue) {
        string query = "UPDATE " + tableName + " SET " + cols[0] + " = " + colsValues[0];
        for (int i = 1; i < colsValues.Length; ++i) query += ", " + cols[i] + " = " + colsValues[i];
        query += " WHERE " + selectKey + " = " + selectValue + " ";
        return ExecuteQuery(query);
    }

    public SqliteDataReader Delete(string tableName, string[] cols, string[] colsValues) {
        string query = "DELETE FROM " + tableName + " WHERE " + cols[0] + " = " + colsValues[0];
        for (int i = 1; i < colsValues.Length; ++i) query += " OR " + cols[i] + " = " + colsValues[i];
        Debug.Log(query);
        return ExecuteQuery(query);
    }

    public SqliteDataReader InsertIntoSpecific(string tableName, string[] cols, string[] values) {
        if (cols.Length != values.Length) throw new SqliteException("columns.Length != values.Length");
        string query = "INSERT INTO " + tableName + "(" + cols[0];
        for (int i = 1; i < cols.Length; ++i) query += ", " + cols[i];
        query += ") VALUES (" + values[0];
        for (int i = 1; i < values.Length; ++i) query += ", " + values[i];
        query += ")";
        return ExecuteQuery(query);
    }

    public SqliteDataReader DeleteContents(string tableName) {
        return ExecuteQuery("DELETE FROM " + tableName);
    }

    public SqliteDataReader CreateTable(string name, string[] col, string[] colType) {
        if (col.Length != colType.Length) throw new SqliteException("columns.Length != colType.Length");
        string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0];
        for (int i = 1; i < col.Length; ++i) query += ", " + col[i] + " " + colType[i];
        query += ")";
        return ExecuteQuery(query);
    }

    public SqliteDataReader SelectWhere(string tableName, string[] items, string[] col, string[] operation, string[] values) {
        if (col.Length != operation.Length || operation.Length != values.Length)
            throw new SqliteException("col.Length != operation.Length != values.Length");
        string query = "SELECT " + items[0];
        for (int i = 1; i < items.Length; ++i) query += ", " + items[i];
        query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' ";
        for (int i = 1; i < col.Length; ++i) query += " AND " + col[i] + operation[i] + "'" + values[i] + "' ";
        return ExecuteQuery(query);
    }
}

各平台實作差異與代碼範例

通用測試腳本 Test.cs(掛在 Main Camera)

using UnityEngine;
using System.Collections;
using Mono.Data.Sqlite;

public class Test : MonoBehaviour {
    void Start() {
        // 1. 建立資料庫
        DbAccess db = new DbAccess("data source=xuanyusong.db");
        
        // 2. 建表
        db.CreateTable("momo", 
            new string[]{"name","qq","email","blog"}, 
            new string[]{"text","text","text","text"});
        
        // 3. 插入資料(字串值需加單引號)
        db.InsertInto("momo", new string[]{"'宣雨松'","'289187120'","'xuanyusong@gmail.com'","'www.xuanyusong.com'"});
        
        // 4. 更新資料
        db.UpdateInto("momo", 
            new string[]{"name","qq"}, 
            new string[]{"'xuanyusong'","'11111111'"}, 
            "email", "'xuanyusong@gmail.com'");
        
        // 5. 刪除資料
        db.Delete("momo", 
            new string[]{"email","email"}, 
            new string[]{"'xuanyusong@gmail.com'","'000@gmail.com'"});
        
        // 6. 查詢資料
        SqliteDataReader sqReader = db.SelectWhere("momo",
            new string[]{"name","email"},
            new string[]{"qq"},
            new string[]{"="},
            new string[]{"289187120"});
        
        while (sqReader.Read()) {
            Debug.Log(sqReader.GetString(sqReader.GetOrdinal("name")) + " " + 
                      sqReader.GetString(sqReader.GetOrdinal("email")));
        }
        
        db.CloseSqlConnection();
    }
}

資料庫檔案位置與檢視

  • Editor 運行時:專案根目錄生成 xuanyusong.db
  • 使用 Navicat Premium 開啟檢視表結構與資料

平台專屬設定

⚠️ 關鍵設定:Api Compatibility Level

Player Settings → Api Compatibility Level → 選擇 .NET 2.0,否則會報錯:Error building Player: Extracting referenced dlls failed.

iOS 平台

資料庫需放在沙盒 Application.persistentDataPath,連線字串用 Data Source=:

string appDBPath = Application.persistentDataPath + "/xuanyusong.db";
DbAccess db = new DbAccess("Data Source=" + appDBPath);

建議用 using 區塊確保 Reader 關閉。Xcode Console 可見查詢結果:

Android 平台

  • DLL 需放在 Assets/Plugins/Android/
  • 連線字串用 URI=file: 前綴
  • Player Settings 需設定正確

Android 可正常顯示中文:

Mac 平台

  • 資料庫路徑:Application.dataPath + "/xuanyusong.db"
  • 連線字串:Data Source=
  • 可用 OnGUI() 直接在畫面顯示查詢結果

Windows 平台

  • 需額外放入 sqlite3.dll 到 Assets/Plugins/
  • 路徑與 Mac 相同:Application.dataPath
  • 打包後 .exe 同目錄下 xxx_Data/ 會有資料庫檔

重要提醒

  1. CreateTable 只能執行一次,重複會拋錯,需自行判斷表是否存在
  2. 中文編碼問題:插入時加單引號,讀取時 iOS 中文可能顯示異常(已寫入 DB),Android/Mac/Win 正常
  3. 專案下載:AndroidSQL.unitypackage.zip、SQLite (1).unitypackage.zip
copyright © 萬盛學電腦網 all rights reserved