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

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);

    }

}

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);
	}

}