Unity配合SQLite开发数据库

来源：互联网发布：中学生知乎编辑：程序博客网时间：2024/05/20 23:07

首先开发数据库我用的是SLQiteStudio(3.0.6)+Unity5.6进行开发

其实我也是根据http://blog.csdn.net/qinyuanpei/article/details/46812655这个来进行修改，从而进行开发。

要想开发的话首先的有dll文件需要的SQLiteStudio和DLL文件都在网盘上。链接：http://pan.baidu.com/s/1qXCUE2g ，提取码：ol3s。

下载完毕以后就可以开始正式的工作了。

打开Unity创建一个项目，把下载好的DLL文件导入Unity工程中去

其实放到Assets文件夹下也可以的，我是创建Plugins文件夹，把DLL文件放到这里面中去。

首先要有一个SQLite辅助的类

//---------------------------
//模块：sqlite辅助类
//功能：
//开发人员：
//---------------------------
using UnityEngine;
using System.Collections;
using Mono.Data.Sqlite;
using System;

public class SQLiteHelper
{
/// <summary>
/// 数据库连接定义
/// </summary>
private SqliteConnection dbConnection;

/// <summary>
/// SQL命令定义
/// </summary>
private SqliteCommand dbCommand;

/// <summary>
/// 数据读取定义
/// </summary>
private SqliteDataReader dataReader;

/// <summary>
/// 构造函数
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
public SQLiteHelper(string connectionString)
{
try
{
//构造数据库连接
dbConnection = new SqliteConnection(connectionString);
//打开数据库
dbConnection.Open();
}
catch (Exception e)
{
Debug.Log(e.Message);
}
}

/// <summary>
/// 执行SQL命令
/// </summary>
/// <returns>The query.</returns>
/// <param name="queryString">SQL命令字符串</param>
public SqliteDataReader ExecuteQuery(string queryString)
{
dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = queryString;
dataReader = dbCommand.ExecuteReader();
Debug.Log("执行SQL命令："+ dataReader);
return dataReader;
}

/// <summary>
/// 关闭数据库连接
/// </summary>
public void CloseConnection()
{
//销毁Command
if (dbCommand != null)
{
dbCommand.Cancel();
}
dbCommand = null;

//销毁Reader
if (dataReader != null)
{
dataReader.Close();
}
dataReader = null;

//销毁Connection
if (dbConnection != null)
{
dbConnection.Close();
}
dbConnection = null;
}

/// <summary>
/// 读取整张数据表
/// </summary>
/// <returns>The full table.</returns>
/// <param name="tableName">数据表名称</param>
public SqliteDataReader ReadFullTable(string tableName)
{
string queryString = "SELECT * FROM " + tableName;
return ExecuteQuery(queryString);
}

/// <summary>
/// 向指定数据表中插入数据
/// </summary>
/// <returns>The values.</returns>
/// <param name="tableName">数据表名称</param>
/// <param name="values">插入的数值</param>
public SqliteDataReader InsertValues(string tableName, string[] values)
{
//获取数据表中字段数目
int fieldCount = ReadFullTable(tableName).FieldCount;
//当插入的数据长度不等于字段数目时引发异常
if (values.Length != fieldCount)
{
throw new SqliteException("values.Length!=fieldCount");
}

string queryString = "INSERT INTO " + tableName + " VALUES (" + values[0];
for (int i = 1; i < values.Length; i++)
{
queryString += ", " + values[i];
}
queryString += " )";
return ExecuteQuery(queryString);
}

/// <summary>
/// 更新指定数据表内的数据
/// </summary>
/// <returns>The values.</returns>
/// <param name="tableName">数据表名称</param>
/// <param name="colNames">字段名</param>
/// <param name="colValues">字段名对应的数据</param>
/// <param name="key">关键字</param>
/// <param name="value">关键字对应的值</param>
public SqliteDataReader UpdateValues(string tableName, string[] colNames, string[] colValues, string key, string operation, string value)
{
//当字段名称和字段数值不对应时引发异常
if (colNames.Length != colValues.Length)
{
throw new SqliteException("colNames.Length!=colValues.Length");
}
string queryString = "UPDATE " + tableName + " SET " + colNames[0] + "=" + colValues[0];
for (int i = 1; i < colValues.Length; i++)
{
queryString += "," + colNames[i] + "=" + colValues[i];
}
queryString += " WHERE " + key + operation + value;
Debug.Log("更改的是：" + queryString);
return ExecuteQuery(queryString);
}

/// <summary>
/// 删除指定数据表内的数据
/// </summary>
/// <returns>The values.</returns>
/// <param name="tableName">数据表名称</param>
/// <param name="colNames">字段名</param>
/// <param name="colValues">字段名对应的数据</param>
public SqliteDataReader DeleteValuesOR(string tableName, string[] colNames, string[] operations, string[] colValues)
{
//当字段名称和字段数值不对应时引发异常
if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)
{
throw new SqliteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");
}

string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + colValues[0];
for (int i = 1; i < colValues.Length; i++)
{
queryString += "OR " + colNames[i] + operations[0] + colValues[i];
}
return ExecuteQuery(queryString);
}

/// <summary>
/// 删除指定数据表内的数据
/// </summary>
/// <returns>The values.</returns>
/// <param name="tableName">数据表名称</param>
/// <param name="colNames">字段名</param>
/// <param name="colValues">字段名对应的数据</param>
public SqliteDataReader DeleteValuesAND(string tableName, string[] colNames, string[] operations, string[] colValues)
{
//当字段名称和字段数值不对应时引发异常
if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)
{
throw new SqliteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");
}

string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + colValues[0];
for (int i = 1; i < colValues.Length; i++)
{
queryString += " AND " + colNames[i] + operations[i] + colValues[i];
}
return ExecuteQuery(queryString);
}

/// <summary>
/// 创建数据表
/// </summary> +
/// <returns>The table.</returns>
/// <param name="tableName">数据表名</param>
/// <param name="colNames">字段名</param>
/// <param name="colTypes">字段名类型</param>
public SqliteDataReader CreateTable(string tableName, string[] colNames, string[] colTypes)
{
string queryString = "CREATE TABLE " + tableName + "( " + colNames[0] + " " + colTypes[0];
for (int i = 1; i < colNames.Length; i++)
{
queryString += ", " + colNames[i] + " " + colTypes[i];
}
queryString += " ) ";
return ExecuteQuery(queryString);
}

/// <summary>
/// Reads the table.
/// </summary>
/// <returns>The table.</returns>
/// <param name="tableName">Table name.</param>
/// <param name="items">Items.</param>
/// <param name="colNames">Col names.</param>
/// <param name="operations">Operations.</param>
/// <param name="colValues">Col values.</param>
public SqliteDataReader ReadTable(string tableName, string[] items, string[] colNames, string[] operations, string[] colValues)
{
string queryString = "SELECT " + items[0];
for (int i = 1; i < items.Length; i++)
{
queryString += ", " + items[i];
}
queryString += " FROM " + tableName + " WHERE " + colNames[0] + " " + operations[0] + " " + colValues[0];
for (int i = 0; i < colNames.Length; i++)
{
queryString += " AND " + colNames[i] + " " + operations[i] + " " + colValues[0] + " ";
}
return ExecuteQuery(queryString);
}
}

然后再有一个SQL操作的类

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

public class SQLiteDemo : MonoBehaviour
{
/// <summary>
/// SQLite数据库辅助类
/// </summary>
private SQLiteHelper sql;

/// <summary>
/// 进行添加UGUI的Image
/// </summary>
private CreateUI create;

int Width;
int Height;
int length = 200;
int count = 0;

void Start()
{
ToCreateOne();
}
/// <summary>
/// 创建一个table1的数据表
/// </summary>
void ToCreateOne()
{
//创建名为sqlite4unity的数据库注意：数据库名称是：SQLiteDemoTwo.db只能创建一次
sql = new SQLiteHelper("data source=SQLiteDemoTwo.db");
//创建名为table1的数据表
sql.CreateTable("table1", new string[] { "ID", "Name", "Age", “Gender”，“Address" }, new string[] { "INTEGER", "TEXT", "INTEGER", "TEXT" });
//插入数据
sql.InsertValues("table1", new string[] { "'1'", "'小明'", "'22'", "'男'"，"'北京'"});
sql.InsertValues("table1", new string[] { "'2'", "'小李'", "'23'", "'男'" ,"'上海'"});
//更新数据，将小李的年龄更新为24 前面的数组是要修改的什么字段，还有对应的字段值，后面是用来查找你要修改的对象，例如ID=2的
//sql.UpdateValues("table1", new string[] { "Age" }, new string[] { "'24'" }, "ID", "=", "'2'");
//插入数据
sql.InsertValues("table1", new string[] { "3", "'小红'", "21", "'女'" ,"'杭州'"});

//删除Name="小李"且Age=23的记录,DeleteValuesOR方法类似
//sql.DeleteValuesAND("table1", new string[] { "Name", "Age" }, new string[] { "=", "=" }, new string[] { "'王五'", "'26'" });

//读取整张表
SqliteDataReader reader = sql.ReadFullTable("table1");
while (reader.Read())
{
//读取ID
Debug.Log(reader.GetInt32(reader.GetOrdinal("ID")));
//读取Name
Debug.Log(reader.GetString(reader.GetOrdinal("Name")));
//读取Age
Debug.Log(reader.GetInt32(reader.GetOrdinal("Age")));
//读取Gender
Debug.Log(reader.GetString(reader.GetOrdinal("Gender")));

//读取Address
Debug.Log(reader.GetString(reader.GetOrdinal("Address")));
}
//读取数据表中Age>=25的所有记录的ID和Name
reader = sql.ReadTable("table1", new string[] { "ID", "Name" }, new string[] { "Age" }, new string[] { ">=" }, new string[] { "'25'" });
while (reader.Read())
{
//读取ID
Debug.Log(reader.GetInt32(reader.GetOrdinal("ID")));
//读取Name
Debug.Log(reader.GetString(reader.GetOrdinal("Name")));
}
//自定义SQL,删除数据表中所有Name="小李"的记录
//sql.ExecuteQuery("DELETE FROM table1 WHERE NAME='小李'");
//关闭数据库连接
sql.CloseConnection();
}
}

好了，这样的话就完成创建了，运行代码就OK了

然后在工程的项目下面就可以创建好的数据库了。

然后甩SQLiteStudio打开皆可以看到了创建的table1表了

最开始打开SQLiteStudio的小伙伴发现我的怎么不是中文的啊，其实点击工具，然后打开配置对话框如下图所示：

点击外观就可以选择语言的类型了

然后添加刚才生成的数据库的文件，就可以看到自己创建的数据库的table表

table2就是创建好的，其实也可以手动创建，在table1表右键创建，点击“Create a table”,然后在右边输入table的名称，然后创建字段

下图是添加字段

或者是通过代码来创建如下图所示

阅读全文

0 0