代码改变世界

Unity sqlite学习笔记一

2014-10-20 13:31  wuzhang  阅读(1003)  评论(0)  编辑  收藏  举报

1.SQLITE的常识

SQLite是一个开源免费的数据库，一般用于嵌入系统或者小规模的应用软件开发中，你可以像使用Access一样使用它。
sqlite的主要优点：零配置(Zero Configuration)紧凑（compactness）可移植(Portability)它是运行在Windows,Linux,BSD,Mac OS X和一些商用Unix系统，比如Sun的Solaris,IBM的AIX，同样，它也可以工作在许多嵌入式操作系统下，比如QNX,VxWorks,Palm OS, 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         else
23         {
24             //创建数据库名称为testSqlite.db
25             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 = new string[]{"name"};  //
45         string []newValue = 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