Unity Sqlite数据库操作
发表于2018-08-02
Sqlite是一个轻量级数据库，在Unity中可以很方便的使用它。下面就来看下Sqlite数据库的操作。
首先在项目中添加两个动态链接库Mono.Data.Sqlite.dll和System.Data.dll
创建数据库管理脚本,该脚本用来直接操作数据库的操作，可以看作是对数据库操作的sql语句的封装。
using UnityEngine;
using System.Data;
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 ()
｛
｝
/// <summary>
/// 打开数据库
/// </summary>
/// <param name="connectionString">Connection string.</param>
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);
｝
｝
/// <summary>
/// 关闭数据库
/// </summary>
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.");
｝
/// <summary>
/// 执行sql语句
/// </summary>
/// <returns>The query.</returns>
/// <param name="sqlQuery">查询语句.</param>
public SqliteDataReader ExecuteQuery (string sqlQuery)
｛
Debug.Log ("sql="+sqlQuery);
dbCommand = dbConnection.CreateCommand ();
dbCommand.CommandText = sqlQuery;
reader = dbCommand.ExecuteReader ();
return reader;
｝
/// <summary>
/// 查询整个table的数据
/// </summary>
/// <returns>The full table.</returns>
/// <param name="tableName">表名.</param>
public SqliteDataReader ReadFullTable (string tableName)
｛
string query = "SELECT * FROM " + tableName;
return ExecuteQuery (query);
｝
/// <summary>
/// 插入数据
/// </summary>
/// <returns>The into.</returns>
/// <param name="tableName">表名</param>
/// <param name="values">需要插入的字段内容，注意字符串需要添加单引号 如 ‘name’</param>
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);
｝
/// <summary>
/// 更新table内容
/// </summary>
/// <returns>The into.</returns>
/// <param name="tableName">Table 名称.</param>
/// <param name="cols">需要更新的字段名称数组.</param>
/// <param name="colsvalues">需要更新的字段对应的值.</param>
/// <param name="selectkey">更新依据的字段.</param>
/// <param name="selectvalue">更新依据字段对应的值</param>
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);
｝
/// <summary>
/// 根据删除条件，删除对应的数据
/// </summary>
/// <param name="tableName">Table 名称.</param>
/// <param name="cols">字段数组.</param>
/// <param name="colsvalues">字段数组对应的值.</param>
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];
｝
return ExecuteQuery (query);
｝
/// <summary>
/// 插入数据，只插入部分字段的数据
/// </summary>
/// <returns>The into specific.</returns>
/// <param name="tableName">Table 名称.</param>
/// <param name="cols">需要插入的字段数组.</param>
/// <param name="values">需要插入的字段数组对应的值.</param>
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);
｝
/// <summary>
/// 根据表名，删除该表的全部数据
/// </summary>
/// <returns>The contents.</returns>
/// <param name="tableName">Table name.</param>
public SqliteDataReader DeleteContents (string tableName)
｛
string query = "DELETE FROM " + tableName;
return ExecuteQuery (query);
｝
/// <summary>
/// 创建一个数据表
/// </summary>
/// <returns>The table.</returns>
/// <param name="name">Name.</param>
/// <param name="col">Col.</param>
/// <param name="colType">Col type.</param>
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);
｝
/// <summary>
/// 根据条件筛选数据
/// </summary>
/// <returns>The where.</returns>
/// <param name="tableName">Table name.</param>
/// <param name="items">需要筛选的字段.</param>
/// <param name="col">筛选条件的健.</param>
/// <param name="operation">筛选符号，如 >,<,= </param>.</param>
/// <param name="values">筛选条件的值.</param>
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 System.Collections;
using System.Collections.Generic;
using UnityEngine;
using Mono.Data.Sqlite;
public class SqliteTest1 : MonoBehaviour ｛
string dbPath;
string dbName;
DbAccess db;
// Use this for initialization
void Start () ｛
dbName="userInfo.db";
dbPath=getPath ();
if (!System.IO.File.Exists (dbPath)) ｛
//创建数据库
db = new DbAccess ("data source=" + dbPath);
//创建数据库表，与字段
SqliteDataReader reader= db.CreateTable ("user", new string[]｛ "name", "age", "sex", "adress" ｝, new string[]｛ "text", "integer", "text", "text" ｝);
if (reader != null)
Debug.Log ("create tabel success");
else
Debug.LogError ("create tabel fail");
｝ else db = new DbAccess ("data source=" + dbPath);
//关闭对象
//db.CloseSqlConnection();
｝
string getPath()｛
string path=Application.dataPath+"/"+dbName;
#if UNITY_EDITOR
path=Application.streamingAssetsPath+"/"+dbName;
if (!System.IO.Directory.Exists (path))｛
// System.IO.DirectoryInfo dir=new System.IO.DirectoryInfo(Application.streamingAssetsPath);
// dir.Create();
System.IO.Directory.CreateDirectory(Application.streamingAssetsPath);
｝
#elif UNITY_ANDROID
path=Application.persistentDataPath+"/"+dbName;
#elif UNITY_IOS
path=Application.persistentDataPath+"/"+dbName;
#endif
Debug.Log (Application.dataPath);
Debug.Log (Application.persistentDataPath);
Debug.Log (Application.temporaryCachePath);
Debug.Log (path);
return path;
｝
void OnDestroy()｛
Debug.Log ("<color=#00ff00>close database</color>");
//关闭对象
db.CloseSqlConnection();
｝
void InsertData()｛
db.InsertInto ("user", new string[]｛ "'zhangsan'","11","'man'","'beijing'"｝);
db.InsertInto ("user", new string[]｛ "'lisi'","11","'woman'","'huoying'"｝);
｝
void UpdateData()｛
db.UpdateInto ("user",new string[]｛ "age", "sex"｝, new string[]｛ "82", "'woman'" ｝,"name","'zhangsan'");
｝
void DeleteData()｛
db.Delete ("user",new string[]｛ "age", "sex"｝, new string[]｛ "82", "'woman'" ｝);
｝
void QueryData()｛
string query = "select * from user where name='zhangsan'";
SqliteDataReader reader=db.ExecuteQuery (query);
while(reader.Read())｛
Debug.Log ("name="+reader["name"]);
Debug.Log ("age="+reader["age"]);
Debug.Log ("sex="+reader["sex"]);
Debug.Log ("adress="+reader["adress"]);
｝
｝
void SelectData()｛
// "select (name,age,sex) from user where age > 20";
SqliteDataReader reader= db.SelectWhere ("user",new string[]｛"name","age","sex"｝,new string[]｛"age"｝,new string[]｛">"｝,new string[]｛"20"｝);
while(reader.Read())｛
Debug.Log ("name="+reader["name"]);
Debug.Log ("age="+reader["age"]);
Debug.Log ("sex="+reader["sex"]);
｝
｝
void OnGUI() ｛
if (GUI.Button (new Rect (10, 20, 100, 40), "insert data")) ｛
InsertData ();
｝ else if (GUI.Button (new Rect (10, 80, 100, 40), "delete data")) ｛
DeleteData ();
｝ else if (GUI.Button (new Rect (10, 120, 100, 40), "updata data")) ｛
UpdateData ();
｝ else if (GUI.Button (new Rect (10, 180, 100, 40), "query data")) ｛
QueryData ();
｝ else if (GUI.Button (new Rect (10, 250, 100, 40), "select data")) ｛
SelectData ();
｝
else if (GUI.Button (new Rect (180, 20, 100, 40), "query all data")) ｛
SqliteDataReader reader= db.ReadFullTable("user");
int count = 0;
while(reader.Read())｛
count++;
// Debug.Log ("name="+reader["name"]);
// Debug.Log ("age="+reader["age"]);
// Debug.Log ("sex="+reader["sex"]);
// Debug.Log ("adress="+reader["adress"]);
Debug.Log ("name=" + reader ["name"] + ",age=" + reader ["age"] + ",sex=" + reader ["sex"] + ",adress=" + reader ["adress"]);
｝
Debug.Log ("count="+count);
｝
｝
｝
资源导包时报错：ArgumentException:TheAssemblySystem.ConfigurationisreferencedbySystem.Data(‘Assets/plugins/System.Data.dll’).Butthedllisnotallowedtobeincludedorcouldnotbefound.
解决方法：在PlaySettings－－othersetting中修改ApiCompatibilityLevel改成.NET2.0
demo下载：http://download.csdn.net/detail/u011484013/9705852
下载内容是一个unity资源包，拖到项目中之后，创建一个空场景，把SqliteTest1.cs挂载到相机上面，就可以运行测试。
来自：https://blog.csdn.net/u011484013/article/details/53521074