原始文章主要是使用JavaScript语言建立本地数据库
以前在开发中一直使用IOS源生的数据库,通过传递消息的形式在与Unity3D中进行交互。本文我在详细说说如何使用C#语言来在MAC 操作系统下创建Unity本地数据库,我是C#控.
首先你需要得到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代码:
| 004 | using System.Collections; |
| 005 | using Mono.Data.Sqlite; |
| 011 | private SqliteConnection dbConnection; |
| 013 | private SqliteCommand dbCommand; |
| 015 | private SqliteDataReader reader; |
| 017 | public DbAccess (string connectionString) |
| 021 | OpenDB (connectionString); |
| 029 | public void OpenDB (string connectionString) |
| 034 | dbConnection = new SqliteConnection (connectionString); |
| 036 | dbConnection.Open (); |
| 038 | Debug.Log ("Connected to db"); |
| 042 | string temp1 = e.ToString(); |
| 048 | public void CloseSqlConnection () |
| 052 | if (dbCommand != null) { |
| 054 | dbCommand.Dispose (); |
| 060 | if (reader != null) { |
| 068 | if (dbConnection != null) { |
| 070 | dbConnection.Close (); |
| 076 | Debug.Log ("Disconnected from db."); |
| 080 | public SqliteDataReader ExecuteQuery (string sqlQuery) |
| 084 | dbCommand = dbConnection.CreateCommand (); |
| 086 | dbCommand.CommandText = sqlQuery; |
| 088 | reader = dbCommand.ExecuteReader (); |
| 094 | public SqliteDataReader ReadFullTable (string tableName) |
| 098 | string query = "SELECT * FROM " + tableName; |
| 100 | return ExecuteQuery (query); |
| 104 | public SqliteDataReader InsertInto (string tableName, string[] values) |
| 108 | string query = "INSERT INTO " + tableName + " VALUES (" + values[0]; |
| 110 | for (int i = 1; i < values.Length; ++i) { |
| 112 | query += ", " + values[i]; |
| 118 | return ExecuteQuery (query); |
| 122 | public SqliteDataReader UpdateInto (string tableName, string []cols,string []colsvalues,string selectkey,string selectvalue) |
| 125 | string query = "UPDATE "+tableName+" SET "+cols[0]+" = "+colsvalues[0]; |
| 127 | for (int i = 1; i < colsvalues.Length; ++i) { |
| 129 | query += ", " +cols[i]+" ="+ colsvalues[i]; |
| 132 | query += " WHERE "+selectkey+" = "+selectvalue+" "; |
| 134 | return ExecuteQuery (query); |
| 137 | public SqliteDataReader Delete(string tableName,string []cols,string []colsvalues) |
| 139 | string query = "DELETE FROM "+tableName + " WHERE " +cols[0] +" = " + colsvalues[0]; |
| 141 | for (int i = 1; i < colsvalues.Length; ++i) { |
| 143 | query += " or " +cols[i]+" = "+ colsvalues[i]; |
| 146 | return ExecuteQuery (query); |
| 149 | public SqliteDataReader InsertIntoSpecific (string tableName, string[] cols, string[] values) |
| 153 | if (cols.Length != values.Length) { |
| 155 | throw new SqliteException ("columns.Length != values.Length"); |
| 159 | string query = "INSERT INTO " + tableName + "(" + cols[0]; |
| 161 | for (int i = 1; i < cols.Length; ++i) { |
| 163 | query += ", " + cols[i]; |
| 167 | query += ") VALUES (" + values[0]; |
| 169 | for (int i = 1; i < values.Length; ++i) { |
| 171 | query += ", " + values[i]; |
| 177 | return ExecuteQuery (query); |
| 181 | public SqliteDataReader DeleteContents (string tableName) |
| 185 | string query = "DELETE FROM " + tableName; |
| 187 | return ExecuteQuery (query); |
| 191 | public SqliteDataReader CreateTable (string name, string[] col, string[] colType) |
| 195 | if (col.Length != colType.Length) { |
| 197 | throw new SqliteException ("columns.Length != colType.Length"); |
| 201 | string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0]; |
| 203 | for (int i = 1; i < col.Length; ++i) { |
| 205 | query += ", " + col[i] + " " + colType[i]; |
| 211 | return ExecuteQuery (query); |
| 215 | public SqliteDataReader SelectWhere (string tableName, string[] items, string[] col, string[] operation, string[] values) |
| 219 | if (col.Length != operation.Length || operation.Length != values.Length) { |
| 221 | throw new SqliteException ("col.Length != operation.Length != values.Length"); |
| 225 | string query = "SELECT " + items[0]; |
| 227 | for (int i = 1; i < items.Length; ++i) { |
| 229 | query += ", " + items[i]; |
| 233 | query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' "; |
| 235 | for (int i = 1; i < col.Length; ++i) { |
| 237 | query += " AND " + col[i] + operation[i] + "'" + values[0] + "' "; |
| 241 | return ExecuteQuery (query); |
首先是创建本地数据库,我们创建C#脚本Test.cs直接绑定在摄像机中。
[代码]java代码:
| 02 | using System.Collections; |
| 04 | public class Test : MonoBehaviour |
| 10 | //创建数据库名称为xuanyusong.db |
| 11 | DbAccess db = new DbAccess("data source=xuanyusong.db"); |
| 14 | db.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"}); |
| 16 | db.CloseSqlConnection(); |
运行游戏后,数据库对象会自动生成在项目的根目录中。查看数据库的软件我使用的是Navicat Premium,如果没有请大家下载,然后继续。如下图所示,数据库文件xuanyusong.db已经生成在项目的根目录中,接着我使用Navicat Premium软件将这个数据库打开。数据库的表名为momo 打开表后字段包含name、 qq 、email、 blog。都是我们在代码中创建的。
![]()
OK,我们继续。首先是插入数据,记得将编码修改成UTF-16 不然中文会乱码。
[代码]java代码:
| 02 | using System.Collections; |
| 04 | public class Test : MonoBehaviour |
| 10 | //创建数据库名称为xuanyusong.db |
| 11 | DbAccess db = new DbAccess("data source=xuanyusong.db"); |
| 12 | //请注意 插入字符串是 已经要加上'宣雨松' 不然会报错 |
| 13 | db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'xuanyusong@gmail.com'","'www.xuanyusong.com'" }); |
| 14 | db.CloseSqlConnection(); |
接着是更新数据。UpdateInto是我新写的方法,接受更新多条数据。
[代码]java代码:
| 02 | using System.Collections; |
| 04 | public class Test : MonoBehaviour |
| 10 | //创建数据库名称为xuanyusong.db |
| 11 | DbAccess db = new DbAccess("data source=xuanyusong.db"); |
| 13 | db.UpdateInto("momo",new string[]{"name","qq"},new string[]{"'xuanyusong'","'11111111'"}, "email", "'xuanyusong@gmail.com'" ); |
| 15 | db.CloseSqlConnection(); |
然后是删除数据DELETE也是我封装的方法。
[代码]java代码:
| 02 | using System.Collections; |
| 04 | public class Test : MonoBehaviour |
| 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"}); |
| 15 | db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'xuanyusong@gmail.com'","'www.xuanyusong.com'" }); |
| 16 | db.InsertInto("momo", new string[]{ "'雨松MOMO'","'289187120'","'000@gmail.com'","'www.xuanyusong.com'" }); |
| 17 | db.InsertInto("momo", new string[]{ "'哇咔咔'","'289187120'","'111@gmail.com'","'www.xuanyusong.com'" }); |
| 20 | db.Delete("momo",new string[]{"email","email"}, new string[]{"'xuanyusong@gmail.com'","'000@gmail.com'"} ); |
| 22 | db.CloseSqlConnection(); |
***是查找数据。
[代码]java代码:
| 02 | using System.Collections; |
| 04 | using Mono.Data.Sqlite; |
| 05 | public class Test : MonoBehaviour |
| 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"}); |
| 16 | db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'xuanyusong@gmail.com'","'www.xuanyusong.com'" }); |
| 17 | db.InsertInto("momo", new string[]{ "'雨松MOMO'","'289187120'","'000@gmail.com'","'www.xuanyusong.com'" }); |
| 18 | db.InsertInto("momo", new string[]{ "'哇咔咔'","'289187120'","'111@gmail.com'","'www.xuanyusong.com'" }); |
| 21 | db.Delete("momo",new string[]{"email","email"}, new string[]{"'xuanyusong@gmail.com'","'000@gmail.com'"} ); |
| 24 | SqliteDataReader sqReader = db.SelectWhere("momo",new string[]{"name","email"},new string[]{"qq"},new string[]{"="},new string[]{"289187120"}); |
| 26 | while (sqReader.Read()) |
| 28 | Debug.Log(sqReader.GetString(sqReader.GetOrdinal("name")) + sqReader.GetString(sqReader.GetOrdinal("email"))); |
| 31 | db.CloseSqlConnection(); |
注解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代码:
| 02 | using System.Collections; |
| 04 | using Mono.Data.Sqlite; |
| 05 | public class Test : MonoBehaviour |
| 11 | string appDBPath = Application.persistentDataPath + "/xuanyusong.db"; |
| 13 | DbAccess db = new DbAccess(@"Data Source=" + appDBPath); |
| 15 | //请注意 插入字符串是 已经要加上'宣雨松' 不然会报错 |
| 16 | db.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"}); |
| 18 | db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'xuanyusong@gmail.com'","'www.xuanyusong.com'" }); |
| 19 | db.InsertInto("momo", new string[]{ "'雨松MOMO'","'289187120'","'000@gmail.com'","'www.xuanyusong.com'" }); |
| 20 | db.InsertInto("momo", new string[]{ "'哇咔咔'","'289187120'","'111@gmail.com'","'www.xuanyusong.com'" }); |
| 23 | db.Delete("momo",new string[]{"email","email"}, new string[]{"'xuanyusong@gmail.com'","'000@gmail.com'"} ); |
| 26 | using (SqliteDataReader sqReader = db.SelectWhere("momo",new string[]{"name","email"},new string[]{"qq"},new string[]{"="},new string[]{"289187120"})) |
| 29 | while (sqReader.Read()) |
| 32 | Debug.Log(sqReader.GetString(sqReader.GetOrdinal("name"))); |
| 34 | Debug.Log(sqReader.GetString(sqReader.GetOrdinal("email"))); |
| 41 | db.CloseSqlConnection(); |
下面开始打包成IOS版本,直接运行如下图所示,已经在XCODE的控制台中将字符串信息打印出来。目前我不知道如何读取中文,但是可以确定的是中文信息已经写入数据库中。不信大家可以打开沙盒看看。
![]()
Android平台SQLite的使用:
Android与IOS在使用SQLite数据库时有点区别,Android需要将第三方DLL放在Plugins当中。脚本也需要修改一下,先看看Test.cs的改动。
[代码]java代码:
| 02 | using System.Collections; |
| 04 | using Mono.Data.Sqlite; |
| 05 | public class Test : MonoBehaviour |
| 12 | string appDBPath = Application.persistentDataPath + "/xuanyusong.db"; |
| 15 | DbAccess db = new DbAccess("URI=file:" + appDBPath); |
| 17 | //请注意 插入字符串是 已经要加上'宣雨松' 不然会报错 |
| 18 | db.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"}); |
| 20 | db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'xuanyusong@gmail.com'","'www.xuanyusong.com'" }); |
| 21 | db.InsertInto("momo", new string[]{ "'雨松MOMO'","'289187120'","'000@gmail.com'","'www.xuanyusong.com'" }); |
| 22 | db.InsertInto("momo", new string[]{ "'哇咔咔'","'289187120'","'111@gmail.com'","'www.xuanyusong.com'" }); |
| 25 | db.Delete("momo",new string[]{"email","email"}, new string[]{"'xuanyusong@gmail.com'","'000@gmail.com'"} ); |
| 28 | using (SqliteDataReader sqReader = db.SelectWhere("momo",new string[]{"name","email"},new string[]{"qq"},new string[]{"="},new string[]{"289187120"})) |
| 31 | while (sqReader.Read()) |
| 33 | Debug.Log("xuanyusong" + sqReader.GetString(sqReader.GetOrdinal("name"))); |
| 35 | Debug.Log("xuanyusong" + sqReader.GetString(sqReader.GetOrdinal("email"))); |
| 42 | db.CloseSqlConnection(); |
| 47 | if (Input.GetKeyDown(KeyCode.Escape) ||Input.GetKeyDown(KeyCode.Home) ) |
如下图所示,Player Settings 请和我保持一致。
![]()
值得庆幸的是在Android下读取数据库时正常的显示了中文。如下图所示,运行打包后的程序后在Eclipse的后台已经能看到数据库显示的中文与英文,呵呵。
![]()
由于工程中需要一些DLL,所以我将工程的下载地址放出,请大家下载。AndroidSQL.unitypackage.zip
MAC平台下的使用:
请先下载原始版本 SQLite (1).unitypackage.zip
我们只需在原始版本之上进行修改即可。
修改Test.cs文件 ,请注意我在代码中标注的内容。
[代码]java代码:
| 02 | using System.Collections; |
| 04 | using Mono.Data.Sqlite; |
| 05 | public class Test : MonoBehaviour |
| 10 | string appDBPath = null; |
| 18 | appDBPath = Application.dataPath + "/xuanyusong.db"; |
| 20 | DbAccess db = new DbAccess(@"Data Source=" + appDBPath); |
| 24 | //请注意 插入字符串是 已经要加上'宣雨松' 不然会报错 |
| 25 | db.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"}); |
| 27 | db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'xuanyusong@gmail.com'","'www.xuanyusong.com'" }); |
| 28 | db.InsertInto("momo", new string[]{ "'雨松MOMO'","'289187120'","'000@gmail.com'","'www.xuanyusong.com'" }); |
| 29 | db.InsertInto("momo", new string[]{ "'哇咔咔'","'289187120'","'111@gmail.com'","'www.xuanyusong.com'" }); |
| 32 | db.Delete("momo",new string[]{"email","email"}, new string[]{"'xuanyusong@gmail.com'","'000@gmail.com'"} ); |
| 34 | SqliteDataReader sqReader = db.SelectWhere("momo",new string[]{"name","email"},new string[]{"qq"},new string[]{"="},new string[]{"289187120"}); |
| 36 | while (sqReader.Read()) |
| 39 | Debug.Log(sqReader.GetString(sqReader.GetOrdinal("name")) + sqReader.GetString(sqReader.GetOrdinal("email"))); |
| 42 | name = sqReader.GetString(sqReader.GetOrdinal("name")); |
| 43 | email = sqReader.GetString(sqReader.GetOrdinal("email")); |
| 46 | db.CloseSqlConnection(); |
| 52 | ///为了让大家看的更清楚 我将数据库取出的内容显示在屏幕中 |
| 55 | GUILayout.Label("XXXXXXXXXXXXX" + name); |
| 61 | GUILayout.Label("XXXXXXXXXXXXX" + email); |
| 66 | GUILayout.Label("数据库的路径" + appDBPath); |
生成工程后,运行生成的mac程序,我们可以看到 数据已经取出来了。
![]()
Windows平台SQLite的使用:
Windows平台下与Mac平台有点区别,废了老半天来找到问题所在。MOMO感谢在博客后面留言的朋友,因为没有你们的留言我也不会去研究MAC Windows下如何使用 呵呵。
进入正题,还是先修改Test.cs文件
[代码]java代码:
| 01 | using System.Collections; |
| 03 | using Mono.Data.Sqlite; |
| 05 | //using Mono.Data.SqliteClient; |
| 07 | public class Test : MonoBehaviour |
| 18 | //注意这里的修改!!!!!!!!!!!!!! |
| 19 | string appDBPath = Application.dataPath + "/xuanyusong.db"; |
| 21 | //-------------------------- |
| 23 | DbAccess db = new DbAccess(@"Data Source=" + appDBPath); |
| 27 | //请注意 插入字符串是 已经要加上'宣雨松' 不然会报错 |
| 28 | db.CreateTable("momo",new string[]{"name","qq","email","blog"}, new string[]{"text","text","text","text"}); |
| 30 | db.InsertInto("momo", new string[]{ "'宣雨松'","'289187120'","'xuanyusong@gmail.com'","'www.xuanyusong.com'" }); |
| 31 | db.InsertInto("momo", new string[]{ "'雨松MOMO'","'289187120'","'000@gmail.com'","'www.xuanyusong.com'" }); |
| 32 | db.InsertInto("momo", new string[]{ "'哇咔咔'","'289187120'","'111@gmail.com'","'www.xuanyusong.com'" }); |
| 35 | db.Delete("momo",new string[]{"email","email"}, new string[]{"'xuanyusong@gmail.com'","'000@gmail.com'"} ); |
| 38 | using (SqliteDataReader sqReader = db.SelectWhere("momo",new string[]{"name","email"},new string[]{"qq"},new string[]{"="},new string[]{"289187120"})) |
| 41 | while (sqReader.Read()) |
| 44 | Debug.Log("xuanyusong" + sqReader.GetString(sqReader.GetOrdinal("name"))); |
| 46 | Debug.Log("xuanyusong" + sqReader.GetString(sqReader.GetOrdinal("email"))); |
| 48 | name = sqReader.GetString(sqReader.GetOrdinal("name")); |
| 49 | email = sqReader.GetString(sqReader.GetOrdinal("email")); |
| 56 | db.CloseSqlConnection(); |
| 63 | GUILayout.Label(name); |
| 68 | GUILayout.Label(email); |
| 73 | GUILayout.Label(path); |
如下图所示打开Unity然后我们需要下载sqlite3.dll文件,接着将dll都放入Plugins文件夹中。不用担心 稍后我会把真个工程的下载地址贴出来其中包括所有的dll 。
![]()
***直接打包成Windows平台工程。双击运行.exe文件,如下图所示数据库的数据以及路径MOMO已经打印在屏幕当中啦。哇咔咔~ 然后xuanyusong.db文件就放在ddd_Date文件夹中,我已经用红圈标注出来了。ddd就是工程的名称,ddd_Date该文件夹是自动生成的。
![]()