Unity3D高级-Sqlite

SQLite 是一种嵌入式数据库，它的数据库就是一个文件。由于SQLite本身是C写的，而且体积很小，所以，经常被集成到各种应用程序中，甚至在iOS和Android的App中都可以集成。SQLite是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它是一个零配置的数据库，这意味着您不需要在系统中配置，就可以使用。

特点

不需要一个单独的服务器进程或操作的系统（无服务器的）。
SQLite 不需要配置，这意味着不需要安装或管理。
一个完整的 SQLite 数据库是存储在一个单一的跨平台的磁盘文件。
SQLite 是非常小的，是轻量级的，完全配置时小于 400KiB，省略可选功能配置时小于250KiB。
SQLite 是自给自足的，这意味着不需要任何外部的依赖。
SQLite 事务是完全兼容 ACID 的，允许从多个进程或线程安全访问。
SQLite 支持 SQL92（SQL2）标准的大多数查询语言的功能。

---

Unity中使用

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

/// <summary>
/// 数据库管理器
/// </summary>
public class DBManager :MonoBehaviour{

    private static DBManager _instance;
    public static DBManager Instance
    {
        get
        {
            if (_instance == null)
            {
                GameObject go = new GameObject("DBManger");
                _instance = go.AddComponent<DBManager>();
            }
            return _instance;
        }
    }
    //用于建立数据库连接，保证数据流
    SqliteConnection connection;
    //数据库命令
    SqliteCommand command;
    //数据库阅读器
    SqliteDataReader reader;

    private const string dbName = "data";

    void Awake()
    {
        InitDB();

        OpenDB();
    }
    void OnDestroy()
    {
        CloseDB();
    }
    
    //拷贝数据库到沙盒目录
    void InitDB()
    {
        Debug.Log("*-> DBManager->Init");
        //判断是移动端
        if (Application.platform == RuntimePlatform.Android || Application.platform == RuntimePlatform.IPhonePlayer)
        {
            string systemDBPath = Application.persistentDataPath + "/data.db";
            // copy data.db from steamingAssetsPath to persistentDataPath
            if (!System.IO.File.Exists(systemDBPath))
            {
                CopyFileFromStreamingAssetsToPersistentDataPath("/data.db");
            }
        }
    }

    void CopyFileFromStreamingAssetsToPersistentDataPath(string relativePath)
    {
        string fileInStreamingAssets = Application.streamingAssetsPath + relativePath;
        string fileInPersistentDataPath = Application.persistentDataPath + relativePath;

        Debug.Log(fileInStreamingAssets + "->"+ fileInPersistentDataPath);

        if (Application.platform == RuntimePlatform.Android)
        {
            WWW fileReader = new WWW(fileInStreamingAssets);
            while (!fileReader.isDone) { }

            //把www加载的数据库的bytes写入到沙盒的data.db
            FileUtil.WriteFile(fileInPersistentDataPath, fileReader.bytes);
        }
        else
        {
            byte[] content = FileUtil.ReadFile(fileInStreamingAssets);
            FileUtil.WriteFile(fileInPersistentDataPath, content);
        }
    }
    public void OpenDB()
    {
        try
        {
            //指定数据库路径，一般放置到StreamingAsset下
            string path = Application.streamingAssetsPath + "/" + dbName + ".db";
            if (Application.platform == RuntimePlatform.Android || Application.platform == RuntimePlatform.IPhonePlayer)
            {
                path = Application.persistentDataPath + "/" + dbName + ".db";
            }

            Debug.Log("dbPath:" + path);
            //新建数据库连接
            connection = new SqliteConnection(@"Data Source = " + path);
            //打开数据库
            connection.Open();
            Debug.Log("打开数据库");
        }
        catch (Exception ex)
        {
            Debug.Log(ex.ToString());
        }
    }

    public void CloseDB()
    {
        if(connection!=null)
            connection.Close();
        if (command != null)
            command.Dispose();//Dispose用于释放，和Close功能一样
        if (reader != null)
            reader.Close();
        Debug.Log("关闭数据库");
    }

    //创建表
    //Activator.CreateInstance(type);//根据System.Type类型去实例化一个类
    //var fields = type.GetFields();//根据System.Type类型拿到类中的字段
    //fields[i].Name//得到单个字段的名字
    //fields[i].Name//T t:  type.GetField(fields[i].Name).GetValue(t);//得到T类型的t中field的值
    //fields[i].Name//T t:  type.GetField(fields[i].Name).SetValue(t,"新的值");//设置T类型的t中field的值
    public void CreateTable<T>()
    {
        var type = typeof(T);
        string sql = "create Table "+type.Name+"(";
        var fields = type.GetFields();
        for (int i = 0; i < fields.Length; i++)
        {
            sql += "[" + fields[i].Name + "] " + CS2DB(fields[i].FieldType)+",";
        }
        sql = sql.TrimEnd(',') + ")";
        Excute(sql);
    }

    public void Insert<T>(T t)
    {
        var type = typeof(T);
        string sql = "insert into " + type.Name + " values (";

        var fields = type.GetFields();
        foreach (var field in fields)
        {
            //通过反射得到对象中的值
            sql += "'" + type.GetField(field.Name).GetValue(t) + "',";
        }
        sql = sql.TrimEnd(',') + ");";

        Excute(sql);
    }
    //插入语句
    //--insert into users values("小新",28,'男','371452646566256456',1235123123);
    public void InsertInto(string tbName,params object[] objs)
    {
        string sql = "insert into " + tbName + " values (";
        foreach (var item in objs)
        {
            sql += "'"+ item + "',";
        }
        //sql=sql.Substring(0, sql.Length - 1);
        sql=sql.TrimEnd(',')+ ");";

        Excute(sql);
    }
    //删除语句
    public void DeleteAnd(string tbName,params object[] objs)
    {
        string sql = "delete from " + tbName + " where (";
        for (int i = 0; i < objs.Length-1; i+=2)
        {
            sql += objs[i] + "='" + objs[i + 1] +"' and ";
        }
        sql=sql.Substring(0, sql.Length - 4)+ ");";
        Excute(sql);

    }
    //修改语句
    //--update users set age = 18 where name = '小史'and cardid isnull;
    public void UpdateAnd(string tbName, object[] colums,params object[] conditions)
    {
        string sql = "update "+tbName+" set ";
        for (int i = 0; i < colums.Length-1; i+=2)
        {
            sql += colums[i] + "='" + colums[i + 1] + "',";
        }
        sql = sql.TrimEnd(',') + " where (";
        for (int i = 0; i < conditions.Length-1; i+=2)
        {
            sql += conditions[i] + "='" + conditions[i + 1]+"' and ";
        }
        sql = sql.Substring(0, sql.Length - 4) + ");";
        Excute(sql);
    }
    //查询>字典
    public List<Dictionary<string, string>> Select(string tbName)
    {
        string sql = "select * from " + tbName;
        Excute(sql);
        List<Dictionary<string, string>> result = new List<Dictionary<string, string>>();
        while(reader.Read())
        {
            var dic = new Dictionary<string, string>();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                dic.Add(reader.GetName(i), reader.GetValue(i).ToString());
            }
            result.Add(dic);
        }
        return result;
    }

    /// <summary>
    /// 泛型约束
    /// class :引用类型
    /// struct:值类型
    /// 接口和抽象类
    /// 普通类
    /// new()：无参构造,(必须是最后一个约束)
    /// </summary>
    //查询：泛型实体类（复用性高，灵活）
    public List<T> Select<T>() where T: new()
    {
        var type = typeof(T);
        string sql = "select * from " + type.Name;
        Excute(sql);

        List<T> result = new List<T>();
        var fields = type.GetFields();
        while (reader.Read())//读取下一行数据
        {
            T t = new T();//使用new() 实例化T
            for (int i = 0; i < reader.FieldCount; i++)
            {
                string key = reader.GetName(i);//获取数据库指定列的key
                object value = reader.GetValue(i);//获取数据库指定列的值
                Debug.Log(key + ":" + value);
                type.GetField(key).SetValue(t, value);//使用反射给T类型t赋值
            }
            result.Add(t);
        }
        return result;
    }

    /// <summary>
    /// 通过Id查询数据
    /// </summary>
    public T SelectById<T>(string id) where T:new()
    {
        Type type = typeof(T);
        string sql = "select * from " + type.Name + " where id='" + id+"'";
        Excute(sql);

        T t = new T();
        reader.Read();
        var count = reader.FieldCount;
        for (int i = 0; i < count; i++)
        {
            string key = reader.GetName(i);
            object value = reader.GetValue(i);
            type.GetField(key).SetValue(t, value);
        }
        return t;
    }

    void Excute(string sql)
    {
        Debug.LogWarning(sql);
        //创建数据库连接命令
        command = connection.CreateCommand();
        //设置命令语句
        command.CommandText = sql;
        //执行命令
        reader = command.ExecuteReader();
    }

    string CS2DB(Type type)
    {
        string result = "Text";
        if(type==typeof(Int32))
        {
            result = "Int";
        }
        else if (type == typeof(String))
        {
            result = "Text";
        }
        else if (type == typeof(Single))
        {
            result = "FLOAT";
        }
        else if (type == typeof(Boolean))
        {
            result = "Bool";
        }
        return result;
    }
}