Unity在安卓端、ios、PC分别使用Sqlite数据库存储数据（这里我用来实现游戏的暂存）
本文来自 https://blog.csdn.net/jiangyangll/article/details/113661165
寒假找了一个线上实习（unity，我的unity是2019.3.14），做了一个消除类型的游戏，其中需要实现棋局暂存的功能，也就是需要在玩家游戏时退出了的时候记录当前局面和未放入进行消除的砖块的数据，下一次再打开时依旧要有上次的残局，这个功能需要在任何退出的情况中实现。游戏的逻辑和算法对我来说轻而易举，但是这种事情就很少做，其实实现并不难，难的就是在不同平台需要不同的操作，还有各种各样的注意事项，饱受折磨终于实现（网上好多人自己不测试就发上去，老坑了…），下面一点一点实现：
暂存的具体逻辑没必要说，棋盘需要存储哪些地方放了砖块哪些没放（0、1二维数组搞定），未放入棋盘进行消除的砖块需要求出其id、类型、旋转的索引，这些都是业务逻辑，没什么值得说的，这里主要说使用Sqlite数据库存这些东西的注意事项：
1.各种平台使用Sqlite数据库需要的配置：
我真的被网上那些瞎说的人搞疯了，最后自己搞出来了，绝对靠谱：
首先是需要的dll文件：
PC仅需要把Mono.Data.Sqlite.dll、sqlite3.dll放入Plugins文件夹
安卓还需要把libsqlite3.so放Plugins文件夹的Android文件夹中
苹果还需要把libsqlite3.so放Plugins文件夹的IOS文件夹中
数据库文件放StreamingAssets目录下（后面我们会动态生成，如果不作为配置文件使用完全可以动态生成）
Mono.Data.Sqlite.dll、sqlite3.dll可以在你的Unity安装路径中找到，根本不需要和你用的unity版本对应，所以你在网上下载的别人的一样能用，libsqlite3.so百度去下就行了，当然我把我这次用到的所有和Sqlite相关的文件都总结到了一个资源中：上传时是0积分，抓紧下载哦
2.对数据库的操作：
先上我封装的一些代码吧：基本上增删改查都没啥问题了
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 ()
{
}
/// <summary>
/// 打开数据库
/// </summary>
/// <param name="connectionString"></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.关闭数据库！");
}
public SqliteDataReader ExecuteQuery (string sqlQuery)
{
dbCommand = dbConnection.CreateCommand ();
dbCommand.CommandText = sqlQuery;
reader = dbCommand.ExecuteReader ();
return reader;
}
/// <summary>
/// 查询表中全部数据 param tableName=表名
/// </summary>
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);
}
/// <summary>
/// 插入数据 param tableName=表名 cols=插入字段 value=插入内容
/// </summary>
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>
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);
}
/// <summary>
/// 查询表
/// </summary>
public SqliteDataReader Select(string tableName, string col, string values)
{
string query = "SELECT * FROM " + tableName + " WHERE " + col + " = " + values;
return ExecuteQuery (query);
}
public SqliteDataReader Select(string tableName, string col,string operation, string values)
{
string query = "SELECT * FROM " + tableName + " WHERE " + col + operation + values;
return ExecuteQuery (query);
}
/// <summary>
/// 升序查询
/// </summary>
public SqliteDataReader SelectOrderASC (string tableName,string col)
{
string query = "SELECT * FROM " + tableName + " ORDER BY " + col + " ASC";
return ExecuteQuery (query);
}
/// <summary>
/// 降序查询
/// </summary>
public SqliteDataReader SelectOrderDESC (string tableName,string col)
{
string query = "SELECT * FROM " + tableName + " ORDER BY " + col + " DESC";
return ExecuteQuery (query);
}
/// <summary>
/// 查询表行数
/// </summary>
public SqliteDataReader SelectCount(string tableName)
{
string query = "SELECT COUNT(*) FROM " + tableName;
return ExecuteQuery (query);
}
}
增删改查没问题了，问题就来了：来看我怎么用这些方法：
using Mono.Data.Sqlite;
using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using UnityEngine;
using UnityEngine.UI;
public class DataBaseController : MonoBehaviour
{
private static DataBaseController _instance;
public static DataBaseController Instance { get { return _instance; } }
/// <summary>数据库路径</summary>
private string appDBPath;
/// <summary>数据库文件</summary>
private DbAccess db;
void Awake()
{
_instance = this;
if (!PlayerPrefs.HasKey("IsCreate"))
{
CreateTable();
PlayerPrefs.SetInt("IsCreate", 1);
}
}
/// <summary>拷贝数据库供移动端使用</summary>
private IEnumerator CopyDataBase()
{
yield return null;
WWW www = new WWW(Application.streamingAssetsPath + "/SmallBlockData.db");
while (!www.isDone)
{
yield return null;
}
File.WriteAllBytes(appDBPath, www.bytes);
}
/// <summary> 创建/打开--数据库</summary>
private void CreateDataBase()
{
#if UNITY_STANDALONE_WIN || UNITY_EDITOR
appDBPath = Application.streamingAssetsPath + "/SmallBlockData.db";
#elif UNITY_ANDROID || UNITY_IPHONE
appDBPath = Application.persistentDataPath + "/SmallBlockData.db";
if (!File.Exists(appDBPath)) { StartCoroutine(CopyDataBase()); }
#endif
db = new DbAccess("URI=file:" + appDBPath);
}
/// <summary> 创建表单 </summary>
public void CreateTable()
{
CreateDataBase();
db.CreateTable("SmallBlocks", new string[] { "PosIndex", "BlockType", "RotateIndex" }, new string[] { "int", "text", "int" });
db.CreateTable("Map", new string[] { "r", "c0", "c1", "c2", "c3", "c4", "c5", "c6", "c7", "c8"}, new string[] { "int", "int", "int", "int", "int", "int", "int", "int", "int", "int" });
db.CloseSqlConnection();
}
/// <summary>向表单中插入砖块数据</summary>
public void InsertBlockData(Block block)
{
CreateDataBase();
db.InsertInto("SmallBlocks", new string[] { block.currentIndex.ToString(), "'" + block.blockType.ToString() + "'", block.currentRotationIndex.ToString() });
db.CloseSqlConnection();
}
/// <summary>向表单中插入地图数据</summary>
public void InsertMapData(int[,] mapInfo)
{
CreateDataBase();
for (int i = 0; i < Map.row; i++)
{
db.InsertInto("Map", new string[] { i.ToString(), mapInfo[i,0].ToString(), mapInfo[i, 1].ToString(), mapInfo[i, 2].ToString(), mapInfo[i, 3].ToString(), mapInfo[i, 4].ToString(), mapInfo[i, 5].ToString(), mapInfo[i, 6].ToString(), mapInfo[i, 7].ToString(), mapInfo[i, 8].ToString() });
}
db.CloseSqlConnection();
}
/// <summary>更新砖块数据</summary>
public void UpdateBlockData(Block block)
{
CreateDataBase();
db.UpdateInto("SmallBlocks", new string[] { "BlockType", "RotateIndex" }, new string[] { "'" + block.blockType.ToString() + "'", block.currentRotationIndex.ToString() }, "PosIndex", block.currentIndex.ToString());
db.CloseSqlConnection();
}
/// <summary>删除砖块数据</summary>
public void DeleteBlockData(Block block)
{
CreateDataBase();
db.Delete("SmallBlocks", new string[] { "PosIndex" }, new string[] { block.currentIndex.ToString() });
db.CloseSqlConnection();
}
/// <summary>删除表单中所有</summary>
public void DeleteData(string TableName)
{
CreateDataBase();
db.DeleteContents(TableName);
db.CloseSqlConnection();
}
/// <summary>读取全部砖块数据</summary>
public List<BlockMessage> ReadBlockData()
{
CreateDataBase();
List<BlockMessage> msgs = new List<BlockMessage>();
for (int i = 1; i <= 4; i++)
{
SqliteDataReader tmp = db.Select("SmallBlocks", "PosIndex", i.ToString());
while (tmp.Read())
{
BlockType blockType = (BlockType)Enum.Parse(typeof(BlockType), tmp.GetString(tmp.GetOrdinal("BlockType")));
int index = i;
int rotateIndex = tmp.GetInt32(tmp.GetOrdinal("RotateIndex"));
msgs.Add(new BlockMessage(blockType, index, rotateIndex));
}
tmp.Close();
}
db.CloseSqlConnection();
return msgs;
}
/// <summary>获取地图快照</summary>
public int[,] GetMapOnLoad()
{
CreateDataBase();
int[,] mapInfo = new int[Map.row, Map.col];
for (int i = 0; i < Map.row; i++)
{
SqliteDataReader tmp = db.Select("Map", "r", i.ToString());
while (tmp.Read())
{
for (int j = 0; j < Map.col; j++)
{
mapInfo[i,j] = tmp.GetInt32(tmp.GetOrdinal("c" + j));
}
}
tmp.Close();
}
db.CloseSqlConnection();
return mapInfo;
}
}
业务逻辑没什么难的，要注意的就是这段：
（1）移动端是不能读写StreamingAssets路径下的文件的，但是会打包到安装包中，所以我们把StreamingAssets中的数据库拷贝到持久化路径中，之后永远对他进行操作即可
（2）为了确保一定会读取完成，注意www是怎么和协程配合用的（没有用最新的那个UnityWebRequest，那个确实好用，但是这里先用这个，我乐意好吧）
/// <summary>拷贝数据库供移动端使用</summary>
private IEnumerator CopyDataBase()
{
yield return null;
WWW www = new WWW(Application.streamingAssetsPath + "/SmallBlockData.db");
while (!www.isDone)
{
yield return null;
}
File.WriteAllBytes(appDBPath, www.bytes);
}
/// <summary> 创建/打开--数据库</summary>
private void CreateDataBase()
{
#if UNITY_STANDALONE_WIN || UNITY_EDITOR
appDBPath = Application.streamingAssetsPath + "/SmallBlockData.db";
#elif UNITY_ANDROID || UNITY_IPHONE
appDBPath = Application.persistentDataPath + "/SmallBlockData.db";
if (!File.Exists(appDBPath)) { StartCoroutine(CopyDataBase()); }
#endif
db = new DbAccess("URI=file:" + appDBPath);
}
别的什么都不用做，这就能在所有平台用了，如果你仍无法，请再次仔细阅读
浙公网安备 33010602011771号