数据库DBUtil类实现(C#_sqlite3/Java_mysql)(记录一)

JAVA实现Mysql.DBUtil

package server.mysql;import java.sql.*;public class DBUtil {//数据库连接private Connection dbConnect;//Query对象private Statement dbQuery;//Reader对象private ResultSet dbReader;public DBUtil(Connection connect) throws SQLException {this.dbConnect = connect;this.dbQuery = this.dbConnect.createStatement();}public void reset() throws SQLException {closeReader();closeQuery();this.dbConnect.setAutoCommit(true);this.dbQuery = this.dbConnect.createStatement();}public void close() {closeReader();closeQuery();//销毁Connectionif (dbConnect != null) {try {dbConnect.close();} catch (SQLException e) {System.out.println(e.getMessage());}}dbConnect = null;}public void closeQuery() {//销毁Queryif (dbQuery != null) {try {dbQuery.close();} catch (SQLException e) {System.out.println(e.getMessage());}}dbQuery = null;}public void closeReader() {//销毁Readerif (dbReader != null) {try {dbReader.close();} catch (SQLException e) {System.out.println(e.getMessage());}}dbReader = null;}//连接是否关闭public boolean isClosed() throws SQLException {return dbConnect == null || dbConnect.isClosed();}//创建一个新的Query对象public void createQuery() throws SQLException {closeReader();closeQuery();this.dbQuery = this.dbConnect.createStatement();}//设置自动事务public void setAutoCommit(boolean auto) throws SQLException {this.dbConnect.setAutoCommit(auto);}//提交事务public void commit() throws SQLException {dbConnect.commit();}//读取整张表全部数据public ResultSet readFullTable(String tableName) throws SQLException {String queryString = "SELECT * FROM " + tableName + " ;";return executeQuery(queryString);}//读取表全字段,从第start开始 读取rows条数据public ResultSet readFullTable(String tableName, int start, int rows) throws SQLException {String queryString = "SELECT * FROM " + tableName + " LIMIT " + start + "," + rows + " ;";return executeQuery(queryString);}//读取表全字段,且符合条件的数据纪录public ResultSet readFullTable(String tableName, String[] colNames, String[] operations, String[] colValues) throws SQLException {String queryString = "SELECT * FROM " + colNames[0] + " " + operations[0] + " " + colValues[0];for (int i = 1; i < colNames.length; i++) {queryString += " AND " + colNames[i] + " " + operations[i] + " " + colValues[0] + " ";}queryString += " ;";return executeQuery(queryString);}//读取表中符合条件的数据纪录的指定字段public ResultSet readTable(String tableName, String[] items, String[] colNames, String[] operations, String[] colValues) throws SQLException {String queryString = "SELECT " + items[0];for (int i = 1; i < items.length; i++) {queryString += ", " + items[i];}queryString += " FROM " + tableName + " WHERE " + colNames[0] + " " + operations[0] + " " + colValues[0];for (int i = 1; i < colNames.length; i++) {queryString += " AND " + colNames[i] + " " + operations[i] + " " + colValues[0] + " ";}queryString += " ;";return executeQuery(queryString);}//插入数据(返回插入次数)public int insertValues(String tableName, String[] values) throws SQLException {//获取数据表中字段数目int fieldCount = getFieldCount(tableName);//当插入的数据长度不等于字段数目时引发异常if (values.length != fieldCount) {throw new SQLException("values.Length!=fieldCount");}String queryString = "INSERT INTO " + tableName + " VALUES (" + values[0];for (int i = 1; i < values.length; i++) {queryString += ", " + values[i];}queryString += " ) ;";return executeUpdate(queryString);}//更新指定数据(返回更新次数<如没有与限定条件匹配的数据纪录,则返回0>)public int updateValues(String tableName, String[] colNames, String[] colValues, String key, String operation, String value) throws SQLException {//当字段名称和字段数值不对应时引发异常if (colNames.length != colValues.length) {throw new SQLException("colNames.Length!=colValues.Length");}String queryString = "UPDATE " + tableName + " SET " + colNames[0] + "=" + colValues[0];for (int i = 1; i < colValues.length; i++) {queryString += ", " + colNames[i] + "=" + colValues[i];}queryString += " WHERE " + key + operation + value + " ;";return executeUpdate(queryString);}//删除指定数据(返回删除多少条数据)public int deleteValuesOR(String tableName, String[] colNames, String[] operations, String[] colValues) throws SQLException {//当字段名称和字段数值不对应时引发异常if (colNames.length != colValues.length || operations.length != colNames.length || operations.length != colValues.length) {throw new SQLException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");}String queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + colValues[0];for (int i = 1; i < colValues.length; i++) {queryString += "OR " + colNames[i] + operations[0] + colValues[i];}queryString += " ;";return executeUpdate(queryString);}public int deleteValuesAND(String tableName, String[] colNames, String[] operations, String[] colValues) throws SQLException {//当字段名称和字段数值不对应时引发异常if (colNames.length != colValues.length || operations.length != colNames.length || operations.length != colValues.length) {throw new SQLException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");}String queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + colValues[0];for (int i = 1; i < colValues.length; i++) {queryString += " AND " + colNames[i] + operations[i] + colValues[i];}queryString += " ;";return executeUpdate(queryString);}//创建数据表public ResultSet createTable(String tableName, String[] colNames, String[] colTypes) throws SQLException {String queryString = "CREATE TABLE " + tableName + "( " + colNames[0] + " " + colTypes[0];for (int i = 1; i < colNames.length; i++) {queryString += ", " + colNames[i] + " " + colTypes[i];}queryString += "  ) ;";return executeQuery(queryString);}//获取table字段数量public int getFieldCount(String tableName) throws SQLException {Statement query = dbConnect.createStatement();ResultSet reader = query.executeQuery("SELECT * FROM " + tableName + " LIMIT 0,0 ;");int fieldCount = reader.getMetaData().getColumnCount();query.close();reader.close();return fieldCount;}//在dbCommand上添加数据public void addParameter(String key, byte[] value) throws SQLException {throw new SQLException("DBUtil Exception: Unrealized method");}//执行SQL查询命令public ResultSet executeQuery(String queryString) throws SQLException {//System.out.println(queryString);dbReader = dbQuery.executeQuery(queryString);return dbReader;}//执行SQL更改命令(返回执行次数)public int executeUpdate(String queryString) throws SQLException {//System.out.println(queryString);int ret = dbQuery.executeUpdate(queryString);return ret;}
}

C# 实现Sqlite3.DBUtil(关键词:Unity/XLua/Sqlite3)

using System;
using UnityEngine;
using Mono.Data.Sqlite;
using System.IO;public class SQLiteSamples
{private static bool isAndroid = Application.platform == RuntimePlatform.Android;//数据库连接private SqliteConnection dbConnection = null;//SQL命令定义private SqliteCommand dbCommand = null;//数据读取定义private SqliteDataReader dataReader = null;//实例化连接对象public SQLiteSamples(string connectionString){if (isAndroid){//在(Unity 2018.2.18f1版本,安卓也是"data source="并不是"URL=file:")//connectionString = "URL=file:" + connectionString;}connectionString = "data source=" + connectionString;//构造数据库连接dbConnection = new SqliteConnection(connectionString);//打开数据库dbConnection.Open();//建立Command连接dbCommand = dbConnection.CreateCommand();}public SQLiteSamples(string dataSource, string connectionString){connectionString = dataSource + connectionString;//构造数据库连接dbConnection = new SqliteConnection(connectionString);//打开数据库dbConnection.Open();//建立Command连接dbCommand = dbConnection.CreateCommand();}//销毁数据库连接public void Close(){//销毁Readerif (dataReader != null){dataReader.Close();}dataReader = null;//销毁Commandif (dbCommand != null){dbCommand.Cancel();}dbCommand = null;//销毁Connectionif (dbConnection != null){dbConnection.Close();}dbConnection = null;}//创建新的Command对象public void CreateCommand(){//销毁Readerif (dataReader != null){dataReader.Close();}dataReader = null;//销毁Commandif (dbCommand != null){dbCommand.Cancel();}dbCommand = dbConnection.CreateCommand();}//读取整张表全部数据public SqliteDataReader ReadFullTable(string tableName){string queryString = "SELECT * FROM " + tableName;return ExecuteQuery(queryString);}//读取表全字段,从第start开始 读取rows条数据public SqliteDataReader ReadFullTable(string tableName, int start, int rows){string queryString = "SELECT * FROM " + tableName + " LIMIT " + start + "," + rows;return ExecuteQuery(queryString);}//读取表中符合条件的数据纪录的指定字段public SqliteDataReader ReadTable(string tableName, string[] items, string[] colNames, string[] operations, string[] colValues){string queryString = "SELECT " + items[0];for (int i = 1; i < items.Length; i++){queryString += ", " + items[i];}queryString += " FROM " + tableName + " WHERE " + colNames[0] + " " + operations[0] + " " + colValues[0];for (int i = 1; i < colNames.Length; i++){queryString += " AND " + colNames[i] + " " + operations[i] + " " + colValues[0] + " ";}return ExecuteQuery(queryString);}//插入数据(返回插入次数)public int InsertValues(string tableName, string[] values){//获取数据表中字段数目int fieldCount = GetFieldCount(tableName);//当插入的数据长度不等于字段数目时引发异常if (values.Length != fieldCount){throw new SqliteException("values.Length!=fieldCount");}string queryString = "INSERT INTO " + tableName + " VALUES (" + values[0];for (int i = 1; i < values.Length; i++){queryString += ", " + values[i];}queryString += " )";return ExecuteUpdate(queryString);}//更新指定数据(返回更新次数<如没有与限定条件匹配的数据纪录,则返回0>)public int UpdateValues(string tableName, string[] colNames, string[] colValues, string key, string operation, string value){//当字段名称和字段数值不对应时引发异常if (colNames.Length != colValues.Length){throw new SqliteException("colNames.Length!=colValues.Length");}string queryString = "UPDATE " + tableName + " SET " + colNames[0] + "=" + colValues[0];for (int i = 1; i < colValues.Length; i++){queryString += ", " + colNames[i] + "=" + colValues[i];}queryString += " WHERE " + key + operation + value;return ExecuteUpdate(queryString);}//删除指定数据(返回删除多少条数据)public int DeleteValuesOR(string tableName, string[] colNames, string[] operations, string[] colValues){//当字段名称和字段数值不对应时引发异常if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length){throw new SqliteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");}string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + colValues[0];for (int i = 1; i < colValues.Length; i++){queryString += "OR " + colNames[i] + operations[0] + colValues[i];}return ExecuteUpdate(queryString);}public int DeleteValuesAND(string tableName, string[] colNames, string[] operations, string[] colValues){//当字段名称和字段数值不对应时引发异常if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length){throw new SqliteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");}string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + colValues[0];for (int i = 1; i < colValues.Length; i++){queryString += " AND " + colNames[i] + operations[i] + colValues[i];}return ExecuteUpdate(queryString);}//创建数据表public SqliteDataReader CreateTable(string tableName, string[] colNames, string[] colTypes){string queryString = "CREATE TABLE " + tableName + "( " + colNames[0] + " " + colTypes[0];for (int i = 1; i < colNames.Length; i++){queryString += ", " + colNames[i] + " " + colTypes[i];}queryString += "  ) ";return ExecuteQuery(queryString);}//获取fieldCountpublic int GetFieldCount(string tableName){var dbCommand = dbConnection.CreateCommand();dbCommand.CommandText = "SELECT * FROM " + tableName + " LIMIT 0,0";var reader = dbCommand.ExecuteReader();int fieldCount = reader.FieldCount;//ClosedbCommand.Cancel();reader.Close();return fieldCount;}//在dbCommand上添加数据public void AddParameter(string key, byte[] value){dbCommand.Parameters.Add(key, System.Data.DbType.Binary).Value = value;}//执行SQL查询命令public SqliteDataReader ExecuteQuery(string queryString){Debug.Log(queryString);dbCommand.CommandText = queryString;dataReader = dbCommand.ExecuteReader();return dataReader;}//执行SQL更改命令(返回执行次数)public int ExecuteUpdate(string queryString){Debug.Log(queryString);dbCommand.CommandText = queryString;var ret = dbCommand.ExecuteNonQuery();return ret;}//创建数据库文件public static bool Create(string fileFullName, bool deleteOld = false){//删除旧文件if (deleteOld && File.Exists(fileFullName)){File.Delete(fileFullName);}if (!File.Exists(fileFullName)){//创建文件夹var dir = Path.GetDirectoryName(fileFullName);if (!Directory.Exists(dir)){Directory.CreateDirectory(dir);}//创建数据库文件SqliteConnection.CreateFile(fileFullName);return true;}return false;}
}