<aside> 💡 SQL때 풀었던 문제들을, JDBC를 이용해서 풀어봅니다!

</aside>

Question 1

→ HNU Entertainment의 부서 코드, 이름, 위치를 검색하시오.

public void getQuestion1() {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        conn = PostgresqlAccess.setConnection();
        conn.setAutoCommit(false);
        String query = "SELECT * FROM DEPARTMENT";
        pstmt = conn.prepareStatement(query);
        rs = pstmt.executeQuery();
        while (rs.next()) {
            System.out.print("[부서 코드] " + rs.getString("dept_code") + " || ");
            System.out.print("[이름] " + rs.getString("dept_name") + " || ");
            System.out.println("[위치] " + rs.getString("dept_loc"));
        }
    } catch (SQLException sqex) {
        System.out.println("SQLException: " + sqex.getMessage());
        System.out.println("SQLState: " + sqex.getSQLState());
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

Question 2

→ HNU Entertainment의 연예관계자 코드, 이름, 관리자, 급여를 검색하시오.

public void getQuestion2() {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        conn = PostgresqlAccess.setConnection();
        conn.setAutoCommit(false);
        String query = "SELECT emp_code, emp_name, emp_mgt, emp_sal FROM EMPLOYEE";
        pstmt = conn.prepareStatement(query);
        rs = pstmt.executeQuery();
        while (rs.next()) {
            System.out.print("[연예관계자 코드] " + rs.getString(1) + " || ");
            System.out.print("[이름] " + rs.getString(2) + " || ");
            System.out.print("[관리자] " + rs.getString(3));
            System.out.println("[급여] " + rs.getString(4));
        }
    } catch (SQLException sqex) {
        System.out.println("SQLException: " + sqex.getMessage());
        System.out.println("SQLState: " + sqex.getSQLState());
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

Question 3

→ HNU Entertainment(HNU-E)에서 제작한 드라마의 코드와 이름을 검색하시오.

public void getQuestion3() {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        conn = PostgresqlAccess.setConnection();
        conn.setAutoCommit(false);
        String query = "SELECT DRM_CODE, DRM_NAME FROM DRAMA";
        pstmt = conn.prepareStatement(query);
        rs = pstmt.executeQuery();
        while (rs.next()) {
            System.out.print("[드라마 코드] " + rs.getString(1) + " || ");
            System.out.println("[드라마 이름] " + rs.getString(2) + " || ");
        }
    } catch (SQLException sqex) {
        System.out.println("SQLException: " + sqex.getMessage());
        System.out.println("SQLState: " + sqex.getSQLState());
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

Question 4

→ 드라마 방영사가 KBC이거나 SBC인 드라마를 검색하시오.

public void getQuestion4() {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        conn = PostgresqlAccess.setConnection();
        conn.setAutoCommit(false);
        String query = "SELECT drm_name, drm_brd FROM DRAMA WHERE DRM_BRD IN ('KBC', 'SBC')";
        pstmt = conn.prepareStatement(query);
        rs = pstmt.executeQuery();
        while (rs.next()) {
            System.out.print("[드라마 이름]: " + rs.getString("drm_name"));
            System.out.println("\\t\\t [방영사]: " + rs.getString("drm_brd"));
        }
    } catch (SQLException sqex) {
        System.out.println("SQLException: " + sqex.getMessage());
        System.out.println("SQLState: " + sqex.getSQLState());
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}