JSON 파라메터 데이터를 불러와서 SQL를 여러번 실행하는 방법

JSON 파라미터는 반드시 배열 형식으로 입력되어야 하며, 각 객체는 키-값 쌍으로 구성

JSON 파라미터 파싱: check_param의 JSON 데이터를 ArrayList<Map<String, Object>> 형태로 변환합니다

import java.sql.*;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.w3c.dom.*;
import java.io.StringReader;
import javax.xml.parsers.ParserConfigurationException;
import org.xml.sax.InputSource;

public class SqlChecker {

    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USER = "your_username";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        String sqlTemplate = "SELECT CASE WHEN COUNT(*) > 0 THEN 'S' ELSE 'F' END AS resultFlag "
                           + "FROM activity WHERE COMPANY = ? AND ST_DT >= ? AND EN_DT <= ?";
        
        String xmlData = "<itf>"
                       + "<param>"
                       + "<company>samsung</company>"
                       + "<stDate>20250102</stDate>"
                       + "<edDate>20250307</edDate>"
                       + "</param>"
                       + "<param>"
                       + "<company>lg</company>"
                       + "<stDate>20250301</stDate>"
                       + "<edDate>20250307</edDate>"
                       + "</param>"
                       + "<param>"
                       + "<company>lg</company>"
                       + "<stDate>20250301</stDate>"
                       + "<edDate>20250307</edDate>"
                       + "</param>"
                       + "</itf>";

        try (Connection connection = DriverManager.getConnection(DB_URL, USER, PASSWORD)) {
            DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
            DocumentBuilder builder = factory.newDocumentBuilder();
            Document doc = builder.parse(new InputSource(new StringReader(xmlData)));
            NodeList paramList = doc.getElementsByTagName("param");

            for (int i = 0; i < paramList.getLength(); i++) {
                Element param = (Element) paramList.item(i);
                String company = param.getElementsByTagName("company").item(0).getTextContent();
                String stDate = param.getElementsByTagName("stDate").item(0).getTextContent();
                String edDate = param.getElementsByTagName("edDate").item(0).getTextContent();
                
                String resultFlag = executeSql(connection, sqlTemplate, company, stDate, edDate);
                updateCheckResult(connection, company, stDate, edDate, resultFlag);
            }
        } catch (SQLException | ParserConfigurationException | Exception e) {
            e.printStackTrace();
        }
    }

    private static String executeSql(Connection connection, String sqlTemplate, String company, String stDate, String edDate) {
        try (PreparedStatement stmt = connection.prepareStatement(sqlTemplate)) {
            stmt.setString(1, company);
            stmt.setString(2, stDate);
            stmt.setString(3, edDate);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getString("resultFlag");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "오류"; // 기본 오류 반환
    }

    private static void updateCheckResult(Connection connection, String company, String stDate, String edDate, String resultFlag) {
        String updateSql = "UPDATE activity SET check_result = ? WHERE COMPANY = ? AND ST_DT = ? AND EN_DT = ?";
        try (PreparedStatement updateStmt = connection.prepareStatement(updateSql)) {
            updateStmt.setString(1, resultFlag);
            updateStmt.setString(2, company);
            updateStmt.setString(3, stDate);
            updateStmt.setString(4, edDate);
            updateStmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

<dependency>
    <groupId>org.json</groupId>
    <artifactId>json</artifactId>
    <version>20210307</version>
</dependency>

 

import java.sql.*;
import java.util.*;
import org.json.JSONArray;
import org.json.JSONObject;

public class SqlChecker {

    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USER = "your_username";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(DB_URL, USER, PASSWORD)) {
            String selectSql = "SELECT id, check_sql, check_param FROM activity";
            try (PreparedStatement selectStmt = connection.prepareStatement(selectSql);
                 ResultSet resultSet = selectStmt.executeQuery()) {

                while (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String checkSql = resultSet.getString("check_sql");
                    String checkParam = resultSet.getString("check_param");

                    List<Map<String, Object>> params = parseJsonParams(checkParam);
                    String checkResult = executeSqlWithParams(connection, checkSql, params);
                    updateCheckResult(connection, id, checkResult);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static List<Map<String, Object>> parseJsonParams(String checkParam) {
        List<Map<String, Object>> paramList = new ArrayList<>();
        JSONArray jsonArray = new JSONArray(checkParam);
        for (int i = 0; i < jsonArray.length(); i++) {
            JSONObject jsonObject = jsonArray.getJSONObject(i);
            Map<String, Object> paramMap = new HashMap<>();
            for (String key : jsonObject.keySet()) {
                paramMap.put(key, jsonObject.get(key));
            }
            paramList.add(paramMap);
        }
        return paramList;
    }

    private static String executeSqlWithParams(Connection connection, String checkSql, List<Map<String, Object>> params) {
        StringBuilder results = new StringBuilder();
        for (Map<String, Object> paramMap : params) {
            try (PreparedStatement stmt = connection.prepareStatement(checkSql)) {
                int index = 1;
                for (String key : paramMap.keySet()) {
                    stmt.setObject(index++, paramMap.get(key));
                }
                ResultSet rs = stmt.executeQuery();
                if (rs.next()) {
                    results.append("성공: ").append(rs.getString(1)).append("; ");
                } else {
                    results.append("실패; ");
                }
            } catch (SQLException e) {
                results.append("오류: ").append(e.getMessage()).append("; ");
            }
        }
        return results.toString();
    }

    private static void updateCheckResult(Connection connection, int id, String checkResult) {
        String updateSql = "UPDATE activity SET check_result = ? WHERE id = ?";
        try (PreparedStatement updateStmt = connection.prepareStatement(updateSql)) {
            updateStmt.setString(1, checkResult);
            updateStmt.setInt(2, id);
            updateStmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}