Unity Android平台 SQLite操作

Unity Android平台 SQLite操作

一、SQLite需要DLL

（1）需要库

（2）库添加的位置

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

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

二、代码实现

（1）数据库操作管理

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）设置数据库信息

    //数据库信息
    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）数据库操作

//开启数据库连接
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();

 

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

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