数据库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();

        //销毁Connection
        if (dbConnect != null) {
            try {
                dbConnect.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
        dbConnect = null;
    }

    public void closeQuery() {
        //销毁Query
        if (dbQuery != null) {
            try {
                dbQuery.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
        dbQuery = null;
    }

    public void closeReader() {
        //销毁Reader
        if (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()
    {
        //销毁Reader
        if (dataReader != null)
        {
            dataReader.Close();
        }
        dataReader = null;

        //销毁Command
        if (dbCommand != null)
        {
            dbCommand.Cancel();
        }
        dbCommand = null;

        //销毁Connection
        if (dbConnection != null)
        {
            dbConnection.Close();
        }
        dbConnection = null;
    }
    //创建新的Command对象
    public void CreateCommand()
    {
        //销毁Reader
        if (dataReader != null)
        {
            dataReader.Close();
        }
        dataReader = null;
        //销毁Command
        if (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);
    }


    //获取fieldCount
    public int GetFieldCount(string tableName)
    {
        var dbCommand = dbConnection.CreateCommand();
        dbCommand.CommandText = "SELECT * FROM " + tableName + " LIMIT 0,0";
        var reader = dbCommand.ExecuteReader();
        int fieldCount = reader.FieldCount;
        //Close
        dbCommand.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;
    }
}