配置SQLite是我遇到的Gradle以外的最恶心的事情了。
环境: Unity2019版本,.NET2.0
电脑篇
-
导入两个dll文件,Mono.Data.Sqlite.dll以及SQLite3.dll,放到Asset/Plugins下面。
-
Mono.Data.Sqlite.dll在Unity安装目录下面找一个,SQLite3.dll网上下一个
-
我有.Net 2.0版本的你们可以试试
https://pan.baidu.com/s/10zfaiH_kY0NP5ub_WfH_fQ,提取码9ls3 -
代码内using一下
using Mono.Data.Sqlite;
- 至此应该可以正常使用了
安卓篇
- 在电脑配置的基础上,你需要额外两个单单适用于Android的文件:libsqlite3.so和System.Data.dll。
- System.Data.dll在
Editor\Data\MonoBleedingEdge\lib\mono\2.0-api
下面,libsqlite3.so网上下。 - 这两个文件上面的链接里面也有
- 然后你会发现Unity报错,说什么和另外一个同名了,所以你需要在Unity内对这两个文件进行配置:
- 还有一个就是比较重要的就是在Build Setting-Player-Other里面,写入权限改为SDCard,也就是文件写入权限。这样在创建数据库时,可以直接使用Directory.CreateDirectory创建目录。
常用操作
/*
* Author : Jk_Chen
*/
using System;
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using Mono.Data.Sqlite;
using System.IO;
public class SQL
{
/// <summary>
/// 单例模式
/// </summary>
private SQL() { }
public static readonly SQL instance = new SQL();
private SqliteConnection conn;
//——————————————————————————————数据库——————————————————————————————//
string path = Config.instance.databasePath;
/// <summary>
/// 创建数据库
/// </summary>
/// <param name="dbName">数据库的名字,不用加.db</param>
public void CreatDatabase(string dbName)
{
dbName += ".db";
Debug.Log(path + dbName);
if (!Directory.Exists(path))
{
Debug.Log("Create Database at " + path);
Directory.CreateDirectory(path);
}
if (!File.Exists(path + dbName))
{
SqliteConnection.CreateFile(path + dbName);
}
}
/// <summary>
/// 打开数据库
/// </summary>
/// <param name="dbName">数据库的名字,不用加.db</param>
public void OpenDataBase(string dbName)
{
dbName += ".db";
Debug.Log(path + dbName);
string connectionString = @"Data Source = " + path + dbName;
try
{
conn = new SqliteConnection(connectionString);
conn.Open();
Debug.Log("Connected");
}
catch (Exception e)
{
Debug.Log(e.ToString());
}
}
/// <summary>
/// 关闭数据库
/// </summary>
public void CloseDataBase()
{
if (conn != null)
{
conn.Close();
}
conn = null;
Debug.Log("Disconnected");
}
/// <summary>
/// 删除数据库
/// </summary>
/// <param name="dbName">数据库名字</param>
/// <returns>删除成功返回1,否则返回0</returns>
public int DeleteDataBase(string dbName)
{
dbName += ".db";
if (System.IO.File.Exists(path + dbName))
{
System.IO.File.Delete(path + dbName);
return 1;
}
return 0;
}
//——————————————————————————————基础操作——————————————————————————————//
/// <summary>
/// Log
/// </summary>
/// <param name="x"></param>
public void Log(string x)
{
Debug.Log("SqliteLog: " + x);
}
/// <summary>
/// 非查询操作
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns>返回影响条数</returns>
public int ExecuteNonQuery(string sql, params SqliteParameter[] parameters)
{
int res = -1;
try
{
SqliteCommand comm = new SqliteCommand(sql, conn);
if (parameters.Length > 0)
comm.Parameters.AddRange(parameters);
res = comm.ExecuteNonQuery();
Log("change lines " + res);
}
catch (SqliteException ex)
{
Log(ex.Message);
}
return res;
}
/// <summary>
/// 执行查询语句,返回第一行的第一列
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public object ExecuteScalar(string sql, params SqliteParameter[] parameters)
{
object res = null;
try
{
SqliteCommand comm = new SqliteCommand(sql, conn);
if (parameters.Length > 0)
comm.Parameters.AddRange(parameters);
res = comm.ExecuteScalar();
Log("Find" + res.ToString());
}
catch (SqliteException ex)
{
Log(ex.Message);
}
return res;
}
/// <summary>
/// 执行查询语句,返回SqliteDataReader
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public SqliteDataReader ExecuteReader(string sql, params SqliteParameter[] parameters)
{
SqliteDataReader res = null;
try
{
SqliteCommand comm = new SqliteCommand(sql, conn);
if (parameters.Length > 0)
comm.Parameters.AddRange(parameters);
res = comm.ExecuteReader();
}
catch (SqliteException ex)
{
Log(ex.Message);
}
return res;
}
/// <summary>
/// 显示所有查询结果,包括表头
/// </summary>
/// <param name="sql"></param>
public void ShowAllOfDataReader(string sql, params SqliteParameter[] parameters)
{
using (var sdr = ExecuteReader(sql,parameters))
{
string s = "";
for (int i = 0; i < sdr.FieldCount; i++)
{
s += sdr.GetName(i) + " ";
}
s += '\n';
while (sdr.Read())
{
for (int i = 0; i < sdr.FieldCount; i++)
{
s += sdr[i] + " ";
}
s += '\n';
}
Log(s);
}
}
//——————————————————————————————表相关——————————————————————————————//
/// <summary>
/// 通过列名和列类型创建表
/// </summary>
/// <param name="name">表的名称</param>
/// <param name="col">列名</param>
/// <param name="colType">列类型</param>
/// <returns></returns>
public int CreateTable(string tbName, string[] col, string[] colType)
{
if (col.Length != colType.Length)
{
throw new SqliteException("columns.Length != colType.Length");
}
string sql = "CREATE TABLE " + tbName + " (" + col[0] + " " + colType[0] + " PRIMARY KEY";
for (int i = 1; i < col.Length; ++i)
{
sql += ", " + col[i] + " " + colType[i];
}
sql += ")";
return ExecuteNonQuery(sql);
}
/// <summary>
/// 删除表
/// </summary>
/// <param name="tbName"></param>
/// <returns></returns>
public int DropTable(string tbName)
{
string sql = "Drop table if exists " + tbName;
return ExecuteNonQuery(sql);
}
/// <summary>
/// 查看所有表的结构
/// </summary>
public void ShowTablesInfo()
{
string sql = "SELECT type as 类别,name as 名称,sql as 结构 FROM sqlite_master WHERE type='table'";
ShowAllOfDataReader(sql);
}
/// <summary>
/// 查看单张表的结构
/// </summary>
public void ShowTableInfo(string tbName)
{
string sql = string.Format("SELECT type as 类别,name as 名称,sql as 结构 FROM sqlite_master WHERE type='table' AND name='{0}'",tbName);
ShowAllOfDataReader(sql);
}
/// <summary>
/// 查看表是否存在
/// </summary>
public bool CheckTableExist(string tbName)
{
string sql = string.Format("SELECT count(*) FROM sqlite_master WHERE type='table' AND name='{0}'", tbName);
int ct = int.Parse(ExecuteScalar(sql).ToString());
return ct > 0;
}
public void ShowAllInTable(string tbName)
{
string sql = "Select * From " + tbName;
ShowAllOfDataReader(sql);
}
/// <summary>
/// CSharp类型转化为SQLite类别
/// </summary>
/// <param name="type">c#中的类别</param>
/// <returns>SQLite中对应的类别</returns>
string CSharpToSQLite(Type type)
{
string result = "Text";
if (type == typeof(Int32))
{
result = "INT";
}
else if (type == typeof(String))
{
result = "Text";
}
else if (type == typeof(Single))
{
result = "Real";
}
else if (type == typeof(Boolean))
{
result = "INT";
}
else if (type == typeof(DateTime))
{
result = "DATETIME";
}
return result;
}
}