using System; using Mono.Data.sqliteClient; using System.Data; using UnityEngine; class DbAccessHelper { private IDbConnection dbConn; private IDbCommand dbCommand; private IDataReader dbReader; public DbAccessHelper(string path) { OpenDb(path); } /// <summary> /// 打开数据库 /// </summary> /// <param name="path">路径</param> private void OpenDb(string path) { dbConn = new sqliteConnection("URI=file:" + path); dbConn.Open(); Debug.Log("连接数据库成功!"); } /// <summary> /// 创建表 /// </summary> /// <param name="name">表明</param> /// <param name="col">数据</param> /// <param name="colType">数据类型</param> /// <returns></returns> public bool CreateTable(string name,string[] col,string[] colType) { string commPath = "CREATE TABLE " + name + "(" + col[0] + " " + colType[0]; for (int i = 1; i < col.Length; i++) { commPath += "," + col[i] + " " + colType[i]; } commPath += ")"; ExecuteCommand(commPath); Debug.Log("创建表成功!"); return true; } /// <summary> /// 插入多条数据 /// </summary> /// <param name="tableName">表名</param> /// <param name="values">数据</param> /// <returns></returns> public bool InsertInto(string tableName,string[] values) { string commPath = "INSERT INTO " + tableName + " VALUES (" + values[0]; for (int i = 1; i < values.Length; i++) { commPath += "," + values[i]; } commPath += ")"; ExecuteCommand(commPath); Debug.Log("插入数据成功!"); return true; } /// <summary> /// 插入单条数据 /// </summary> /// <param name="tableName">表名</param> /// <param name="colName">列名</param> /// <param name="value">数据</param> public bool InsertIntoSingle(string tableName,string colName,string value) { string commPath = "INSERT INTO " + tableName + "(" + colName + ") " + "VALUES (" + value + ")"; ExecuteCommand(commPath); Debug.Log("插入单条数据成功!"); return true; } /// <summary> /// 根据列插入数据 /// </summary> /// <param name="tableName">表名</param> /// <param name="colnames">列名</param> /// <param name="values">数据</param> /// <returns></returns> public bool InsertIntoSpecific(string tableName,string[] colnames,string[] values) { string commPath = "INSERT INTO " + tableName + "(" + colnames[0]; for (int i = 1; i < colnames.Length; i++) { commPath += "," + colnames[i]; } commPath += ") VALUES (" + values[0]; for (int i = 1; i < values.Length; i++) { commPath += "," + values[i]; } ExecuteCommand(commPath); return true; } /// <summary> /// 更新表中数据 /// </summary> /// <param name="tableName">表名</param> /// <param name="colNames">列名</param> /// <param name="colValues">更新的数据</param> /// <param name="selectKey">主键</param> /// <param name="selectValue">主键值</param> public bool UpdateInto(string tableName,string[] colNames,string[] colValues,string selectKey,string selectValue) { string commPath = "UPDATE " + tableName + " SET " + colNames[0] + "=" + colValues[0]; for (int i = 1; i < colNames.Length; i++) { commPath += "," + colNames[i] + "=" + colValues[i]; } commPath += " WHERE " + selectKey + "=" + selectValue; ExecuteCommand(commPath); Debug.Log("更新数据成功!"); return true; } /// <summary> /// 执行删除数据语句 /// </summary> /// <param name="tableName">表名</param> /// <param name="colNames">列名</param> /// <param name="values">某列对应的数据</param> /// <returns></returns> public void Delete(string tableName,string[] values) { string commPath = "DELETE FROM " + tableName + " WHERE " + colNames[0] + "=" + values[0]; for (int i = 1; i < colNames.Length; i++) { commPath += " OR " + colNames[i] + "=" + values[i]; } ExecuteCommand(commPath); Debug.Log("删除成功!"); } public IDataReader SelectWhere(string tableName,string[] item,string[] operations,string[] values) { string commPath = "SELECT " + item[0]; for (int i = 1; i < item.Length; i++) { commPath += "," + item[i]; } commPath += " FROM " + tableName + " WHERE " + colNames[0] + operations[0] + values[0]; for (int i = 1; i < colNames.Length; i++) { commPath += " OR " + colNames[i] + operations[i] + values[i]; } return ExecuteCommand(commPath); } /// <summary> /// 执行命令 /// </summary> /// <param name="commPath"></param> /// <returns></returns> private IDataReader ExecuteCommand(string commPath) { try { dbCommand = dbConn.CreateCommand(); dbCommand.CommandText = commPath; dbReader = dbCommand.ExecuteReader(); } catch (Exception e) { Debug.LogError(e); } return dbReader; } /// <summary> /// 关闭数据库 /// </summary> public void CloseDatabase() { dbReader.Close(); dbReader = null; dbCommand.Dispose(); dbCommand = null; dbConn.Close(); dbConn = null; Debug.Log("关闭数据库!"); } }
上面这个类就是封装了sqlite的打开数据库、关闭数据库、增、删、改、查等相应功能，如果有其他需求可以自己增加功能
下面我们可以测试一下功能是否正常使用。
using UnityEngine; using System.Collections; using System.Data; using System.IO; public class Testsqlite : MonoBehavIoUr { private string dbPath; private DbAccessHelper dbAccessHelper; private string[] name = new string[3]; private int[] age= new int[3]; private float[] exp =new float[3]; private int i = 0; private int size = 0; void OnGUI() { if (GUILayout.Button("连接数据库")) { CreateDataBase(); } else if (GUILayout.Button("创建数据表")) { CreateTable(); } else if (GUILayout.Button("插入数据")) { InsertData(); } else if (GUILayout.Button("插入单条数据")) { InsertSigleData(); } else if (GUILayout.Button("根据列插入多条数据")) { InsertSpecificData(); } else if (GUILayout.Button("更新数据")) { UpdateData(); } else if (GUILayout.Button("删除数据")) { DeleteData(); } else if (GUILayout.Button("查找数据")) { FindData(); } GUILayout.Label("name: " + name[0] + " age: " + age[0] + " exp: " + exp[0]); GUILayout.Label("name: " + name[1] + " age: " + age[1] + " exp: " + exp[1]); GUILayout.Label(dbPath); GUILayout.Label(size.ToString()); } /// <summary> /// 创建数据库 /// </summary> void CreateDataBase() { #if UNITY_EDITOR dbPath = Application.dataPath + "/TestDb.db"; #elif UNITY_STANDALONE_WIN dbPath = Application.dataPath + "/TestDb.db"; #elif UNITY_ANDROID dbPath = Application.persistentDataPath + "/TestDb.db"; if (!File.Exists(dbPath)) { //拷贝数据库 StartCoroutine(CopyDataBase()); } #elif UNITY_IPHONE dbPath = Application.persistentDataPath + "/TestDb.db"; if (!File.Exists(dbPath)) { //拷贝数据库 StartCoroutine(CopyDataBase()); } #endif dbAccessHelper = new DbAccessHelper(dbPath); } IEnumerator CopyDataBase() { #if UNITY_ANDROID WWW www = new WWW("jar:file://" + Application.streamingAssetsPath + "/TestDb.db"); #elif UNITY_IPHONE WWW www = new WWW(Application.streamingAssetsPath + "/TestDb.db"); #elif UNITY_EDITOR || UNITY_STANDALONE_WIN WWW www = new WWW(Application.dataPath + "/TestDb.db"); #endif yield return www; size = www.size; File.WriteAllBytes(dbPath,www.bytes); } void CreateTable() { //建立数据库连接 CreateDataBase(); //创建数据表 dbAccessHelper.CreateTable("Role",new string[] {"id","name","age","lv","exp"},new string[] {"int","text","int","float"}); //关闭数据库 dbAccessHelper.CloseDatabase(); } void InsertData() { CreateDataBase(); dbAccessHelper.InsertInto("Role",new string[] {"2","'张三'","18","12","2.2"}); dbAccessHelper.CloseDatabase(); } void InsertSigleData() { CreateDataBase(); dbAccessHelper.InsertIntoSingle("Role","id","3"); dbAccessHelper.CloseDatabase(); } void InsertSpecificData() { CreateDataBase(); dbAccessHelper.InsertIntoSpecific("Role","name"},new string[] {"4","12"}); dbAccessHelper.CloseDatabase(); } void UpdateData() { CreateDataBase(); dbAccessHelper.UpdateInto("Role",new string[] {"lv",new string[] {"10","100"},"1"); dbAccessHelper.CloseDatabase(); } void DeleteData() { CreateDataBase(); dbAccessHelper.Delete("Role",new string[] { "id","id" },new string[] { "2","3" }); dbAccessHelper.CloseDatabase(); } void FindData() { CreateDataBase(); IDataReader reader = dbAccessHelper.SelectWhere("Role",new string[] {"name",new string[] {"=","="},new string[] {"1","'12'"}); while (reader.Read()) { name[i] = reader.GetString(reader.GetOrdinal("name")); age[i] = reader.GetInt32(reader.GetOrdinal("age")); exp[i] = reader.GetFloat(reader.GetOrdinal("exp")); i++; } dbAccessHelper.CloseDatabase(); } }
拷贝上述测试代码到unity中运行即可 原文链接：https://www.f2er.com/sqlite/198935.html
