Unity Android平台 SQLite操作

一、SQLite需要DLL

（1）需要库

（2）库添加的位置

在SQLite中创建数据库，xxx.db

将创建好的数据库文件加到 StreamingAssets 文件夹中

 

（3）发布

注意：当发布时在 OtherSettings——Api Compatibility Level* 将.NET 2.0 Subset（子集） 选择 .NET 2.0

防止发布时报错或缺少DLL集合

二、代码实现

（1）数据库操作管理

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264

public class SQLiteHelper : MonoBehaviour { private string connection; private IDbConnection dbcon; private IDbCommand dbcmd; private IDataReader reader; private StringBuilder builder;   public void OpenDB(string p) { string filepath = Application.persistentDataPath + "/" + p; if (!File.Exists(filepath)) { WWW loadDB = new WWW("jar:file://" + Application.dataPath + "!/assets/" + p); while (!loadDB.isDone) { } // then save to Application.persistentDataPath File.WriteAllBytes(filepath, loadDB.bytes); }   //open db connection connection = "URI=file:" + filepath; Debug.Log("Stablishing connection to: " + connection); dbcon = new SqliteConnection(connection); dbcon.Open(); }   public void CloseDB() { if(reader != null) reader.Close(); // clean everything up   reader = null; dbcmd.Dispose(); dbcmd = null; dbcon.Close(); dbcon = null; }   public IDataReader BasicQuery(string query) { // run a basic Sqlite query dbcmd = dbcon.CreateCommand(); // create empty command dbcmd.CommandText = query; // fill the command reader = dbcmd.ExecuteReader(); // execute command which returns a reader return reader; // return the reader }   /// <summary> /// 创建表 /// </summary> public bool CreateTable(string name, string[] col, string[] colType) { // Create a table, name, column array, column type array string query; query = "CREATE TABLE " + name + "(" + col[0] + " " + colType[0]; for (var i = 1; i < col.Length; i++) { query += ", " + col[i] + " " + colType[i]; } query += ")";   try { dbcmd = dbcon.CreateCommand(); // create empty command dbcmd.CommandText = query; // fill the command reader = dbcmd.ExecuteReader(); // execute command which returns a reader } catch (Exception e) { Debug.Log(e); return false; } return true; }   /// <summary> /// 增 插入某一单行值 /// </summary> public int InsertIntoSingle(string tableName, string colName, string value) { // single insert string query; query = "INSERT INTO " + tableName + "(" + colName + ") " + "VALUES ('" + value + "')"; Debug.Log(query);   try { dbcmd = dbcon.CreateCommand(); // create empty command dbcmd.CommandText = query; // fill the command reader = dbcmd.ExecuteReader(); // execute command which returns a reader } catch (Exception e) { Debug.LogError(e); return 0; } return 1; }   /// <summary> /// 增 指定列插入多个值 /// </summary> public int InsertIntoSpecific(string tableName, string[] col, string[] values) { // Specific insert with col and values string query; query = "INSERT INTO " + tableName + " (" + col[0]; for (int i = 1; i < col.Length; i++) { query += ", " + col[i]; } query += ") VALUES ('" + values[0]; for (int i = 1; i < col.Length; i++) { query += "', '" + values[i]; } query += "')"; Debug.Log(query);   try { dbcmd = dbcon.CreateCommand(); dbcmd.CommandText = query; reader = dbcmd.ExecuteReader(); } catch (Exception e) { Debug.Log(e); return 0; } return 1; }   /// <summary> /// 增 未指定列插入多个值 /// </summary> public int InsertInto(string tableName, string[] values) { // basic Insert with just values string query; query = "INSERT INTO " + tableName + " VALUES ('" + values[0]; for (int i = 1; i < values.Length; i++) { query += "', '" + values[i]; } query += "')"; try { dbcmd = dbcon.CreateCommand(); dbcmd.CommandText = query; reader = dbcmd.ExecuteReader(); } catch (Exception e) { Debug.Log(e); return 0; } return 1; }   /// <summary> /// 查询 sql /// </summary> public ArrayList Select(string sql) { Debug.Log("---" + sql); dbcmd = dbcon.CreateCommand(); dbcmd.CommandText = sql; reader = dbcmd.ExecuteReader();   string[] row; ArrayList readArray = new ArrayList(); while (reader.Read()) { row = new string[reader.FieldCount]; int j = 0; while (j < reader.FieldCount) { row[j] = reader.GetValue(j).ToString(); j++; } readArray.Add(row); } return readArray; }   /// <summary> /// 单行查询 列名 操作符号 值 /// </summary> public ArrayList SingleSelectWhere(string tableName, string itemToSelect, string wCol, string wPar, string wValue) { // Selects a single Item string query; query = "SELECT " + itemToSelect + " FROM " + tableName + " WHERE " + wCol + wPar + wValue; dbcmd = dbcon.CreateCommand(); dbcmd.CommandText = query; reader = dbcmd.ExecuteReader(); string[] row = new string[reader.FieldCount]; ArrayList readArray = new ArrayList(); while (reader.Read()) { int j = 0; while (j < reader.FieldCount) { row[j] = reader.GetString(j); j++; } readArray.Add(row); } return readArray; }   /// <summary> /// 改 指定列更新值 /// </summary> public int UpdateTableTargetCol(string tableName, string[] updateCols, string[] values, int id = 0) { string query = ""; query = "UPDATE " + tableName + " SET "; query += updateCols[0] + " = '" + values[0]; for (int i = 1; i < updateCols.Length; i++) { query += "' , " + updateCols[i] + " = '" + values[i]; } query += " '"; if (id > 0) query += " WHERE Id = " + id;   Debug.Log(query); try { dbcmd = dbcon.CreateCommand(); dbcmd.CommandText = query; reader = dbcmd.ExecuteReader(); } catch (Exception e) { Debug.Log(e); return 0; } return 1; }   /// <summary> /// 删 删除指定列及值 /// </summary> public bool DeleteTableTargetCol(string tableName, string colName, string targetValue) { string query = ""; query = "DELETE FROM " + tableName + " WHERE " + colName + " = " + targetValue; //Debug.Log(query); try { dbcmd = dbcon.CreateCommand(); dbcmd.CommandText = query; reader = dbcmd.ExecuteReader(); } catch (Exception e) { Debug.Log(e); return false; } return true; } }

　　

（2）设置数据库信息

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

//数据库信息 public class DBInfo { public const string DBName = "xxx.db";//数据库名称   public const string Table_xxx = "xxx";   public class xxxTable { public const string Col_Id = "id";//ID   public const string Col_xxx = "xxx"; } }

　　

（3）数据库操作

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

//开启数据库连接 sqHelper.OpenDB(DBInfo.DBName);   bool result = sqHelper.CreateTable("表名", new string[] { "id", "列名" }, new string[] { "INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL", "CHAR(50)" }); if (result) Debug.Log("创建表成功"); else Debug.Log("表已经存在");   //向表中增加一条数据，id自增 sqHelper.InsertIntoSpecific(表名, new string[] { 列名 }, new string[] { 数据值 });   //查找表中数据 ArrayList result = sqHelper.Select("SELECT * FROM " + 表名); if (result != null && result.Count > 0) { for (int i = 0; i < result.Count; i++) { string[] row = (string[])result[i];//一行中所有数据信息 for (int j = 0; j < row.Length; j++) { 一行数据中各个列的数据 Debug.Log("---" + row[j]); } } }   //删除指定数据 sqHelper.DeleteTableTargetCol(表名, "id", "2");   //关闭数据库连接 sqHelper.CloseDB();