[LG U+ 유레카 3기] JSP + Servlet + DAO 기반 도서관리 CRUD 실습

 

❶ 상황 설명

이 글은 JSP + Servlet + DAO로 도서관리 CRUD를 구현한 실습을 정리한 것이다. 톰캣 10(Jakarta), JNDI DataSource로 연결 풀을 구성하고, BookServlet(Controller) → BookDao(Model) → JSP(View) 흐름으로 전체 기능을 완성했다.

• 컨트롤러: BookServlet (@WebServlet("/books/*"))
• 모델: DBManager(연결 풀), BookDao(JDBC), BookDto(데이터 전달)
• 뷰: JSP들 (bookList.jsp, bookDetail.jsp, bookInsert.jsp 등)
• JNDI: java:comp/env/jdbc/madang (Connection Pool)

❷ 실행 흐름 다이어그램

[Client] ──▶ GET /BookManager/books/list
   │
   ▼
[Controller] BookServlet
   │ calls listBook()
   ▼
[Model] BookDao.listBook() ──▶ DB (madang.book)
   │ returns List<BookDto>
   ▼
[View] forward("/bookList.jsp")
   │
   ▼
[Client] HTML 렌더링

❸ 핵심 코드 (Model & Controller)

DBManager.java (JNDI DataSource + 자원 해제)

package common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class DBManager {
    public static Connection getConnection() {
        Connection con = null;
        try {
            Context context = new InitialContext();
            DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/madang"); // connection pool
            con = ds.getConnection(); // 풀에서 빌려옴
        }catch(Exception e) {
            e.printStackTrace();
        }
        return con;
    }
    
    public static void releaseConnection(PreparedStatement pstmt, Connection con) {
        try {
            if( pstmt != null ) pstmt.close();
            if( con != null ) con.close(); // 반납
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
    
    public static void releaseConnection(ResultSet rs, PreparedStatement pstmt, Connection con) {
        try {
            if(rs != null ) { rs.close(); }
            if(pstmt != null ) { pstmt.close(); }
            if(con != null ) { con.close(); }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

BookDto.java (DTO)

package dto;

public class BookDto {
    private int bookId;
    private String bookName;
    private String publisher;
    private int price;
    
    public BookDto() { }
    public BookDto(int bookId, String bookName, String publisher, int price) {
        this.bookId = bookId;
        this.bookName = bookName;
        this.publisher = publisher;
        this.price = price;
    }
    public int getBookId() { return bookId; }
    public void setBookId(int bookId) { this.bookId = bookId; }
    public String getBookName() { return bookName; }
    public void setBookName(String bookName) { this.bookName = bookName; }
    public String getPublisher() { return publisher; }
    public void setPublisher(String publisher) { this.publisher = publisher; }
    public int getPrice() { return price; }
    public void setPrice(int price) { this.price = price; }

    @Override
    public String toString() {
        return "BookDto [bookId=" + bookId + ", bookName=" + bookName 
             + ", publisher=" + publisher + ", price=" + price + "]";
    }
}

BookDao.java (JDBC CRUD)

package dao;

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

import common.DBManager;
import dto.BookDto;

// madang.book 에 대한 CRUD
public class BookDao {

    // 목록
    public List<BookDto> listBook(){
        List<BookDto> list = new ArrayList<>();
        String sql = "select * from book;";
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = DBManager.getConnection();
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                BookDto bookDto = new BookDto();
                bookDto.setBookId(rs.getInt("bookid"));
                bookDto.setBookName(rs.getString("bookname"));
                bookDto.setPublisher(rs.getString("publisher"));
                bookDto.setPrice(rs.getInt("price"));
                list.add(bookDto);
            }
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
            DBManager.releaseConnection(rs, pstmt, con);
        }
        return list;
    }
    
    // 상세
    public BookDto detailBook(int bookId) {
        BookDto bookDto = null;
        String sql = "select * from book where bookid = ?;";
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = DBManager.getConnection();
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, bookId);
            rs = pstmt.executeQuery();
            if(rs.next()) {
                bookDto = new BookDto();
                bookDto.setBookId(rs.getInt("bookid"));
                bookDto.setBookName(rs.getString("bookname"));
                bookDto.setPublisher(rs.getString("publisher"));
                bookDto.setPrice(rs.getInt("price"));
            }
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
            DBManager.releaseConnection(rs, pstmt, con);
        }
        return bookDto;
    }
    
    // 등록
    public int insertBook(BookDto bookDto) {
        int ret = -1;
        String sql = "insert into book values ( ?, ?, ?, ? );";
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DBManager.getConnection();
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, bookDto.getBookId());
            pstmt.setString(2, bookDto.getBookName());
            pstmt.setString(3, bookDto.getPublisher());
            pstmt.setInt(4, bookDto.getPrice());
            ret = pstmt.executeUpdate();
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
            DBManager.releaseConnection(pstmt, con);
        }
        return ret;
    }
    
    // 수정
    public int updateBook(BookDto bookDto) {
        int ret = -1;
        String sql = "update book set bookname = ?, publisher = ?, price = ? where bookid = ?;";
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DBManager.getConnection();
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, bookDto.getBookName());
            pstmt.setString(2, bookDto.getPublisher());
            pstmt.setInt(3, bookDto.getPrice());
            pstmt.setInt(4, bookDto.getBookId());
            ret = pstmt.executeUpdate();
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
            DBManager.releaseConnection(pstmt, con);
        }
        return ret;
    }
    
    // 삭제
    public int deleteBook(int bookId) {
        int ret = -1;
        String sql = "delete from book where bookid = ?;";
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DBManager.getConnection();
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, bookId);
            ret = pstmt.executeUpdate();
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
            DBManager.releaseConnection(pstmt, con);
        }
        return ret;
    }
}

BookServlet.java (Controller, URL 라우팅)

package servlet;

import java.io.IOException;
import java.util.List;

import dao.BookDao;
import dto.BookDto;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

@WebServlet("/books/*")
public class BookServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        String job = request.getRequestURI().substring(request.getContextPath().length());
        switch(job) {
            case "/books/list"   : listBook(request, response);   break;
            case "/books/detail" : detailBook(request, response); break;
            case "/books/insert" : insertBook(request, response); break;
            case "/books/update" : updateBook(request, response); break;
            case "/books/delete" : deleteBook(request, response); break;
        }
    }

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

    private void listBook(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException{
        BookDao bookDao = new BookDao();
        List<BookDto> bookList = bookDao.listBook();
        request.setAttribute("bookList", bookList);
        request.getRequestDispatcher("/bookList.jsp").forward(request, response);
    }
    
    private void detailBook(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException{
        BookDao bookDao = new BookDao();
        int bookId = Integer.parseInt(request.getParameter("bookId"));
        BookDto bookDto = bookDao.detailBook(bookId);
        request.setAttribute("bookDto", bookDto);
        request.getRequestDispatcher("/bookDetail.jsp").forward(request, response);
    }
    
    private void insertBook(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException{
        BookDao bookDao = new BookDao();
        int bookId = Integer.parseInt(request.getParameter("bookId"));
        String bookName = request.getParameter("bookName");
        String publisher = request.getParameter("publisher");
        int price = Integer.parseInt(request.getParameter("price"));
        BookDto bookDto = new BookDto(bookId, bookName, publisher, price);
        int ret = bookDao.insertBook(bookDto);
        // PRG 패턴을 쓰려면 아래로 교체:
        // response.sendRedirect(request.getContextPath() + "/books/list");
        request.getRequestDispatcher("/bookInsertResult.jsp").forward(request, response);
    }
    
    private void updateBook(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException{
        BookDao bookDao = new BookDao();
        int bookId = Integer.parseInt(request.getParameter("bookId"));
        String bookName = request.getParameter("bookName");
        String publisher = request.getParameter("publisher");
        int price = Integer.parseInt(request.getParameter("price"));
        BookDto bookDto = new BookDto(bookId, bookName, publisher, price);
        int ret = bookDao.updateBook(bookDto);
        // response.sendRedirect(request.getContextPath() + "/books/list");
        request.getRequestDispatcher("/bookUpdateResult.jsp").forward(request, response);
    }    
    
    private void deleteBook(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException{
        BookDao bookDao = new BookDao();
        int bookId = Integer.parseInt(request.getParameter("bookId"));
        int ret = bookDao.deleteBook(bookId);
        // response.sendRedirect(request.getContextPath() + "/books/list");
        request.getRequestDispatcher("/bookDeleteResult.jsp").forward(request, response);
    }    
}

❹ View (JSP)

bookList.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.util.List, dto.BookDto" %>
<%
    List<BookDto> bookList = (List<BookDto>) request.getAttribute("bookList");
%>
<!DOCTYPE html>
<html>
<head><meta charset="UTF-8"><title>도서 목록</title></head>
<body>
  <h1>도서 목록</h1>
  <table>
    <tr><th>bookId</th><th>bookName</th><th>publisher</th><th>price</th></tr>
<%
  for(BookDto bookDto : bookList ){
%>
    <tr>
      <td><a href="/BookManager/books/detail?bookId=<%= bookDto.getBookId()%>"><%= bookDto.getBookId()%></a></td>
      <td><%= bookDto.getBookName()%></td>
      <td><%= bookDto.getPublisher()%></td>
      <td><%= bookDto.getPrice()%></td>
    </tr>
<%
  }
%>
  </table>
  <hr />
  <a href="/BookManager/bookInsert.jsp">등록</a>
</body>
</html>

bookDetail.jsp (상세 + 수정/삭제)

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="dto.BookDto" %>
<%
  BookDto bookDto = (BookDto) request.getAttribute("bookDto");
%>
<!DOCTYPE html>
<html>
<head><meta charset="UTF-8"><title>도서 상세 & 삭제</title></head>
<body>
  <h1>도서 상세 & 삭제</h1>
  <form action="/BookManager/books/update" method="post">
    <input type="text" name="bookId" value="<%= bookDto.getBookId()%>" /><br/>
    <input type="text" name="bookName" value="<%= bookDto.getBookName()%>" /><br/>
    <input type="text" name="publisher" value="<%= bookDto.getPublisher()%>" /><br/>
    <input type="text" name="price" value="<%= bookDto.getPrice()%>" /><br/>
    <button type="submit">수정</button>
  </form>
  <hr/>
  <a href="/BookManager/books/delete?bookId=<%= bookDto.getBookId()%>">삭제</a>
</body>
</html>

bookInsert.jsp (등록 폼)

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head><meta charset="UTF-8"><title>도서 등록</title></head>
<body>
  <h1>도서 등록</h1>
  <form action="/BookManager/books/insert" method="post">
    <input type="text" name="bookId" /><br/>
    <input type="text" name="bookName" /><br/>
    <input type="text" name="publisher" /><br/>
    <input type="text" name="price" /><br/>
    <button type="submit">등록</button>
  </form>
</body>
</html>

 

결과 페이지 (insert/update/delete)

 

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head><meta charset="UTF-8"><title>결과</title></head>
<body>
  <h1>처리 성공</h1>
  <hr/>
  <a href="/BookManager/books/list">목록</a>
</body>
</html>

 

 

❺ 개념 정리

• MVC: Controller-Model-View 분리로 유지보수성과 테스트 용이성 향상.
• RequestDispatcher.forward(): 서버 내부 자원으로 제어를 넘김(브라우저 URL 변경 없음).
• response.sendRedirect(): 클라이언트에 재요청 지시(브라우저 URL 변경). PRG 패턴에 사용.
• JNDI DataSource: java:comp/env/jdbc/madang 로 커넥션 풀 사용 → 커넥션 재활용/반납 패턴 필수.
• DAO/DTO: DB 접근 로직과 데이터 전달 객체 분리로 관심사 분리(Separation of Concerns) 실천.

❼ 교훈 / 핵심 요약

• 폼 처리 후에는 PRG(Post-Redirect-Get)로 중복 제출 방지.
• JDBC 자원은 항상 finally 에서 반납 (또는 try-with-resources).
• URL 패턴은 /books/list, /books/detail처럼 명확히 설계하고, 필요시 getPathInfo() 활용해 라우팅을 단순화.
• JSP에는 비즈니스 로직을 넣지 말고, 출력과 폼만 담당하게 하자.

---

체크리스트

• JNDI 리소스 이름과 톰캣 context.xml 매핑 확인
• DB 스키마: madang.book(bookid, bookname, publisher, price) 준비
• 서블릿 매핑: @WebServlet("/books/*") 정상 동작
• PRG 적용 시 목록으로 redirect 처리