서블릿 DB 연동(3)

서블릿 DB 연동(3)

연습문제

패키지 생성 : sec05

• BookVO
• BookDAO
• BookSelectServlet

book 테이블 생성

• 도서번호 / 도서명 / 저자 / 가격 / 발행일 / 출판사번호

CREATE TABLE book(
    bookNo VARCHAR(10) NOT NULL PRIMARY KEY,
    bookName VARCHAR(30),
    bookAuthor VARCHAR(10),
    bookPrice INT,
    bookDate date,
    pubNo VARCHAR(10)
);

테이블 데이터 입력

insert into book values ('001', 'java', '홍길동', 21000, '2021-12-28', '001'),
('002', 'javascript', '성춘향', 24000, '2020-03-28', '002'),
('003', 'python', '말포이', 19000, '2018-05-15', '003');

BookVO

package com.example.servlet01.sec05;

import java.util.Date;

public class BookVO {
    private String bookNo;
    private String bookName;
    private String bookAuthor;
    private Integer bookPrice;
    private Date bookDate;
    private String pubNo;

    public BookVO() {}

    public BookVO(String bookNo, String bookName, String bookAuthor, Integer bookPrice, Date bookDate, String pubNo) {
        this.bookNo = bookNo;
        this.bookName = bookName;
        this.bookAuthor = bookAuthor;
        this.bookPrice = bookPrice;
        this.bookDate = bookDate;
        this.pubNo = pubNo;
    }

    public String getBookNo() {
        return bookNo;
    }

    public void setBookNo(String bookNo) {
        this.bookNo = bookNo;
    }

    public String getBookName() {
        return bookName;
    }

    public void setBookName(String bookName) {
        this.bookName = bookName;
    }

    public String getBookAuthor() {
        return bookAuthor;
    }

    public void setBookAuthor(String bookAuthor) {
        this.bookAuthor = bookAuthor;
    }

    public Integer getBookPrice() {
        return bookPrice;
    }

    public void setBookPrice(Integer bookPrice) {
        this.bookPrice = bookPrice;
    }

    public Date getBookDate() {
        return bookDate;
    }

    public void setBookDate(Date bookDate) {
        this.bookDate = bookDate;
    }

    public String getPubNo() {
        return pubNo;
    }

    public void setPubNo(String pubNo) {
        this.pubNo = pubNo;
    }
}

BookDAO

package com.example.servlet01.sec05;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;

public class BookDAO {
    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 해서 반환 : BookVO 반환)
    // BookVO 여러 행 반환 : ArrayList<BookVO>
    public ArrayList<BookVO> bookSelect() {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList<BookVO> bookList = new ArrayList<BookVO>();

        try {
            con = connDB();

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

            while (rs.next()) { // 결과 세트에서 한 행씩 처리
                // 한 행(회원 1명당) 처리
                String bookNo = rs.getString("bookNo");
                String bookName = rs.getString("bookName");
                String bookAuthor = rs.getString("bookAuthor");
                Integer bookPrice = rs.getInt("bookPrice");
                Date bookDate = rs.getDate("bookDate");
                String pubNo = rs.getString("pubNo");

                // 한 행 정보 가져와서 MemberVO에 저장 : setter 메소드 사용
                BookVO vo = new BookVO();
                vo.setBookNo(bookNo);
                vo.setBookName(bookName);
                vo.setBookAuthor(bookAuthor);
                vo.setBookPrice(bookPrice);
                vo.setBookDate(bookDate);
                vo.setPubNo(pubNo);

                // 각 MemberVO를 ArrayList에 추가(저장)
                bookList.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 bookList;
    }
}

BookSelectServlet

package com.example.servlet01.sec05;

import com.example.servlet01.sec04.MemberDAO;
import com.example.servlet01.sec04.MemberVO;

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(name = "BookSelectServlet", value = "/BookSelectServlet")
public class BookSelectServlet 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();

        BookDAO dao = new BookDAO();

        ArrayList<BookVO> bookList = dao.bookSelect();

        out.print("<html><head></head><body>");
        out.print("<table border=1><tr align='center' bgcolor='black' color='white'>");
        out.print("<td>도서번호</td><td>도서명</td><td>저자</td>" +
                "<td>가격</td><td>발행일</td><td>출판사번호</td>");

        for(int i=0; i<bookList.size(); i++) {
            BookVO vo = (BookVO) bookList.get(i);
            String bookNo = vo.getBookNo();
            String bookName = vo.getBookName();
            String bookAuthor = vo.getBookAuthor();
            Integer bookPrice = vo.getBookPrice();
            Date bookDate = vo.getBookDate();
            String pubNo = vo.getPubNo();

            // 한 행씩 출력
            out.print("<tr><td>" +
                    bookNo + "</td><td>" +
                    bookName + "</td><td>" +
                    bookAuthor + "</td><td>" +
                    bookPrice + "</td><td>" +
                    bookDate + "</td><td>" +
                    pubNo + "</td></tr>");
        }

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