서블릿 DB 연동(2)

서블릿 DB 연동(2)

서블릿에서 사용할 테이블 생성

MySQL Workbench에서 Create Schema

• Name : servletdb
• Charset/Collation : utf8 / Default Collation
• [Apply] / [Next] / [Finish]

테이블 생성 : member 테이블

• 가입일을 현재 날짜/시간으로 디폴트로 저장되도록 설정 : TIMESTAMP DEFAULT NOW()

create table member(
    memId VARCHAR(10) NOT NULL PRIMARY KEY,
    memPwd VARCHAR(10),
    memName VARCHAR(30),
    memEmail VARCHAR(30),
    memJoinDate TIMESTAMP DEFAULT NOW()
);

테이블 데이터 입력

INSERT INTO member 
VALUES('hong', '1234', '홍길동', 'hkd@naver.com', default),
      ('lee', '1234', '이몽룡', 'lee@naver.com', default),
          ('sch', '1234', '성춘향', 'sch@naver.com', default);

INSERT INTO member(memId, memPwd, memName, memEmail)  
VALUES('kim', '1234', '김길동', 'hkd@naver.com');

패키지 생성 : sec04

• MemberVO
• MemberDAO
• MemberServlet

MemberVO

package com.example.servlet01.sec04;

import java.util.Date;

public class MemberVO {
    private String id;
    private String pwd;
    private String name;
    private String email;
    private Date joinDate;

    // 디폴트 생성자
    public MemberVO() {}

    // 현재는 매개변수 있는 생성자 필요 없음
    public MemberVO(String id, String pwd, String name, String email, Date joinDate) {
        this.id = id;
        this.pwd = pwd;
        this.name = name;
        this.email = email;
        this.joinDate = joinDate;
    }

    // Getter Setter
    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getJoinDate() {
        return joinDate;
    }

    public void setJoinDate(Date joinDate) {
        this.joinDate = joinDate;
    }
}

MemberDAO

package com.example.servlet01.sec04;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;

public class MemberDAO {
    // db 연결 담당 메소드
    private Connection connDB() {
        Connection con = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            String url = "jdbc:mysql://localhost:3306/servletdb?serverTimezone=UTC";
            String user = "root";
            String pwd = "1234";

            con = DriverManager.getConnection(url, user, pwd);
            if(con != null) {
                System.out.println("연결 성공");
            }
        } catch(Exception e) {
            System.out.println("연결 오류 발생!");
            e.printStackTrace();
        }
        return con;
    }
    // 회원 정보 조회 (전체 회원 정보 SELECT 해서 반환 : MemberVO 반환)
    // MemberVO를 여러 행 반환 : ArrayList<MemberVO>
    public ArrayList<MemberVO> memberSelect(){
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList<MemberVO> memList = new ArrayList<MemberVO>();

        try {
            con = connDB();

            String query = "select * from member";
            pstmt = con.prepareStatement(query);
            rs = pstmt.executeQuery();

            while(rs.next()) { // 결과 세트에서 한 행씩 처리
                // 한 행(회원 1명당) 처리
                String id = rs.getString("memId");
                String pwd = rs.getString("memPwd");
                String name = rs.getString("memName");
                String email = rs.getString("memEmail");
                Date joinDate = rs.getDate("memJoinDate");

                // 한 행 정보 가져와서 MemberVO에 저장 : setter 메소드 사용
                MemberVO vo = new MemberVO();
                vo.setId(id);
                vo.setPwd(pwd);
                vo.setName(name);
                vo.setEmail(email);
                vo.setJoinDate(joinDate);

                // 각 MemberVO를 ArrayList에 추가(저장)
                memList.add(vo);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {

            try {
                rs.close();
                pstmt.close();
                con.close();
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return memList;
    }
}

MemberSelectServlet

package com.example.servlet01.sec04;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.Date;

@WebServlet("/memberSelect")
public class MemberSelectServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doProcess(request, response);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doProcess(request, response);
    }

    protected void doProcess(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");
        PrintWriter out = response.getWriter();

        MemberDAO dao = new MemberDAO();

        ArrayList<MemberVO> memList = dao.memberSelect();

        out.print("<html><head></head><body>");
        out.print("<table border=1><tr align='center' bgcolor='green'>");
        out.print("<td>아이디</td><td>비밀번호</td><td>이름</td>" +
                "<td>이메일</td><td>가입일</td>");

        for(int i=0; i<memList.size(); i++) {
            MemberVO vo = (MemberVO) memList.get(i);
            String id = vo.getId();
            String pwd = vo.getPwd();
            String name = vo.getName();
            String email = vo.getEmail();
            Date joinDate = vo.getJoinDate();

            // 한 행씩 출력
            out.print("<tr><td>" + id + "</td><td>" +
                    pwd + "</td><td>" +
                    name + "</td><td>" +
                    email + "</td><td>" +
                    joinDate + "</td></tr>");
        }

        out.print("</table></body></html>");
    }
}