Unity C# SQLite4Unity的介绍
为什么要记录这一篇呢？使用过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的使用
浙公网安备 33010602011771号