加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

Unity Sqlite数据库操作

发布时间：2020-12-12 23:41:59 所属栏目：百科 来源：网络整理
导读：在Unity中使用sqlite的教程有很多。 包括： 研究院之使用C＃语言建立本地数据库（二十三） 游戏开发之SQLite让数据库开发更简单 看完这两篇呢，基本上对sqlite数据库的使用都没有问题了，但是想在Unity中更方便的使用，我有对两者进行了改进和升级。 首先，

在Unity中使用sqlite的教程有很多。 包括：

研究院之使用C＃语言建立本地数据库（二十三）

游戏开发之SQLite让数据库开发更简单

看完这两篇呢，基本上对sqlite数据库的使用都没有问题了，但是想在Unity中更方便的使用，我有对两者进行了改进和升级。
首先，先来看下基本的SqliteManager，对Sqlite的基本操作工具类：


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

public class SqlManager : MonoBehaviour {

    private const string dbName = "QikuEdu";

    //链接数据库
    private SqliteConnection connection;
    //数据库命令
    private SqliteCommand command;
    //数据库阅读器
    private SqliteDataReader reader;

    private static SqlManager _instance = null;
    public static SqlManager Instance
    {
        get
        {
            if (_instance == null)
            {
                GameObject obj = new GameObject();
                _instance = obj.AddComponent<SqlManager>();
            }
            return _instance;
        }
    }

    void Awake()
    {
        if(null == _instance){
            _instance = this;
        }else{
            GameObject.Destroy(this);
            return;
        }

        //初始化，打开数据库
        OpenDB(dbName);
    }

    //public SqlManager() { }
    ////必须有参数的构造方法
    //public SqlManager(string dbName)
    //{
    //    OpenDB(dbName);
    //}

    public void OpenDB(string dbName)
    {
        try
        {   //链接数据库操作
            string dbPath = Application.streamingAssetsPath + "/" + dbName + ".db";
            //固定sqlite格式data source
            connection = new SqliteConnection(@"Data Source = " + dbPath);
            connection.Open();

            Debug.Log("DataBase Connect");
        }
        catch (System.Exception e)
        {
            Debug.LogError(e.ToString());
        }
    }
    public void CloseDB()
    {
        /*
         * IDispose接口可以通过Using(){}关键字实现使用后立刻销毁
         * Close （）方法回滚任何挂起的事务。然后，它将连接释放到连接池，或者在连接池被禁用的情况下关闭连接，
         * 应用程序可以多次调用 Close。不会生成任何异常。
         * Dispose（）方法实际是和close（）做的同一件事，唯一的区别是Dispose方法清空了connectionString，即设置为了null.
         */
        if (reader != null)
            reader.Close();

        if (command != null)
            command.Dispose();

        if (connection != null)
            connection.Close();

        Debug.Log("DataBase Close");
    }

    //创建数据库表
    public void CreateTabel(string _tableName,string[] col,string[] colType)
    {
        //string createSql = "CREATE TABLE player(ID int,name text,damage int)";
        if (col.Length != colType.Length)
        {
            Debug.LogError("Colum's Length != ColumType's Length");
            return;
        }

        string sql = "CREATE TABLE "+_tableName+"(";
        for(int i=0;i<col.Length;i++)
        {
            sql += col[i] + " " + colType[i] + ",";
        }
        sql = sql.TrimEnd(',');
        sql += ")";

        ExcuteSql(sql);
    }

    //插入数据
    public void Insert(string _tableName,object[] values)
    {
        //string createSql = "INSERT INTO player(ID,name,damage) VALUES (102,'ak47',120)";
        if (values.Length == 0)
            Debug.LogError("Values's length == 0");

        string sql = "INSERT INTO "+_tableName + " VALUES(";

        foreach(object value in values)
        {
            sql += "'" + value.ToString() + "'" +",');
        sql += ")";

        ExcuteSql(sql);
    }
    //删除数据 OR
    public void DeleteOR(string _tabelName,params object[] colums)
    {
        //string createSql = "delete from player where rowid=1";
        if (colums == null || colums.Length == 0)
            Debug.LogError("colums == null || colums.Length == 0");

        string sql = "DELETE FROM " + _tabelName + " WHERE ";

        for (int i = 0; i < colums.Length; i += 2)
        {
            sql += colums[i] + " = '" + colums[i + 1].ToString() + "' OR ";
        }
        sql = sql.Remove(sql.Length - 3);
        ExcuteSql(sql);
    }

    //删除数据 AND
    public void DeleteAND(string _tabelName,params object[] colums)
    {
        if (colums == null || colums.Length == 0)
            Debug.LogError("colums == null || colums.Length == 0");

        string sql = "DELETE FROM " + _tabelName + " WHERE ";

        for (int i=0;i<colums.Length;i+=2)
        {
            sql += colums[i] + " = '" + colums[i+1].ToString() + "' AND ";
        }
        sql = sql.Remove(sql.Length - 4);
        ExcuteSql(sql);
    }

    //更新数据 OR
    public void UpdateOR(string _tableNmae,object[] colums,params object[] options)
    {
        //string createSql = "Update player set name='M16' where id=102";
        if (colums == null || colums.Length == 0 || colums.Length % 2 == 1)
            Debug.LogError("colums Length has error!!!");
        if (options == null || options.Length == 0 || options.Length % 2 == 1)
            Debug.LogError("options Length has error!!!");

        string sql = "UPDATE " + _tableNmae + " SET ";
        for (int i = 0; i < colums.Length; i += 2)
        {
            sql += colums[i] + " =  '" + colums[i + 1].ToString() + "' ";
        }
        sql += " WHERE ";

        for (int i = 0; i < options.Length; i+=2)
        {
            sql += options[i] + "=" + options[i+1].ToString() + " OR ";
        }
        sql = sql.Remove(sql.Length - 3);

        ExcuteSql(sql);
    }

    //更新数据 AND
    public void UpdateAND(string _tableNmae,params object[] options)
    {
        //string createSql = "Update player set name='M16' where id=102";
        if (colums == null || colums.Length == 0 || colums.Length % 2 == 1)
            Debug.LogError("colums Length has error!!!");
        if (options == null || options.Length == 0 || options.Length % 2 == 1)
            Debug.LogError("options Length has error!!!");

        string sql = "UPDATE " + _tableNmae + " SET ";
        for (int i = 0; i < colums.Length; i += 2)
        {
            sql += colums[i] + " =  '" + colums[i + 1].ToString() + "' ";
        }
        sql += " WHERE ";

        for (int i = 0; i < options.Length; i += 2)
        {
            sql += options[i] + "=" + options[i + 1].ToString() + " AND ";
        }
        sql = sql.Remove(sql.Length - 4);

        ExcuteSql(sql);
    }

    //查询制定列名的整张表（老方法）
    //public List<Dictionary<string,object>> Select(string _tableName)
    //{
    //    string sql = "SELECT * FROM "+ _tableName;
    //    reader = ExcuteSql(sql);
    //    List<Dictionary<string,object>> dicArr = new List<Dictionary<string,object>>();
    //    //阅读电子书，翻页
    //    while(reader.Read())
    //    {
    //        //Debug.Log(reader.GetInt32(0) + ":" + reader.GetString(1) + ":" + reader.GetInt32(2));
    //        //int idex2 = reader.GetOrdinal("rowid");
    //        Dictionary<string,object> dic = new Dictionary<string,object>();
    //        dic.Add("ID",reader.GetValue(reader.GetOrdinal("ID")));
    //        dic.Add("Name",reader.GetValue(reader.GetOrdinal("Name")));
    //        dic.Add("Damage",reader.GetValue(reader.GetOrdinal("Damage")));
    //        dicArr.Add(dic);
    //    }
    //    reader.Close();
    //    return dicArr;
    //}

    ////按照ID查询（ID是主键）
    //public Dictionary<string,object> SelectByID(string _tableName,int Id)
    //{
    //    string sql = "SELECT * FROM " + _tableName +" WHERE Id ="+Id;
    //    reader = ExcuteSql(sql);
    //    Dictionary<string,object>();
    //    //阅读电子书，翻页
    //    reader.Read();
    //    for (int i = 0; i < reader.FieldCount; i++)
    //    {
    //        dic.Add(reader.GetName(i),reader.GetValue(i));
    //    }
    //    reader.Close();
    //    return dic;
    //}

    //查找所有
    public List<Dictionary<string,object>> SelectAll(string _tableName)
    {
        string sql = "SELECT * FROM " + _tableName;
        List<Dictionary<string,object>> dataArr = new List<Dictionary<string,object>>();
        reader = ExcuteSql(sql);
        while (reader.Read())
        {
            Dictionary<string,object> data = new Dictionary<string,object>();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                string key = reader.GetName(i);
                object value = reader.GetValue(i);
                data.Add(key,value);
            }
            dataArr.Add(data);
        }
        return dataArr;
    }

    //按照自定义条件查找单条数据
    public Dictionary<string,object> SelectWithCondition(string _tableName,params object[] options)
    {
        if (options == null || options.Length == 0 || options.Length % 2 == 1)
            Debug.LogError("options Length has error!!!");

        string sql = "SELECT * FROM " + _tableName + " WHERE ";
        Dictionary<string,object>();

        for(int i=0;i< options.Length;i+=2)
        {
            sql += options[i] + "= '" + options[i + 1]+"' AND ";
        }
        sql = sql.Remove(sql.Length-4);

        reader = ExcuteSql(sql);
        reader.Read();
        for(int i=0;i<reader.FieldCount;i++)
        {
            string key = reader.GetName(i);
            object value = reader.GetValue(i);
            dic.Add(key,value);
        }
        return dic;
    }

    //按照自定义条件查找整张表数据
    public List<Dictionary<string,object>> SelectAllWithCondition(string _tableName,params object[] options)
    {
        if (options == null || options.Length == 0 || options.Length % 2 == 1)
            Debug.LogError("options Length has error!!!");

        string sql = "SELECT * FROM " + _tableName + " WHERE ";
        List<Dictionary<string,object>>();

        for (int i = 0; i < options.Length; i += 2)
        {
            sql += options[i] + "= '" + options[i + 1] + "' AND ";
        }
        sql = sql.Remove(sql.Length - 4);

        reader = ExcuteSql(sql);
        while (reader.Read())
        {
            Dictionary<string,value);
            }
            dataArr.Add(data);
        }
        return dataArr;
    }

    //执行数据库语句
    SqliteDataReader ExcuteSql(string _sql)
    {
        Debug.Log("Excuted Sql :" + _sql);
        //创建数据库连接命令（事务管理、命令管理：向数据库发送指令）
        command = connection.CreateCommand();
        //设置命令语句
        command.CommandText = _sql;
        //执行命令后 返回 阅读器信息
        using (reader = command.ExecuteReader())
        {
            return reader;
        }
    }
}


然后是数据实体类： 
using UnityEngine;
using System.Collections.Generic;

public class SqlDataBase
{
    public virtual SqlDataBase InitWithSqlData(Dictionary<string,object> _data)
    {
        return null;
    }
}
public class Weapons : SqlDataBase
{
    public int id;
    public string name;
    public int damage;
    public int level;
    public int color;

    public static Weapons Copy(Weapons other)
    {
        Weapons ret = new Weapons();
        ret.id = other.id;
        ret.name = other.name;
        ret.damage = other.damage;
        ret.level = other.level;
        ret.color = other.color;

        return ret;
    }

    public override SqlDataBase InitWithSqlData(Dictionary<string,object> data)
    {
        this.id = System.Convert.ToInt32(data["id"]);
        this.name = System.Convert.ToString(data["name"]);
        this.damage = System.Convert.ToInt32(data["damage"]);
        this.level = System.Convert.ToInt32(data["level"]);
        this.color = System.Convert.ToInt32(data["color"]);

        return this;
    }
}


public class Player : SqlDataBase
{
    public int id;
    public string name;
    public int level;
    public int hp;
    public int ep;
    public int exp;

    public static Player Copy(Player other)
    {
        Player ret = new Player();
        ret.id = other.id;
        ret.name = other.name;
        ret.level = other.level;
        ret.hp = other.hp;
        ret.ep = other.ep;
        ret.exp = other.exp;

        return ret;
    }
    public override SqlDataBase InitWithSqlData(Dictionary<string,object> data)
    {
        this.id = System.Convert.ToInt32(data["id"]);
        this.name = System.Convert.ToString(data["name"]);
        this.level = System.Convert.ToInt32(data["level"]);
        this.hp = System.Convert.ToInt32(data["hp"]);
        this.ep = System.Convert.ToInt32(data["ep"]);
        this.exp = System.Convert.ToInt32(data["exp"]);

        return this;
    }
}

public class Enemy : SqlDataBase
{
    public int id;
    public string name;
    public int level;
    public int damage;
    public int hp;
    public int ep;
    public int exp;

    public static Enemy Copy(Enemy other)
    {
        Enemy ret = new Enemy();
        ret.id = other.id;
        ret.name = other.name;
        ret.level = other.level;
        ret.damage = other.damage;
        ret.hp = other.hp;
        ret.ep = other.ep;
        ret.exp = other.exp;

        return ret;
    }
    public override SqlDataBase InitWithSqlData(Dictionary<string,object> data)
    {
        this.id = System.Convert.ToInt32(data["id"]);
        this.name = System.Convert.ToString(data["name"]);
        this.level = System.Convert.ToInt32(data["level"]);
        this.damage = System.Convert.ToInt32(data["damage"]);
        this.hp = System.Convert.ToInt32(data["hp"]);
        this.ep = System.Convert.ToInt32(data["ep"]);
        this.exp = System.Convert.ToInt32(data["exp"]);

        return this;
    }
}

从sqlite数据库读取出来的数据，转换到可以使用的实体类当中，需要经过一个DAO（Data Access Object）中间工具类 
using UnityEngine;
using System.Collections.Generic;

public class DAO<T> where T :SqlDataBase,new() {

    public static T GetById(int id)
    {
        return GetInfoWithCondition(typeof(T).Name,new object[] { "id",id });
    }

    public static List<T> GetAllInfos()
    {
        string tableName = typeof(T).ToString().ToLower();
        List<Dictionary<string,object>> resultList = SqlManager.Instance.SelectAll(tableName);
        if (resultList.Count == 0)
        {
            return default(List<T>);
        }

        List<T> t = new List<T>();
        for (int i = 0; i < resultList.Count; i++)
        {
            T tmp = new T();
            tmp.InitWithSqlData(resultList[i]);
            t.Add(tmp);
        }
        return t;
    }

    public static T GetInfoWithCondition(string tableName,object[] options)
    {
        UnityEngine.Assertions.Assert.IsTrue(options.Length % 2 == 0,"[DAO GetInfoFromTable] options error.");
        Dictionary<string,object> resultList = SqlManager.Instance.SelectWithCondition(tableName,options);
        T tmp = new T();
        tmp.InitWithSqlData(resultList);
        return tmp;
    }

    public static List<T> GetInfosWithCondition(string tableName,"[DAO GetInfoFromTable] options error.");
        List<Dictionary<string,object>> resultList = SqlManager.Instance.SelectAllWithCondition(tableName,options);
        if (resultList.Count == 0)
        {
            return default(List<T>);
        }

        List<T> t = new List<T>();
        for (int i = 0; i < resultList.Count; i++)
        {
            T tmp = new T();
            tmp.InitWithSqlData(resultList[i]);
            t.Add(tmp);
        }
        return t;
    }
}

这样，我们可以直接操作DAO类，获取我们需要的实体类的数据。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同