2013年9月16日 星期一

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

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"},new string[]{"="},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_EDITORDefine for calling Unity Editor scripts from your game code.
UNITY_STANDALONE_OSXPlatform define for compiling/executing code specifically for Mac OS (This includes Universal, PPC and Intel architectures).
UNITY_DASHBOARD_WIDGETPlatform define when creating code for Mac OS dashboard widgets.
UNITY_STANDALONE_WINUse this when you want to compile/execute code for Windows stand alone applications.
UNITY_WEBPLAYERPlatform define for web player content (this includes Windows and Mac Web player executables).
UNITY_WIIPlatform define for compiling/executing code for the Wii console.
UNITY_IPHONEPlatform define for compiling/executing code for the iPhone platform.
UNITY_ANDROIDPlatform define for the Android platform.
UNITY_PS3Platform define for running PlayStation 3 code.
UNITY_XBOX360Platform define for executing Xbox 360 code.
UNITY_NACLPlatform define when compiling code for Google native client (this will be set additionally to UNITY_WEBPLAYER).
UNITY_FLASHPlatform 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_6Platform define for the major version of Unity 2.6.
UNITY_2_6_1Platform define for specific version 1 from the major release 2.6.
UNITY_3_0Platform define for the major version of Unity 3.0.
UNITY_3_0_0Platform define for the specific version 0 of Unity 3.0.
UNITY_3_1Platform define for major version of Unity 3.1.
UNITY_3_2Platform define for major version of Unity 3.2.
UNITY_3_3Platform define for major version of Unity 3.3.
UNITY_3_4Platform define for major version of Unity 3.4.
UNITY_3_5Platform define for major version of Unity 3.5. 
用法就不解释了,地球人都知道。
雨松MOMO希望和大家一起进步,加油~~~最后文本的源码下载:http://vdisk.weibo.com/s/ac3xI
雨松MOMO祝大家学习愉快,哇咔咔,晚安啦啦啦~~