DBService.java

package com.workbenchclassic;

import java.sql.*;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import org.json.JSONArray;
import org.json.JSONObject;

/**
 * DBService handles database connections, queries, and results.
 */
public class DBService {

    /** Logger for this class. */
    private static final Logger LOGGER = Logger.getLogger(DBService.class.getName());
    private String dataSourceName; // Make DATASOURCE_NAME an instance variable

    /**
     * Constructor to set the DataSource name.
     *
     * @param dataSourceName The name of the DataSource.
     */
    public DBService(String dataSourceName) {
        this.dataSourceName = dataSourceName;
    }

    /**
     * Connects to the database and executes a query.
     *
     * @param query The SQL statement.
     * @return JSON response with results or error.
     */
    public String dbConnect(String query) {
        try (Connection connection = getConnection();
                Statement statement = connection.createStatement()) {

            return isSelectQuery(query) ? executeSelect(statement, query) : executeUpdate(statement, query);

        } catch (SQLException e) {
            return handleSQLException("SQL error", e);
        }
    }

    /**
     * Fetches rows from the database as a list of maps.
     *
     * @param query The SQL SELECT query.
     * @return List of rows with column names as keys.
     * @throws SQLException if a database error occurs.
     */
    public List<Map<String, Object>> dbConnectAndGetRows(String query) throws SQLException {
        List<Map<String, Object>> results = new ArrayList<>();

        try (Connection connection = getConnection();
                Statement statement = connection.createStatement();
                ResultSet rs = statement.executeQuery(query)) {

            processResultSet(rs, results);

        } catch (SQLException sqle) {
            LOGGER.log(Level.SEVERE, "SQL error in dbConnectAndGetRows", sqle);
            throw sqle;
        }
        return results;
    }

    public List<Map<String, Object>> preparedDbConnectAndGetRows(PreparedStatement ps) throws SQLException {
        List<Map<String, Object>> results = new ArrayList<>();

        try (ResultSet rs = ps.executeQuery()) {
            processResultSet(rs, results);
        } catch (SQLException sqle) {
            LOGGER.log(Level.SEVERE, "SQL error in dbConnectAndGetRows(PreparedStatement)", sqle);
            throw sqle;
        }
        return results;
    }

    /**
     * Executes a SELECT query and formats the result as JSON.
     */
    private String executeSelect(Statement statement, String query) throws SQLException {
        JSONArray rows = new JSONArray();
        try (ResultSet rs = statement.executeQuery(query)) {
            processResultSet(rs, rows);
        }
        return getJsonResponse("0", "Success", rows.toString());
    }

    /**
     * Executes an UPDATE/INSERT/DELETE query.
     */
    private String executeUpdate(Statement statement, String query) throws SQLException {
        int resultCount = statement.executeUpdate(query);
        JSONArray updateResult = new JSONArray();
        updateResult.put("Result");
        updateResult.put(resultCount);
        return getJsonResponse("0", "Done", updateResult.toString());
    }

    /**
     * Processes the ResultSet and stores rows into a JSON array.
     */
    private void processResultSet(ResultSet rs, JSONArray rows) throws SQLException {
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        while (rs.next()) {
            JSONObject row = new JSONObject();
            for (int i = 1; i <= columnCount; i++) {
                row.put(metaData.getColumnLabel(i).toLowerCase(), rs.getObject(i));
            }
            rows.put(row);
        }
    }

    /**
     * Processes the ResultSet and stores rows into a List of Maps.
     */
    private void processResultSet(ResultSet rs, List<Map<String, Object>> results) throws SQLException {
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        while (rs.next()) {
            Map<String, Object> rowMap = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                rowMap.put(metaData.getColumnLabel(i).toLowerCase(), rs.getObject(i));
            }
            results.add(rowMap);
        }
    }

    /**
     * Determines if the query is a SELECT statement.
     */
    private boolean isSelectQuery(String query) {
        return query != null && query.trim().toLowerCase().startsWith("select");
    }

    /**
     * Handles SQL exceptions and logs errors.
     */
    private String handleSQLException(String message, SQLException e) {
        LOGGER.log(Level.SEVERE, message + ": " + e.getMessage(), e);
        return getJsonResponse("-999", message, e.getMessage());
    }

    /**
     * Retrieves database product name.
     */
    public String getDatabaseProductName() {
        try (Connection connection = getConnection()) {
            return connection.getMetaData().getDatabaseProductName();
        } catch (SQLException e) {
            return handleSQLException("Database error", e);
        }
    }

    /**
     * Retrieves a DataSource via JNDI lookup.
     */
    private DataSource lookupDataSource(String dataSourceName) throws NamingException {
        InitialContext ctx = new InitialContext();
        return (DataSource) ctx.lookup(dataSourceName); // Corrected JNDI lookup
    }

    /**
     * Retrieves a database connection.
     */
    public Connection getConnection() throws SQLException {
        try {
            return lookupDataSource(dataSourceName).getConnection();
        } catch (NamingException ne) {
            throw new SQLException("JNDI NamingException occurred while getting connection", ne);
        }
    }

    /**
     * Builds a JSON response.
     */
    private String getJsonResponse(String errorCode, String errorMessage, String data) {
        JSONObject obj = new JSONObject();
        obj.put("errorCode", errorCode);
        obj.put("errorMessage", errorMessage);
        obj.put("data", data);
        return obj.toString();
    }
}