C#系 SQLite4Unity的介绍_sqlite4unity3d

作者：小小林熬夜学编程 | 2024-04-26 12:30:59

踩

sqlite4unity3d

为什么要记录这一篇呢？使用过Unity的猿媛们可能知道，Unity使用的.net框架一般比较老，比如说Unity5.6.3使用的是.net3.5Framework 导致有很多高效的库无法使用，比如将汉字转笔画或者转拼音的一些库就无法使用。这让我在项目开发中很抓狂。为了能在Unity项目中高效的使用SQLite数据库，经过各种对比，此插件更胜一筹。特此记录一下来分享给各位猿媛！（不要问我为什么要在Unity 项目中使用SQLite,因为软件是不联网的，又要有很多数据的支撑）

SQLite4Unity3d 下载地址如下：

https://github.com/robertohuertasm/SQLite4Unity3d

下载完成后将按照下图中的路径找到Plugins目录和SQLite 导入到项目的Assets目录下

SQLite文件是对底层的一些封装，在后续操作中我们只需要在SQLite基础上进行操作即可

另外一个操作就是将DataService.cs导入到工程，这个操作其实是不一定是必要的，根据自己喜好。

此文件中定义了一些基础操作，我们可以直接利用

具体代码如下：


using SQLite4Unity3d;
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using System.IO;
 
 
public class Sqlite3BaseTable
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public Sqlite3BaseTable()
    {
 
 
    }
}
 
 
public class DataService
{
    private SQLiteConnection _connection;
 
 
    public DataService(string DatabaseName)
    {
 
 
#if UNITY_EDITOR
        var dbPath = string.Format(@"Assets/StreamingAssets/DB/{0}", DatabaseName);
        if (!Directory.Exists(@"Assets/StreamingAssets/DB/"))
        {
            Directory.CreateDirectory(@"Assets/StreamingAssets/DB/");
            UnityEditor.AssetDatabase.Refresh();
        }
#else
        // check if file exists in Application.persistentDataPath
        var filepath = string.Format("{0}/{1}", Application.persistentDataPath, DatabaseName);
 
 
        if (!File.Exists(filepath))
        {
            Debug.Log("Database not in Persistent path");
            // if it doesn't ->
            // open StreamingAssets directory and load the db ->
 
 
#if UNITY_ANDROID
            var loadDb = new WWW("jar:file://" + Application.dataPath + "!/assets/" + DatabaseName);  // this is the path to your StreamingAssets in android
            while (!loadDb.isDone) { }  // CAREFUL here, for safety reasons you shouldn't let this while loop unattended, place a timer and error check
            // then save to Application.persistentDataPath
            File.WriteAllBytes(filepath, loadDb.bytes);
#elif UNITY_IOS
                 var loadDb = Application.dataPath + "/Raw/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
                // then save to Application.persistentDataPath
                File.Copy(loadDb, filepath);
#elif UNITY_WP8
                var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
                // then save to Application.persistentDataPath
                File.Copy(loadDb, filepath);
 
 
#elif UNITY_WINRT
    var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
    // then save to Application.persistentDataPath
    File.Copy(loadDb, filepath);
    
#elif UNITY_STANDALONE_OSX
    var loadDb = Application.dataPath + "/Resources/Data/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
    // then save to Application.persistentDataPath
    File.Copy(loadDb, filepath);
#else
  var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
  // then save to Application.persistentDataPath
  File.Copy(loadDb, filepath);
 
 
#endif
 
 
            Debug.Log("Database written");
        }
 
 
        var dbPath = filepath;
#endif
        _connection = new SQLiteConnection(dbPath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create);
        Debug.Log("Final PATH: " + dbPath);
    }
 
 
    public void CreateTable<T>()
    {
        _connection.DropTable<T>();
        _connection.CreateTable<T>();
    }
    /// <summary>
    ///  插入多条数据到表中
    /// </summary>
    /// <param name="objects"></param>
    /// <returns>插入到表中数据额条数</returns>
    public int InsertDataToTable(IEnumerable objects)
    {
        return _connection.InsertAll(objects);
    }
 
 
    /// <summary>
    /// 插入一条数据到表中
    /// </summary>
    /// <param name="obj"></param>
    /// <returns>插入到表中数据额条数</returns>
    public int InsertDataToTable<T>(T obj) where T:Sqlite3BaseTable
    {
         return _connection.Insert(obj);
    }
 
 
    public T GetRowById<T>(int id) where T:Sqlite3BaseTable,new()
    {
        //var ret = from x in _connection.Table<T>()
        //          where x.Id == id
        //          select x;
        //return ret.FirstOrDefault();
        return _connection.Table<T>().Where(x => x.Id == id).FirstOrDefault();
    }
 
 
    /// <summary>
    /// 删除表
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public void DropTable<T>()
    {
        _connection.DropTable<T>();
    }
 
 
    public IEnumerable<T> GetAllRows<T>() where T:Sqlite3BaseTable,new()
    {
        return _connection.Table<T>();
    }
}

DataService 可以帮助我们创建和数据库操纵的链接，以及其他操作

例如创建数据库的操作


public void CreateTable<T>()
{
    //删除数据库
    _connection.DropTable<T>();
    //创建数据库
    _connection.CreateTable<T>();
}

插入多条数据的操作


/// <summary>
///  插入多条数据到表中
/// </summary>
/// <param name="objects"></param>
/// <returns>插入到表中数据额条数</returns>
public int InsertDataToTable(IEnumerable objects)
{
    return _connection.InsertAll(objects);
}

插入一条数据


/// <summary>
/// 插入一条数据到表中
/// </summary>
/// <param name="obj"></param>
/// <returns>插入到表中数据额条数</returns>
public int InsertDataToTable<T>(T obj) where T:Sqlite3BaseTable
{
     return _connection.Insert(obj);
}

通过Id 的带一条数据

下面展示了两种操作数据的方式，在实际操作中任选其一


public T GetRowById<T>(int id) where T:Sqlite3BaseTable,new()
{
    //使用linq 操作数据库
    var ret = from x in _connection.Table<T>()
              where x.Id == id
              select x;
    return ret.FirstOrDefault();
    //使用lambda 表达式
    return _connection.Table<T>().Where(x => x.Id == id).FirstOrDefault();
}

删除表的操作


/// <summary>
/// 删除表
/// </summary>
/// <typeparam name="T"></typeparam>
public void DropTable<T>()
{
    _connection.DropTable<T>();
}

得到所有的条目数据


public IEnumerable<T> GetAllRows<T>() where T:Sqlite3BaseTable,new()
{
    return _connection.Table<T>();
}

以上针对与数据库的操作都支持Linq ,在此篇文章中不在赘述。可参考通过Id 的带一条数据的操作

从其构造函数中可以看出，他支持多个平台，ANDROID，iOS,WP8,Window，并且它的数据库文件在Assets/StreamingAssets/DB 目录下，如果在创建的时候目录不存在，则会帮猿媛们创建对应的目录


  public DataService(string DatabaseName)
  {
 
 
#if UNITY_EDITOR
      var dbPath = string.Format(@"Assets/StreamingAssets/DB/{0}", DatabaseName);
      if (!Directory.Exists(@"Assets/StreamingAssets/DB/"))
      {
          Directory.CreateDirectory(@"Assets/StreamingAssets/DB/");
          UnityEditor.AssetDatabase.Refresh();
      }
#else
      // check if file exists in Application.persistentDataPath
      var filepath = string.Format("{0}/{1}", Application.persistentDataPath, DatabaseName);
 
 
      if (!File.Exists(filepath))
      {
          Debug.Log("Database not in Persistent path");
          // if it doesn't ->
          // open StreamingAssets directory and load the db ->
 
 
#if UNITY_ANDROID
          var loadDb = new WWW("jar:file://" + Application.dataPath + "!/assets/" + DatabaseName);  // this is the path to your StreamingAssets in android
          while (!loadDb.isDone) { }  // CAREFUL here, for safety reasons you shouldn't let this while loop unattended, place a timer and error check
          // then save to Application.persistentDataPath
          File.WriteAllBytes(filepath, loadDb.bytes);
#elif UNITY_IOS
               var loadDb = Application.dataPath + "/Raw/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
              // then save to Application.persistentDataPath
              File.Copy(loadDb, filepath);
#elif UNITY_WP8
              var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
              // then save to Application.persistentDataPath
              File.Copy(loadDb, filepath);
 
 
#elif UNITY_WINRT
  var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
  // then save to Application.persistentDataPath
  File.Copy(loadDb, filepath);
  
#elif UNITY_STANDALONE_OSX
  var loadDb = Application.dataPath + "/Resources/Data/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
  // then save to Application.persistentDataPath
  File.Copy(loadDb, filepath);
#else
var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
// then save to Application.persistentDataPath
File.Copy(loadDb, filepath);
 
 
#endif
 
 
          Debug.Log("Database written");
      }
 
 
      var dbPath = filepath;
#endif
      _connection = new SQLiteConnection(dbPath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create);
      Debug.Log("Final PATH: " + dbPath);
  }

SQlite4Unity的插件是不是很强大！由于篇幅较长，SQLite的介绍先到这里，下一篇我们介绍SQlite4Unity的使用

历史文章目录
https://blog.csdn.net/yy763496668/article/details/113117040
【关注、点赞，收藏】
关注公众号，您将第一时间收到文章更新
QQ群号：1056320746
微信公众号：猿媛大本营

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小小林熬夜学编程/article/detail/490993