Rylah's Study & Daily Life

11. JSP, DataBase (3) - JSP CRUD 본문

Web Study/JSP

11. JSP, DataBase (3) - JSP CRUD

Rylah 2022. 3. 20. 16:54

1. DB Setting

DB 설명
DB Name basicjsp
DB ID test
DB PW  1234
Tool HeidiSQL, Eclipse
TableName member

 

- Table : Member

1
2
3
4
5
6
create table member(
    id varchar(50) not null primary key,
    passwd varchar(16) not null,
    name varchar(10) not null,
    reg_date datetime not null
);
 cs
Row Setting Etc
ID varchar(50) NOT NULL, PRIMARY KEY
PASSWD varchar(16) NOT NULL
NAME varchar(10) NOT NULL
REG_DATE DATETIME NOT NULL

2. Insert

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert Record</title>
</head>
<body>
    <h2>member 테이블에 레코드 추가</h2>
    
    <form method="post" action="insertTestPro.jsp">
        아이디 : <input type="text" name="id" maxlength="50"> <br>
        패스워드 : <input type="password" name="passwd" maxlength="16"> <br>
        이름 : <input type="text" name="name" maxlength="10"> <br>
        <input type="submit" value="입력 완료">
    </form>
</body>
</html>
 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
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
    <% request.setCharacterEncoding("UTF-8"); %>
    <%
        String id = request.getParameter("id");
        String passwd = request.getParameter("passwd");
        String name = request.getParameter("name");
        Timestamp register = new Timestamp(System.currentTimeMillis());
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        String str = "";
        
        try {
            String jdbcUrl = "jdbc:mysql://localhost:3306/basicjsp?serverTimezone=UTC&useSSL=false";
            String dbId = "test";
            String dbPass = "1234";
            
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(jdbcUrl, dbId, dbPass);
            
            String sql = "insert into member values (?, ?, ? ,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            pstmt.setString(2, passwd);
            pstmt.setString(3, name);
            pstmt.setTimestamp(4, register);
            pstmt.executeUpdate();
            
            str = "member 테이블에 새로운 데이터를 추가했습니다.";
        } catch(Exception ex) {
            ex.printStackTrace();
            
            str = "member 테이블에 새로운 데이터를 추가하는데 실패했습니다.";
        } finally {
            if (pstmt != null)
                try { pstmt.close(); } catch(SQLException sqle){}
            if (conn != null)
                try { conn.close(); } catch(SQLException sqle){}
        }
        
    %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert Data</title>
</head>
<body>
    <%=str %>
</body>
</html>
 cs

1
select * from member;
 cs

 

3. Select

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
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Select Record</title>
</head>
<body>
    <h2>member 테이블의 레코드 표시</h2>
    <table border="1">
        <tr>
            <td width="100">아이디</td>
            <td width="100">패스워드</td>
            <td width="100">이름</td>
            <td width="250">가입일자</td>
        </tr>
    <%
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            String jdbcUrl = "jdbc:mysql://localhost:3306/basicjsp?serverTimezone=UTC&useSSL=false";
            String dbId = "test";
            String dbPass = "1234";
            
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(jdbcUrl, dbId, dbPass);
            
            String sql = "select * from member";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            
            while(rs.next()) {
                String id = rs.getString("id");
                String passwd = rs.getString("passwd");
                String name = rs.getString("name");
                Timestamp register = rs.getTimestamp("reg_date");
    %>
        <tr>
            <td width="100"><%=id %></td>
            <td width="100"><%=passwd %></td>
            <td width="100"><%=name %></td>
            <td width="250"><%=register %></td>
        </tr>
    <%         }
        } catch(Exception ex) {
            ex.printStackTrace();
        } finally {
            if (rs != null)
                try { rs.close(); } catch(SQLException sqle) {}
            if (pstmt != null)
                try { pstmt.close(); } catch(SQLException sqle) {}
            if (conn != null)
                try { conn.close(); } catch(SQLException sqle) {}
        }
    %>        
    </table>
    
</body>
</html>
 cs

4. Update

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Update Query</title>
</head>
<body>
    <h2>member Table Data Update</h2>
    
    <form method="post" action="updateTestPro.jsp">
        아이디 : <input type="text" name="id" maxlength="50"><br>
        패스워드 : <input type="password" name="passwd" maxlength="16"> <br>
        변경할 이름 : <input type="text" name="name" maxlength=10> <br>
        <input type="submit" value="입력 완료">
        
    
    </form>
</body>
</html>
 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
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
 
<% request.setCharacterEncoding("UTF-8"); %>
 
<%
    String id = request.getParameter("id");
    String passwd = request.getParameter("passwd");
    String name = request.getParameter("name");
    
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    
    try {
        String jdbcUrl = "jdbc:mysql://localhost:3306/basicjsp?serverTimezone=UTC&useSSL=false";
        String dbId = "test";
        String dbPass = "1234";
        
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(jdbcUrl, dbId, dbPass);
        
        String sql = "select id, passwd from member where id = ?";
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, id);
        rs = pstmt.executeQuery();
        
        if (rs.next()) {
            String rId = rs.getString("id");
            String rPasswd = rs.getString("passwd");
 
            if (id.equals(rId) && passwd.equals(rPasswd)) {
                sql = "update member set name = ? where id = ?";
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, name);
                pstmt.setString(2, id);
                pstmt.executeUpdate();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Update Table</title>
</head>
<body>
    테이블의 레코드를 수정했습니다.
</body>
</html>
<%
            } else
                out.println("패스워드가 틀렸습니다.");
        } else
            out.println("아이디가 틀렸습니다.");
    } catch(Exception ex) {
        ex.printStackTrace();
    } finally {
        if (rs != null)
            try { rs.close(); } catch(SQLException sqle) {}
        if (pstmt != null)
            try { pstmt.close(); } catch(SQLException sqle) {}
        if (conn != null)
            try { conn.close(); } catch(SQLException sqle) {}
        }    
%>
    
 
 cs

5. Delete

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>레코드 삭제</title>
</head>
<body>
    <h2>member 테이블의 레코드 삭제</h2>
    
    <form method="post" action="deleteTestPro.jsp">
        아이디 : <input type="text" name="id" maxlength="50"> <br>
        패스워드 : <input type="password" name="passwd" maxlength="16"> <br>
        <input type="submit" value="입력 완료">
    </form>
</body>
</html>
 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
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<% request.setCharacterEncoding("UTF-8"); %>
<%
    String id = request.getParameter("id");
    String passwd = request.getParameter("passwd");
    
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    
    try {
        String jdbcUrl = "jdbc:mysql://localhost:3306/basicjsp?serverTimezone=UTC&useSSL=false";
        String dbId = "test";
        String dbPass = "1234";
        
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(jdbcUrl, dbId, dbPass);
        
        String sql = "select id, passwd from member where id = ?";
        pstmt = conn.prepareStatement(sql);
        
        pstmt.setString(1, id);
        rs = pstmt.executeQuery();
        
        
        if (rs.next()) {
            String rId = rs.getString("id");
            String rPasswd = rs.getString("passwd");
            
            if (id.equals(rId) && passwd.equals(rPasswd)) {
                String newsql = "delete from member where id = ?";
                pstmt = conn.prepareStatement(newsql);
                pstmt.setString(1, id);
                pstmt.executeUpdate();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Delete Data</title>
</head>
<body>
    member 테이블의 레코드를 삭제했습니다.
</body>
</html>
<%
            } else
                out.println("패스워드가 틀렸습니다.");
        } else 
            out.println("아이디가 틀렸습니다.");
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        if (rs != null)
            try { rs.close(); } catch(SQLException ex){}
        if (pstmt != null)
            try { pstmt.close(); } catch(SQLException ex){}
        if (conn != null)
            try { conn.close(); } catch(SQLException ex){}
    }
%>
 cs

 

실제라면 DBConnection을 따로 뺴두고 쓰는 것이 더 맞겠으나 기본을 익힌다는 생각으로 일일히 작성했다.

저작자표시 비영리 변경금지

'Web Study > JSP' 카테고리의 다른 글

12. JSP - Cookie, Session  (0) 2022.03.21
11. JSP, DataBase (4) - DBCP API  (0) 2022.03.20
11. JSP, DataBase (2) - Basic Query  (0) 2022.03.20
11. JSP, DataBase (1) - MySQL  (0) 2022.03.20
10. Java Bean  (0) 2022.03.20
'Web Study/JSP' Related Articles more