-
1. SQL 파일 생성
-- 파일명: sql.sql CREATE TABLE MEM( ID VARCHAR2(10), NAME VARCHAR2(15), PWD VARCHAR2(20), PHONE VARCHAR2(15) ); INSERT INTO MEM VALUES('SOMI','이소미','1234','010-1234-1234'); INSERT INTO MEM VALUES('SANG','전상오','1234','010-5555-6666'); INSERT INTO MEM VALUES('LIGHT','김빛나','1234','010-2222-3333');
2. JSP 파일 생성
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@page import="java.sql.ResultSet" %> <%@page import="java.sql.PreparedStatement" %> <%@page import="java.sql.Connection" %> <%@page import="java.sql.DriverManager" %> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>MemberMGR.jsp</title> <%! Connection con=null; PreparedStatement pstmt=null; ResultSet rs=null; String driver="oracle.jdbc.OracleDriver"; String url="jdbc:oracle:thin:@localhost:1521:xe"; String uid="scott"; String pass= "tiger"; String sql="select*from mem"; %> </head> <body> <!-- MEM테이블의 레코드를 모두 읽어와서 화면에 레코드를 행별로 필드를 열에 맞춰서 표시 --> <table width="800" border="1"> <tr><th>이름</th><th>아이디</th><th>암호</th><th>전화번호</th> </tr> <% try{ // 테이블을 읽어오기 위해 데이터베이스에 연결함 Class.forName(driver); con=DriverManager.getConnection(url, uid, pass); pstmt=con.prepareStatement(sql); // sql과 con을 pstmt에 장착 rs=pstmt.executeQuery(); // 장착된 sql 실행 후 결과를 rs에 저장 while(rs.next()) { out.println("<tr>"); out.println("<td>"+rs.getString("name")+"</td>"); out.println("<td>"+rs.getString("id")+"</td>"); out.println("<td>"+rs.getString("pwd")+"</td>"); out.println("<td>"+rs.getString("phone")+"</td>"); out.println("</tr>"); } } catch(Exception e){e.printStackTrace(); } finally{ try{ if(rs!=null) rs.close(); if(pstmt!=null) pstmt.close(); if(con!=null) con.close(); } catch(Exception e){e.printStackTrace();} } %> </table> </body> </html>
JSP 파일 실행. 데이터베이스 정보를 불러오지 못함 3. JSP파일 데이터베이스에 연결하기
프로젝트 안 WEB-INF 폴더에 lib 우클릭 
import 클릭 
General에 File System 클릭 후 Next 
Browse..클릭 
파일 경로: C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib 
연결할 데이터베이스 클릭 후 Finish 
Web App Libraries에 선택한 데이터베이스 추가된 것을 확인 
JSP 파일 실행. 데이터베이스 정보 가져옴
main문 작성
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@page import="java.sql.ResultSet" %> <%@page import="java.sql.PreparedStatement" %> <%@page import="java.sql.Connection" %> <%@page import="java.sql.DriverManager" %> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>MemberMGR.jsp</title> <%! Connection con=null; PreparedStatement pstmt=null; ResultSet rs=null; String driver="oracle.jdbc.OracleDriver"; String url="jdbc:oracle:thin:@localhost:1521:xe"; String uid="scott"; String pass= "tiger"; String sql="select*from mem"; %> </head> <body> <table width="800" border="1"> <tr><th>이름</th><th>아이디</th><th>암호</th><th>전화번호</th><th>수정</th><th>삭제</th> </tr> try{ Class.forName(driver); con=DriverManager.getConnection(url, uid, pass); pstmt=con.prepareStatement(sql); rs=pstmt.executeQuery(); while(rs.next()) { out.println("<tr>"); out.println("<td>"+rs.getString("name")+"</td>"); out.println("<td>"+rs.getString("id")+"</td>"); out.println("<td>"+rs.getString("pwd")+"</td>"); out.println("<td>"+rs.getString("phone")+"</td>"); out.println("<td align='center'><a href='updateForm.jsp?id="+rs.getString("id")+"'>수정</a></td>"); out.println("<td align='center'><a href='delete.jsp?="+rs.getString("id")+"'>삭제</a></td>"); out.println("</tr>"); } } catch(Exception e){e.printStackTrace(); } finally{ try{ if(rs!=null) rs.close(); if(pstmt!=null) pstmt.close(); if(con!=null) con.close(); } catch(Exception e){e.printStackTrace();} } %> </table> <a href="InsertForm.jsp">멤버 추가</a> </body> </html>
1. insert
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>InsertForm.jsp</title> </head> <body> <h2>회원 정보 입력 폼</h2> <form method="get" action="insertMember_do.jsp"> <table> <tr><td>이름</td><td> <input type="text" name="name" size="20"></td></tr> <tr><td>아이디</td><td> <input type="text" name="userid" size="20"></td></tr> <tr><td>비밀번호</td><td> <input type="password" name="pwd" size="20"></td></tr> <tr><td>전화번호</td><td> <input type="text" name="phone" size="11"></td></tr> <tr><td><input type="submit" value="전송"></td> <td><input type="reset" value="취소"></td></tr> </table> </form> </body> </html><%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@page import="java.sql.PreparedStatement" %> <%@page import="java.sql.Connection" %> <%@page import="java.sql.DriverManager" %> <% // inserMember_do.jsp Connection con=null; PreparedStatement pstmt=null; String driver="oracle.jdbc.OracleDriver"; String url="jdbc:oracle:thin:@localhost:1521:xe"; String uid="scott"; String pass= "tiger"; String sql="insert into mem(id,name,pwd,phone) values(?, ?, ?, ?)"; String userid=request.getParameter("userid"); String name=request.getParameter("name"); String pwd=request.getParameter("pwd"); String phone=request.getParameter("phone"); try { Class.forName(driver); con=DriverManager.getConnection(url,uid,pass); pstmt = con.prepareStatement(sql); pstmt.setString(1, userid); pstmt.setString(2, name); pstmt.setString(3, pwd); pstmt.setString(4, phone); pstmt.executeUpdate(); } catch (Exception e){e.printStackTrace(); } finally{ try{ if(pstmt!=null) pstmt.close(); if(con!=null) con.close(); } catch(Exception e){e.printStackTrace();} } response.sendRedirect("MemberMGR.jsp"); %>
2. update
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@page import="java.sql.DriverManager" %> <%@page import="java.sql.ResultSet" %> <%@page import="java.sql.PreparedStatement" %> <%@page import="java.sql.Connection" %> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>UpdateForm.jsp</title> <% Connection con=null; PreparedStatement pstmt=null; ResultSet rs=null; String driver="oracle.jdbc.OracleDriver"; String url="jdbc:oracle:thin:@localhost:1521:xe"; String sql="select*from mem where id=?"; %> </head> <body> <% String userid=request.getParameter("id"); String name=null ,phone=null; try { Class.forName(driver); con=DriverManager.getConnection(url,"scott","tiger"); pstmt = con.prepareStatement(sql); pstmt.setString(1, userid); rs=pstmt.executeQuery(); if(rs.next()){ name=rs.getString("name"); phone=rs.getString("phone"); } } catch (Exception e){e.printStackTrace(); } finally{ try{ if(rs!=null) rs.close(); if(pstmt!=null) pstmt.close(); if(con!=null) con.close(); } catch(Exception e){e.printStackTrace();} } %> <h2>회원 정보 수정 폼</h2> <form method="post" action="updateMember_do.jsp"> <table> <tr><td>이름</td><td> <input type="text" name="name" size="20" value="<%=name %>"></td></tr> <tr><td>아이디</td><td> <input type="text" name="userid" size="20" value="<%=userid %>"></td></tr> <tr><td>비밀번호</td><td> <input type="password" name="pwd" size="20"></td></tr> <tr><td>전화번호</td><td> <input type="text" name="phone" size="11" value="<%=phone %>"></td></tr> <input type="hidden" name="updateid" value="<%=userid %>"/> <tr><td><input type="submit" value="정보수정"></td><td><input type="reset" value="취소"></td></tr> </table> </form> </body> </html><%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@page import="java.sql.PreparedStatement" %> <%@page import="java.sql.Connection" %> <%@page import="java.sql.DriverManager" %> <% // updateMember_do.jsp request.setCharacterEncoding("UTF-8"); Connection con=null; PreparedStatement pstmt=null; String driver="oracle.jdbc.OracleDriver"; String url="jdbc:oracle:thin:@localhost:1521:xe"; String name=request.getParameter("name"); String userid=request.getParameter("userid"); // updateform에서 수정이 되었을 수도 있는 userid String pwd=request.getParameter("pwd"); String phone=request.getParameter("phone"); String updateid=request.getParameter("updateid"); // hidden으로 숨어서 전송된 원래 userid String sql="update mem set id=?, name=?, pwd=?, phone=? where id=?"; try { Class.forName(driver); con=DriverManager.getConnection(url,"scott","tiger"); pstmt = con.prepareStatement(sql); pstmt.setString(1, userid); pstmt.setString(2, name); pstmt.setString(3, pwd); pstmt.setString(4, phone); pstmt.setString(5, updateid); pstmt.executeUpdate(); } catch (Exception e){e.printStackTrace(); } finally{ try{ if(pstmt!=null) pstmt.close(); if(con!=null) con.close(); } catch(Exception e){e.printStackTrace();} } response.sendRedirect("MemberMGR.jsp"); %>
3. delete
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@page import="java.sql.PreparedStatement" %> <%@page import="java.sql.Connection" %> <%@page import="java.sql.DriverManager" %> <% Connection con=null; PreparedStatement pstmt=null; String driver="oracle.jdbc.OracleDriver"; String url="jdbc:oracle:thin:@localhost:1521:xe"; String id=request.getParameter("id"); String sql = "delete from mem where id=?"; try { Class.forName(driver); con=DriverManager.getConnection(url,"scott","tiger"); pstmt= con.prepareStatement(sql); pstmt.setString(1, id); pstmt.executeUpdate(); } catch (Exception e){e.printStackTrace(); } finally{ try{ if(pstmt!=null) pstmt.close(); if(con!=null) con.close(); } catch(Exception e){e.printStackTrace();} } response.sendRedirect("MemberMGR.jsp"); %>
홍길서 정보 '삭제' 클릭한 결과 'WEB > JSP' 카테고리의 다른 글
[JSP] bean (0) 2022.09.09 [JSP] session을 이용한 loginform (0) 2022.09.09 [JSP] cookie, session, removeAttribute (0) 2022.09.09 [JSP] servlet, action tag form (0) 2022.09.09 [JSP] login form, forward form, server object (0) 2022.09.09