Unity中数据库建立与读写
发表于2018-07-28
想在unity开发中去简历数据库与读写数据库，需要用到两个类，DbAccess和TestDB，下面就分别给大家介绍下这两个类是怎么实现我们想要的功能的。
TestDB类测试，创建数据库，创建数据表，插入数据，更新数据，删除数据，查询数据
using UnityEngine;
using System.Collections;
using Mono.Data.Sqlite;
using System.IO;
public class TestDB : MonoBehaviour ｛
DbAccess db; //数据库
string appDBPath; //数据库路径
string name;
int age;
float exp;
//创建数据库
void CreateDataBase()
｛
#if UNITY_EDITOR
appDBPath = Application.dataPath + ”/Test.db”;
#elif UNITY_STANDALONE_WIN
appDBPath = Application.dataPath + ”/Test.db”;
#elif UNITY_ANDROID
appDBPath = Application.persistentDataPath + ”/Test.db”;
//判断路径内数据库是否存在
if(!File.Exists(appDBPath))=
｛
//拷贝数据库
StartCoroutine(CopyDataBase());
｝
#elif UNITY_IPHONE
appDBPath = Application.persistentDataPath + ”/Test.db”;
//判断路径内数据库是否存在
if(!File.Exists(appDBPath))
｛
//拷贝数据库
StartCoroutine(CopyDataBase());
｝
#endif
//”URI=file:” 必须添加
db = new DbAccess (“URI=file:” + appDBPath);
｝
//拷贝数据库
IEnumerator CopyDataBase()
｛
//用www先从unity中下载数据库
//Application.streamingAssetsPath
#if UNITY_IPHONE
WWW loadDB = new WWW(Application.dataPath
+ ”/Raw” + “/Test.db”);
#elif UNITY_ANDROID
WWW loadDB = new WWW(“jar:file://” +
Application.dataPath + ”!/assets” + “/Test.db”);
#elif UNITY_EDITOR||UNITY_STANDALONE_WIN
WWW loadDB = new WWW(
Application.dataPath + ”/Test.db”);
#endif
yield return loadDB;
//拷贝数据库
File.WriteAllBytes (appDBPath, loadDB.bytes);
｝
void OnGUI()
｛
if (GUILayout.Button (“创建数据库”))
CreateDataBase ();
if (GUILayout.Button (“创建数据表”))
CreateTable ();
if (GUILayout.Button (“插入数据”))
InsertData ();
if (GUILayout.Button (“更新数据”))
UpdateData ();
if (GUILayout.Button (“删除数据”))
DeleteData ();
if (GUILayout.Button (“查询数据”))
FindData ();
GUILayout.Label (”name:” + name);
GUILayout.Label (”age:” + age);
GUILayout.Label (”exp:” + exp);
GUILayout.Label (”path:” + appDBPath);
｝
//创建表
void CreateTable()
｛
//打开数据库
CreateDataBase ();
//创建数据库表
db.CreateTable (”role”,
new string[]｛“id”,“name”,
”age”,“lv”,“exp”｝,
new string[]｛“int”,“text”,
”int”,“int”,“float”｝);
//关闭数据库
db.CloseSqlConnection ();
｝
//插入数据
void InsertData()
｛
CreateDataBase ();
//插入数据
db.InsertInto (”role”, new string[]
｛”1”,“’张三’”,“18”,“1”,“1.2”｝);
db.CloseSqlConnection ();
｝
//更新数据
void UpdateData()
｛
CreateDataBase ();
db.UpdateInto (”role”,new string[]｛“lv”,“exp”｝,
new string[]｛“10”,“100”｝,“id”,“1”);
db.CloseSqlConnection ();
｝
//删除数据
void DeleteData()
｛
CreateDataBase ();
db.Delete (”role”,new string[]｛“id”,“id”｝,
new string[]｛“1”,“2”｝);
db.CloseSqlConnection ();
｝
//查找数据
void FindData()
｛
CreateDataBase ();
SqliteDataReader sqReader =
db.SelectWhere (”role”,
new string[]｛“name”,“age”,“exp”｝,
new string[]｛“id”｝,new string[]｛“=”｝,
new string[]｛“3”｝);
while(sqReader.Read())
｛
name = sqReader.GetString
(sqReader.GetOrdinal(”name”));
age = sqReader.GetInt32
(sqReader.GetOrdinal(”age”));
exp = sqReader.GetFloat
(sqReader.GetOrdinal(”exp”));
｝
Debug.Log (”name:” + name);
db.CloseSqlConnection ();
｝
｝
DbAccess，链接数据库
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);
｝
/// <summary>
/// 插入数据 param tableName=表名 values=数据内容
/// </summary>
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>
/// 插入数据 param tableName=表名 cols=更新字段 colsvalues=更新内容 selectkey=查找字段（主键) selectvalue=查找内容
/// </summary>
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>
/// 删除数据 param tableName=表名 cols=字段 colsvalues=内容
/// </summary>
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);
｝
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);
｝
/// <summary>
/// 创建表 param name=表名 col=字段名 colType=字段类型
/// </summary>
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>
/// 插入数据 param tableName=表名 items=结果字段 col=查找字段 operation=运算符 values=内容
/// </summary>
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);
｝
｝