在unity中使用内嵌数据库sqlite

前言
  • 这个任务是因为我在给别人写外包,需要数据库。
  • 如果使用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();
        }
    }
}

上一篇:踩坑指南:入门OpenTenBase之部署篇


下一篇:【译】新的 MSBuild 编辑体验