티스토리 뷰

JSP

[JSP] JDBC

먹태 2018. 7. 18. 17:44
JDBC (Java DataBase Connectivity )
- 다양한 데이터 베이스에 대해서 별도의 프로그램을 만들 필요 없이 해당 데이터 베이스의 jdbc를 이용하면 하나의 프로그램으로 
  데이터베이스를 관리할 수 있다.


데이터 베이스 연결순서
 ▶ 연결
     - JDBC 드라이버 로드 - DriverManager
     - 데이터베이스 연결 - Connection (커넥션 객체생성)
      
 ▶ SQL문 실행 
     - Statement, PreparedStatement (PrepareStatement가 더 유용)
     - exexuteQuery() : sql문 실행 후 여려개의 결과값이 생기는 경우 ex ) select
     - executeUpdate(): sql문 실행 후 테이블의 내용만 변경되는 경우 ex) insert, delete, update

 ▶ 결과전송 
    - ResultSet : 쿼리실행 결과 값을 받는다

▶ close처리






간단예제(회원가입, 로그인, 정보수정, 로그아웃)
- DB : Oracle SQL developer 사용





<join.html>

  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. <meta charset="UTF-8">
  5. <title>Insert title here</title>
  6. </head>
  7. <body>
  8.  
  9.     <form method="post" action="joinOk">
  10.         이름 : <input type="text" name="name" size ="5"><br>
  11.         아이디 : <input type="text" name="id" size ="5"><br>
  12.         패스워드 : <input type="password" name="pw" size ="5"><br>
  13.         전화번호 : <select name="phone1">
  14.                     <option value="010">010</option>
  15.                     <option value="011">011</option>
  16.                     <option value="012">012</option>
  17.                     <option value="031">031</option>
  18.                     <option value="02">02</option>
  19.         </select>-
  20.         <input type="text" name="phone2" size="5"> - <input type="text" name="phone3" size="5"><br>
  21.         <input type="radio" name="gender" value="men">남
  22.         <input type="radio" name="gender" value="women">여
  23.         <br>
  24.         <input type="submit" value="회원가입">
  25.         <input type="reset" value="취소">
  26.        
  27.     </form>
  28.  
  29. </body>
  30. </html>



폼 입력후 회원가입 버튼 누르면 JoinOk 실행







<JoinOk.java>

  1. package com.javalex.ex;
  2. import java.io.IOException;
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.Statement;
  6. import javax.servlet.ServletException;
  7. import javax.servlet.annotation.WebServlet;
  8. import javax.servlet.http.HttpServlet;
  9. import javax.servlet.http.HttpServletRequest;
  10. import javax.servlet.http.HttpServletResponse;
  11.  
  12. /**
  13.  * Servlet implementation class JoinOk
  14.  */
  15. @WebServlet("/joinOk")
  16. public class JoinOk extends HttpServlet {
  17.     private static final long serialVersionUID = 1L;
  18.    
  19.     private Connection connection;
  20.     private Statement stmt;
  21.    
  22.     private String name, id, pw, phone1, phone2, phone3, gender;
  23.    
  24.     /**
  25.      * @see HttpServlet#HttpServlet()
  26.      */
  27.     public JoinOk() {
  28.         super();
  29.         // TODO Auto-generated constructor stub
  30.        
  31.     }
  32.  
  33.     /**
  34.      * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
  35.      */
  36.     protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  37.         System.out.println("doGet");
  38.         actionDo(request, response);
  39.     }
  40.  
  41.     /**
  42.      * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
  43.      */
  44.     protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  45.         System.out.println("doPost");
  46.         actionDo(request, response);
  47.     }
  48.    
  49.     private void actionDo(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  50.        
  51.         request.setCharacterEncoding("utf-8");
  52.        
  53.         name = request.getParameter("name");
  54.         id = request.getParameter("id");
  55.         pw = request.getParameter("pw");
  56.         phone1 = request.getParameter("phone1");
  57.         phone2 = request.getParameter("phone2");
  58.         phone3 = request.getParameter("phone3");
  59.         gender = request.getParameter("gender");
  60.        
  61.         String query = "insert into member values('" + name + "', '" + id + "', '" + pw + "', '" + phone1 + "', '" +         phone2 + "', '"+ phone3 + "', '" + gender + "')";
  62.        
  63.         try {
  64.             Class.forName("oracle.jdbc.driver.OracleDriver");
  65.             connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe" , "hr" , "hrpw");
  66.             stmt = connection.createStatement();
  67.             int i = stmt.executeUpdate(query);
  68.             if(i == 1){
  69.                 System.out.println("insert success");
  70.                 response.sendRedirect("joinResult.jsp");
  71.             } else {
  72.                 System.out.println("insert fail");
  73.                 response.sendRedirect("join.html");
  74.             }
  75.         } catch (Exception e) {
  76.             e.printStackTrace();
  77.         } finally {
  78.             try {
  79.                 if(stmt != null) stmt.close();
  80.                 if(connection != null) connection.close();
  81.             } catch (Exception e) {}
  82.         }
  83.     }
  84. } 



<joinResult.jsp>

  1. <%@ page language="java" contentType="text/html; charset=UTF-8"
  2.     pageEncoding="UTF-8"%>
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
  4. <html>
  5. <head>
  6. <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  7. <title>Insert title here</title>
  8. </head>
  9. <body>
  10.     <p>회원가입이 완료되었습니다</p>
  11.     <a href="login.html">로그인하러가기</a>
  12. </body>
  13. </html>





- DB에도 입력됨

- 로그인하러가기 클릭







<login.html>

  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. <meta charset="UTF-8">
  5. <title>Insert title here</title>
  6. </head>
  7. <body>
  8.     <form method="post" action="LoginOk">
  9.         아이디 : <input type ="text" name="id" size= "5"><br>
  10.         비밀번호 : <input type ="password" name="pw" size= "5"><br>
  11.         <input type="submit" value="로그인">
  12.     </form>
  13. </body>
  14. </html>


로그인 클릭 -> LoginOk실행




<LoginOk.java>


  1. package com.javalex.ex;
  2. import java.io.IOException;
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.ResultSet;
  6. import java.sql.Statement;
  7. import javax.servlet.ServletException;
  8. import javax.servlet.annotation.WebServlet;
  9. import javax.servlet.http.HttpServlet;
  10. import javax.servlet.http.HttpServletRequest;
  11. import javax.servlet.http.HttpServletResponse;
  12. import javax.servlet.http.HttpSession;
  13.  
  14. /**
  15.  * Servlet implementation class LoginOk
  16.  */
  17. @WebServlet("/LoginOk")
  18. public class LoginOk extends HttpServlet {
  19.     private static final long serialVersionUID = 1L;
  20.    
  21.     private Connection conn;
  22.     private Statement stmt;
  23.     private ResultSet rs;
  24.     private String name,id,pw,phone1,phone2,phone3,gender;
  25.    
  26.     public LoginOk() {
  27.         super();
  28.     }
  29.  
  30.    
  31.     protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  32.         // TODO Auto-generated method stub
  33.         response.getWriter().append("Served at: ").append(request.getContextPath());
  34.     }
  35.  
  36.     protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  37.         // TODO Auto-generated method stub
  38.         actionDo(request, response);
  39.     }
  40.  
  41.     private void actionDo(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  42.        
  43.         request.setCharacterEncoding("utf-8");
  44.         id = request.getParameter("id");
  45.         pw = request.getParameter("pw");
  46.        
  47.         String query = "SELECT * FROM MEMBER WHERE ID = '" + id + "' AND PW = '" + pw + "'" ;
  48.        
  49.        
  50.        
  51.         try {
  52.             Class.forName("oracle.jdbc.driver.OracleDriver");
  53.             conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hrpw");
  54.             stmt = conn.createStatement();
  55.             rs = stmt.executeQuery(query);
  56.            
  57.             while(rs.next()) { //데이터가 있다면
  58.                 name = rs.getString("name"); //select에서 가져온걸 name에 저장
  59.                 id = rs.getString("id");
  60.                 pw = rs.getString("pw");
  61.                 phone1 = rs.getString("phone1");
  62.                 phone2 = rs.getString("phone2");
  63.                 phone3 = rs.getString("phone3");
  64.                 gender = rs.getString("gender");
  65.                
  66.                
  67.                 HttpSession httpSession = request.getSession();
  68.                 httpSession.setAttribute("name", name);
  69.                 httpSession.setAttribute("id", id);
  70.                 httpSession.setAttribute("pw", pw);
  71.                
  72.                 response.sendRedirect("loginResult.jsp"); //위에 set세션들을 loginResult.jsp로 보냄
  73.             }
  74.         } catch (Exception e) {
  75.             // TODO Auto-generated catch block
  76.             e.printStackTrace();
  77.         } finally {
  78.                 try {                  
  79.                     if(rs != null) rs.close();
  80.                     if(stmt != null) stmt.close();
  81.                     if(conn != null) conn.close();
  82.                 } catch (Exception e2) {
  83.                     // TODO Auto-generated catch block
  84.                     e2.printStackTrace();
  85.                 }
  86.         }     
  87.     }
  88. }




<loginResult.jsp>

  1. <%@ page language="java" contentType="text/html; charset=UTF-8"
  2.     pageEncoding="UTF-8"%>
  3. <%!
  4.     String id, pw, name;
  5. %>
  6. <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
  7. <html>
  8. <head>
  9. <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  10. <title>Insert title here</title>
  11. </head>
  12. <body>
  13.    
  14.     <%
  15.         name = (String)session.getAttribute("name");
  16.         id = (String)session.getAttribute("id");
  17.         pw = (String)session.getAttribute("pw");
  18.     %>
  19.    
  20.     <%=name %> 님 안녕하세요
  21.    
  22.    
  23.     <!--
  24.     <%=session.getAttribute("name") %> 님 환영합니다
  25.     나는 요렇게 했는데 강좌에서는 위에방법으로 함
  26.      -->
  27.    
  28.    
  29.     <a href ="modify.jsp">회원정보수정</a>
  30.  
  31. </body>
  32. </html>





회원정보수정 클릭 -> modify.jsp이동







<modify.jsp>

  1. <%@page import="java.sql.DriverManager"%>
  2. <%@page import="java.sql.ResultSet"%>
  3. <%@page import="java.sql.Statement"%>
  4. <%@page import="java.sql.Connection"%>
  5. <%@ page language="java" contentType="text/html; charset=UTF-8"
  6.     pageEncoding="UTF-8"%>
  7.  
  8. <%!
  9.     Connection conn;
  10.     Statement stmt;
  11.     ResultSet rs;
  12.     String name, id, pw, phone1, phone2, phone3, gender;
  13. %>
  14.  
  15. <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
  16. <html>
  17. <head>
  18. <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  19. <title>회원정보수정</title>
  20. </head>
  21. <body>
  22.  
  23. <h2>정보수정페이지</h2>
  24.  
  25.     <% 
  26.         id = (String)session.getAttribute("id");
  27.         name = (String)session.getAttribute("name");
  28.         String query = "SELECT * FROM MEMBER WHERE ID = '" + id + "'";
  29.        
  30.         Class.forName("oracle.jdbc.driver.OracleDriver");
  31.         conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe" , "hr" , "hrpw");
  32.         stmt = conn.createStatement();
  33.         rs = stmt.executeQuery(query);
  34.  
  35.         while(rs.next()){
  36.             name = rs.getString("name");
  37.             id = rs.getString("id");
  38.             pw = rs.getString("pw");
  39.             phone1 = rs.getString("phone1");
  40.             phone2 = rs.getString("phone2");
  41.             phone3 = rs.getString("phone3");
  42.             gender = rs.getString("gender");
  43.         }
  44.     %>
  45.  
  46.     <form action="ModifyOk" method="post">
  47.         이름 : <input type="text" name="name" value=<%=name %>><br>
  48.         아이디 : <input type="text" name="id" value=<%=id %>><br>
  49.         패스워드 : <input type="password" name="pw" size ="10"><br>
  50.         전화번호 : <select name="phone1">
  51.                     <option value="010">010</option>
  52.                     <option value="011">011</option>
  53.                     <option value="012">012</option>
  54.                     <option value="031">031</option>
  55.                     <option value="02">02</option>
  56.         </select>-
  57.         <input type="text" name="phone2" size="5" value=<%=phone2 %>> - <input type="text" name="phone3" size="5" value=<%=phone3 %>><br>
  58.         <%
  59.             if(gender.equals("women")){
  60.         %>
  61.             <input type="radio" name="gender" value="women" checked="checked">여 &nbsp;<input type="radio" name="gender" value="men">남
  62.         <%
  63.             } else {               
  64.         %> 
  65.             <input type="radio" name="gender" value="women">여 &nbsp;<input type="radio" name="gender" value="men" checked="checked">남
  66.         <%
  67.             }
  68.         %>
  69.         <br>
  70.         <input type="submit" value="정보수정">
  71.         <input type="reset" value="취소">
  72.     </form>

  74. </body>
  75. </html>










회원번호 바꿔서 정보수정 클릭 -> ModifyOk실행




< ModifyOk.java>

  1. package com.javalex.ex;
  2. import java.io.IOException;
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7. import javax.servlet.ServletException;
  8. import javax.servlet.annotation.WebServlet;
  9. import javax.servlet.http.HttpServlet;
  10. import javax.servlet.http.HttpServletRequest;
  11. import javax.servlet.http.HttpServletResponse;
  12. import javax.servlet.http.HttpSession;
  13.  
  14. /**
  15.  * Servlet implementation class ModifyOk
  16.  */
  17. @WebServlet("/ModifyOk")
  18. public class ModifyOk extends HttpServlet {
  19.     private static final long serialVersionUID = 1L;
  20.    
  21.     private Connection conn;
  22.     private Statement stmt;
  23.     String name, id, pw, phone1, phone2, phone3, gender;
  24.     HttpSession session;
  25.    
  26.     public ModifyOk() {
  27.         super();
  28.        
  29.     }

  31.     protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  32.        
  33.         response.getWriter().append("Served at: ").append(request.getContextPath());
  34.     }
  35.  
  36.    
  37.     protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  38.  
  39.         actionDo(request, response);
  40.     }
  41.    
  42.     private void actionDo(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  43.         request.setCharacterEncoding("utf-8");
  44.         session = request.getSession();
  45.        
  46.         name = request.getParameter("name");
  47.         id = request.getParameter("id");
  48.         pw = request.getParameter("pw");
  49.         phone1 = request.getParameter("phone1");
  50.         phone2 = request.getParameter("phone2");
  51.         phone3 = request.getParameter("phone3");
  52.         gender = request.getParameter("gender");
  53.    
  54.         String query = "UPDATE MEMBER SET NAME = '" + name + "', ID = '" + id + "', PW = '" + pw + "', PHONE1 = '" + phone1 + "', PHONE2 = '" + phone2 + "',"
  55.                 + "PHONE3 = '" + phone3 + "', GENDER = '" + gender + "'";
  56.    
  57.    
  58.         try {
  59.             Class.forName("oracle.jdbc.driver.OracleDriver");
  60.             conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe" , "hr" , "hrpw");
  61.             stmt = conn.createStatement();
  62.             int i = stmt.executeUpdate(query);
  63.            
  64.             if(i == 1) {
  65.                 System.out.println("수정완료");
  66.                 session.setAttribute("name", name);
  67.                 response.sendRedirect("modifyResult.jsp");
  68.             }else {
  69.                 System.out.println("수정실패");
  70.                 response.sendRedirect("modify.jsp");
  71.             }
  72.         } catch (Exception e) {
  73.             e.printStackTrace();
  74.         } finally {
  75.             try {
  76.                 if(stmt != null)stmt.close();
  77.                 if(conn != null) conn.close();
  78.                 } catch (SQLException e) {
  79.                     e.printStackTrace();
  80.                 }
  81.         }
  82.     }
  83. }




<modifyResult.jsp>

  1. <%@ page language="java" contentType="text/html; charset=UTF-8"
  2.     pageEncoding="UTF-8"%>
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
  4. <html>
  5. <head>
  6. <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  7. <title>Insert title here</title>
  8. </head>
  9. <body>
  10.     <%=session.getAttribute("name") %> 님 정보수정이 완료되었습니다.
  11.     <a href ="modify.jsp">회원정보수정</a>
  12.     <a href ="logout.jsp">로그아웃</a>
  13. </body>
  14. </html>









- DB도 수정됨
- 로그아웃 클릭 







<logout.jsp>

  1. <%@ page language="java" contentType="text/html; charset=UTF-8"
  2.     pageEncoding="UTF-8"%>
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
  4. <html>
  5. <head>
  6. <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  7. <title>Insert title here</title>
  8. </head>
  9. <body>
  10.     <%
  11.         session.invalidate();
  12.         response.sendRedirect("login.html");
  13.     %>
  14. </body>
  15. </html>




로그아웃되며 로그인화면으로 돌아감






전~에 했던거 복습겸 해봤는데 복잡복잡하다. 요즘에는 이렇게 안하겠지??


아 자꾸 오류나서 왜이러지 했는데 lip 폴더에 파일들 inport 해줘야 한다 ㅎ

학원에서는 그냥 계속 프로젝트 복사해서 써서 깜빡하고 있었음

그냥 파일들도 싹다 import해버림




'JSP' 카테고리의 다른 글

[JSP] MVC패턴 회원 등록, 조회하는 개간단한 예제  (0) 2021.05.02
[JSP] Scope 간단예제 - 상품선택, 내가선택한 품목보기  (0) 2018.07.13
[JSP] 자바빈 JavaBean  (0) 2018.07.13
[JSP] 쿠키(Cooki), 세션(Session)  (0) 2018.07.11
[JSP] Request, Response 간단한 메뉴계산 예제  (0) 2018.07.10
댓글
최근에 올라온 글
최근에 달린 댓글
링크
Total
Today
Yesterday