|
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;
}
}
|
转播
