오라클-테이블, 시퀀스 + jsp

카테고리 없음

오라클-테이블, 시퀀스 + jsp

히연쓰 2021. 4. 30. 17:52

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

package com.heeyeon.model;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
 
public class MemberDao {
    
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:xe";
    String id = "heeyeon0";
    String pw = "1234";
    
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    
    //jsp에서 각가의 페이지에서 했던 일을 MemberDao 의 메서드로 바꾸어 처리한다.
    public int insertMember(MemberBean memberBean) {
        int result = 0;
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url,id,pw);
            String sql = "INSERT INTO MEMBER03 VALUES (MEMBER_SEQ.NEXTVAL,?,?,?,?,?,?,?)";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, memberBean.getId());
            pstmt.setString(2, memberBean.getName());
            pstmt.setString(3, memberBean.getPassword());
            pstmt.setString(4, memberBean.getEmail());
            pstmt.setString(5, memberBean.getPhone());
            pstmt.setString(6, memberBean.getInterest());
            pstmt.setString(7, memberBean.getAge());
            result = pstmt.executeUpdate();
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(pstmt!=null) pstmt.close();
                if(con!=null) pstmt.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return result;
    }
    
    public ArrayList<MemberBean> showAllMember() {
        ArrayList<MemberBean> memberList = new ArrayList<MemberBean>();
        
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url, id, pw);
            String sql = "SELECT * FROM MEMBER03";
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                MemberBean memberBean = new MemberBean();
                memberBean.setNo(rs.getInt(1));
                memberBean.setId(rs.getString(2));
                memberBean.setName(rs.getString(3));
                memberBean.setPassword(rs.getString(4));
                memberBean.setEmail(rs.getString(5));
                memberBean.setPhone(rs.getString(6));
                memberBean.setInterest(rs.getString(7));
                memberBean.setAge(rs.getString(8));
                memberList.add(memberBean);
            }
            
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(rs!=null) rs.close();
                if(pstmt!=null) pstmt.close();
                if(con!=null) con.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return memberList;
    }
}
 
 
 
Colored by Color Scripter

cs

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

package com.heeyeon.model;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
 
public class MemberDao {
    
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:xe";
    String id = "heeyeon0";
    String pw = "1234";
    
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    
    //jsp에서 각가의 페이지에서 했던 일을 MemberDao 의 메서드로 바꾸어 처리한다.
    public int insertMember(MemberBean memberBean) {
        int result = 0;
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url,id,pw);
            String sql = "INSERT INTO MEMBER03 VALUES (MEMBER_SEQ.NEXTVAL,?,?,?,?,?,?,?)";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, memberBean.getId());
            pstmt.setString(2, memberBean.getName());
            pstmt.setString(3, memberBean.getPassword());
            pstmt.setString(4, memberBean.getEmail());
            pstmt.setString(5, memberBean.getPhone());
            pstmt.setString(6, memberBean.getInterest());
            pstmt.setString(7, memberBean.getAge());
            result = pstmt.executeUpdate();
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(pstmt!=null) pstmt.close();
                if(con!=null) pstmt.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return result;
    }
    
    public ArrayList<MemberBean> showAllMember() {
        ArrayList<MemberBean> memberList = new ArrayList<MemberBean>();
        
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(url, id, pw);
            String sql = "SELECT * FROM MEMBER03";
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                MemberBean memberBean = new MemberBean();
                memberBean.setNo(rs.getInt(1));
                memberBean.setId(rs.getString(2));
                memberBean.setName(rs.getString(3));
                memberBean.setPassword(rs.getString(4));
                memberBean.setEmail(rs.getString(5));
                memberBean.setPhone(rs.getString(6));
                memberBean.setInterest(rs.getString(7));
                memberBean.setAge(rs.getString(8));
                memberList.add(memberBean);
            }
            
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(rs!=null) rs.close();
                if(pstmt!=null) pstmt.close();
                if(con!=null) con.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return memberList;
    }
}
 
 
Colored by Color Scripter

cs

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

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="preconnect" href="https://fonts.gstatic.com">
<link href="https://fonts.googleapis.com/css2?family=Noto+Sans+KR:wght@100;300;400;500;700;900&display=swap" rel="stylesheet">
<style>
    * {margin:0; padding:0;}
    body {
        font-family: "Noto Sans KR";
    }
    table {
        border: none;
        border-collapse: collapse;
        border-top: 1px solid #d9d9d9;
    }
    table tr th {
        background-color: #f8f8f8;
        
    }
    table tr th,table tr td {
        padding:15px;
        border-bottom: 1px solid #d9d9d9;
    }
    table tr td input[type="text"],table tr td input[type="password"], table tr td select {
        height: 30px;
        border: 1px solid #d9d9d9;
        padding:5px;
        border-radius: 3px;
        width:50%;
    }
    table tr td select {
        width:120px;
    }
 
    .formBox {
        width:1000px;
        margin: 100px auto;
    }
    .formBox h2 {
        margin-bottom: 50px;
        text-align: center;
    }
    .formBox .btns {
        text-align: center;
        margin-top: 50px;
    }
    .formBox .btns * {
        display: inline-block;
        width:150px;
        height: 50px;
        border: none;
        border-radius: 5px;
        box-shadow: 0 5px 5px rgba(0,0,0,0.1);
        font-size: 16px;
        font-weight: 500;
        background-color: rgb(109, 109, 109);
        color:#fff;
    }
    .formBox .btns #join {
        background-color: rgb(1, 121, 177);
    }
 
</style>
</head>
<body>
    <div class="formBox" id="join">
        <h2>회원가입</h2>
        <form action="member_join_action.jsp" method="POST">
            <table>
                <colgroup>
                    <col style="width:150px">
                    <col style="width:850px">
                </colgroup>
                <tbody>
                    <tr>
                        <th>ID</th>
                        <td><input type="text" name="id"></td>
                    </tr>
                    <tr>
                        <th>NAME</th>
                        <td><input type="text" name="name"></td>
                    </tr>
                    <tr>
                        <th>PASSWORD</th>
                        <td><input type="text" name="password"></td>
                    </tr>
                    <tr>
                        <th>E-MAIL</th>
                        <td><input type="text" name="email"></td>
                    </tr>
                    <tr>
                        <th>PHONE</th>
                        <td><input type="text" name="phone"></td>
                    </tr>
                    <tr>
                        <th>INTEREST</th>
                        <td>
                            <select name="interest" id="">
                                <option value="JAVA">JAVA</option>
                                <option value="PYTHON">PYTHON</option>
                                <option value="VUE">VUE</option>
                                <option value="HTML">HTML</option>
                                <option value="JSP">JSP</option>
                            </select>
                        </td>
                    </tr>
                    <tr>
                        <th>AGE</th>
                        <td>
                            <select name="age" id="">
                                <option value="10">10</option>
                                <option value="20">20</option>
                                <option value="30">30</option>
                                <option value="40">40</option>
                                <option value="50">50</option>
                            </select>
                        </td>
                    </tr>
                </tbody>
            </table>
            <div class="btns">
                <input type="submit" value="회원가입" id="join">
                <input type="reset" value="취소" id="cancel">
            </div>
        </form>
    </div>
</body>
</html>
Colored by Color Scripter

cs

member_join.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

<%@ page import="com.heeyeon.model.MemberDao"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
 
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
    <%
    request.setCharacterEncoding("UTF-8");
    %>
    <jsp:useBean id="memberBean" class="com.heeyeon.model.MemberBean" />
    <jsp:setProperty name="memberBean" property="*" />
 
    <%
    MemberDao memberDao = new MemberDao();
    int result = memberDao.insertMember(memberBean);
    //out.println("member03테이블에 영향을 미친 갯수는 : "+ result);
    if (result > 0) {
    %>
    <script>
        alert("회원가입이 완료 되었습니다.");
        location.href = "member_list.jsp";
    </script>
    <%
    } else {
    %>
    <script>
        alert("회원가입에 실패하였습니다.");
        location.href = "member_join.jsp";
    </script>
    <%
    }
    %>
</body>
</html>
 
 
 
 
 
Colored by Color Scripter

cs

member_join_action.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

<%@page import="com.heeyeon.model.MemberBean"%>
<%@page import="java.util.ArrayList"%>
<%@page import="com.heeyeon.model.MemberDao"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="preconnect" href="https://fonts.gstatic.com">
<link href="https://fonts.googleapis.com/css2?family=Noto+Sans+KR:wght@100;300;400;500;700;900&display=swap" rel="stylesheet">
<style>
    * {margin:0; padding:0;}
    body {
        font-family: "Noto Sans KR";
    }
    table {
        border: none;
        border-collapse: collapse;
        border-top: 1px solid #d9d9d9;
    }
    table tr th {
        background-color: #f8f8f8;
        
    }
    table tr th,table tr td {
        padding:15px;
        border-bottom: 1px solid #d9d9d9;
    }
    table tr td input[type="text"],table tr td input[type="password"], table tr td select {
        height: 30px;
        border: 1px solid #d9d9d9;
        padding:5px;
        border-radius: 3px;
        width:50%;
    }
    table tr td select {
        width:120px;
    }
 
    .formBox {
        width:1000px;
        margin: 100px auto;
    }
    .formBox h2 {
        margin-bottom: 50px;
        text-align: center;
    }
    .formBox .btns {
        text-align: center;
        margin-top: 50px;
    }
    .formBox .btns * {
        display: inline-block;
        width:150px;
        height: 50px;
        border: none;
        border-radius: 5px;
        box-shadow: 0 5px 5px rgba(0,0,0,0.1);
        font-size: 16px;
        font-weight: 500;
        background-color: rgb(109, 109, 109);
        color:#fff;
    }
    .formBox .btns #join {
        background-color: rgb(1, 121, 177);
    }
    #list tbody td {
        text-align:center;
    }
 
</style>
</head>
<body>
    <% 
        MemberDao memberDao = new MemberDao();
        ArrayList<MemberBean> memberList = memberDao.showAllMember();
    %>
    <div class="formBox" id="list">
        <h2>회원가입</h2>
        <form action="" method="POST">
            <table>
                <colgroup>
                    <col style="width:50px">
                    <col style="width:150px">
                    <col style="width:100px">
                    <col style="width:100px">
                    <col style="width:250px">
                    <col style="width:200px">
                    <col style="width:50px">
                    <col style="width:50px">
                </colgroup>
                <thead>
                    <tr>
                        <th>NO</th>
                        <th>ID</th>
                        <th>NAME</th>
                        <th>PASSWORD</th>
                        <th>E-MAIL</th>
                        <th>PHONE</th>
                        <th>INTEREST</th>
                        <th>AGE</th>
                    </tr>
                </thead>
                <tbody>
                    <%
                        for(int i = 0; i < memberList.size(); i++){
                            MemberBean memberBean = memberList.get(i);
                    %>
                            <tr>
                                <td><%=memberBean.getNo() %></td>
                                <td><%=memberBean.getId() %></td>
                                <td><%=memberBean.getName() %></td>"D:/jsp/MemberDao.java"
                                <td><%=memberBean.getPassword() %></td>
                                <td><%=memberBean.getEmail() %></td>
                                <td><%=memberBean.getPhone() %></td>
                                <td><%=memberBean.getInterest() %></td>
                                <td><%=memberBean.getAge() %></td>
                            </tr>
                    <%
                        }
                    %>
                </tbody>
            </table>
        </form>
    </div>
</body>
</html>
 
 
 
 
 
Colored by Color Scripter

cs