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

JDBC - JDBC 프로그래밍 예제(3) - 소스코드

dyjung 2018. 7. 21. 18:14

-View1 : 사용자 요청 데이터를 입력받는 페이지-


Register2.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
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
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">    
 
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>네이버 : 회원가입</title>
    <script src="https://code.jquery.com/jquery-1.10.2.js"></script>
    <link rel="stylesheet" type="text/css" href="register2.css">
</head>
 
<body>
    <jsp:include page="index.jsp"></jsp:include>
 
    <div id="wrap">
        <div id="header">
            <h1><a href="#" id="logo">NAVER</a></h1>
        </div>
        <div id="content">
            <form action="RegisterAction.jsp" method="post" onsubmit="return check();">
            
                <label for="userid"><b>아이디</b></label><br>
                <div id="divtext"><input type="text" id="text1" class="id" name="user_id"><span>@naver.com</span></div>
                <div id="return_id" class="return"></div>
 
                <label for="userid"><b>비밀번호</b></label><br>
                <div id="divtext"><input type="password" id="text1" class="pwd" name="user_pw"><img src="pwdnormal1.PNG" id="pwdimg1"></img>
                </div>
                <div id="return_pwd" class="return"></div>
 
                <label for="userid"><b>비밀번호 재확인</b></label><br>
                <div id="divtext"><input type="password" id="text1" class="pwd2"><img src="pwdnormal2.PNG" id="pwdimg2"></img>
                </div>
                <div id="return_pwd2" class="return"></div>
 
                <label for="userid"><b>이름</b></label><br>
                <div id="divtext"><input type="text" id="text1" class="name" name="user_name"></div>
                <div id="return_name" class="return"></div>
 
                <label for="userid"><b>생년월일</b></label><br>
                <div id="divtext2"><input type="text" id="text2" class="birthyear" placeholder="년(4자)" name="user_year"></div>
                <div id="divtext2"><select id=birthselect class="birthmonth" name="user_month">
                <option>월</option>
                <option value="1">1</option>
                <option value="2">2</option>
                <option value="3">3</option>
                <option value="4">4</option>
                <option value="5">5</option>
                <option value="6">6</option>
                <option value="7">7</option>
                <option value="8">8</option>
                <option value="9">9</option>
                <option value="10">10</option>
                <option value="11">11</option>
                <option value="12">12</option>
            </select></div>
                <div id="divtext2"><input type="text" id="text2" class="birthday" placeholder="일" name="user_day"></div><br/>
                <div id="return_year" class="return"></div>
 
                <label for="userid"><b>성별</b></label><br>
                <input type=checkbox id="gendercheck" name="user_gender" value="남자"><label for="gendercheck" id="genderlabel"value="남자">남자</label>
                <input type=checkbox id="gendercheck2" name="user_gender" value="여자"><label for="gendercheck2" id="genderlabel2" value="남자">여자</label><br/>
                <div id="return_gender" class="return"></div>
 
                <label for="userid"><b>본인확인이메일</b></label><br>
                <div id="divtext"><input type="text" id="emailtext" class="email" placeholder="선택입력" name="user_email"></div>
                <div id="return_email" class="return"></div>
 
                <label for="userid"><b>휴대전화</b></label><br>
                <div id="divtext"><select id=phoneselect>
                <option>대한민국 +82</option>
            </select></div>
                <div id="divtext3"><input type="text" id="phonetext" class="phone" placeholder="전화번호 입력" name="user_phone"></div>
                <input type="button" id="cfbutton" value="인증번호 받기"><br/>
 
                <div id="divtext4"><input type="text" id="cfinput" disabled="true" placeholder="인증번호를 입력하세요"></div>
                <div id="return_phone" class="return"></div>
                <input type="submit" value="가입하기" id="sub">
 
 
            </form>
 
            <div id="footer">
                <ul>
                    <li id="footlist"><a href="#" id="foottext2">이용약관</a></li>
                    <li id="footlist"><a href="#" id="foottext2"><b>개인정보처리방침</b></a></li>
                    <li id="footlist"><a href="#" id="foottext2">책임의 한계와 법적고지</a></li>
                    <li id="footlist"><a href="#" id="foottext2">회원정보 고객센터</a></li>
                </ul>
                <ul id="footbanner">
                    <li id="footlist2"><a href="#" id="footlogo"><b>NAVER</b></a></li>
                    <li id="footlist2">Copyrigth</li>
                    <li id="footlist2"><a href="#" id="foota"><b>NAVER Corp.</b>
                    <li id ="footlist2">All Rights Reseved</li>
                    </a></li>
 
                </ul>
            </div>
 
        </div>
    </div>
    <script>
        //submit하기 전 유효성 검사
        //1. 입력사항을 입력하지 않은 경우 (빈값제출)
        //id,비밀번호, 비밀번호 확인,이름,성별,- 필수 입력 사항, 핸드폰 - 필수입력사항 + 인증이 필요, 생년월일 - 태어난 년도 4자리를 정확하게 입력하세요
        $(document).ready(function() {
            var idSet = /^[A-za-z0-9]{5,20}$/g; //6~20자리 아이디 정규식
            var pwdSet = /^.*(?=^.{6,16}$)(?=.*\d)(?=.*[a-zA-Z])(?=.*[!@#$%^&+=]).*$/; //6~16자리 특수기호포함 정규식
            var nameSet = /^[가-힣a-zA-Z]+$/; //이름 정규식
            var yearSet = /^[0-9]*$/; //숫자만 정규식
            var emailSet = /^[0-9a-zA-Z]([-_.]?[0-9a-zA-Z])*@[0-9a-zA-Z]([-_.]?[0-9a-zA-Z])*.[a-zA-Z]{2,3}$/i; //이메일 정규식
            var phoneSet = /^\d{3}-\d{3,4}-\d{4}$/;; //핸드폰정규식
 
 
            //아이디 유효성검사.
            //해당 입력창을 벗어나면 바로 결과가 나오게 하기위해서 focusout사용
            $('.id').focusout(function() {
                if ($(this).val() == null || $(this).val().length == 0) {
                    $('#return_id').html("필수 입력 정보입니다.");
                } else {
                    if (!idSet.test($(this).val())) {
                        $('#return_id').html("5~20자의 영문 소문자, 숫자와 특수기호(_),(-)만 사용 가능합니다.");
                    } else
                        $('#return_id').html("멋진 아이디네요!").css('color', 'green');
                }
 
            });
 
            //비밀번호 유효성검사.
            //해당 입력창을 벗어나면 바로 결과가 나오게 하기위해서 focusout사용
            $('.pwd').focusout(function() {
                if ($(this).val() == null || $(this).val().length == 0) {
                    $('#return_pwd').html("필수 입력 정보입니다.");
                    $('#pwdimg1').prop('src', "pwdnormal1.png");
                } else {
                    if (!pwdSet.test($(this).val())) {
                        $('#pwdimg1').prop('src', "pwdno.png");
                        $('#return_pwd').html("6~16자 영문 대 소문자, 숫자, 특수문자를 사용하세요.");
                    } else
                        $('#pwdimg1').prop('src', "pwdok.png");
                    $('#return_pwd').html("");
                }
            });
            //비빌번호 재확인 일치하는지 유효성검사.
            $('.pwd2').focusout(function() {
                if ($(this).val() == null || $(this).val().length == 0) {
                    $('#return_pwd2').html("필수 입력 정보입니다.");
                    $('#pwdimg2').prop('src', "pwdnormal2.png");
                } else {
                    if ($(this).val() == $('.pwd').val()) {
                        $('#pwdimg2').prop('src', "pwdcheckok.png");
                        $('#return_pwd2').html("확인");
                    } else {
                        $('#return_pwd2').html("비밀번호가 일치하지 않습니다.");
                        $('#pwdimg2').prop('src', "pwdnormal2.png");
                    }
                }
            });
 
            //이름 유효성검사.
            //해당 입력창을 벗어나면 바로 결과가 나오게 하기위해서 focusout사용
            $('.name').focusout(function() {
                if ($(this).val() == null || $(this).val().length == 0) {
                    $('#return_name').html("필수 입력 정보입니다.");
                } else {
                    if (!nameSet.test($(this).val())) {
                        $('#return_name').html("한글과 영문 대 소문자를 사용하세요. (특수기호, 공백 사용 불가)");
                    } else
                        $('#return_name').html("");
                }
 
            });
 
            //생년월일- 년 유효성검사
            $('.birthyear').focusout(function() {
                if ($(this).val() == null || $(this).val().length == 0) {
                    $('#return_year').html("필수 입력 정보입니다.");
                } else {
                    if ($(this).val().length != 4) {
                        $('#return_year').html("태어난 년도 4자리를 정확하게 입력하세요.");
                    } else
                        $('#return_year').html("");
                }
 
            });
 
            //생년월일 - 월 유효성검사
            $('.birthmonth').focusout(function() {
                if ($(this).val() == "월") {
                    $('#return_year').html("태어난 일(날짜) 2자리를 정확하게 입력하세요.");
                } else {
                    $('#return_year').html("");
                }
 
            });
 
            //생년월일 - 일 유효성검사
            $('.birthday').focusout(function() {
                if ($(this).val() == null || $(this).val().length == 0) {
                    $('#return_year').html("태어난 일(날짜) 2자리를 정확하게 입력하세요.2");
                } else {
                    if (!yearSet.test($(this).val())) {
                        $('#return_year').html("형식에 맞지 않습니다.");
                    } else
                        $('#return_year').html("");
                }
 
            });
 
            //이메일 유효성검사
            $('.email').focusout(function() {
                if ($(this).val() == null || $(this).val().length == 0) {
                    $('#return_email').html("필수 입력 정보입니다.");
                } else {
                    if (!emailSet.test($(this).val())) {
                        $('#return_email').html("이메일 주소를 다시 확인해주세요.");
                    } else
                        $('#return_email').html("");
                }
 
            });
 
            //전화번호 유효성검사
            //인증번호 받기 클릭시
            $('#cfbutton').click(function() {
                if ($('.phone').val() == null || $('.phone').val().length == 0) {
                    $('#return_phone').html("필수 입력 정보입니다.");
                } else {
                    if (!($('.phone').val().length == 10 || $('.phone').val().length == 11)) {
                        $('#return_phone').html("형식에 맞지 않는 번호입니다.");
                    } else
                        $('#return_phone').html("인증번호를 발송했습니다.(유효시간30분)").css('color', 'green');
                    $('#cfinput').prop('disabled', false);
                    $('#divtext4').css('background-color', 'white');
                }
            });
 
            //남,여 성별체크시 색변경
            $('#genderlabel').on('click', function() {
                $('#genderlabel').css('border', '1px solid green').css('color', 'green');
                $('#genderlabel2').css('border', '1px solid gray').css('color', 'gray');
            });
 
            $('#genderlabel2').on('click', function() {
                $('#genderlabel2').css('border', '1px solid green').css('color', 'green');
                $('#genderlabel').css('border', '1px solid gray').css('color', 'gray');
            });
        });
 
    </script>
</body>
 
</html>
cs

Login.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
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<% request.setCharacterEncoding("utf-8");%>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>네이버: 로그인</title>
    <link rel="stylesheet" type="text/css" href="login.css">
</head>
<body>
    <jsp:include page="index.jsp"></jsp:include>
   <div id="wrap">
       <div id="header">
        <h1><a href="#" id="logo">NAVER</a></h1>
    </div>
    <div id="content">
        
        <form action="loginAction.jsp" method="post">
            <div id="divtext"><input type="text" id="text1" name="userid"></div>
            <input type="hidden" name ="noid" value="1">
            <div id="divtext"><input type="password" id="text1" name="userpw"></div>
            <input type="hidden" name ="nopw">
            <input type="submit" id="login" value="로그인"><br/>
            <input type="checkbox" id="logincheck" name="check" value="1"><label for="logincheck">아이디 저장</label> 
            <a id="register" href="Register2.jsp">회원가입</a>
        </form>
    </div>
    <div id="hiddendiv" name="hiddens" value=""></div>
</div>
</body>
</html>
cs

emp_insertForm.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
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
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
<%@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 import="java.io.PrintWriter"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<% //세션이 있는경우  login=true
        String sessonId = (String)session.getAttribute("MEMBERID");
        boolean login = sessonId == null ? false : true;
        
        if(login==false){
            
            PrintWriter script = response.getWriter();
            script.println("<script>");
            script.println("alert('로그인 후 사용 가능합니다.')");
            script.println("location.href='Login.jsp'");
            script.println("</script>");
        
         }else{%>
<%
    
    //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>
    <jsp:include page="index.jsp"></jsp:include>
    <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


-Action페이지 : 사용자가 입력한 요청 데이터를  처리하는 페이지-


LoginAction.jsp - Login.jsp에서 입력받은 데이터로 로그인 처리하는 페이지

DB에 저장되있는 데이터와 사용자가 입력한 데이터를 비교한다.

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
<%@page import="Users.User"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@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 import="java.io.PrintWriter"%>
<jsp:useBean id="user" class="Users.User"></jsp:useBean>
<jsp:setProperty property="*" name="user"/>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>로그인 처리</title>
</head>
<body>
    <%    
        //로그인페이지에서 사용자가 입력한 값
        //DB에 있는 값과 비교하기위해
        String userid = request.getParameter("userid");
        String userpw = request.getParameter("userpw");
            
        //2. 불러온정보를  DB에 넣음(insert).
                // 사용자의 입력 데이터의 한글처리
                request.setCharacterEncoding("utf-8");
                
                // 2. 데이터 베이스 연결    
                //2.1 JDBC 드라이버 로딩
                Class.forName("oracle.jdbc.driver.OracleDriver");
                //JDBC URL
                String jdbcDriver = "jdbc:apache:commons:dbcp:pooltest";
                Connection conn = null;
                PreparedStatement pstmt = null;
                ResultSet rs = null;
                
                //처리할 SQL문 - 회원가입정보 INSERT
                String sql_select ="select * from users where user_id=?";
                
                try{
                    // 2.2 데이터베이스 커넥션 생성
                    conn = DriverManager.getConnection(jdbcDriver);
                    pstmt = conn.prepareStatement(sql_select);
                    pstmt.setString(1,userid); //userid = 로그인페이지에서 입력한 데이터
                    rs = pstmt.executeQuery();
                    
                    //아이디가 있는경우
                    if(rs.next()){
                        //사용자가 입력한 비밀번호와 DB에 저장된 비밀번호가 같은경우 (로그인성공) / 메인페이지로
                        if(rs.getString(2).equals(userpw)){
                            //세션생성
                            session.setAttribute("MEMBERID",rs.getString("user_id"));
                            session.setAttribute("MEMBERBIRTH",rs.getString("user_birth"));
                            session.setAttribute("MEMBERNAME",rs.getString("user_name"));
                            response.sendRedirect("OkLogin.jsp");
                        } 
                        else{ //아이디는 있지만 비밀번호가 틀린경우 -->
                            PrintWriter script = response.getWriter();
                            script.println("<script>");
                            script.println("alert('아이디 혹은 비밀번호가 틀렸습니다.')");
                            script.println("location.href='Login.jsp'");
                            script.println("</script>");
                         }
                    }else{//아이디가 없는경우  ()
                        PrintWriter script = response.getWriter();
                        script.println("<script>");
                        script.println("alert('아이디 혹은 비밀번호가 틀렸습니다.')");
                        script.println("location.href='Login.jsp'");
                        script.println("</script>");
                    }
                    // 4. sql 실행
                    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) {
                    }
                }
            %>    
    
</body>
</html>
cs

RegisterAction.jsp - 사용자가 입력한 데이터를 DB에 저장하는 페이지

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
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@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"%>
<!-- 1. 회원가입 정보를 불러온다. (usebean사용 )-->
<%request.setCharacterEncoding("utf-8"); %>
<jsp:useBean id="user" class="Users.User"></jsp:useBean>
<jsp:setProperty property="*" name="user"/>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>회원가입처리</title>
</head>
<body>
    <%
        //2. 불러온정보를  DB에 넣음(insert).
        // 사용자의 입력 데이터의 한글처리
        
        
        // 2. 데이터 베이스 연결    
        //2.1 JDBC 드라이버 로딩
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //JDBC URL
        String jdbcDriver = "jdbc:apache:commons:dbcp:pooltest";
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        //처리할 SQL문 - 회원가입정보 INSERT
            
        String sql_insert ="insert into users values(?,?,?,?,?,?,?)";
        
        try{
            // 2.2 데이터베이스 커넥션 생성
            
            conn = DriverManager.getConnection(jdbcDriver);
                
            pstmt = conn.prepareStatement(sql_insert);
            
            pstmt.setString(1,user.getUser_id());
            pstmt.setString(2,user.getUser_pw());
            pstmt.setString(3,user.getUser_name());
            pstmt.setString(4,user.getUser_year()+"-"+user.getUser_month()+"-"+user.getUser_day());
            pstmt.setString(5,user.getUser_gender());
            pstmt.setString(6,user.getUser_email());
            pstmt.setString(7,user.getUser_phone());
            
            // 4. sql 실행
            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)
                //회원가입완료창으로 이~동
                response.sendRedirect("OkRegister.jsp");
                try {
                    conn.close();
                } catch (SQLException ex) {
            }
        }
    %>    
</body>
</html>
cs

-View2 : 사용자가 요청한 데이터를 처리한 결과를 보여주는 페이지-


mypage.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
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ page import="java.io.PrintWriter"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>마이페이지</title>
<style>
    .wrap{
  }
</style>
</head>
<body>
    <jsp:include page="index.jsp"></jsp:include>
 
    <%    
        //세션정보를 가져옴
        String sessonId = (String)session.getAttribute("MEMBERID");
        String sessonBirth = (String)session.getAttribute("MEMBERBIRTH");
        String sessonName = (String)session.getAttribute("MEMBERNAME");
        //세션이 있는경우  login=true
        
        boolean login = sessonId == null ? false : true; 
    
    
    
        //세션이 없는 경우에는 alert띄우고 로그인창으로
        if(login==false){
            PrintWriter script = response.getWriter();
            script.println("<script>");
            script.println("alert('로그인 후 사용 가능합니다.')");
            script.println("location.href='Login.jsp'");
            script.println("</script>");
        }else{
            //마이페이지 보여줌
            %>
            <h2>회원 마이 페이지</h2>
            <div class="wrap">
                <img src="http://blogfiles4.naver.net/20120410_272/foreverdec_1334042019412IwRio_JPEG/%C4%BB%B0%C5%B7%E7.jpg" alt="그림없다" style="width:300px;"><br>
                <table>
                    <tr>
                        <td>회원생일</td>
                        <td><%=sessonBirth %></td>
                    </tr>
                    <tr>
                        <td>회원아이디</td>
                        <td><%=sessonId %></td>
                    </tr>
                    <tr>
                        <td>회원이름</td>
                        <td><%=sessonName %></td>
                    </tr>
                </table>
            </div>
        <% }
    %>
</body>
</html>
cs


Userlist.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
110
111
112
113
114
115
116
117
118
<%@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"%>
<%@ page import="java.io.PrintWriter"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>회원 리스트 출력</title>
<style>
    td {
        text-align: right;
        padding : 10px 20px;;
        border-bottom: 1px solid gray; 
    }
</style>
</head>
<body>
    <% //세션이 있는경우  login=true
        String sessonId = (String)session.getAttribute("MEMBERID");
        boolean login = sessonId == null ? false : true;
        
        if(login==false){
            
            PrintWriter script = response.getWriter();
            script.println("<script>");
            script.println("alert('로그인 후 사용 가능합니다.')");
            script.println("location.href='Login.jsp'");
            script.println("</script>");
        
         }else{%>
            
        
    
    <jsp:include page="index.jsp"></jsp:include>
    <h1>회원 목록</h1>
    <table border="1">
        <tr>
            <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 {
                //커넥션 풀 연결 URL
                String jdbcDriver = "jdbc:apache:commons:dbcp:pooltest";
                
                String query = "select * from users order by user_id";
                // 2. 데이터베이스 커넥션 생성
                conn = DriverManager.getConnection(jdbcDriver);
                // 3. Statement 생성
                stmt = conn.createStatement();
                // 4. 쿼리 실행
                rs = stmt.executeQuery(query);
                // 5. 쿼리 실행 결과 출력
                while (rs.next()) {
        %>
 
 
        <tr>
            <td><%= rs.getString(1)%></td>
            <td><%= rs.getString(2)%></td>
            <td><%= rs.getString(3)%></td>
            <td><%= rs.getString(4)%></td>
            <td><%= rs.getString(5)%></td>
            <td><%= rs.getString(6)%></td>
            <td><%=    rs.getString(7)%></td>
            <td> <a href="emp_UpdateForm2.jsp?empno=<%= rs.getString(1)%>">수정</a> </td>
            <td> <a href="emp_delete.jsp?empno=<%= rs.getString(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

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
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
<%@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"%>
<%@ page import="java.io.PrintWriter"%>
<!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>
<% //세션이 있는경우  login=true
        String sessonId = (String)session.getAttribute("MEMBERID");
        boolean login = sessonId == null ? false : true;
        
        if(login==false){
            
            PrintWriter script = response.getWriter();
            script.println("<script>");
            script.println("alert('로그인 후 사용 가능합니다.')");
            script.println("location.href='Login.jsp'");
            script.println("</script>");
        
         }else{%>
    <jsp:include page="index.jsp"></jsp:include>
    <h1>사원리스트</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"; */
                //커넥션 풀 연결 URL
                String jdbcDriver = "jdbc:apache:commons:dbcp:pooltest2";
                
                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);
                conn = DriverManager.getConnection(jdbcDriver);
                // 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방식으로 곧 바꿔볼 예정이다



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

웹 어플리케이션 구조 - DAO , 서비스클래스 , 트랜잭션 , 예외처리 , 커넥션 풀  (0) 2018.07.21
JDBC - JDBC 프로그래밍 예제(3) - 실행화면  (0) 2018.07.21
JDBC - 커넥션 풀  (0) 2018.07.21
JDBC - 트랜잭션 처리  (0) 2018.07.21
JDBC - JDBC 프로그래밍 예제(2)  (0) 2018.07.21

'프로그래밍/JSP' Related Articles

티스토리툴바