首先你需要得到Mono.Data.Sqlite.dll 文件 與System.Data.dll文件。如果你在Mac 操作系統下使用Unity那麼很悲劇,找不到這兩個文件,至少我沒能找到。後來我在Windows下的Unity安裝路徑中找到了它。為了方便大家我將這兩個文件上傳至網盤中,如果沒有這兩個文件的朋友請下載。Unity數據庫文件.zip
.zip文件下載完畢後直接解壓,然後將Mono.Data.Sqlite.dll 文件 與System.Data.dll文件放在Unity工程中的Assets文件夾中。如下圖所示,兩個文件已經放置在Project視圖當中。
Ok ,我們編寫C#腳本,原始文章沒有Unity數據庫更新與刪除的方法,我在這裡加上更新與刪除的方法,方便大家開發時使用。因為其實Unity中更新與刪除數據庫也是個比較重要的功能。
注意:下面腳本不要綁定在任何游戲對象身上,大家無需把它當作腳本可以當作一個工具類來使用。
[代碼]java代碼:
001 using UnityEngine; 002 003 using System; 004 using System.Collections; 005 using Mono.Data.Sqlite; 006 007 public class DbAccess 008 009 { 010 011 private SqliteConnection dbConnection; 012 013 private SqliteCommand dbCommand; 014 015 private SqliteDataReader reader; 016 017 public DbAccess (string connectionString) 018 019 { 020 021 OpenDB (connectionString); 022 023 } 024 public DbAccess () 025 { 026 027 } 028 029 public void OpenDB (string connectionString) 030 031 { 032 try 033 { 034 dbConnection = new SqliteConnection (connectionString); 035 036 dbConnection.Open (); 037 038 Debug.Log ("Connected to db"); 039 } 040 catch(Exception e) 041 { 042 string temp1 = e.ToString(); 043 Debug.Log(temp1); 044 } 045 046 } 047 048 public void CloseSqlConnection () 049 050 { 051 052 if (dbCommand != null) { 053 054 dbCommand.Dispose (); 055 056 } 057 058 dbCommand = null; 059 060 if (reader != null) { 061 062 reader.Dispose (); 063 064 } 065 066 reader = null; 067 068 if (dbConnection != null) { 069 070 dbConnection.Close (); 071 072 } 073 074 dbConnection = null; 075 076 Debug.Log ("Disconnected from db."); 077 078 } 079 080 public SqliteDataReader ExecuteQuery (string sqlQuery) 081 082 { 083 084 dbCommand = dbConnection.CreateCommand (); 085 086 dbCommand.CommandText = sqlQuery; 087 088 reader = dbCommand.ExecuteReader (); 089 090 return reader; 091 092 } 093 094 public SqliteDataReader ReadFullTable (string tableName) 095 096 { 097 098 string query = "SELECT * FROM " + tableName; 099 100 return ExecuteQuery (query); 101 102 } 103 104 public SqliteDataReader InsertInto (string tableName, string[] values) 105 106 { 107 108 string query = "INSERT INTO " + tableName + " VALUES (" + values[0]; 109 110 for (int i = 1; i < values.Length; ++i) { 111 112 query += ", " + values[i]; 113 114 } 115 116 query += ")"; 117 118 return ExecuteQuery (query); 119 120 } 121 122 public SqliteDataReader UpdateInto (string tableName, string []cols,string []colsvalues,string selectkey,string selectvalue) 123 { 124 125 string query = "UPDATE "+tableName+" SET "+cols[0]+" = "+colsvalues[0]; 126 127 for (int i = 1; i < colsvalues.Length; ++i) { 128 129 query += ", " +cols[i]+" ="+ colsvalues[i]; 130 } 131 132 query += " WHERE "+selectkey+" = "+selectvalue+" "; 133 134 return ExecuteQuery (query); 135 } 136 137 public SqliteDataReader Delete(string tableName,string []cols,string []colsvalues) 138 { 139 string query = "DELETE FROM "+tableName + " WHERE " +cols[0] +" = " + colsvalues[0]; 140 141 for (int i = 1; i < colsvalues.Length; ++i) { 142 143 query += " or " +cols[i]+" = "+ colsvalues[i]; 144 } 145 Debug.Log(query); 146 return ExecuteQuery (query); 147 } 148 149 public SqliteDataReader InsertIntoSpecific (string tableName, string[] cols, string[] values) 150 151 { 152 153 if (cols.Length != values.Length) { 154 155 throw new SqliteException ("columns.Length != values.Length"); 156 157 } 158 159 string query = "INSERT INTO " + tableName + "(" + cols[0]; 160 161 for (int i = 1; i < cols.Length; ++i) { 162 163 query += ", " + cols[i]; 164 165 } 166 167 query += ") VALUES (" + values[0]; 168 169 for (int i = 1; i < values.Length; ++i) { 170 171 query += ", " + values[i]; 172 173 } 174 175 query += ")"; 176 177 return ExecuteQuery (query); 178 179 } 180 181 public SqliteDataReader DeleteContents (string tableName) 182 183 { 184 185 string query = "DELETE FROM " + tableName; 186 187 return ExecuteQuery (query); 188 189 } 190 191 public SqliteDataReader CreateTable (string name, string[] col, string[] colType) 192 193 { 194 195 if (col.Length != colType.Length) { 196 197 throw new SqliteException ("columns.Length != colType.Length"); 198 199 } 200 201 string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0]; 202 203 for (int i = 1; i < col.Length; ++i) { 204 205 query += ", " + col[i] + " " + colType[i]; 206 207 } 208 209 query += ")"; 210 211 return ExecuteQuery (query); 212 213 } 214 215 public SqliteDataReader SelectWhere (string tableName, string[] items, string[] col, string[] operation, string[] values) 216 217 { 218 219 if (col.Length != operation.Length || operation.Length != values.Length) { 220 221 throw new SqliteException ("col.Length != operation.Length != values.Length"); 222 223 } 224 225 string query = "SELECT " + items[0]; 226 227 for (int i = 1; i < items.Length; ++i) { 228 229 query += ", " + items[i]; 230 231 } 232 233 query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' "; 234 235 for (int i = 1; i < col.Length; ++i) { 236 237 query += " AND " + col[i] + operation[i] + "'" + values[0] + "' "; 238 239 } 240 241 return ExecuteQuery (query); 242 243 } 244 245 }首先是創建本地數據庫,我們創建C#腳本Test.cs直接綁定在攝像機中。
[代碼]java代碼:
01 using UnityEngine; 02 using System.Collections; 03 04 public class Test : MonoBehaviour 05 { 06 07 void Start () 08 { 09 10 //創建數據庫名稱為xuanyusong.db 11 DbAccess db = new DbAccess("data source=xuanyusong.db"); 12 13 //創建數據庫表,與字段 14 db.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"}); 15 //關閉對象 16 db.CloseSqlConnection(); 17 } 18 19 }運行游戲後,數據庫對象會自動生成在項目的根目錄中。查看數據庫的軟件我使用的是Navicat Premium,如果沒有請大家下載,然後繼續。如下圖所示,數據庫文件xuanyusong.db已經生成在項目的根目錄中,接著我使用Navicat Premium軟件將這個數據庫打開。數據庫的表名為momo 打開表後字段包含name、 qq 、email、 blog。都是我們在代碼中創建的。
OK,我們繼續。首先是插入數據,記得將編碼修改成UTF-16 不然中文會亂碼。
[代碼]java代碼:
01 using UnityEngine; 02 using System.Collections; 03 04 public class Test : MonoBehaviour 05 { 06 07 void Start () 08 { 09 10 //創建數據庫名稱為xuanyusong.db 11 DbAccess db = new DbAccess("data source=xuanyusong.db"); 12 //請注意 插入字符串是 已經要加上'宣雨松' 不然會報錯 13 db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'[email protected]'","'www.xuanyusong.com'" }); 14 db.CloseSqlConnection(); 15 } 16 17 }接著是更新數據。UpdateInto是我新寫的方法,接受更新多條數據。
[代碼]java代碼:
01 using UnityEngine; 02 using System.Collections; 03 04 public class Test : MonoBehaviour 05 { 06 07 void Start () 08 { 09 10 //創建數據庫名稱為xuanyusong.db 11 DbAccess db = new DbAccess("data source=xuanyusong.db"); 12 13 db.UpdateInto("momo",new string[]{"name","qq"},new string[]{"'xuanyusong'","'11111111'"}, "email", "'[email protected]'" ); 14 15 db.CloseSqlConnection(); 16 } 17 18 }然後是刪除數據DELETE也是我封裝的方法。
[代碼]java代碼:
01 using UnityEngine; 02 using System.Collections; 03 04 public class Test : MonoBehaviour 05 { 06 07 void Start () 08 { 09 10 //創建數據庫名稱為xuanyusong.db 11 DbAccess db = new DbAccess("data source=xuanyusong.db"); 12 //請注意 插入字符串是 已經要加上'宣雨松' 不然會報錯 13 db.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"}); 14 //我在數據庫中連續插入三條數據 15 db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'[email protected]'","'www.xuanyusong.com'" }); 16 db.InsertInto("momo", new string[]{ "'雨松MOMO'","'289187120'","'[email protected]'","'www.xuanyusong.com'" }); 17 db.InsertInto("momo", new string[]{ "'哇咔咔'","'289187120'","'[email protected]'","'www.xuanyusong.com'" }); 18 19 //然後在刪掉兩條數據 20 db.Delete("momo",new string[]{"email","email"}, new string[]{"'[email protected]'","'[email protected]'"} ); 21 22 db.CloseSqlConnection(); 23 } 24 25 }最後是查找數據。
[代碼]java代碼:
01 using UnityEngine; 02 using System.Collections; 03 04 using Mono.Data.Sqlite; 05 public class Test : MonoBehaviour 06 { 07 08 void Start () 09 { 10 11 //創建數據庫名稱為xuanyusong.db 12 DbAccess db = new DbAccess("data source=xuanyusong.db"); 13 //請注意 插入字符串是 已經要加上'宣雨松' 不然會報錯 14 db.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"}); 15 //我在數據庫中連續插入三條數據 16 db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'[email protected]'","'www.xuanyusong.com'" }); 17 db.InsertInto("momo", new string[]{ "'雨松MOMO'","'289187120'","'[email protected]'","'www.xuanyusong.com'" }); 18 db.InsertInto("momo", new string[]{ "'哇咔咔'","'289187120'","'[email protected]'","'www.xuanyusong.com'" }); 19 20 //然後在刪掉兩條數據 21 db.Delete("momo",new string[]{"email","email"}, new string[]{"'[email protected]'","'[email protected]'"} ); 22 23 //注解1 24 SqliteDataReader sqReader = db.SelectWhere("momo",new string[]{"name","email"},new string[]{"qq"},new string[]{"="},new string[]{"289187120"}); 25 26 while (sqReader.Read()) 27 { 28 Debug.Log(sqReader.GetString(sqReader.GetOrdinal("name")) + sqReader.GetString(sqReader.GetOrdinal("email"))); 29 } 30 31 db.CloseSqlConnection(); 32 } 33 34 }注解1:這裡的結構非常像安卓的數據庫指針,然後while循環把每一條數據都取出來。 sqReader.Gerordinal()方法就是拿到對應列名稱的數據。如下圖所示,經過一些列的添加與刪除的操作最後數據庫的內容如下。
如下圖所示,我使用Log也將數據庫name 與 email的字段打印了出來。最後我在強調一點,我們在OnStart方法中db.CreateTable創建數據庫表,如果重復創建系統會拋出錯誤。避免這個情況請保證你的數據庫表只會被創建一次。祝大家學習愉快嘎嘎嘎~~~
如下圖所示,請先在PlaySettings中修改Api Compatibility Level 改成.NET 2.0,如果不修改會報錯
注意:Error building Player: Extracting referenced dlls failed.
無論你編譯任何平台都請修改一下這裡, 留言中有朋友在編譯PC平台中 因為沒有修改這裡導致無法編譯成功。。
IOS平台SQLite的使用:
然後需要修改Test.cs的腳本,在修改一下數據庫保存的路徑,我們將數據庫放在沙盒當中。這樣IOS中才可以讀取數據庫。
[代碼]java代碼:
01 using UnityEngine; 02 using System.Collections; 03 04 using Mono.Data.Sqlite; 05 public class Test : MonoBehaviour 06 { 07 08 void Start () 09 { 10 //數據庫文件儲存地址 11 string appDBPath = Application.persistentDataPath + "/xuanyusong.db"; 12 13 DbAccess db = new DbAccess(@"Data Source=" + appDBPath); 14 15 //請注意 插入字符串是 已經要加上'宣雨松' 不然會報錯 16 db.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"}); 17 //我在數據庫中連續插入三條數據 18 db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'[email protected]'","'www.xuanyusong.com'" }); 19 db.InsertInto("momo", new string[]{ "'雨松MOMO'","'289187120'","'[email protected]'","'www.xuanyusong.com'" }); 20 db.InsertInto("momo", new string[]{ "'哇咔咔'","'289187120'","'[email protected]'","'www.xuanyusong.com'" }); 21 22 //然後在刪掉兩條數據 23 db.Delete("momo",new string[]{"email","email"}, new string[]{"'[email protected]'","'[email protected]'"} ); 24 25 //注解1 26 using (SqliteDataReader sqReader = db.SelectWhere("momo",new string[]{"name","email"},new string[]{"qq"},new string[]{"="},new string[]{"289187120"})) 27 { 28 29 while (sqReader.Read()) 30 { 31 //目前中文無法顯示 32 Debug.Log(sqReader.GetString(sqReader.GetOrdinal("name"))); 33 34 Debug.Log(sqReader.GetString(sqReader.GetOrdinal("email"))); 35 36 } 37 38 sqReader.Close(); 39 } 40 41 db.CloseSqlConnection(); 42 } 43 44 }下面開始打包成IOS版本,直接運行如下圖所示,已經在XCODE的控制台中將字符串信息打印出來。目前我不知道如何讀取中文,但是可以確定的是中文信息已經寫入數據庫中。不信大家可以打開沙盒看看。
Android平台SQLite的使用:
Android與IOS在使用SQLite數據庫時有點區別,Android需要將第三方DLL放在Plugins當中。腳本也需要修改一下,先看看Test.cs的改動。
[代碼]java代碼:
01 using UnityEngine; 02 using System.Collections; 03 04 using Mono.Data.Sqlite; 05 public class Test : MonoBehaviour 06 { 07 08 void Start () 09 { 10 //數據庫文件儲存地址 11 12 string appDBPath = Application.persistentDataPath + "/xuanyusong.db"; 13 14 //注意!!!!!!!這行代碼的改動 15 DbAccess db = new DbAccess("URI=file:" + appDBPath); 16 17 //請注意 插入字符串是 已經要加上'宣雨松' 不然會報錯 18 db.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"}); 19 //我在數據庫中連續插入三條數據 20 db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'[email protected]'","'www.xuanyusong.com'" }); 21 db.InsertInto("momo", new string[]{ "'雨松MOMO'","'289187120'","'[email protected]'","'www.xuanyusong.com'" }); 22 db.InsertInto("momo", new string[]{ "'哇咔咔'","'289187120'","'[email protected]'","'www.xuanyusong.com'" }); 23 24 //然後在刪掉兩條數據 25 db.Delete("momo",new string[]{"email","email"}, new string[]{"'[email protected]'","'[email protected]'"} ); 26 27 //注解1 28 using (SqliteDataReader sqReader = db.SelectWhere("momo",new string[]{"name","email"},new string[]{"qq"},new string[]{"="},new string[]{"289187120"})) 29 { 30 31 while (sqReader.Read()) 32 { 33 Debug.Log("xuanyusong" + sqReader.GetString(sqReader.GetOrdinal("name"))); 34 35 Debug.Log("xuanyusong" + sqReader.GetString(sqReader.GetOrdinal("email"))); 36 37 } 38 39 sqReader.Close(); 40 } 41 42 db.CloseSqlConnection(); 43 } 44 45 void Update() 46 { 47 if (Input.GetKeyDown(KeyCode.Escape) ||Input.GetKeyDown(KeyCode.Home) ) 48 { 49 50 Application.Quit(); 51 } 52 } 53 54 }如下圖所示,Player Settings 請和我保持一致。
值得慶幸的是在Android下讀取數據庫時正常的顯示了中文。如下圖所示,運行打包後的程序後在Eclipse的後台已經能看到數據庫顯示的中文與英文,呵呵。
由於工程中需要一些DLL,所以我將工程的下載地址放出,請大家下載。AndroidSQL.unitypackage.zip
MAC平台下的使用:
請先下載原始版本 SQLite (1).unitypackage.zip
我們只需在原始版本之上進行修改即可。
修改Test.cs文件 ,請注意我在代碼中標注的內容。
[代碼]java代碼:
01 using UnityEngine; 02 using System.Collections; 03 04 using Mono.Data.Sqlite; 05 public class Test : MonoBehaviour 06 { 07 08 string name = null; 09 string email = null; 10 string appDBPath = null; 11 void Start () 12 { 13 14 //////////-------- 15 //請注意!!!!!!! 16 //這裡的修改 17 18 appDBPath = Application.dataPath + "/xuanyusong.db"; 19 20 DbAccess db = new DbAccess(@"Data Source=" + appDBPath); 21 22 //////////-------- 23 24 //請注意 插入字符串是 已經要加上'宣雨松' 不然會報錯 25 db.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"}); 26 //我在數據庫中連續插入三條數據 27 db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'[email protected]'","'www.xuanyusong.com'" }); 28 db.InsertInto("momo", new string[]{ "'雨松MOMO'","'289187120'","'[email protected]'","'www.xuanyusong.com'" }); 29 db.InsertInto("momo", new string[]{ "'哇咔咔'","'289187120'","'[email protected]'","'www.xuanyusong.com'" }); 30 31 //然後在刪掉兩條數據 32 db.Delete("momo",new string[]{"email","email"}, new string[]{"'[email protected]'","'[email protected]'"} ); 33 34 SqliteDataReader sqReader = db.SelectWhere("momo",new string[]{"name","email"},new string[]{"qq"},new string[]{"="},new string[]{"289187120"}); 35 36 while (sqReader.Read()) 37 { 38 39 Debug.Log(sqReader.GetString(sqReader.GetOrdinal("name")) + sqReader.GetString(sqReader.GetOrdinal("email"))); 40 41 //取值 42 name = sqReader.GetString(sqReader.GetOrdinal("name")); 43 email = sqReader.GetString(sqReader.GetOrdinal("email")); 44 } 45 46 db.CloseSqlConnection(); 47 } 48 49 void OnGUI() 50 { 51 52 ///為了讓大家看的更清楚 我將數據庫取出的內容顯示在屏幕中 53 if(name != null) 54 { 55 GUILayout.Label("XXXXXXXXXXXXX" + name); 56 57 } 58 59 if (email!= null) 60 { 61 GUILayout.Label("XXXXXXXXXXXXX" + email); 62 } 63 64 if(appDBPath != null) 65 { 66 GUILayout.Label("數據庫的路徑" + appDBPath); 67 } 68 69 } 70 71 }生成工程後,運行生成的mac程序,我們可以看到 數據已經取出來了。
Windows平台SQLite的使用:
Windows平台下與Mac平台有點區別,廢了老半天來找到問題所在。MOMO感謝在博客後面留言的朋友,因為沒有你們的留言我也不會去研究MAC Windows下如何使用 呵呵。
進入正題,還是先修改Test.cs文件
[代碼]java代碼:
01 using System.Collections; 02 03 using Mono.Data.Sqlite; 04 05 //using Mono.Data.SqliteClient; 06 07 public class Test : MonoBehaviour 08 { 09 10 string name = null; 11 string email = null; 12 string path = null; 13 14 void Start () 15 { 16 //數據庫文件儲存地址 17 18 //注意這裡的修改!!!!!!!!!!!!!! 19 string appDBPath = Application.dataPath + "/xuanyusong.db"; 20 21 //-------------------------- 22 23 DbAccess db = new DbAccess(@"Data Source=" + appDBPath); 24 25 path = appDBPath; 26 27 //請注意 插入字符串是 已經要加上'宣雨松' 不然會報錯 28 db.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"}); 29 //我在數據庫中連續插入三條數據 30 db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'[email protected]'","'www.xuanyusong.com'" }); 31 db.InsertInto("momo", new string[]{ "'雨松MOMO'","'289187120'","'[email protected]'","'www.xuanyusong.com'" }); 32 db.InsertInto("momo", new string[]{ "'哇咔咔'","'289187120'","'[email protected]'","'www.xuanyusong.com'" }); 33 34 //然後在刪掉兩條數據 35 db.Delete("momo",new string[]{"email","email"}, new string[]{"'[email protected]'","'[email protected]'"} ); 36 37 //注解1 38 using (SqliteDataReader sqReader = db.SelectWhere("momo",new string[]{"name","email"},new string[]{"qq"},new string[]{"="},new string[]{"289187120"})) 39 { 40 41 while (sqReader.Read()) 42 { 43 //目前中文無法顯示 44 Debug.Log("xuanyusong" + sqReader.GetString(sqReader.GetOrdinal("name"))); 45 46 Debug.Log("xuanyusong" + sqReader.GetString(sqReader.GetOrdinal("email"))); 47 48 name = sqReader.GetString(sqReader.GetOrdinal("name")); 49 email = sqReader.GetString(sqReader.GetOrdinal("email")); 50 51 } 52 53 sqReader.Close(); 54 } 55 56 db.CloseSqlConnection(); 57 } 58 59 void OnGUI() 60 { 61 if(name != null) 62 { 63 GUILayout.Label(name); 64 } 65 66 if(email != null) 67 { 68 GUILayout.Label(email); 69 } 70 71 if(path != null) 72 { 73 GUILayout.Label(path); 74 } 75 } 76 77 }如下圖所示打開Unity然後我們需要下載sqlite3.dll文件,接著將dll都放入Plugins文件夾中。不用擔心 稍後我會把真個工程的下載地址貼出來其中包括所有的dll 。
最後直接打包成Windows平台工程。雙擊運行.exe文件,如下圖所示數據庫的數據以及路徑MOMO已經打印在屏幕當中啦。哇咔咔~ 然後xuanyusong.db文件就放在ddd_Date文件夾中,我已經用紅圈標注出來了。ddd就是工程的名稱,ddd_Date該文件夾是自動生成的。