GetMetadata.java

package com.workbenchclassic;

import org.json.JSONArray;
import org.json.JSONObject;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * Example refactoring: return a JSON string instead of JAX-RS Response.
 */
public class GetMetadata {

    private static final DBService DB_SERVICE = new DBService("dsMesMiiNJTA");
    private static final Logger LOGGER = Logger.getLogger(GetMetadata.class.getName());

    public String getExtPlantJson(String station) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt1 = null;
        ResultSet rs1 = null;
        PreparedStatement pstmt2 = null;
        ResultSet rs2 = null;

        try {
            conn = DB_SERVICE.getConnection();

            // 1) Query #1
            String werkQuery = "select bw.ext_werk_nr , bw.werk_nr, b.ext_company_nr,  b.client_id from bde.werk bw join BDE.buchkreis b on bw.company_id = b.company_id JOIN bde.kast k ON bw.werk_id = k.werk_id where k.KAP_TYP = 'T' and "
                    +
                    " k.kap_nr = ?";
            pstmt1 = conn.prepareStatement(werkQuery);
            pstmt1.setString(1, station);
            rs1 = pstmt1.executeQuery();

            JSONArray dataArray1 = new JSONArray();
            String werkNr = null;
            String ext_company_nr = null;
            String clientID = null;

            if (rs1.next()) {
                ext_company_nr = rs1.getString("ext_company_nr");
                werkNr = rs1.getString("ext_werk_nr");
                clientID = rs1.getString("client_id");
                JSONObject row = new JSONObject();
                row.put("ext_company_nr", ext_company_nr);
                row.put("ext_werk_nr", werkNr);
                row.put("client_id", clientID);
                dataArray1.put(row);
            }

            if (dataArray1.length() == 0) {
                // No data for that station
                JSONObject noDataResponse = new JSONObject();
                noDataResponse.put("errorCode", "1");
                noDataResponse.put("errorMessage", "No werk found for station: " + station);
                noDataResponse.put("data", "[]");
                return noDataResponse.toString(); // Return the JSON string
            }

            // Assume the FIRST row is enough
            JSONObject firstRow = dataArray1.getJSONObject(0);
            werkNr = firstRow.optString("ext_werk_nr", null);
            ext_company_nr = firstRow.optString("ext_company_nr", null);

            if (werkNr == null) {
                JSONObject missingWerkNr = new JSONObject();
                missingWerkNr.put("errorCode", "1");
                missingWerkNr.put("errorMessage", "werk_nr not found for station: " + station);
                missingWerkNr.put("data", "[]");
                return missingWerkNr.toString();
            }

            // 2) Query #2 for clientNr
            String clientNrQuery = "SELECT CLIENT_NR AS werknummer " +
                    "FROM bde.mandant m " +
                    "WHERE m.client_id = ?";
            pstmt2 = conn.prepareStatement(clientNrQuery);
            pstmt2.setString(1, clientID);
            rs2 = pstmt2.executeQuery();

            String clientNr = null;
            if (rs2.next()) {
                clientNr = rs2.getString("werknummer");
            }

            // 3) Combine results into final JSON
            JSONObject combinedRow = new JSONObject();
            combinedRow.put("ext_company_nr", ext_company_nr);
            combinedRow.put("werk_nr", werkNr);
            combinedRow.put("client_nr", clientNr != null ? clientNr : JSONObject.NULL);

            JSONArray finalDataArray = new JSONArray();
            finalDataArray.put(combinedRow);

            JSONObject finalResult = new JSONObject();
            finalResult.put("errorCode", "0");
            finalResult.put("errorMessage", "Done");
            finalResult.put("data", finalDataArray.toString());

            return finalResult.toString();

        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "SQL error in getExtPlant", e);
            JSONObject errorJson = new JSONObject();
            errorJson.put("errorCode", "99");
            errorJson.put("errorMessage", "Database Error: " + e.getMessage());
            errorJson.put("data", "[]");
            return errorJson.toString();
        } catch (Exception e) {
            LOGGER.log(Level.SEVERE, "General error in getExtPlant", e);
            JSONObject errorJson = new JSONObject();
            errorJson.put("errorCode", "99");
            errorJson.put("errorMessage", "General Error: " + e.getMessage());
            errorJson.put("data", "[]");
            return errorJson.toString();
        } finally {
            // close resources
            if (rs1 != null)
                try {
                    rs1.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if (pstmt1 != null)
                try {
                    pstmt1.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if (rs2 != null)
                try {
                    rs2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if (pstmt2 != null)
                try {
                    pstmt2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
    }
}