JSP 맛보기 3일차

MemberAdd.java 클래스를 생성하여 

index.jsp에서 회원정보를 입력받아 DB에 입력해보자.

회원 추가(Insert)

package com.javalesson.source;
 
import javax.servlet.ServletException;
import java.io.IOException;
 
import javax.servlet.http.*;
import javax.servlet.annotation.*;
 
//DB 연결
import java.sql.*;
 
@WebServlet("/MemberAdd")//web.xml을 대신해서 만들어진 것
//서블릿을 맵핑해서 요청하는 주소를 설정할 수 있다.
public class MemberAdd extends HttpServlet    {
    String url = "jdbc:mysql://localhost:3306/java_lesson"//DB서버 주소
            + "?useUincode=true&characterEncoding=utf8";
    String uid = "root";
    String upw = "mysql";
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        
        Connection conn = null;
        String sql = "INSERT INTO MEMBER (id,pwd,name,email,phone,admin) VALUE(?,?,?,?,?,?)";
        String id_sql = "SELECT id FROM member WHERE ID = ?";
        PreparedStatement pstmt = null; //sql을 실행
        ResultSet rs = null; // ID검색 결과값을 받는다
        req.setCharacterEncoding("UTF-8");
        
        int insert = 0; //저장을 몇 개 성공했는지 확인
        
        String id = req.getParameter("uId");
        String pwd = req.getParameter("uPass");
        String name = req.getParameter("uName");
        String email = req.getParameter("uEmail");
        String phone = req.getParameter("uPhone");
        int admin = Integer.parseInt(req.getParameter("admin"));
                
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url,uid,upw);
            pstmt = conn.prepareStatement(id_sql);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();
            
            if(!rs.next()){
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, id);
                pstmt.setString(2, pwd);
                pstmt.setString(3, name);
                pstmt.setString(4, email);
                pstmt.setString(5, phone);
                pstmt.setInt(6, admin);
                insert = pstmt.executeUpdate();//INSERT, UPDATE or DELETE
            }
            
            if(insert>0){
                //DB 저장 성공시 memberList 호출
                resp.sendRedirect("./MemberList");        
                //디스패처를 이용하여 호출 하는 방법
                //RequestDispatcher dispatcher = req.getRequestDispatcher("./MemberList");
                //dispatcher.forward(req, resp);
            }else{
                resp.sendRedirect("./index.jsp");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try{
                if(pstmt!=null)pstmt.close();
                if(conn!=null)conn.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
    }
}
 

입력 하면

MemberList로 Redirect 되면서 입력된 결과 출력

---

회원 갱신(Upadate)

①MemberList에서 '수정'버튼을 만든다.

②선택한 회원의 num 파라미터를 받아 MemberDetail으로 이동 후

③MemberVo에서 회원의 파라미터를 받아

④updateForm.jsp으로 넘어간다. (수정 작업을 하는 페이지)

⑤MemberUpdate를 통하여 입력한 정보로 회원을 갱신한다.

MemberList의 수정 버튼을 추가한 부분.

            out.print("<h1>会員リスト</h1>");
            out.print("<table border='1'>");
            out.print("<tr>"
                    + "<th>番号</th>"
                    + "<th>ID</th>"
                    + "<th>PW</th>"
                    + "<th>名前</th>"
                    + "<th>E-mail</th>"
                    + "<th>携帯</th>"
                    + "<th>年月日</th>"
                    + "<th>権限</th>"
                    + "<th>修正</th>"
                    + "<th>削除</th>"
                    + "</tr>");
            while(rs.next()){
                out.print("<tr>");
                out.print("<td>"+rs.getInt("num")+"</td>");
                out.print("<td>"+rs.getString("id")+"</td>");
                out.print("<td>"+rs.getString("pwd")+"</td>");
                out.print("<td>"+rs.getString("name")+"</td>");
                out.print("<td>"+rs.getString("email")+"</td>");
                out.print("<td>"+rs.getString("phone")+"</td>");
                out.print("<td>"+rs.getString("indate")+"</td>");
                out.print("<td>"+rs.getString("admin")+"</td>");
                out.print("<td><a href='./MemberDetail?num="+rs.getInt("num")+"'>修正</a></td>");
                out.print("<td><a href='./MemberDelete?id="+rs.getString("id")+"'>削除</a></td>");
                out.print("</tr>");
 

MemberDetail.java

package com.javalesson.source;
 
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import java.io.IOException;
import java.io.PrintWriter;
 
import javax.servlet.http.*;
import javax.servlet.annotation.*;
 
//DB 연결
import java.sql.DriverManager;//접속 DB 드라이버를 설정
import java.sql.Connection;//DB 접속해서
import java.sql.PreparedStatement; //쿼리를 실행하는 클래스
import java.sql.ResultSet; //쿼리의 결과값을 받는다.
import java.sql.SQLException;
 
@WebServlet("/MemberDetail")//web.xml을 대신해서 만들어진 것
//서블릿을 맵핑해서 요청하는 주소를 설정할 수 있다.
public class MemberDetail extends HttpServlet    {
    String url = "jdbc:mysql://localhost:3306/java_lesson"//DB서버 주소
            + "?useUincode=true&characterEncoding=utf8";
    String uid = "root";
    String upw = "mysql";
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        
        Connection conn = null;
        String sql = "select * from member where num = ?";
        PreparedStatement pstmt = null; //sql을 실행
        ResultSet rs = null; //쿼리문의 결과를 받는다.
        int num = Integer.parseInt(req.getParameter("num"));
        MemberVo vo = null;
 
        try {
            Class.forName("com.mysql.jdbc.Driver");//mysql lib를 사용해서 접속준비
            conn = DriverManager.getConnection(url,uid,upw);//접속
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, num);            
            rs = pstmt.executeQuery();
            if(rs.next()){
                vo = new MemberVo();
                vo.setNum(rs.getInt("num"));
                vo.setId(rs.getString("id"));
                vo.setPwd(rs.getString("pwd"));
                vo.setName(rs.getString("name"));
                vo.setEmail(rs.getString("email"));
                vo.setPhone(rs.getString("phone"));
                vo.setIndate(rs.getString("indate"));
                vo.setAdmin(rs.getInt("admin"));
                req.setAttribute("member", vo);
                //setAttribute는 포워드를 페이지에 따라 파라미터를 Object 타입으로 넘긴다.
                RequestDispatcher dispatcher = req.getRequestDispatcher("./updateForm.jsp");
                dispatcher.forward(req, resp);                
            }else{
                resp.sendRedirect("./MemberList?info=detail failed");
            }
            
            //dispatcher와 redirect의 차이
            //redirect는 get방식으로 통신하기 때문에 파라미터를 url로 보낼 수 밖에 없다.
            //->url은 문자열 -> 파라미터의 데이터 타입은 문자열
            //redirect는 response 객체에 있다.
            
            //dispatcher는 post 방식으로 통신하기 때문에 파라미터를 헤더로 보낼 수 있다.
            //->header에 정보를 담기 때문에 Object 타입으로 파라미터를 보낼 수 있다.
            //->request.setAttribute("key",Object);    
            //dispatcher request 객체에 있다.
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(rs!=null)rs.close();
                if(pstmt!=null)pstmt.close();
                if(conn!=null)conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
 

updateForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="com.javalesson.source.MemberVo" %>
<%    MemberVo vo = (MemberVo)request.getAttribute("member");%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>index.jsp</title>
</head>
<body>
    <h2>会員情報修正 フォーム(insert)</h2>
    <h4>UPDATE SET id=?,pwd=?,name=?,phone=?,email=?,admin=?</h4>
    <h4>UPDATE 쿼리를 실행하는 MemberUpdate servlet을 호출한다.</h4>
    <h4>성공시 MemberList servlet을 호출, 실패시 회원수정 form으로 돌아온다.</h4>
    <!-- a href="MemberList"> 멤버리스트 출력 </a -->
    <form action="MemberUpdate" method="post">
    <!-- aciton은 특정 페이지를 호출(a태그와 동일한 역할) -->
    <!-- method 는 서블릿의 특정 페이지 doGet or doPost 중 하나를 호출함  -->
    
        <p>
            <label for="uNum">num :</label>
            <b><%out.print(vo.getNum());%></b>
            <input type="hidden" name="uNum" value="<%=vo.getNum()%>">
            <!-- hidden : 눈에 보이지는 않지만 파라미터로 값은 넘길 수 있음 -->
        </p>
        <p>
            <label for="uId">ID :</label>    
            <strong><%=vo.getId()%></strong>
            <input type="hidden" name="uId" size="20" value="<%=vo.getId()%>">
        </p>
        <p>
            <label for="uPass">PW :</label>    
            <input type="password" id="uPass" name="uPass" size="20" value="<%=vo.getPwd()%>">
        </p>
        <p>
            <label for="uName">名前 :</label>
            <input type="text" id="uName" name="uName" size="20" value="<%=vo.getName()%>">
        </p>
        <p>
            <label for="uPhone">Phone :</label>    
            <input type="tel" id="uPhone" name="uPhone" size="20" value="<%=vo.getPhone()%>">
        </p>
        <p>
            <label for="uEmail">E-mail :</label>    
            <input type="email" id="uEmail" name="uEmail" size="20" value="<%=vo.getEmail()%>">
        </p>
        <p>
            <label for="admin">管理者</label>    
            <input type="radio" id="admin" name="admin" value="0">
            <label for="normal">一般ユーザ</label>    
            <input type="radio" id="noraml" name="admin" value="1">
        </p>
        <input type="submit" value="提出">
    </form>
</body>
</html>

MemberUpdate.java

package com.javalesson.source;
 
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
@WebServlet("/MemberUpdate")
public class MemberUpdate extends HttpServlet {
    private static final long serialVersionUID = 1L;
 
    String url = "jdbc:mysql://localhost:3306/java_lesson"//DB서버 주소
            + "?useUincode=true&characterEncoding=utf8";
    String uid = "root";
    String upw = "mysql";
 
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        
        Connection conn = null;
        String sql = "UPDATE member SET pwd=?,name=?,phone=?,email=?,admin=? WHERE num=?";
        PreparedStatement pstmt = null; //sql을 실행
        ResultSet rs = null; // ID검색 결과값을 받는다
        req.setCharacterEncoding("UTF-8");
        
        int insert = 0; //저장을 몇 개 성공했는지 확인
        
        String pwd = req.getParameter("uPass");
        String name = req.getParameter("uName");
        String email = req.getParameter("uEmail");
        String phone = req.getParameter("uPhone");
        int admin = Integer.parseInt(req.getParameter("admin"));
        int num = Integer.parseInt(req.getParameter("uNum"));        
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url,uid,upw);
            
            pstmt = conn.prepareStatement(sql);
            
            pstmt.setString(1, pwd);
            pstmt.setString(2, name);
            pstmt.setString(3, phone);
            pstmt.setString(4, email);
            pstmt.setInt(5, admin);
            pstmt.setInt(6, num);
            
            insert = pstmt.executeUpdate();
            if(insert>0){
                resp.sendRedirect("./MemberList");        ;
            }else{
                resp.sendRedirect("./updateForm.jsp");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try{
                if(pstmt!=null)pstmt.close();
                if(conn!=null)conn.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
    }
}

실행하면 다음과 같다 

16번 유저를 수정한다

유저 수정 후 

---

회원 삭제(Delete)

MemberList에 삭제버튼 추가

ID값을 파라미터로 받아 MemberDelete에서 삭제 실행

MemberList에서 삭제버튼 추가한 부분

out.print("<h1>会員リスト</h1>");
            out.print("<table border='1'>");
            out.print("<tr>"
                    + "<th>番号</th>"
                    + "<th>ID</th>"
                    + "<th>PW</th>"
                    + "<th>名前</th>"
                    + "<th>E-mail</th>"
                    + "<th>携帯</th>"
                    + "<th>年月日</th>"
                    + "<th>権限</th>"
                    + "<th>修正</th>"
                    + "<th>削除</th>"
                    + "</tr>");
            while(rs.next()){
                out.print("<tr>");
                out.print("<td>"+rs.getInt("num")+"</td>");
                out.print("<td>"+rs.getString("id")+"</td>");
                out.print("<td>"+rs.getString("pwd")+"</td>");
                out.print("<td>"+rs.getString("name")+"</td>");
                out.print("<td>"+rs.getString("email")+"</td>");
                out.print("<td>"+rs.getString("phone")+"</td>");
                out.print("<td>"+rs.getString("indate")+"</td>");
                out.print("<td>"+rs.getString("admin")+"</td>");
                out.print("<td><a href='./MemberDetail?num="+rs.getInt("num")+"'>修正</a></td>");
                out.print("<td><a href='./MemberDelete?id="+rs.getString("id")+"'>削除</a></td>");
                out.print("</tr>");

MemberDelete.java

package com.javalesson.source;
 
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
@WebServlet("/MemberDelete")
public class MemberDelete extends HttpServlet {
    private static final long serialVersionUID = 1L;
 
    String url = "jdbc:mysql://localhost:3306/java_lesson"//DB서버 주소
            + "?useUincode=true&characterEncoding=utf8";
    String uid = "root";
    String upw = "mysql";
 
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        
        Connection conn = null;
        String sql = "DELETE FROM member WHERE id = ?";
        PreparedStatement pstmt = null; //sql을 실행
        req.setCharacterEncoding("UTF-8");
        
        int insert = 0;
        
        String id = req.getParameter("id");
        
        System.out.println(id);
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url,uid,upw);
            
            pstmt = conn.prepareStatement(sql);
            
            pstmt.setString(1, id);
 
            insert = pstmt.executeUpdate();
            if(insert>0){
                resp.sendRedirect("./MemberList");
            }else{
                resp.sendRedirect("./index.jsp");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try{
                if(pstmt!=null)pstmt.close();
                if(conn!=null)conn.close();
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
    }
}

그 다음 실행하면 

'VVV' 유저를 삭제하는 버튼클릭

삭제 완료