본문 바로가기
프로그래밍/JSP

JDBC - JDBC 프로그래밍 예제(2)

dyjung 2018. 7. 21. 16:33


EMP 테이블



emp_insertForm.jsp - 사원정보입력(insert)
emp_list.jsp - 사원정보확인(select)
수정, 삭제 클릭시 해당 사원 수정,삭제


emp_UpdateForm1.jsp - 사원정보수정

emp_UpdateForm2.jsp - emp_list에서 수정 클릭시 이동하는 페이지

1. emp_list.jsp - emp테이블을 jsp페이지에서로 출력


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>EMP 테이블 직원 리스트 출력</title>
<style>
    td {
        text-align: right;
        padding : 10px 20px;;
        border-bottom: 1px solid gray; 
    }
</style>
</head>
<body>
    <h1>EMP 테이블 직원 목록</h1>
    <table border="1">
        <tr>
            <td>사원번호</td>
            <td>사원이름</td>
            <td>업무</td>
            <td>상관번호</td>
            <td>입사일</td>
            <td>급여</td>
            <td>커미션</td>
            <td>부서번호</td>
            <td>부서이름</td>
            <td>부서위치</td>
            <td colspan="2">관리</td>
        </tr>
 
        <%
            // 1. JDBC 드라이버 로딩
            Class.forName("oracle.jdbc.driver.OracleDriver");
 
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
 
            try {
                String jdbcDriver = "jdbc:oracle:thin:localhost:1521:orcl";
                String dbUser = "student";
                String dbPass = "1234";
                
                String query = "select e.empno, e.ename, e.job, e.ename, e.hiredate, e.sal, nvl(e.comm, 0), e.deptno, d.dname, d.loc "
                             + " from emp e, emp ee, dept d "
                             + " where e.mgr=ee.empno and e.deptno=d.deptno "
                             + " order by e.ename ";
                // 2. 데이터베이스 커넥션 생성
                conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPass);
                // 3. Statement 생성
                stmt = conn.createStatement();
                // 4. 쿼리 실행
                rs = stmt.executeQuery(query);
                // 5. 쿼리 실행 결과 출력
                while (rs.next()) {
        %>
 
 
        <tr>
            <td><%= rs.getInt(1)%></td>
            <td><%= rs.getString(2)%></td>
            <td><%= rs.getString(3)%></td>
            <td><%= rs.getString(4)%></td>
            <td><%= rs.getDate(5)%></td>
            <td><%= rs.getInt(6)%></td>
            <td><%= rs.getInt(7)%></td>
            <td><%= rs.getInt(8)%></td>
            <td><%= rs.getString(9)%></td>
            <td><%= rs.getString(10)%></td>
            <td> <a href="emp_UpdateForm2.jsp?empno=<%= rs.getInt(1)%>">수정</a> </td>
            <td> <a href="emp_delete.jsp?empno=<%= rs.getInt(1)%>">삭제</a> </td>
        </tr>
        <%
            }
            } catch (SQLException ex) {
                out.println(ex.getMessage());
                ex.printStackTrace();
            } finally {
                // 6. 사용한 Statement 종료
                if (rs != null)
                    try {
                        rs.close();
                    } catch (SQLException ex) {
                    }
                if (stmt != null)
                    try {
                        stmt.close();
                    } catch (SQLException ex) {
                    }
                // 7. 커넥션 종료
                if (conn != null)
                    try {
                        conn.close();
                    } catch (SQLException ex) {
                    }
            }
        %>
 
    </table>
 
</body>
</html>
cs


2. emp_UpdateForm1.jsp - 사원정보수정페이지에서 회원정보를 수정

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
    //1. JDBC 드라이버 로딩
    Class.forName("oracle.jdbc.driver.OracleDriver");
 
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    String jdbcDriver = "jdbc:oracle:thin:localhost:1521:orcl";
    String dbUser = "student";
    String dbPass = "1234";
    String sql = "select empno, ename from emp order by ename";
 
    try {
        // 2. 데이터베이스 커넥션 생성
        conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPass);
        // 3. Statement 생성
        stmt = conn.createStatement();
        // 4. 쿼리 실행
        rs = stmt.executeQuery(sql);
%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>emp 테이블의 사원 정보 수정</title>
<style>
</style>
</head>
<body>
    <h1>사원 정보 수정</h1>
 
    <hr>
    <form action="emp_UpdateAction1.jsp">
    <table>
        <tr>
            <td>사원번호</td>
            <td>
                <select name="empno">
                    <%
                        while (rs.next()) {
                    %>
                    <option value="<%=rs.getInt(1)%>">(<%=rs.getInt(1)%>)
                        <%=rs.getString(2)%>
                    </option>
                    <%
                        }
 
                        } catch (SQLException ex) {
                            out.println(ex.getMessage());
                            ex.printStackTrace();
                        } finally {
                            // 6. 사용한 Statement 종료
                            if (rs != null)
                                try {
                                    rs.close();
                                } catch (SQLException ex) {
                                }
                            if (stmt != null)
                                try {
                                    stmt.close();
                                } catch (SQLException ex) {
                                }
                            // 7. 커넥션 종료
                            if (conn != null)
                                try {
                                    conn.close();
                                } catch (SQLException ex) {
                                }
                        }
                    %>
            </select></td>
        </tr>
        <tr>
            <td>사원이름</td>
            <td><input type="text" name="ename"></td>
        </tr>
        <tr>
            <td>급여</td>
            <td><input type="number" name="sal"></td>
        </tr>
        <tr>
            <td colspan="2" style="text-align: center;" > <input type="submit" value="변경"></td>
        </tr>
 
        </table>
</form>
 
 
 
 
 
 
 
 
 
 
 
 
 
</body>
</html>
 
cs

3.emp_UpdateForm2.jsp - 사원리스트에서 선택한 사원만 수정

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
    // 수정하고자하는 사원번호 데이터 받기
    String empno = request.getParameter("empno");
 
    /* if (empno != null) {
        int empno_num = Integer.parseInt(empno);
    } */
 
    //1. JDBC 드라이버 로딩
    Class.forName("oracle.jdbc.driver.OracleDriver");
 
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    String jdbcDriver = "jdbc:oracle:thin:localhost:1521:orcl";
    String dbUser = "student";
    String dbPass = "1234";
    //String sql = "select empno, ename from emp order by ename";
    String sql2 = "select empno, ename, sal from emp where empno ="+empno;
 
    try {
        // 2. 데이터베이스 커넥션 생성
        conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPass);
        // 3. Statement 생성
        stmt = conn.createStatement();
        // 4. 쿼리 실행
        rs = stmt.executeQuery(sql2);
%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>emp 테이블의 사원 정보 수정</title>
<style>
</style>
</head>
<body>
    <h1>사원 정보 수정</h1>
 
    <hr>
    <%
        if (rs.next()) {
    %>
    <form action="emp_UpdateAction2.jsp">
        <table>
            <tr>
                <td>사원번호</td>
                <td>(<%= rs.getInt(1) %>) <%= rs.getString(2) %>
                
                    <input type="hidden" name="empno" value="<%= rs.getInt(1) %>">
                    
                </td>
            </tr>
            <tr>
                <td>사원이름</td>
                <td><input type="text" name="ename" value="<%= rs.getString(2)%>"></td>
            </tr>
            <tr>
                <td>급여</td>
                <td><input type="number" name="sal" value="<%= rs.getInt(3) %>"></td>
            </tr>
            <tr>
                <td colspan="2" style="text-align: center;"><input
                    type="submit" value="변경"></td>
            </tr>
 
        </table>
    </form>
 
    <%
        }
 
        } catch (SQLException ex) {
            out.println(ex.getMessage());
            ex.printStackTrace();
        } finally {
            // 6. 사용한 Statement 종료
            if (rs != null)
                try {
                    rs.close();
                } catch (SQLException ex) {
                }
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException ex) {
                }
            // 7. 커넥션 종료
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException ex) {
                }
        }
    %>
 
</body>
</html>
 
cs

 4. emp_insertForm - 새로운 사원을 입력(insert)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
    
    //1. JDBC 드라이버 로딩
    Class.forName("oracle.jdbc.driver.OracleDriver");
    
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs= null;
 
    String jdbcDriver = "jdbc:oracle:thin:localhost:1521:orcl";
    String dbUser = "student";
    String dbPass = "1234";
    String sql = "select empno, ename from emp where job = 'MANAGER' or job='PRESIDENT' order by ename";
    String sql2 = "select deptno, dname from dept order by deptno";
    
    //insert는  입력처리 페이지인 emp_edit4.jsp에서 
    
 
    try {
        // 2. 데이터베이스 커넥션 생성
        conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPass);
        // 3. Statement 생성
        pstmt = conn.prepareStatement(sql);
        rs = pstmt.executeQuery();    
        
%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>emp 테이블의 사원 정보 입력</title>
<style>
</style>
</head>
<body>
    <h1>사원 정보 입력</h1>
    <hr>
    
    <form action="emp_insertAction.jsp">
        <table>
            <tr>
                <td>사원번호</td>
                <td>        
                    <input type="number" name="empno">            
                </td>
            </tr>
            <tr>
                <td>사원이름</td>
                <td><input type="text" name="ename"></td>
            </tr>
            <tr>
                <td>직급</td>
                <td><select name="job">
                    <option value="CLERK">CLERK</option>
                    <option value="SALESMAN">SALESMAN</option>
                    <option value="ANALYST">ANALYST</option>
                    <option value="MANAGER">MANAGER</option>
                    <option value="PRESIDENT">PRESIDENT</option>
                </select></td> <!-- name은 mgr -->
            </tr>
            <tr>
                <td>매니저</td>
                <td><select name="mgr">
                    <%
                        while (rs.next()){
                    %>
                    <option value="<%=rs.getInt("empno")%>">
                        <%=rs.getString("ename")%>                
                    </option>
                        <%
                        }%>
                </select></td> <!-- name은 mgr -->
            </tr>
            <tr>
                <td>입사일</td>
                <td><input type="date" name="hiredate"></td>
            </tr>
            <tr>
                <td>급여</td>
                <td><input type="number" name="sal"></td>
            </tr>
            <tr>
                <td>커미션</td>
                <td><input type="number" name="comm"></td>
            </tr>
            <tr>
                <td>부서명</td>
                <td><select name="mgr">
                    <%
                        rs = pstmt.executeQuery(sql2);    
                        while (rs.next()){
                    %>
                    <option value="<%=rs.getInt("deptno")%>">
                        <%=rs.getString("dname")%>                
                    </option>
                        <%
                        }%>
                </select></td> <!-- name은 mgr -->
            </tr>
 
            <tr>
                <td colspan="2"><input type="submit" value="입력"></td>
            </tr>
        </table>
    </form>
    <% 
        } catch (SQLException ex) {
            out.println(ex.getMessage());
            ex.printStackTrace();
        } finally {
            // 6. 사용한 Statement 종료
            if (rs!= null)
                try {
                    rs.close();
                } catch (SQLException ex) {
                }
            if (pstmt != null)
                try {
                    pstmt.close();
                } catch (SQLException ex) {
                }
            // 7. 커넥션 종료
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException ex) {
                }
        }
    %>
 
 
</body>
</html>
 
cs

2.1 emp_UpdateAciotn1.jsp - emp_UpdateForm1.jsp에 대한 Action처리

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
    // 1. 사용자 데이터 받기
    
    // 사용자의 입력 데이터의 한글처리
    request.setCharacterEncoding("utf-8");
 
    // 사용자의 입력 데이터를 변수로 받는다.
    int empno = Integer.parseInt(request.getParameter("empno"));
    String ename = request.getParameter("ename");
    int sal = Integer.parseInt(request.getParameter("sal"));
    
    // 응답결과를 구분하기 위한 변수 : 업데이트 실행 유무 확인
    int updateCnt = 0;
    
    // 2. 데이터 베이스 연결
    // 2.1. JDBC 드라이버 로딩
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = null;
    Statement stmt = null;
    
    String jdbcDriver = "jdbc:oracle:thin:localhost:1521:orcl";
    String dbUser = "student";
    String dbPass = "1234";
    
    String sql_update = "update emp " 
                      + " set ename='" + ename + "' , sal=" + sal 
                      + " where empno=" + empno;
    
    try{
    // 2. 데이터베이스 커넥션 생성
    conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPass);
    // 3. Statement 생성
    stmt = conn.createStatement(); 
    // 4. sql 실행
    updateCnt = stmt.executeUpdate(sql_update);
    
    
    } catch (SQLException ex) {
        out.println(ex.getMessage());
        ex.printStackTrace();
    } finally {
        // 6. 사용한 Statement 종료        
        if (stmt != null)
            try {
                stmt.close();
            } catch (SQLException ex) {
            }
        // 7. 커넥션 종료
        if (conn != null)
            try {
                conn.close();
            } catch (SQLException ex) {
            }
    }
    
%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<style>
</style>
</head>
<body>
 
<%
    if(updateCnt > 0){
%>
 
<h1>정보 변경이 정상적으로 처리되었습니다.</h1>
 
<%
    } else {
%>
<h1>해당 사원번호 데이터를 찾지 못했습니다.</h1>
<%
    }
%>
 
 
 
 
 
 
 
 
 
 
 
 
</body>
</html>
 
cs

3.1 emp_UpdateAciotn2.jsp - emp_UpdateForm2.jsp에 대한 Action처리

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
    // 1. 사용자 데이터 받기
    
    // 사용자의 입력 데이터의 한글처리
    request.setCharacterEncoding("utf-8");
 
    // 사용자의 입력 데이터를 변수로 받는다.
    int empno = Integer.parseInt(request.getParameter("empno"));
    String ename = request.getParameter("ename");
    int sal = Integer.parseInt(request.getParameter("sal"));
    
    // 응답결과를 구분하기 위한 변수 : 업데이트 실행 유무 확인
    int updateCnt = 0;
    
    // 2. 데이터 베이스 연결
    // 2.1. JDBC 드라이버 로딩
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = null;
    //Statement stmt = null;
    PreparedStatement pstmt = null;
    
    String jdbcDriver = "jdbc:oracle:thin:localhost:1521:orcl";
    String dbUser = "student";
    String dbPass = "1234";
    
    /* String sql_update = "update emp " 
                      + " set ename='" + ename + "' , sal=" + sal 
                      + " where empno=" + empno; */
    String sql_update ="update emp set ename=?, sal=? where empno=?";
                      
    try{
    // 2. 데이터베이스 커넥션 생성
    conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPass);
    // 3. Statement 생성
    //stmt = conn.createStatement();
    pstmt = conn.prepareStatement(sql_update);
    pstmt.setString(1,ename);
    pstmt.setInt(2,sal);
    pstmt.setInt(3,empno);
    // 4. sql 실행
    //updateCnt = stmt.executeUpdate(sql_update);
    updateCnt = pstmt.executeUpdate();
    
    } catch (SQLException ex) {
        out.println(ex.getMessage());
        ex.printStackTrace();
    } finally {
        // 6. 사용한 Statement 종료        
        if (pstmt != null)
            try {
                pstmt.close();
            } catch (SQLException ex) {
            }
        // 7. 커넥션 종료
        if (conn != null)
            try {
                conn.close();
            } catch (SQLException ex) {
            }
    }
    
    
    
    
    
    // 3. 데이터 업데이트
    
    // 4. 데이터 베이스 연결 종료
    
    // 5. html 형식으로 응답 처리 : 성공/실패
 
%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<style>
</style>
</head>
<body>
 
<%
    if(updateCnt > 0){
%>
 
<h1>정보 변경이 정상적으로 처리되었습니다.</h1>
 
<%
    } else {
%>
<h1>해당 사원번호 데이터를 찾지 못했습니다.</h1>
<%
    }
%>
 
 
 
 
 
 
 
 
 
 
 
 
</body>
</html>
 
cs

4.1 emp_insertAction.jsp - emp_insertForm.jsp에 대한 Action처리

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!-- // 1. 사용자 데이터 받기 -->
<jsp:useBean id="member" class="Member.Member"></jsp:useBean>
<jsp:setProperty property="*" name="member"/>
<%
    
    
    // 사용자의 입력 데이터의 한글처리
    request.setCharacterEncoding("utf-8");
    
    // 2. 데이터 베이스 연결
    
    // 2.1. JDBC 드라이버 로딩
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = null;
    //Statement stmt = null;
    PreparedStatement pstmt = null;
    int insertCnt = 0;
    
    String jdbcDriver = "jdbc:oracle:thin:localhost:1521:orcl";
    String dbUser = "student";
    String dbPass = "1234";
    
    
    String sql_insert ="insert into emp values(?,?,?,?,?,?,?,?)";
                      
    try{
    // 2. 데이터베이스 커넥션 생성
    conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPass);
    // 3. Statement 생성
    //stmt = conn.createStatement();
    pstmt = conn.prepareStatement(sql_insert);
    
    pstmt.setInt(1,member.getEmpno());
    pstmt.setString(2,member.getEname());
    pstmt.setString(3,member.getJob());
    pstmt.setInt(4,member.getMgr());
    pstmt.setString(5,member.getHiredate());
    pstmt.setInt(6,member.getSal());
    pstmt.setInt(7,member.getComm());
    pstmt.setInt(8,member.getDeptno());
    // 4. sql 실행
    insertCnt = pstmt.executeUpdate();
    
    } catch (SQLException ex) {
        out.println(ex.getMessage());
        ex.printStackTrace();
    } finally {
        // 6. 사용한 Statement 종료        
        if (pstmt != null)
            try {
                pstmt.close();
            } catch (SQLException ex) {
            }
        // 7. 커넥션 종료
        if (conn != null)
            try {
                conn.close();
            } catch (SQLException ex) {
            }
    }
    
    
    
    
    
    // 3. 데이터 업데이트
    
    // 4. 데이터 베이스 연결 종료
    
    // 5. html 형식으로 응답 처리 : 성공/실패
 
%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<style>
</style>
</head>
<body>
 
<%
    if(insertCnt > 0){
%>
 
<h1>정보 변경이 정상적으로 처리되었습니다.</h1>
 
<%
    } else {
%>
<h1>해당 사원번호 데이터를 찾지 못했습니다.</h1>
<%
    }
%>
 
 
 
 
 
 
 
 
 
 
 
 
</body>
</html>
 
cs

5. emp_delete.jsp - emp_list.jsp에서 출력한 사원테이블에서 선택한 사원을 삭제

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
    // 삭제하고자 하는 사원번호 받기
    
    String empno = request.getParameter("empno");
 
    if(empno ==null){
        empno = "0"; //처리되지 못하는 데이터
    }
    
    
    
    // 응답결과를 구분하기 위한 변수 : 업데이트 실행 유무 확인
    int deleteCnt = 0;
    
    // 2. 데이터 베이스 연결
    // 2.1. JDBC 드라이버 로딩
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = null;
    Statement stmt = null;
    
    String jdbcDriver = "jdbc:oracle:thin:localhost:1521:orcl";
    String dbUser = "student";
    String dbPass = "1234";
    
    String sql_delete = "delete from emp where empno= "+empno;
                      
    try{
    // 2. 데이터베이스 커넥션 생성
    conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPass);
    // 3. Statement 생성
    stmt = conn.createStatement(); 
    // 4. sql 실행
    deleteCnt = stmt.executeUpdate(sql_delete);
    
    
    } catch (SQLException ex) {
        out.println(ex.getMessage());
        ex.printStackTrace();
    } finally {
        // 6. 사용한 Statement 종료        
        if (stmt != null)
            try {
                stmt.close();
            } catch (SQLException ex) {
            }
        // 7. 커넥션 종료
        if (conn != null)
            try {
                conn.close();
            } catch (SQLException ex) {
            }
    }
    
    // 3. 데이터 업데이트
    
    // 4. 데이터 베이스 연결 종료
    
    // 5. html 형식으로 응답 처리 : 성공/실패
 
%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<style>
</style>
</head>
<body>
 
<%
    if(deleteCnt > 0){
%>
 
<h1>정보 변경이 정상적으로 처리되었습니다.</h1>
<a href="emp_list.jsp">사원리스트</a>
<%
    } else {
%>
<h1>해당 사원번호 데이터를 찾지 못했습니다.</h1>
<%
    }
%>
 
 
 
 
 
 
 
 
 
 
 
 
</body>
</html>
 
cs



소스를 보면 알다싶이 DB관련 부분에서 중복되는 소스가 많은걸 알 수 있다.

이러한 문제를 해결하기 위해 모델2를 사용하는것이 바람직하다.

'프로그래밍 > JSP' 카테고리의 다른 글

JDBC - 커넥션 풀  (0) 2018.07.21
JDBC - 트랜잭션 처리  (0) 2018.07.21
JDBC - JDBC란?  (0) 2018.07.21
JDBC - JDBC 프로그래밍 예제(1)  (0) 2018.07.21
필터 - 캐릭터 인코딩  (0) 2018.07.21

'프로그래밍/JSP' Related Articles

티스토리툴바