准备工作
sql语句
创建表
Create Table 表名 （列1，类型，列2，类型，...）
增加
Insert into 表名 values(列1，列2，...);
删除
Delete from 表名 where 条件
修改
Update 表名 set age=18 where 条件;
查询
Select * from 表名 Select * from 表名 where 条件
反射
反射是什么？自行百度。
为什么使用反射？
查询本身是非常简单的，只需要输入相应的sql语句就行了，但是把查询的数据简单便捷的转化为对应的实体类是非常麻烦的，这里使用了“反射+泛型”得到需要查询的实体类，然后利用反射得到实体类的属性或者字段，然后赋值后直接返回需要的数据集合。
代码
using System;
using System.Collections.Generic;
using UnityEngine;
using Mono.Data.sqlite;
/// <summary>
/// 数据库管理器
/// </summary>
public class DBManager :MonoBehavIoUr{
private static DBManager _instance;
public static DBManager Instance
{
get
{
if (_instance == null)
{
GameObject go = new GameObject("DBManger");
_instance = go.AddComponent<DBManager>();
}
return _instance;
}
}
//用于建立数据库连接，保证数据流
sqliteConnection connection;
//数据库命令
sqliteCommand command;
//数据库阅读器
sqliteDataReader reader;
private const string dbName = "data";
void Awake()
{
InitDB();
OpenDB();
}
void OnDestroy()
{
CloseDB();
}
//拷贝数据库到沙盒目录
void InitDB()
{
Debug.Log("*-> DBManager->Init");
//判断是移动端
if (Application.platform == RuntimePlatform.Android || Application.platform == RuntimePlatform.IPhonePlayer)
{
string systemDBPath = Application.persistentDataPath + "/data.db";
// copy data.db from steamingAssetsPath to persistentDataPath
if (!System.IO.File.Exists(systemDBPath))
{
CopyFileFromStreamingAssetsToPersistentDataPath("/data.db");
}
}
}
void CopyFileFromStreamingAssetsToPersistentDataPath(string relativePath)
{
string fileInStreamingAssets = Application.streamingAssetsPath + relativePath;
string fileInPersistentDataPath = Application.persistentDataPath + relativePath;
Debug.Log(fileInStreamingAssets + "->"+ fileInPersistentDataPath);
if (Application.platform == RuntimePlatform.Android)
{
WWW fileReader = new WWW(fileInStreamingAssets);
while (!fileReader.isDone) { }
//把www加载的数据库的bytes写入到沙盒的data.db
FileUtil.WriteFile(fileInPersistentDataPath,fileReader.bytes);
}
else
{
byte[] content = FileUtil.ReadFile(fileInStreamingAssets);
FileUtil.WriteFile(fileInPersistentDataPath,content);
}
}
public void OpenDB()
{
try
{
//指定数据库路径，一般放置到StreamingAsset下
string path = Application.streamingAssetsPath + "/" + dbName + ".db";
if (Application.platform == RuntimePlatform.Android || Application.platform == RuntimePlatform.IPhonePlayer)
{
path = Application.persistentDataPath + "/" + dbName + ".db";
}
Debug.Log("dbPath:" + path);
//新建数据库连接
connection = new sqliteConnection(@"Data Source = " + path);
//打开数据库
connection.Open();
Debug.Log("打开数据库");
}
catch (Exception ex)
{
Debug.Log(ex.ToString());
}
}
public void CloseDB()
{
if(connection!=null)
connection.Close();
if (command != null)
command.Dispose();//Dispose用于释放，和Close功能一样
if (reader != null)
reader.Close();
Debug.Log("关闭数据库");
}
//创建表
//Activator.CreateInstance(type);//根据System.Type类型去实例化一个类
//var fields = type.GetFields();//根据System.Type类型拿到类中的字段
//fields[i].Name//得到单个字段的名字
//fields[i].Name//T t: type.GetField(fields[i].Name).GetValue(t);//得到T类型的t中field的值
//fields[i].Name//T t: type.GetField(fields[i].Name).SetValue(t,"新的值");//设置T类型的t中field的值
public void CreateTable<T>()
{
var type = typeof(T);
string sql = "create Table "+type.Name+"(";
var fields = type.GetFields();
for (int i = 0; i < fields.Length; i++)
{
sql += "[" + fields[i].Name + "] " + CS2DB(fields[i].FieldType)+",";
}
sql = sql.TrimEnd(',') + ")";
Excute(sql);
}
public void Insert<T>(T t)
{
var type = typeof(T);
string sql = "insert into " + type.Name + " values (";
var fields = type.GetFields();
foreach (var field in fields)
{
//通过反射得到对象中的值
sql += "'" + type.GetField(field.Name).GetValue(t) + "',') + ");";
Excute(sql);
}
//插入语句
//--insert into users values("小新",28,'男','371452646566256456',1235123123);
public void InsertInto(string tbName,params object[] objs)
{
string sql = "insert into " + tbName + " values (";
foreach (var item in objs)
{
sql += "'"+ item + "',";
}
//sql=sql.Substring(0,sql.Length - 1);
sql=sql.TrimEnd(',')+ ");";
Excute(sql);
}
//删除语句
public void DeleteAnd(string tbName,params object[] objs)
{
string sql = "delete from " + tbName + " where (";
for (int i = 0; i < objs.Length-1; i+=2)
{
sql += objs[i] + "='" + objs[i + 1] +"' and ";
}
sql=sql.Substring(0,sql.Length - 4)+ ");";
Excute(sql);
}
//修改语句
//--update users set age = 18 where name = '小史'and cardid isnull;
public void UpdateAnd(string tbName,object[] colums,params object[] conditions)
{
string sql = "update "+tbName+" set ";
for (int i = 0; i < colums.Length-1; i+=2)
{
sql += colums[i] + "='" + colums[i + 1] + "',') + " where (";
for (int i = 0; i < conditions.Length-1; i+=2)
{
sql += conditions[i] + "='" + conditions[i + 1]+"' and ";
}
sql = sql.Substring(0,sql.Length - 4) + ");";
Excute(sql);
}
//查询>字典
public List<Dictionary<string,string>> Select(string tbName)
{
string sql = "select * from " + tbName;
Excute(sql);
List<Dictionary<string,string>> result = new List<Dictionary<string,string>>();
while(reader.Read())
{
var dic = new Dictionary<string,string>();
for (int i = 0; i < reader.FieldCount; i++)
{
dic.Add(reader.GetName(i),reader.GetValue(i).ToString());
}
result.Add(dic);
}
return result;
}
/// <summary>
/// 泛型约束
/// class :引用类型
/// struct:值类型
/// 接口和抽象类
/// 普通类
/// new()：无参构造,(必须是最后一个约束)
/// </summary>
//查询：泛型实体类（复用性高，灵活）
public List<T> Select<T>() where T: new()
{
var type = typeof(T);
string sql = "select * from " + type.Name;
Excute(sql);
List<T> result = new List<T>();
var fields = type.GetFields();
while (reader.Read())//读取下一行数据
{
T t = new T();//使用new() 实例化T
for (int i = 0; i < reader.FieldCount; i++)
{
string key = reader.GetName(i);//获取数据库指定列的key
object value = reader.GetValue(i);//获取数据库指定列的值
Debug.Log(key + ":" + value);
type.GetField(key).SetValue(t,value);//使用反射给T类型t赋值
}
result.Add(t);
}
return result;
}
/// <summary>
/// 通过Id查询数据
/// </summary>
public T SelectById<T>(string id) where T:new()
{
Type type = typeof(T);
string sql = "select * from " + type.Name + " where id='" + id+"'";
Excute(sql);
T t = new T();
reader.Read();
var count = reader.FieldCount;
for (int i = 0; i < count; i++)
{
string key = reader.GetName(i);
object value = reader.GetValue(i);
type.GetField(key).SetValue(t,value);
}
return t;
}
void Excute(string sql)
{
Debug.LogWarning(sql);
//创建数据库连接命令
command = connection.CreateCommand();
//设置命令语句
command.CommandText = sql;
//执行命令
reader = command.ExecuteReader();
}
string CS2DB(Type type)
{
string result = "Text";
if(type==typeof(Int32))
{
result = "Int";
}
else if (type == typeof(String))
{
result = "Text";
}
else if (type == typeof(Single))
{
result = "FLOAT";
}
else if (type == typeof(Boolean))
{
result = "Bool";
}
return result;
}
} 原文链接：https://www.f2er.com/sqlite/198034.html