Unity Android平台 SQLite操作

一、SQLite需要DLL

（1）需要库

（2）库添加的位置

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

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

（3）发布

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

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

二、代码实现

（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();