前言
- 这个任务是因为我在给别人写外包，需要数据库。
- 如果使用MySQL，实在是太笨重了，需要额外安装MySQL这个软件
- 所以我决定用sqlite这个嵌入式数据库。
网络上两种情况
-
第一种是使用这两个dll来进行连接并创建，在自己电脑上就可以很轻易的找到，我这里使用的是everythingTool工具，所以比较方便找到了。
-
视频教程B站视频
-
第二种是github上某人写的一个连接库
视频教程 -
我这里使用的是第二种
-
数据库可视化工具用的sqliteStudio
-
GitHub连接
如何使用
- 导入插件
插件 - 写脚本，连接数据库。
- 再者，创建数据表
- 注意事项
- 1.他这个插件会根据你的类自动创建对应的表的表头，如果你需要主键，就需要继承IPrimaryKey
- 而且因为我没找到怎么让她自动生成主键，所以我是自己用Guid来填充的
- 2.在测试阶段退出player后一定要关闭和数据库的connection，不然你没有办法删除生成的表。
三个脚本
- 脚本是我根据插件里面自带的例子更改而成的
- model模型类，用来对表的数据做映射
- Table类-提供基本的对表的增删改查的操作，比如按照ID搜索，按照名字搜索等等。
- ModelManager，用table的方法来提供对上层的支持
using SQLite4Unity3d;
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
public enum Role
{
None = 0,
InWork = 1,
LoseWork = 2,
ExitWork = 3,
Other = 4,
}
public enum Major
{
None = 0,
Machine = 1,
NotMachine = 2
}
public interface IPrimaryKey
{
public string ID { get; set; }
}
public class User : IPrimaryKey
{
[PrimaryKey]
public string ID { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
public Role Role { get; set; } = Role.None;
public string Name { get; set; }
public int StudyNumber { get; set; }
public Major MajorCode { get; set; }
}
- 通用的表类
using SQLite4Unity3d;
using UnityEngine;
#if !UNITY_EDITOR
using System.Collections;
using System.IO;
#endif
using System.Collections.Generic;
public class UserTable : TableService<User>
{
public User FindByUserName(string name)
{
return _connection.Table<User>().Where(x => x.UserName == name).FirstOrDefault();
}
}
public class TableService<T> where T : IPrimaryKey, new()
{
protected static SQLiteConnection _connection;
public static void DisConnect()
{
_connection.Dispose();
}
const string DatabaseName = "Crane.db";
static TableService()
{
#if UNITY_EDITOR
var dbPath = string.Format(@"Assets/StreamingAssets/{0}", DatabaseName);
#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);
var count = _connection.GetTableInfo(typeof(T).Name).Count;
//创建对应的Table
if (count == 0)
{
_connection.CreateTable<T>();
}
}
public int InsertData(T obj)
{
return _connection.Insert(obj);
}
public int Delete(T obj)
{
return _connection.Delete(obj);
}
public int Update(T obj)
{
return _connection.Update(obj);
}
public T FindByID(string ID)
{
return _connection.Table<T>().Where(x => x.ID == ID).FirstOrDefault();
}
//public void CreateDB<T>()
//{
// _connection.CreateTable<T>();
//}
//public IEnumerable<Person> GetPersons()
//{
// //_connection.
// return _connection.Table<Person>();
//}
//public IEnumerable<Person> GetPersonsNamedRoberto()
//{
// return _connection.Table<Person>().Where(x => x.Name == "Roberto");
//}
//public Person GetJohnny()
//{
// return _connection.Table<Person>().Where(x => x.Name == "Johnny").FirstOrDefault();
//}
//public Person CreatePerson()
//{
// var p = new Person
// {
// Name = "Johnny",
// Surname = "Mnemonic",
// Age = 21
// };
// _connection.Insert(p);
// return p;
//}
}
- ModelMaanger
using System;
using System.Collections;
using System.Collections.Generic;
using System.Security.Cryptography;
using System.Text;
using Unity.VisualScripting;
using UnityEngine;
public class ModelManager
{
public static UserTable UserTable;
public static ModelManager Inst { get; private set; } = new ModelManager();
static ModelManager()
{
UserTable = new UserTable();
}
private ModelManager()
{
}
public void OnQuit()
{
UserTable.DisConnect();
}
public void RegisterUser(User user)
{
user.ID = Guid.NewGuid().ToString();
user.Password = EnCryption(user.Password + salt);
UserTable.InsertData(user);
}
public bool IsExistName(string name, out User user)
{
var currUser = UserTable.FindByUserName(name);
user = currUser;
return currUser != null;
}
public bool IsExistName(string name)
{
var currUser = UserTable.FindByUserName(name);
return currUser != null;
}
public void UpdatePassword(User user, string password)
{
user.Password = EnCryption(password + salt);
UserTable.Update(user);
}
public bool IsMatchName(string name, string password, out bool passwordRight)
{
var currUser = UserTable.FindByUserName(name);
if (currUser == null)
{
passwordRight = false;
return false;
}
else
{
passwordRight = EnCryption(password + salt) == currUser.Password;
return true;
}
}
string salt = "sdsax";
public string EnCryption(string content)
{
using (MD5 md5Hash = MD5.Create())
{
byte[] data = md5Hash.ComputeHash(Encoding.UTF8.GetBytes(content));
StringBuilder builder = new StringBuilder();
for (int i = 0; i < data.Length; i++)
{
builder.Append(data[i].ToString("x2")); // 将每个字节转换为十六进制并添加到结果中
}
return builder.ToString();
}
}
}