首页 > 代码库 > Unity sqlite学习笔记一
Unity sqlite学习笔记一
1.SQLITE的常识
SQLite是一个开源免费的数据库,一般用于嵌入系统或者小规模的应用软件开发中,你可以像使用Access一样使用它。
sqlite的主要优点:零配置(Zero Configuration)紧凑(compactness)可移植(Portability)它是运行在Windows,Linux,BSD,Mac OSX和一些商用Unix系统,比如Sun的Solaris,IBM的AIX,同样,它也可以工作在许多嵌入式操作系统下,比如QNX,VxWorks,PalmOS, Symbin和Windows CE,尤其对于移动端来说,Sqlite确实是个好东西,废话少说,实战演习。
2.建立Unity sqlite工程 文件结构
将对数据库的操作封装成DbAccess类,代码如下:
DbAccess.cs
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System; 6 using System.Collections; 7 using Mono.Data.Sqlite; 8 9 namespace Assets.Asserts.Scripts 10 { 11 public class DbAccess 12 { 13 14 private SqliteConnection dbConnection; 15 private SqliteCommand dbCommand; 16 private SqliteDataReader reader; 17 18 public DbAccess (string connectionString) 19 { 20 OpenDB (connectionString); 21 } 22 public DbAccess () 23 { 24 25 } 26 27 /// <summary> 28 /// 数据库的打开 29 /// </summary> 30 /// <param name="connectionString"></param> 31 public void OpenDB (string connectionString) 32 { 33 try 34 { 35 dbConnection = new SqliteConnection (connectionString); 36 dbConnection.Open (); 37 } 38 catch(Exception e) 39 { 40 string temp1 = e.ToString(); 41 Console.WriteLine(temp1); 42 } 43 } 44 45 /// <summary> 46 /// 数据库的连接 47 /// </summary> 48 public void CloseSqlConnection () 49 { 50 if (dbCommand != null) 51 { 52 dbCommand.Dispose (); 53 } 54 dbCommand = null; 55 if (reader != null) 56 { 57 reader.Dispose (); 58 } 59 reader = null; 60 if (dbConnection != null) 61 { 62 dbConnection.Close (); 63 } 64 dbConnection = null; 65 } 66 67 /// <summary> 68 /// 语句的执行 69 /// </summary> 70 /// <param name="sqlQuery"></param> 71 /// <returns></returns> 72 public SqliteDataReader ExecuteQuery (string sqlQuery) 73 { 74 dbCommand = dbConnection.CreateCommand (); 75 dbCommand.CommandText = sqlQuery; 76 reader = dbCommand.ExecuteReader (); 77 return reader; 78 79 } 80 81 /// <summary> 82 /// 数据的读取 83 /// </summary> 84 /// <param name="tableName"></param> 85 /// <returns></returns> 86 public SqliteDataReader ReadFullTable (string tableName) 87 { 88 string query = "SELECT * FROM " + tableName; 89 return ExecuteQuery (query); 90 } 91 92 /// <summary> 93 /// 数据的插入 94 /// </summary> 95 /// <param name="tableName"></param> 96 /// <param name="values"></param> 97 /// <returns></returns> 98 public SqliteDataReader InsertInto (string tableName, string[] values) 99 {100 string query = "INSERT INTO " + tableName + " VALUES (‘" + values[0]+"‘";101 for (int i = 1; i < values.Length; ++i) 102 {103 query += ",‘" + values[i]+"‘";104 }105 query += ")";106 return ExecuteQuery (query);107 }108 109 /// <summary>110 /// 数据库的更新111 /// </summary>112 /// <param name="tableName"></param>113 /// <param name="cols"></param>114 /// <param name="colsvalues"></param>115 /// <param name="selectkey"></param>116 /// <param name="selectvalue"></param>117 /// <returns></returns>118 public SqliteDataReader UpdateInto (string tableName, string []cols,string []colsvalues,string selectkey,string selectvalue)119 {120 string query = "UPDATE "+tableName+" SET "+cols[0]+" = "+colsvalues[0];121 for (int i = 1; i < colsvalues.Length; ++i) 122 {123 query += ", " +cols[i]+" ="+ colsvalues[i];124 }125 query += " WHERE "+selectkey+" = "+selectvalue+" ";126 return ExecuteQuery (query);127 }128 129 /// <summary>130 /// 数据的多删除131 /// </summary>132 /// <param name="tableName"></param>133 /// <param name="cols"></param>134 /// <param name="colsvalues"></param>135 /// <returns></returns>136 public SqliteDataReader Delete(string tableName,string []cols,string []colsvalues)137 {138 string query = "DELETE FROM "+tableName + " WHERE " +cols[0] +" = " + colsvalues[0];139 for (int i = 1; i < colsvalues.Length; ++i) 140 {141 query += " or " +cols[i]+" = "+ colsvalues[i];142 }143 return ExecuteQuery (query);144 }145 146 /// <summary>147 /// 数据的单删除148 /// </summary>149 /// <param name="tableName"></param>150 /// <param name="cols"></param>151 /// <param name="colsvalues"></param>152 /// <returns></returns>153 public SqliteDataReader SimpleDelete(string tableName, string cols, string colsvalues)154 {155 string query = "DELETE FROM " + tableName + " WHERE " + cols + " = " + colsvalues;156 return ExecuteQuery(query);157 }158 159 /// <summary>160 /// 条件插入161 /// </summary>162 /// <param name="tableName"></param>163 /// <param name="cols"></param>164 /// <param name="values"></param>165 /// <returns></returns>166 public SqliteDataReader InsertIntoSpecific (string tableName, string[] cols, string[] values)167 {168 if (cols.Length != values.Length)169 {170 throw new SqliteException ("columns.Length != values.Length");171 }172 string query = "INSERT INTO " + tableName + "(" + cols[0];173 for (int i = 1; i < cols.Length; ++i) 174 {175 query += ", " + cols[i];176 }177 query += ") VALUES (" + values[0];178 for (int i = 1; i < values.Length; ++i) 179 {180 query += ", " + values[i];181 }182 query += ")";183 return ExecuteQuery (query);184 }185 186 /// <summary>187 /// 表的删除188 /// </summary>189 /// <param name="tableName"></param>190 /// <returns></returns>191 public SqliteDataReader DeleteContents (string tableName)192 {193 string query = "DELETE FROM " + tableName;194 return ExecuteQuery (query);195 }196 197 /// <summary>198 /// 表的创建199 /// </summary>200 /// <param name="name"></param>201 /// <param name="col"></param>202 /// <param name="colType"></param>203 /// <returns></returns>204 public SqliteDataReader CreateTable (string name, string[] col, string[] colType)205 {206 if (col.Length != colType.Length) 207 {208 throw new SqliteException ("columns.Length != colType.Length");209 }210 string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0];211 for (int i = 1; i < col.Length; ++i)212 {213 query += ", " + col[i] + " " + colType[i];214 }215 query += ")";216 return ExecuteQuery (query);217 }218 219 /// <summary>220 /// 条件查询221 /// </summary>222 /// <param name="tableName"></param>223 /// <param name="items"></param>224 /// <param name="col"></param>225 /// <param name="operation"></param>226 /// <param name="values"></param>227 /// <returns></returns>228 public SqliteDataReader SelectWhere (string tableName, string[] items, string[] col, string[] operation, string[] values)229 {230 if (col.Length != operation.Length || operation.Length != values.Length) 231 {232 throw new SqliteException ("col.Length != operation.Length != values.Length");233 }234 string query = "SELECT " + items[0];235 for (int i = 1; i < items.Length; ++i) 236 {237 query += ", " + items[i];238 }239 query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "‘" + values[0] + "‘ ";240 for (int i = 1; i < col.Length; ++i) 241 {242 query += " AND " + col[i] + operation[i] + "‘" + values[0] + "‘ ";243 }244 return ExecuteQuery (query);245 }246 }247 }
接下来我们来实例数据库并创建表结构。
TestDb.cs
1 using UnityEngine; 2 using System.Collections; 3 using Assets.Asserts.Scripts; 4 using System.IO; 5 using Mono.Data.Sqlite; 6 7 public class TestDb : MonoBehaviour 8 { 9 SqliteDataReader reader;10 void Start ()11 { 12 string path = Application.dataPath + "/testSqlite.db";13 string []info = new string[]{"wuzhang","930116897","930116897@qq.com","www.blogs.wuzhang.com"};14 string []info1 = new string[]{ "wanggang", "123456", "123456@qq.com", "www.blogs.wanggang.com" };15 DbAccess db = null;16 if (File.Exists(path))17 {18 Debug.Log("Momo Exists!");19 db = new DbAccess(@"data source=" + path);20 21 }22 else23 {24 //创建数据库名称为testSqlite.db25 db = new DbAccess(@"data source="+path);26 //创建数据库表,与字段27 db.CreateTable("user", new string[] { "name", "qq", "email", "blog" }, new string[] { "text", "text", "text", "text" });28 }29 30 /***************31 * 数据的插入32 ***************/33 db.InsertInto("user", info);34 db.InsertInto("user", info1);35 36 /***************37 * 数据的读取38 * *************/39 readtable(db, db.ReadFullTable("user"));40 41 /******************42 * 数据库的更新43 * ***************/44 string []setValue = http://www.mamicode.com/new string[]{"name"}; //45 string []newValue = http://www.mamicode.com/new string[] { "‘jim‘" };46 // db.UpdateInto("user",setValue,newValue,"name","‘wanggang‘");47 48 /**************49 *数据的简单删除 50 *************/51 //db.SimpleDelete("user","name","‘wanggang‘");52 53 //关闭对象54 db.CloseSqlConnection();55 }56 57 /// <summary>58 /// 表内容的读取显示59 /// 参数一:数据库名称60 /// 参数二:接收数据库读取的表61 /// </summary>62 /// <param name="db"></param>63 /// <param name="reader "></param>64 void readtable(DbAccess db, SqliteDataReader reader)65 {66 //将表内容逐个字段读取67 while (reader.Read())68 {69 string name = reader["name"].ToString();70 string qq = reader["qq"].ToString();71 string email = reader["email"].ToString();72 string blog = reader["blog"].ToString();73 Debug.Log(name + "," + qq + "," + email + "," + blog);74 }75 }76 }
将testDb.cs挂到摄像机上
运行结果如下:
现在我们去工程下打开testSqlite.db
哇咔咔,没问题了。最后附上项目工程文件:
http://pan.baidu.com/s/1pJpzXCR
Unity sqlite学习笔记一
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。