Unity3D--SQLite

一，SQLite简介

SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它是D.RichardHipp建立的公有领域项目。它的设计目标是嵌入式的而且目前已经在很多嵌入式产品中使用了它，它占用资源非常低。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言向结合，如Tcl，C#，PHP，Java等，还有ODBC接口。

二，功能特性

1，ACID事务
2，零配置 – 无需安装和管理配置
3，储存在单一磁盘文件中的一个完整的数据库
4，数据库文件可以在不同字节顺序的机器间自由的共享
5，支持数据库大小至2TB
6，足够小, 大致13万行C代码, 4.43M
7，比一些流行的数据库在大部分普通数据库操作要快
8，简单, 轻松的API
9，包含TCL绑定, 同时通过Wrapper支持其他语言的绑定
10，良好注释的源代码, 并且有着90%以上的测试覆盖率
11，独立: 没有额外依赖
12，源码完全的开源, 你可以用于任何用途, 包括出售它
13， 支持多种开发语言，C, C++, PHP, Perl, Java, C#,Python, Ruby等

三，在Unity中使用SQLite

准备工作：导入mono.data.sqlite.dll，sqlite3.dll，System.Data.dll到文件夹，代码添加库：using Mono.Data.Sqlite；，using System.Data；

在写U3D 脚本之前，可以先编写一个SqliteHelper.cs的辅助类。

using System;
using UnityEngine;
using Mono.Data.Sqlite;
using System.Data;
using System.Collections.Generic;
using System.Collections;


namespace AssemblyCSharp
{
    /// <summary>
    /// 接口
    /// </summary>
    public interface ISQLite
    {
        Dictionary<string,object> GetDict ();

        void SetData (Dictionary<string,object> dict);
    }

    /// <summary>
    /// SqliteHelPer类
    /// </summary>
    public class SqliteHelPer
    {

        private SqliteHelPer()
        {
            try{
                string dataPath = "Data Source = "+ Application.dataPath +"/database.sqlite";
                con = new SqliteConnection(dataPath);
                con.Open();
            }catch(SqliteException e) {
                Debug.Log (e);
            }
        }

        private static SqliteHelPer instance;//单例

        /// <summary>
        /// 获取单例
        /// </summary>
        /// <value>The instance.</value>
        public static SqliteHelPer Instance
        {
            get
            {
                if(instance==null)
                    instance = new SqliteHelPer();
                return instance;
            }
        }

        SqliteConnection con;//数据库拦截对象
        SqliteCommand command;//数据库指令对象
        SqliteDataReader reader;//数据库读取对象

        /// <summary>
        /// Threesthestep方法
        /// </summary>
        /// <param name="strSql">String sql.</param>
        public void ThreeStep(string strSql)
        {
            command = con.CreateCommand ();
            command.CommandText = strSql;
            command.ExecuteNonQuery ();
        }

        /// <summary>
        /// 关闭连接，释放资源
        /// </summary>
        public void CloseConnection()
        {
            if (command != null) {
                command.Dispose ();
            }
            if(con != null){
                con.Close ();
            }
        }

        /// <summary>
        /// 在数据库中创建表
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="column">可变长表字段组</param>
        public void CreateTable(string tableName,params string[] column)
        {
            string columnSql = "";
            for (int i = 0; i < column.Length; i++) {
                columnSql += column[i]+" text";
                if (i != column.Length - 1) 
                    columnSql+=",";
                }
                Debug.Log (columnSql);
                string strSql = string.Format ("create table if not exists {0}({1})", tableName, columnSql);
                Debug.Log (strSql);

            ThreeStep (strSql);
        }


        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="table">要插入数据的表名</param>
        /// <param name="dict">泛型字典Key为string类型代表字段名称，value为Object类型代表字段对应的值</param>
        public void InsertData(string table,Dictionary<string,object>dict)
        {
            //拼接字段和值列表
            string strKey="",strValue="";
            foreach (string key in dict.Keys) {
                strKey += key+",";
                strValue += "'" + dict [key].ToString () + "'" + ",";
            }

            //删除末尾逗号
            strKey = strKey.Remove(strKey.Length-1);
            strValue = strValue.Remove (strValue.Length - 1);


            //生成SQL指令
            string strSql = string.Format("insert into {0}({1})values({2})",table,strKey,strValue);

            Debug.Log (strSql);

            ThreeStep (strSql);

        }

        /// <summary>
        /// 读取表中所有信息
        /// </summary>
        /// <returns>读取器</returns>
        /// <param name="tableName">表名</param>
        public SqliteDataReader SelectAll(string tableName)
        {
            string strSql = "select * from " + tableName;
            command = con.CreateCommand ();
            command.CommandText = strSql;

            reader = command.ExecuteReader ();
            return reader;
        }

        public Dictionary<string,object> ReadOneObject()
        {
            if (reader.Read ()) {
                Dictionary<string,object> dict = new Dictionary<string, object> ();
                for (int i = 0; i < reader.FieldCount; i++) {
                    dict.Add (reader.GetName (i), reader.GetValue (i));
                }
                return dict;
            }
            return null;
        }

        /// <summary>
        /// 读取部分信息
        /// </summary>
        /// <returns>The part colum.</returns>
        /// <param name="tableName">Table name.</param>
        /// <param name="columns">Columns.</param>
        public SqliteDataReader SelectPartColum(string tableName,params string[] columns)
        {
            string strColumn = "";
            for (int i = 0; i < columns.Length; i++) {
                strColumn += columns [i]+",";
            }
            strColumn = strColumn.Remove (strColumn.Length - 1);
            string strSql = string.Format ("select {0} from {1}", strColumn, tableName);

            command = con.CreateCommand ();
            command.CommandText = strSql;
            reader = command.ExecuteReader ();

            return reader;
        }


        /// <summary>
        /// 获取满足条件记录中的指定字段值
        /// </summary>
        /// <returns>读取器。</returns>
        /// <param name="tableName">表名.</param>
        /// <param name="condition">条件.</param>
        /// <param name="columns">可变长字段列表.</param>
        public SqliteDataReader SelectPartColumWithCondition(string tableName,string condition, params string[] columns)
        {
            string strColumn = "";
            for (int i = 0; i < columns.Length; i++) {
                strColumn += columns [i]+",";
            }
            strColumn = strColumn.Remove (strColumn.Length - 1);

            string strSql = "";
            //判断是否设置查询条件
            if (condition != null || condition.Length > 0) {
                strSql = string.Format ("select {0} from {1} where {2}", strColumn, tableName, condition);
            } else {
                strSql = string.Format ("select {0} from {1}", strColumn, tableName);
            }
            command = con.CreateCommand ();
            command.CommandText = strSql;
            reader = command.ExecuteReader ();

            return reader;
        }


        /// <summary>
        /// 读取一行数据
        /// </summary>
        /// <returns>The data.</returns>
        /// <param name="reader">Reader.</param>
        /// <param name="oneColumn">If set to <c>true</c> one column.</param>
        public string[] ReadData(SqliteDataReader reader,bool oneColumn)
        {
            string[] str = null;
            while (reader.Read ()) {
                str = new string[reader.FieldCount];
                for (int i = 0; i < reader.FieldCount; i++) {
                    str [i] = reader.GetValue (i).ToString ();
                }
                break;
            }
            reader.Close ();
            return str;
        }

        public string[] RaedData(SqliteDataReader reader,bool oneColumn)
        {
            List<string> dataList = new List<string> ();
            int i = 0;
            while (reader.Read ()) {
                dataList.Add (reader.GetValue (0).ToString ());
                i++;
            }

            string[] str = new string[i];

            for (int j = 0; j < i; j++) {
                str [j] = dataList [j];
            }
            reader.Close ();
            return str;
        }

        public void Close()
        {
            if (reader != null) {
                reader.Close ();
                reader = null;
            }

            if (command != null) {
                command.Dispose ();
                command = null;
            }
            if (con != null) {
                con.Close ();
                con = null;
            }
            instance = null;
        }

        /// <summary>
        /// 移除旧的信息
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="condition">条件</param>
        public void Remove(string tableName, string condition)
        {
            string strSql = "";
            if (condition != null && condition.Length > 0) {
                strSql = "delete from " + tableName + " where " + condition;
            } else {
                strSql = "delete from " + tableName;
            }

            ThreeStep (strSql);

        }

    }
}

上面代码中不止添加了增，删，查，改，的方法，还有其他一些使用性更高的方法，通过对SQL语句的理解，将关键字拼接一个实用的方法就出来了。