Unity中数据库建立与读写

发表于2018-07-28
评论0 884浏览

想免费获取内部独家PPT资料库?观看行业大牛直播?点击加入腾讯游戏学院游戏程序行业精英群

711501594
想在unity开发中去简历数据库与读写数据库,需要用到两个类,DbAccess和TestDB,下面就分别给大家介绍下这两个类是怎么实现我们想要的功能的。

TestDB类测试,创建数据库,创建数据表,插入数据,更新数据,删除数据,查询数据
using UnityEngine;  
using System.Collections;  
using Mono.Data.Sqlite;  
using System.IO;  
public class TestDB : MonoBehaviour {  
    DbAccess db;    //数据库  
    string appDBPath;   //数据库路径  
    string name;  
    int age;  
    float exp;  
    //创建数据库  
    void CreateDataBase()  
    {  
#if UNITY_EDITOR  
        appDBPath = Application.dataPath + ”/Test.db”;  
#elif UNITY_STANDALONE_WIN  
        appDBPath = Application.dataPath + ”/Test.db”;  
#elif UNITY_ANDROID  
        appDBPath = Application.persistentDataPath + ”/Test.db”;  
        //判断路径内数据库是否存在  
        if(!File.Exists(appDBPath))=  
        {  
            //拷贝数据库  
            StartCoroutine(CopyDataBase());  
        }  
#elif UNITY_IPHONE  
        appDBPath = Application.persistentDataPath + ”/Test.db”;  
        //判断路径内数据库是否存在  
        if(!File.Exists(appDBPath))  
        {  
            //拷贝数据库  
            StartCoroutine(CopyDataBase());  
        }  
#endif  
        //”URI=file:” 必须添加  
        db = new DbAccess (“URI=file:” + appDBPath);  
    }  
    //拷贝数据库  
    IEnumerator CopyDataBase()  
    {  
        //用www先从unity中下载数据库  
        //Application.streamingAssetsPath  
#if UNITY_IPHONE  
        WWW loadDB = new WWW(Application.dataPath  
                             + ”/Raw” + “/Test.db”);  
#elif UNITY_ANDROID  
        WWW loadDB = new WWW(“jar:file://” +   
        Application.dataPath + ”!/assets” + “/Test.db”);  
#elif UNITY_EDITOR||UNITY_STANDALONE_WIN  
        WWW loadDB = new WWW(  
            Application.dataPath + ”/Test.db”);  
#endif  
        yield return loadDB;  
        //拷贝数据库  
        File.WriteAllBytes (appDBPath, loadDB.bytes);  
    }  
    void OnGUI()  
    {  
        if (GUILayout.Button (“创建数据库”))  
            CreateDataBase ();  
        if (GUILayout.Button (“创建数据表”))  
            CreateTable ();  
        if (GUILayout.Button (“插入数据”))  
            InsertData ();  
        if (GUILayout.Button (“更新数据”))  
            UpdateData ();  
        if (GUILayout.Button (“删除数据”))  
            DeleteData ();  
        if (GUILayout.Button (“查询数据”))  
            FindData ();  
        GUILayout.Label (”name:” + name);  
        GUILayout.Label (”age:” + age);  
        GUILayout.Label (”exp:” + exp);  
        GUILayout.Label (”path:” + appDBPath);  
    }  
    //创建表  
    void CreateTable()  
    {  
        //打开数据库  
        CreateDataBase ();  
        //创建数据库表  
        db.CreateTable (”role”,   
                        new string[]{“id”,“name”,  
                        ”age”,“lv”,“exp”},   
                        new string[]{“int”,“text”,  
                        ”int”,“int”,“float”});  
        //关闭数据库  
        db.CloseSqlConnection ();  
    }  
    //插入数据  
    void InsertData()  
    {  
        CreateDataBase ();  
        //插入数据  
        db.InsertInto (”role”, new string[]  
                       {”1”,“’张三’”,“18”,“1”,“1.2”});  
        db.CloseSqlConnection ();  
    }  
    //更新数据  
    void UpdateData()  
    {  
        CreateDataBase ();  
        db.UpdateInto (”role”,new string[]{“lv”,“exp”},  
        new string[]{“10”,“100”},“id”,“1”);  
        db.CloseSqlConnection ();  
    }  
    //删除数据  
    void DeleteData()  
    {  
        CreateDataBase ();  
        db.Delete (”role”,new string[]{“id”,“id”},  
                    new string[]{“1”,“2”});  
        db.CloseSqlConnection ();  
    }  
    //查找数据  
    void FindData()  
    {  
        CreateDataBase ();  
        SqliteDataReader sqReader =   
            db.SelectWhere (”role”,  
                            new string[]{“name”,“age”,“exp”},  
            new string[]{“id”},new string[]{“=”},  
            new string[]{“3”});   
        while(sqReader.Read())  
        {  
            name = sqReader.GetString  
                (sqReader.GetOrdinal(”name”));  
            age = sqReader.GetInt32  
                (sqReader.GetOrdinal(”age”));  
            exp = sqReader.GetFloat  
                (sqReader.GetOrdinal(”exp”));  
        }  
        Debug.Log (”name:” + name);  
        db.CloseSqlConnection ();  
    }  
}  

DbAccess,链接数据库
using UnityEngine;
using System;
using System.Collections;
using Mono.Data.Sqlite;
public class DbAccess
{
    private SqliteConnection dbConnection;
    private SqliteCommand dbCommand;
    private SqliteDataReader reader;
    public DbAccess (string connectionString)
    {
        OpenDB (connectionString);
    }
    public DbAccess ()
    {
    }
    public void OpenDB (string connectionString)
    {
        try
        {
            dbConnection = new SqliteConnection (connectionString);
            dbConnection.Open ();
            Debug.Log ("Connected to db,连接数据库成功!");
        }
        catch(Exception e)
        {
            string temp1 = e.ToString();
            Debug.Log(temp1);
        }
    }
    public void CloseSqlConnection ()
    {
        if (dbCommand != null) {
            dbCommand.Dispose ();
        }
        dbCommand = null;
        if (reader != null) {
            reader.Dispose ();
        }
        reader = null;
        if (dbConnection != null) {
            dbConnection.Close ();
        }
        dbConnection = null;
        Debug.Log ("Disconnected from db.关闭数据库!");
    }
    public SqliteDataReader ExecuteQuery (string sqlQuery)
    {
        dbCommand = dbConnection.CreateCommand ();
        dbCommand.CommandText = sqlQuery;
        reader = dbCommand.ExecuteReader ();
        return reader;
    }
    public SqliteDataReader ReadFullTable (string tableName)
    {
        string query = "SELECT * FROM " + tableName;
        return ExecuteQuery (query);
    }
    /// <summary>
    /// 插入数据 param tableName=表名 values=数据内容
    /// </summary>
    public SqliteDataReader InsertInto (string tableName, string[] values)
    {
        string query = "INSERT INTO " + tableName + " VALUES (" + values[0];
        for (int i = 1; i < values.Length; ++i) {
            query += ", " + values[i];
        }
        query += ")";
        return ExecuteQuery (query);
    }
    /// <summary>
    /// 插入数据 param tableName=表名 cols=更新字段 colsvalues=更新内容 selectkey=查找字段(主键) selectvalue=查找内容
    /// </summary>
    public SqliteDataReader UpdateInto (string tableName, string []cols,string []colsvalues,string selectkey,string selectvalue)
    {
        string query = "UPDATE "+tableName+" SET "+cols[0]+" = "+colsvalues[0];
        for (int i = 1; i < colsvalues.Length; ++i) {
            query += ", " +cols[i]+" ="+ colsvalues[i];
        }
        query += " WHERE "+selectkey+" = "+selectvalue+" ";
        return ExecuteQuery (query);
    }
    /// <summary>
    /// 删除数据 param tableName=表名 cols=字段 colsvalues=内容
    /// </summary>
    public SqliteDataReader Delete(string tableName,string []cols,string []colsvalues)
    {
        string query = "DELETE FROM "+tableName + " WHERE " +cols[0] +" = " + colsvalues[0];
        for (int i = 1; i < colsvalues.Length; ++i) {
            query += " or " +cols[i]+" = "+ colsvalues[i];
        }
        return ExecuteQuery (query);
    }
    public SqliteDataReader InsertIntoSpecific (string tableName, string[] cols, string[] values)
    {
        if (cols.Length != values.Length) {
            throw new SqliteException ("columns.Length != values.Length");
        }
        string query = "INSERT INTO " + tableName + "(" + cols[0];
        for (int i = 1; i < cols.Length; ++i) {
            query += ", " + cols[i];
        }
        query += ") VALUES (" + values[0];
        for (int i = 1; i < values.Length; ++i) {
            query += ", " + values[i];
        }
        query += ")";
        return ExecuteQuery (query);
    }
    public SqliteDataReader DeleteContents (string tableName)
    {
        string query = "DELETE FROM " + tableName;
        return ExecuteQuery (query);
    }
    /// <summary>
    /// 创建表 param name=表名 col=字段名 colType=字段类型
    /// </summary>
    public SqliteDataReader CreateTable (string name, string[] col, string[] colType)
    {
        if (col.Length != colType.Length) {
            throw new SqliteException ("columns.Length != colType.Length");
        }
        string query = "CREATE TABLE " + name + " (" + col[0] + " " + colType[0];
        for (int i = 1; i < col.Length; ++i) {
            query += ", " + col[i] + " " + colType[i];
        }
        query += ")";
        return ExecuteQuery (query);
    }
    /// <summary>
    /// 插入数据 param tableName=表名 items=结果字段 col=查找字段 operation=运算符 values=内容
    /// </summary>
    public SqliteDataReader SelectWhere (string tableName, string[] items, string[] col, string[] operation, string[] values)
    {
        if (col.Length != operation.Length || operation.Length != values.Length) {
            throw new SqliteException ("col.Length != operation.Length != values.Length");
        }
        string query = "SELECT " + items[0];
        for (int i = 1; i < items.Length; ++i) {
            query += ", " + items[i];
        }
        query += " FROM " + tableName + " WHERE " + col[0] + operation[0] + "'" + values[0] + "' ";
        for (int i = 1; i < col.Length; ++i) {
            query += " AND " + col[i] + operation[i] + "'" + values[0] + "' ";
        }
        return ExecuteQuery (query);
    }
}

原文链接

著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

如社区发表内容存在侵权行为,您可以点击这里查看侵权投诉指引

游戏学院公众号二维码
腾讯游戏学院
微信公众号

提供更专业的游戏知识学习平台