【SQLite】Unity和SQLite数据库的应用
发表于2017-08-28
主要的三大类 sqliteConnection sqliteCommand sqliteReader
主要的三个方法 ExecuteNoQuery ExecuteReader ExexuteScalar
语法特点：SQL语句关键字不区分大小写，规范上用大写。
select * from 表名称
select 列名称，列名称 from 表名称
例子 select name,sex,age from person(表名称) where sex = “女” and age >= 16 and age <= 20
创建SQLite数据库 主要步骤
- 给出存储地址
- 创建连接
- 打开连接
- 创建命令
- 将SQL命令赋值
- 执行SQL命令
- 读取数据
代码如下：
using UnityEngine; using System.Collections; using Mono.Data.Sqlite; public class SQL_IDUS : MonoBehaviour ｛ SqliteConnection sqliteConection; SqliteCommand sqliteCommand; void Awake () ｛ //给出存储地址 string sqliteConnection = "Data Source = " + Application.dataPath + "/mylove.sqlite"; //创建连接 sqliteConection = new SqliteConnection (sqliteConnection); //创建命令 sqliteCommand = sqliteConection.CreateCommand (); //连接对象 sqliteConection.Open (); //命令赋值 sqliteCommand.CommandText = "select * from SQL_Pra"; object o = sqliteCommand.ExecuteScalar (); sqliteConection.Close (); ｝ ｝
using UnityEngine; using System.Collections; using Mono.Data.Sqlite; /// <summary> /// </summary> public class SqliteReasderDemo : MonoBehaviour ｛ SqliteConnection connection; SqliteCommand command; SqliteDataReader reader; void Awake () ｛ string path = "Data Source =" + <span style="font-family: Arial, Helvetica, sans-serif;">Application.dataPath + "/mylove.sqlite";</span> connection = new SqliteConnection (path); connection.Open (); command = connection.CreateCommand (); command.CommandText = "select * from BigUnity"; reader = command.ExecuteReader (); while (reader.Read ()) ｛ //reader.FieldCount ： 第一行有多少列 for (int i = 0; i < reader.FieldCount; i++) ｛ //GetName 字段名字 （表头） ID Age Sex 之类的 Debug.Log (reader.GetName (i)); //获取的值 Debug.Log (reader.GetValue (i)); ｝ ｝ connection.Clone (); ｝ ｝
CommandTimeout :超时时间
ExecuteNonQuery 返回一个受影响的列数（增删改查）
ExexuteReader 返回一个存所有数据的类()
ExecuteScalar 只会返回第一个结果 （同理 多个数据也是一样）
sqliteConnection.对象.Clone 关闭并释放内存
sqliteConnection.对象.Close 关闭
代码如下：
using UnityEngine;
using System.Collections;
using Mono.Data.Sqlite;
using System.IO;
/// <summary>
/// 脚本功能：创建数据库
/// 添加对象：
///
/// </summary>
/// 因为是单例类 没有集成所以不会执行Awake
public class CreateData
｛
//数据库三大类
public SqliteConnection connection;
public SqliteCommand command;
public SqliteDataReader reader;
string[] bbb;
private static CreateData instance;
private CreateData ()
｛
bbb = new string[]｛ ｝;
// //通过路径找到第三发过数据库
// string addDBPath = Application.dataPath + "/Plugins/Android/assets/" + "NBAGame.sqlite";
string addDBPath = "Data Source =" + Application.dataPath + "/Plugins/NBAGame.sqlite";
// connection = new SqliteConnection ("URI=file:" + addDBPath);
//window测试
connection = new SqliteConnection (addDBPath);
command = connection.CreateCommand ();
connection.Open ();
｝
public static CreateData Instance ｛
get ｛
if (instance == null) ｛
instance = new CreateData ();
｝
return instance;
｝
｝
void Awake ()
｛
bbb = new string[]｛ ｝;
// //通过路径找到第三发过数据库
// string addDBPath = Application.dataPath + "/Plugins/Android/assets/" + "NBAGame.sqlite";
string addDBPath = "Data Source =" + Application.dataPath + "/Plugins/NBAGame.sqlite";
// connection = new SqliteConnection ("URI=file:" + addDBPath);
//window测试
connection = new SqliteConnection (addDBPath);
command = connection.CreateCommand ();
// //讲第三方数据库拷贝到Android可找到的地方
// string addDBPath = Application.persistentDataPath + "/" + "NBAGame.sqlite";
//如果已知路径没有地方放数据库，那么我们从unity中拷贝
// if (!File.Exists (addDBPath)) ｛
//
// WWW loadDb = new WWW ("jar:file://" + Application.dataPath + "!/assets/" + "NBAGame.sqlite");
//
//
//
// // connection = new SqliteConnection ("URI=file:" + addDBPath);
//
// //拷贝到指定的地方
// File.WriteAllBytes (addDBPath, loadDb.bytes);
//
// ｝
connection.Open ();
//在游戏界面读取 现在只是留个口
// Debug.Log (command);
// //测试
// command.CommandText = "SELECT LongScore FROM Blue where Block = '82'";
// object o = command.ExecuteScalar ();
// while (reader.Read ()) ｛
// for (int i = 0; i < reader.FieldCount; i++) ｛
// bbb [i] = reader.GetValue (i).ToString ();
// Debug.Log (bbb [i]);
// ｝
//
// ｝
Debug.Log (o.ToString ());
｝