java Mssql to Oracle #009

mssql 조회 oracle 저장

• oracle insert 문은 merge 문으로 사용하였습니다.
• 아래 #005에 작성된 내용에서 Runnable task 부분만 변경합니다.

 

2025.04.29 - [dev/java 배포] - java properties 조회 #005

java properties 조회 #005

소스 코드

Runnable task = () -> {
    Connection sqlServerConn = null;
    PreparedStatement sqlServerStmt = null;
    ResultSet rs = null;
    Connection oracleConn = null;
    PreparedStatement oracleStmt = null;

    PreparedStatement oracleStmtCommon = null;
    PreparedStatement oracleStmtText = null;

    try {
        // MS SQL Server 연결
        String sqlServerUrl = dbProps.getProperty("sqlserver.url");
        String sqlServerUser = dbProps.getProperty("sqlserver.username");
        String sqlServerPassword = dbProps.getProperty("sqlserver.password");
        //String sqlServerDriver = dbProps.getProperty("sqlserver.driver");
        //Class.forName(sqlServerDriver); // 드라이버 클래스 로드
        sqlServerConn = DriverManager.getConnection(sqlServerUrl, sqlServerUser, sqlServerPassword);

        // 데이터 조회 쿼리 실행
        String selectQuery = "SELECT CODE_GUBUN, COM_CODE ,COM_CODE_NM ,ORD ,DEF_VAL ,USE_YN ,MOD_YN ,DSC ,REG_ID FROM common";
        sqlServerStmt = sqlServerConn.prepareStatement(selectQuery);
        rs = sqlServerStmt.executeQuery();

        // Oracle 연결
        String oracleUrl = dbProps.getProperty("oracle.url");
        String oracleUser = dbProps.getProperty("oracle.username");
        String oraclePassword = dbProps.getProperty("oracle.password");
        //String oracleDriver = dbProps.getProperty("oracle.driver");
        //Class.forName(oracleDriver); // 드라이버 클래스 로드
        oracleConn = DriverManager.getConnection(oracleUrl, oracleUser, oraclePassword);
        oracleConn.setAutoCommit(false);

        // 조회 결과 처리 및 Oracle에 삽입
        String insertQuery = "MERGE INTO common T\n" +
                "USING (\n" +
                "    SELECT\n" +
                "        ? AS code_gubun,\n" +
                "        ? AS com_code,\n" +
                "        ? AS com_code_nm,\n" +
                "        ? AS ord,\n" +
                "        ? AS def_val,\n" +
                "        ? AS use_yn,\n" +
                "        ? AS mod_yn,\n" +
                "        ? AS dsc,\n" +
                "        ? AS reg_id\n" +
                "    FROM dual\n" +
                ") S\n" +
                "ON (T.code_gubun = S.code_gubun AND T.com_code = S.com_code)\n" +
                "WHEN MATCHED THEN\n" +
                "    UPDATE SET\n" +
                "        T.com_code_nm = S.com_code_nm,\n" +
                "        T.ord = S.ord,\n" +
                "        T.def_val = S.def_val,\n" +
                "        T.use_yn = S.use_yn,\n" +
                "        T.mod_yn = S.mod_yn,\n" +
                "        T.dsc = S.dsc,\n" +
                "        T.reg_id = S.reg_id,\n" +
                "        T.reg_dd = sysdate\n" +
                "WHEN NOT MATCHED THEN\n" +
                "    INSERT (code_gubun, com_code, com_code_nm, ord, def_val, use_yn, mod_yn, dsc, reg_id, reg_dd)\n" +
                "    VALUES (S.code_gubun, S.com_code, S.com_code_nm, S.ord, S.def_val, S.use_yn, S.mod_yn, S.dsc, S.reg_id, sysdate)";

        System.out.println("쿼리 : " + insertQuery);

        oracleStmt = oracleConn.prepareStatement(insertQuery);

        while (rs.next()) {
            oracleStmt.setString(1, rs.getString("CODE_GUBUN"));
            oracleStmt.setString(2, rs.getString("COM_CODE"));
            oracleStmt.setString(3, rs.getString("COM_CODE_NM"));
            oracleStmt.setInt(4, rs.getInt("ORD"));
            oracleStmt.setString(5, rs.getString("DEF_VAL"));
            oracleStmt.setString(6, rs.getString("USE_YN"));
            oracleStmt.setString(7, rs.getString("MOD_YN"));
            oracleStmt.setString(8, rs.getString("DSC"));
            oracleStmt.setString(9, rs.getString("REG_ID"));
            oracleStmt.executeUpdate();
        }

        oracleConn.commit();
        System.out.println("MS SQL Server 데이터 조회 후 Oracle에 삽입 완료 - 현재 시간: " + LocalDateTime.now());


    } catch (SQLException e) {
        logger.error("SQL Exception 발생", e);
        if (oracleConn != null) {
            try {
                oracleConn.rollback();
            } catch (SQLException ex) {
                logger.error("oracleStmt Exception 발생", ex);
            }
        }
    } finally {
        try { if (oracleStmtText != null) oracleStmtText.close(); } catch (SQLException e) { logger.error("oracleStmtText Exception 발생", e); }
        try { if (oracleStmtCommon != null) oracleStmtCommon.close(); } catch (SQLException e) { logger.error("oracleStmtCommon Exception 발생", e); }

        try { if (oracleStmt != null) oracleStmt.close(); } catch (SQLException e) { logger.error("oracleStmt Exception 발생", e); }
        try { if (oracleConn != null) oracleConn.close(); } catch (SQLException e) { logger.error("oracleConn Exception 발생", e); }
        try { if (rs != null) rs.close(); } catch (SQLException e) { logger.error("rs Exception 발생", e); }
        try { if (sqlServerStmt != null) sqlServerStmt.close(); } catch (SQLException e) { logger.error("sqlServerStmt Exception 발생", e); }
        try { if (sqlServerConn != null) sqlServerConn.close(); } catch (SQLException e) { logger.error("sqlServerConn Exception 발생", e); }
    }
};