/*** encoding: utf-8* 版權所有 2025 ?涂聚文有限公司 ?* 許可信息查看:言語成了邀功盡責的功臣,還需要行爲每日來值班嗎* 描述: https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html ojdbc11* Author : geovindu,Geovin Du 涂聚文.* IDE : IntelliJ IDEA 2024.3.6 Java 17* # database : Oracle21c,MySQL 9.0,SQL Server 2019,PostgreSQL 17.1 Neo4j* # OS : window10* Datetime : 2025 - 2025/7/13 - 14:25* User : geovindu* Product : IntelliJ IDEA* Project : oracledemo* File : DuOracleHelper.java* explain : 學習 類**/package Geovin.UtilitieDB;import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.sql.Driver.*;
import java.util.HashMap;
import java.util.Map;
import java.sql.Connection;
import java.sql.DriverManager;/***geovindu,Geovin Du*/
public class DuOracleHelper {/*** 命令類型枚舉*/public enum CommandType {TEXT, STORE_PROCEDURE}/*** 參數方向枚舉*/public enum ParameterDirection {IN, OUT}/*** 數據庫參數類*/public static class Parameter {public String name; // 注意:這個字段在簡單查詢中可能不需要,但保留用于存儲過程public Object value;public ParameterDirection direction;public int type;/*** 新增三參數構造函數(用于簡單查詢)* @param value* @param direction* @param type*/public Parameter(Object value, ParameterDirection direction, int type) {this.name = null; // 簡單查詢不需要參數名this.value = value;this.direction = direction;this.type = type;}/*** 保留原有的四參數構造函數(用于存儲過程)* @param name* @param value* @param direction* @param type*/public Parameter(String name, Object value, ParameterDirection direction, int type) {this.name = name;this.value = value;this.direction = direction;this.type = type;}}/*** 數據列類*/public static class DataColumn {public String columnName;public Object value;public DataColumn(String columnName, Object value) {this.columnName = columnName;this.value = value;}@Overridepublic String toString() {return "\"" + columnName + "\": " + (value == null ? "null" : "\"" + value.toString() + "\"");}}/*** 數據行類*/public static class DataRow {private List<DataColumn> columns = new ArrayList<>();public void addColumn(DataColumn column) {columns.add(column);}public DataColumn getColumn(String columnName) {for (DataColumn column : columns) {if (column.columnName.equalsIgnoreCase(columnName)) {return column;}}return null;}public List<DataColumn> getColumns() {return columns;}@Overridepublic String toString() {StringBuilder sb = new StringBuilder("{");for (int i = 0; i < columns.size(); i++) {sb.append(columns.get(i).toString());if (i < columns.size() - 1) {sb.append(", ");}}sb.append("}");return sb.toString();}}/*** 數據表類*/public static class DataTable {private List<DataRow> rows = new ArrayList<>();public void addRow(DataRow row) {rows.add(row);}public List<DataRow> getRows() {return rows;}@Overridepublic String toString() {StringBuilder sb = new StringBuilder("[");for (int i = 0; i < rows.size(); i++) {sb.append(rows.get(i).toString());if (i < rows.size() - 1) {sb.append(",\n");}}sb.append("]");return sb.toString();}}/*** 數據集類*/public static class DataSet {private List<DataTable> tables = new ArrayList<>();public void addTable(DataTable table) {tables.add(table);}public List<DataTable> getTables() {return tables;}@Overridepublic String toString() {StringBuilder sb = new StringBuilder("{");for (int i = 0; i < tables.size(); i++) {sb.append("\"table").append(i).append("\": ").append(tables.get(i).toString());if (i < tables.size() - 1) {sb.append(",\n");}}sb.append("}");return sb.toString();}}/*** 從JSON配置文件讀取數據庫連接信息* @param configFilePath 配置文件路徑* @return 包含數據庫連接信息的Map* @throws IOException 如果讀取文件或解析JSON出錯*/public static Map<String, String> getConnectionInfo(String configFilePath) throws IOException {StringBuilder content = new StringBuilder();try (BufferedReader reader = new BufferedReader(new FileReader(configFilePath))) {String line;while ((line = reader.readLine()) != null) {content.append(line);}}// 解析JSON配置Map<String, String> config = new HashMap<>();// 簡單解析,忽略JSON格式的復雜性String[] pairs = content.toString().replaceAll("[{}\"]", "").split(",");for (String pair : pairs) {String[] keyValue = pair.split(":");if (keyValue.length == 2) {config.put(keyValue[0].trim(), keyValue[1].trim());}}// 驗證必要的配置項if (!config.containsKey("host") ||!config.containsKey("port") ||!config.containsKey("serviceName") ||!config.containsKey("user") ||!config.containsKey("password")) {throw new IOException("配置文件缺少必要的連接參數");}return config;}/*** 創建Oracle數據庫連接* @param configFilePath 配置文件路徑* @return Connection對象* @throws SQLException 如果連接數據庫失敗* @throws IOException 如果讀取配置文件失敗*/public static Connection getConnection(String configFilePath) throws SQLException, IOException {Map<String, String> config = getConnectionInfo(configFilePath);try {// 加載Oracle驅動Class.forName("oracle.jdbc.OracleDriver");} catch (ClassNotFoundException e) {throw new SQLException("找不到Oracle JDBC驅動", e);}// 構建Oracle連接字符串String host = config.get("host");String port = config.get("port");String serviceName = config.get("serviceName");String user = config.get("user");String password = config.get("password");if (host == null || port == null || serviceName == null || user == null || password == null) {throw new IOException("配置文件缺少必要的連接參數");}// 構建連接URL jdbc:oracle:thin:@//localhost:1521/TechnologyGameString url = String.format("jdbc:oracle:thin:@//%s:%s/%s",config.get("host"),config.get("port"),config.get("serviceName"));// 創建連接return DriverManager.getConnection(url, config.get("user"), config.get("password"));}/*** 執行查詢并返回DataSet* @param configFilePath* @param commandType* @param commandText* @return* @throws Exception*/public static DataSet executeDataSet(String configFilePath, CommandType commandType, String commandText) throws Exception {return executeDataSet(configFilePath, commandType, commandText, new Parameter[0]);}/*** 執行查詢并返回DataSet,帶參數* @param configFilePath* @param commandType* @param commandText* @param parameters* @return* @throws Exception*/public static DataSet executeDataSet(String configFilePath, CommandType commandType, String commandText, Parameter... parameters) throws Exception {try (Connection conn = getConnection(configFilePath)) {return executeDataSet(conn, commandType, commandText, parameters);}}/*** 執行查詢并返回DataSet* @param connection* @param commandType* @param commandText* @return* @throws Exception*/public static DataSet executeDataSet(Connection connection, CommandType commandType, String commandText) throws Exception {return executeDataSet(connection, commandType, commandText, new Parameter[0]);}/*** * 執行查詢并返回DataSet,帶參數* @param connection* @param commandType* @param commandText* @param parameters* @return* @throws Exception*/public static DataSet executeDataSet(Connection connection, CommandType commandType, String commandText, Parameter... parameters) throws Exception {DataSet ds = new DataSet();System.out.println("開始執行查詢: " + commandText);if (commandType == CommandType.TEXT) {try (PreparedStatement pstmt = connection.prepareStatement(commandText)) {// 設置參數for (int i = 0; i < parameters.length; i++) {Parameter p = parameters[i];if (p.direction == ParameterDirection.IN) {System.out.println("設置輸入參數 " + (i + 1) + ": " + p.name + " = " + p.value);pstmt.setObject(i + 1, p.value, p.type);}}// 執行查詢try (ResultSet rs = pstmt.executeQuery()) {System.out.println("執行SQL查詢成功,開始處理結果集");DataTable dt = convertResultSetToDataTable(rs);System.out.println("結果集轉換為DataTable,包含 " + dt.getRows().size() + " 行");ds.addTable(dt);}}} else if (commandType == CommandType.STORE_PROCEDURE) {StringBuilder paramPlaceholders = new StringBuilder();for (int i = 0; i < parameters.length; i++) {paramPlaceholders.append("?, ");}if (paramPlaceholders.length() > 0) {paramPlaceholders = new StringBuilder(" ( " + paramPlaceholders.substring(0, paramPlaceholders.length() - 2) + " ) ");}String sql = "{ call " + commandText + paramPlaceholders + " }";System.out.println("準備調用存儲過程: " + sql);try (CallableStatement proc = connection.prepareCall(sql)) {// 設置參數for (int i = 0; i < parameters.length; i++) {Parameter p = parameters[i];System.out.println("設置參數 " + (i + 1) + ": " + p.name + " (方向: " + p.direction + ", 類型: " + p.type + ")");if (p.direction == ParameterDirection.IN) {proc.setObject(i + 1, p.value, p.type);} else if (p.direction == ParameterDirection.OUT) {proc.registerOutParameter(i + 1, p.type);}}// 執行存儲過程System.out.println("開始執行存儲過程...");boolean hasResultSet = proc.execute();System.out.println("存儲過程執行完成,是否有結果集: " + hasResultSet);// 處理所有結果集int resultSetCount = 0;while (hasResultSet) {try (ResultSet rs = proc.getResultSet()) {System.out.println("處理結果集 #" + (resultSetCount + 1));if (rs != null) {DataTable dt = convertResultSetToDataTable(rs);System.out.println("結果集 #" + (resultSetCount + 1) + " 包含 " + dt.getRows().size() + " 行");ds.addTable(dt);resultSetCount++;} else {System.out.println("結果集 #" + (resultSetCount + 1) + " 為空");}}hasResultSet = proc.getMoreResults();System.out.println("是否還有更多結果集: " + hasResultSet);}// 處理輸出參數System.out.println("開始處理輸出參數...");for (int i = 0; i < parameters.length; i++) {Parameter p = parameters[i];if (p.direction == ParameterDirection.OUT) {p.value = proc.getObject(i + 1);System.out.println("輸出參數 " + (i + 1) + " (" + p.name + ") 的值: " + p.value);// 特殊處理 Oracle 游標if (p.type == oracle.jdbc.OracleTypes.CURSOR) {System.out.println("檢測到游標參數,嘗試獲取結果集...");// 使用 Oracle 特定的 API 獲取游標oracle.jdbc.OracleCallableStatement oracleProc = (oracle.jdbc.OracleCallableStatement) proc;ResultSet rs = oracleProc.getCursor(i + 1);if (rs != null) {System.out.println("成功獲取游標結果集,開始轉換...");DataTable dt = convertResultSetToDataTable(rs);System.out.println("游標結果集包含 " + dt.getRows().size() + " 行");ds.addTable(dt);// 關閉結果集rs.close();} else {System.out.println("游標結果集為空");}}}}System.out.println("處理了 " + resultSetCount + " 個結果集和 " + parameters.length + " 個輸出參數");}} else {throw new IllegalArgumentException("不支持的命令類型: " + commandType);}System.out.println("DataSet 包含 " + ds.getTables().size() + " 個表,總共 " +ds.getTables().stream().mapToInt(t -> t.getRows().size()).sum() + " 行數據");// 打印詳細數據結構(調試用)if (ds.getTables().size() > 0) {System.out.println("第一個表的結構: " + ds.getTables().get(0));}return ds;}/*** 執行非查詢命令* @param configFilePath* @param commandType* @param commandText* @throws Exception*/public static void executeNonQuery(String configFilePath, CommandType commandType, String commandText) throws Exception {executeNonQuery(configFilePath, commandType, commandText, new Parameter[0]);}/*** 執行非查詢命令,帶參數* @param configFilePath* @param commandType* @param commandText* @param parameters* @throws Exception*/public static void executeNonQuery(String configFilePath, CommandType commandType, String commandText, Parameter... parameters) throws Exception {try (Connection conn = getConnection(configFilePath)) {executeNonQuery(conn, commandType, commandText, parameters);}}/*** 執行非查詢命令* @param connection* @param commandType* @param commandText* @throws Exception*/public static void executeNonQuery(Connection connection, CommandType commandType, String commandText) throws Exception {executeNonQuery(connection, commandType, commandText, new Parameter[0]);}/*** 執行非查詢命令,帶參數* @param connection* @param commandType* @param commandText* @param parameters* @throws Exception*/public static void executeNonQuery(Connection connection, CommandType commandType, String commandText, Parameter... parameters) throws Exception {if (commandType == CommandType.TEXT) {try (PreparedStatement pstmt = connection.prepareStatement(commandText)) {// 設置參數for (int i = 0; i < parameters.length; i++) {Parameter p = parameters[i];if (p.direction == ParameterDirection.IN) {pstmt.setObject(i + 1, p.value, p.type);}}// 執行更新pstmt.executeUpdate();}} else if (commandType == CommandType.STORE_PROCEDURE) {StringBuilder paramPlaceholders = new StringBuilder();for (int i = 0; i < parameters.length; i++) {paramPlaceholders.append("?, ");}if (paramPlaceholders.length() > 0) {paramPlaceholders = new StringBuilder(" ( " + paramPlaceholders.substring(0, paramPlaceholders.length() - 2) + " ) ");}String sql = "{ call " + commandText + paramPlaceholders + " }";try (CallableStatement proc = connection.prepareCall(sql)) {// 設置參數for (int i = 0; i < parameters.length; i++) {Parameter p = parameters[i];if (p.direction == ParameterDirection.IN) {proc.setObject(i + 1, p.value, p.type);} else if (p.direction == ParameterDirection.OUT) {proc.registerOutParameter(i + 1, p.type);}}// 執行存儲過程proc.execute();// 處理輸出參數for (int i = 0; i < parameters.length; i++) {Parameter p = parameters[i];if (p.direction == ParameterDirection.OUT) {p.value = proc.getObject(i + 1);}}}} else {throw new IllegalArgumentException("不支持的命令類型: " + commandType);}}/*** 將ResultSet轉換為DataTable* @param rs* @return* @throws Exception*/private static DataTable convertResultSetToDataTable(ResultSet rs) throws Exception {ResultSetMetaData rsmd = rs.getMetaData();int columnCount = rsmd.getColumnCount();DataTable dt = new DataTable();while (rs.next()) {DataRow dr = new DataRow();for (int i = 1; i <= columnCount; i++) {DataColumn dc = new DataColumn(rsmd.getColumnName(i), rs.getObject(i));dr.addColumn(dc);}dt.addRow(dr);}return dt;}
}
/**** @param schoolId* @return DataSet*/@Overridepublic DuOracleHelper.DataSet getSchoolByDataSet(String schoolId) {String sql = "SELECT * FROM School WHERE SchoolId = ?";try {// 示例1:執行SQL查詢DuOracleHelper.DataSet resultSet = DuOracleHelper.executeDataSet("oraclecon.json",DuOracleHelper.CommandType.TEXT,sql,new DuOracleHelper.Parameter(schoolId, DuOracleHelper.ParameterDirection.IN, Types.VARCHAR));return resultSet;}catch (Exception e) {e.printStackTrace();return null;}}
調用:
DuOracleHelper.DataSet resultSet =schoolBLL.getSchoolByDataSet("U0040");System.out.println(resultSet.toString());
項目結構: