Unity3D研究院之在Unity中打开第三方数据库配合Android开发（三十二）

最新推荐文章于 2022-11-04 10:55:01 发布

chenluwolf

最新推荐文章于 2022-11-04 10:55:01 发布

阅读量1k

点赞数

文章标签： unity unity3d 数据库沙盒手游

http://www.xuanyusong.com/archives/831

http://www.xuanyusong.com/archives/1454

如果大家对Unity中如何使用数据库还不是很了解那么请看我之前的这篇文章。Unity3D研究院之使用C＃语言建立本地数据库（二十三）本篇文章我们讨论如何在Unity中打开一个第三方数据库配合Android与编辑器进行同步开发。如下图所示，这个是我目前工程的结构，为了方便调试MOMO使用预定义标签将编辑器与Android平台区分开，方便编辑器与Android平台同时调试。

下图如果看的不清楚，点击图片可查看大图。不仅在Unity编辑器中可以显示数据库读取的内容，直接编译在真机中也可以显示数据库读取的内容。

按照上图所示我们把第三方数据库放在Plugins->Android->assets中，切记必须放在这里，否无无效，然后是代码。这个第三方数据库是MOMO以前做测试一个号码归属地的时候制作的。所以数据库的内容还是比较大的，蛤蛤。

DbAccess.cs

 
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)
 
        {
 
            string temp1 = e.ToString();
 
            Debug.Log(temp1);
 
        }
 
 
 
    }
 
 
 
    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)
 
 
 
    {
 
 
 
        string query = "SELECT * FROM " + tableName;
 
 
 
        return ExecuteQuery (query);
 
 
 
    }
 
 
 
    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)
 
 
 
    {
 
 
 
        string query = "DELETE FROM " + tableName;
 
 
 
        return ExecuteQuery (query);
 
 
 
    }
 
 
 
    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[0] + "' ";
 
 
 
        }
 
 
 
        return ExecuteQuery (query);
 
 
 
    }
 
 
 
}

然后是Test.cs直接把它挂在摄像机对象身上。

 
using UnityEngine;
 
using System.Collections;
 
using System.Collections.Generic;
 
using System.IO;
 
using Mono.Data.Sqlite;
 
public class Test  :MonoBehaviour
 
{
 
    //这三个是从数据库中取得的字段内容
 
    string  number = "null";
 
    string  city= "null";
 
    string  location = "null";
 
 
 
    //处理在Android中显示中文
 
    public GUISkin skin;
 
 
 
    void Start ()
 
    {
 
 
 
        loadSQL ();
 
    }
 
 
 
    void loadSQL ()
 
    {
 
 
 
//如果运行在编辑器中
 
#if UNITY_EDITOR
 
            //通过路径找到第三方数据库
 
            string appDBPath = Application.dataPath + "/Plugins/Android/assets/" + "location.db";
 
            DbAccess db = new DbAccess("URI=file:" + appDBPath);
 
//如果运行在Android设备中
 
#elif UNITY_ANDROID
 
 
 
        //将第三方数据库拷贝至Android可找到的地方
 
        string appDBPath = Application.persistentDataPath + "/" + "location.db";
 
 
 
        //如果已知路径没有地方放数据库，那么我们从Unity中拷贝
 
        if(!File.Exists(appDBPath))
 
 
 
        {
 
            //用www先从Unity中下载到数据库
 
            WWW loadDB = new WWW("jar:file://" + Application.dataPath + "!/assets/" + "location.db");
 
 
 
            //拷贝至规定的地方
 
            File.WriteAllBytes(appDBPath, loadDB.bytes);
 
 
 
        }
 
 
 
        //在这里重新得到db对象。
 
        DbAccess db = new DbAccess("URI=file:" + appDBPath);
 
 
 
#endif 
 
 
 
        using (SqliteDataReader sqReader = db.SelectWhere("location_date",new string[]{"number","city","location"},new string[]{"_id"},newstring[]{"="},new string[]{"25"}))
 
        {
 
 
 
            while (sqReader.Read())
 
            {
 
                  //从数据库中找到对应字段
 
                  number =  sqReader.GetString(sqReader.GetOrdinal("number"));
 
                  city =  sqReader.GetString(sqReader.GetOrdinal("city"));
 
                  location = sqReader.GetString(sqReader.GetOrdinal("location")) ;         
 
 
 
                Debug.Log("number =" + number + " city =" + city + " location =" + location );
 
 
 
            }
 
 
 
            sqReader.Close();
 
        }
 
 
 
        db.CloseSqlConnection();
 
 
 
    }
 
 
 
    void OnGUI()
 
    {
 
         GUI.skin = skin;
 
 
 
            GUILayout.Box("number =" + number);
 
            GUILayout.Box("city =" + city);
 
            GUILayout.Box("location =" + location);
 
    }
 
 
 
}

上面代码中我们使用到了预定于标签，用于编译时区分游戏平台与版本。这么一来我们既可以在编辑器中操作数据库也可以在Android中操作数据库一举两得，主要是是Android平台比较特殊，不能从Unity的Assets资源路径中读取二进制文件。说道二进制文件，这里不仅读取第三方数据库还可以读取其它文件，二进制文件都可以读取，比如文本文件、图片、资源等等。

需要注意的是你得把你的二进制文件放在Plugins->Android->assets中，然后根据下面的路径就可以在Android中读取。

string Path = jar:file://” + Application.dataPath + “!/assets/” + “你的文件“;

另外，使用这种方法读取地方放数据库后，是可以继续向数据库执行插入、删除、修改、查询、等操作，用起来还是比较方便的。

Unity预定义标签的种类还有很多，不仅可以区分平台还可以区分版本。


UNITY_EDITOR	Define for calling Unity Editor scripts from your game code.
UNITY_STANDALONE_OSX	Platform define for compiling/executing code specifically for Mac OS (This includes Universal, PPC and Intel architectures).
UNITY_DASHBOARD_WIDGET	Platform define when creating code for Mac OS dashboard widgets.
UNITY_STANDALONE_WIN	Use this when you want to compile/execute code for Windows stand alone applications.
UNITY_WEBPLAYER	Platform define for web player content (this includes Windows and Mac Web player executables).
UNITY_WII	Platform define for compiling/executing code for the Wii console.
UNITY_IPHONE	Platform define for compiling/executing code for the iPhone platform.
UNITY_ANDROID	Platform define for the Android platform.
UNITY_PS3	Platform define for running PlayStation 3 code.
UNITY_XBOX360	Platform define for executing Xbox 360 code.
UNITY_NACL	Platform define when compiling code for Google native client (this will be set additionally to UNITY_WEBPLAYER).
UNITY_FLASH	Platform define when compiling code for Adobe Flash.

Note: These defines were introduced at version 3.0.

Also you can compile code selectively depending on the version of the engine you are working on. Currently the supported ones are:

UNITY_2_6	Platform define for the major version of Unity 2.6.
UNITY_2_6_1	Platform define for specific version 1 from the major release 2.6.
UNITY_3_0	Platform define for the major version of Unity 3.0.
UNITY_3_0_0	Platform define for the specific version 0 of Unity 3.0.
UNITY_3_1	Platform define for major version of Unity 3.1.
UNITY_3_2	Platform define for major version of Unity 3.2.
UNITY_3_3	Platform define for major version of Unity 3.3.
UNITY_3_4	Platform define for major version of Unity 3.4.
UNITY_3_5	Platform define for major version of Unity 3.5.