unity中使用本機資料庫sqlite

標籤：

sqlite的安全性沒用mySql高，因為sqlite不用建立使用者，以及使用權限設定，sqlite是單機資料庫，功能簡約，小型化，MySql試用於伺服器資料量大功能多需要安裝，例如網站訪問量比較大的資料管理

其實MySQL與Sqlite中的資料庫操作函數基本上都一樣，沒什麼區別，主要區別就是許可權和庫大小

注意：在使用這個sqlite資料管理時，也要把，幾個需要的動態庫放到Plugins裡面（Mono.Data.Sqlite,   sqlite3,   System.Data）這幾個動態庫百度都能搜到

1.先建立一個資料庫封裝類DbAccess

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

public class DbAccess

{

private SqliteConnection dbConnection;
private SqliteCommand dbCommand;
private SqliteDataReader reader;

public DbAccess (string connectionString)
{

OpenDB (connectionString);

}

//開啟資料庫
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.");

}

//執行SQL語句
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);

}

}

2.下面建立一個類用來對資料哭進行操作，這個指令碼掛在一個啟用的gameobject上就可以了

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

public class Test : MonoBehaviour
{

void Start()
{
//資料庫檔案儲存地址
string appDBPath = Application.dataPath + "/MZ.db";

DbAccess db = new DbAccess(@"Data Source=" + appDBPath);
path = appDBPath;

if(!System.IO.File.Exists(@"Data Source=" + appDBPath))
{
//建立表格
db.CreateTable("CQ", new string[] { "name", "qq", "email", "blog" }, new string[] { "text", "text", "text", "text" });
}

//插入資料
db.InsertInto("CQ",new string[] {"‘XQ‘", "‘520520‘", "‘[email protected]‘", "‘www.blog1.com‘"});
db.InsertInto("CQ", new string[] { "‘XX‘", "‘552200‘", "‘[email protected]‘", "‘www.blog2.com‘" });
db.InsertInto("CQ", new string[] { "‘XQ‘", "‘520520‘", "‘[email protected]‘", "‘www.blog3.com‘" });
//刪除資料
db.Delete("CQ", new string[] { "email", "email" }, new string[] { "‘[email protected]‘", "‘[email protected]‘" });

using (SqliteDataReader sqReader = db.SelectWhere("CQ", new string[] { "name", "email" }, new string[] { "qq" }, new string[] { "=" }, new string[] { "520520" }))
{
while (sqReader.Read())
{
//目前中文無法顯示
Debug.Log("CQ" + sqReader.GetString(sqReader.GetOrdinal("name")));

Debug.Log("CQ" + sqReader.GetString(sqReader.GetOrdinal("email")));

}

sqReader.Close();
}

db.CloseSqlConnection();
}

}

unity中使用本機資料庫sqlite