首页 > 代码库 > 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 }
View Code


接下来我们来实例数据库并创建表结构。

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 }
View Code

将testDb.cs挂到摄像机上

运行结果如下:

现在我们去工程下打开testSqlite.db

哇咔咔,没问题了。最后附上项目工程文件:

http://pan.baidu.com/s/1pJpzXCR

 

Unity sqlite学习笔记一