15. JSP + JDBC Select 예제 정리2 _ Book

1. select.jsp (전체 도서 조회 페이지)

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import ="myPkg.*" %>
<%@ page import ="java.util.*" %>

select.jsp <br>

<style type="text/css">
    table {
        width: 800px;
        margin: auto;
        text-align: center;
    }
    table th {
        background-color: #F6EAC2;
    }
</style>

<!-- 1. DAO 객체 생성 -->
<jsp:useBean id="bdao" class="myPkg.BookDao"/>

<%
    // 2. 전체 도서 조회 메서드 호출
    ArrayList<BookBean> lists = bdao.getAllbook();
%>

<!-- 3. 결과 테이블 출력 -->
<table border="1">
    <tr>
        <th>번호</th>
        <th>제목</th>
        <th>저자</th>
        <th>출판사</th>
        <th>가격</th>
        <th>입고일</th>
        <th>배송비</th>
        <th>구매 가능 서점</th>
        <th>보유 수량</th>
        <th>수정</th>
        <th>삭제</th>
    </tr>
    
    <% for(BookBean bb : lists) { %>
    <tr>
        <td><%=bb.getNo()%></td>
        <td><%=bb.getTitle()%></td>
        <td><%=bb.getAuthor()%></td>
        <td><%=bb.getPublisher()%></td>
        <td><%=bb.getPrice()%></td>
        <td><%=bb.getDay()%></td>
        <td><%=bb.getKind()%></td>
        <td><%=bb.getBookstore()%></td>
        <td><%=bb.getCount()%></td>
        <td>수정</td>
        <td>삭제</td>
    </tr>
    <% } %>
</table>

<br>
<a href="insertForm.jsp">삽입폼</a>

 

✔ 설명

• <jsp:useBean> : BookDao 객체 생성
• bdao.getAllbook() : DAO에서 전체 도서 조회 후 ArrayList 반환
• for-each 반복문으로 리스트 출력 → 테이블 생성
• 추후 [수정] / [삭제] 버튼에 링크 달면 CRUD 완성 가능

 

---

2. BookDao.java (DAO 클래스)

package myPkg;

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

public class BookDao {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:orcl";
    String id = "sqlid";
    String pw = "sqlpw";

    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    // 1. 드라이버 로드
    public BookDao() {
        try {
            Class.forName(driver);
            System.out.println("드라이버 로드 성공");
        } catch (ClassNotFoundException e) {
            System.out.println("드라이버 로드 실패");
            e.printStackTrace();
        }
    }

    // 2. DB 연결
    public void getConnection() {
        try {
            conn = DriverManager.getConnection(url, id, pw);
            System.out.println("계정 접속 성공");
        } catch (SQLException e) {
            System.out.println("계정 접속 실패");
            e.printStackTrace();
        }
    }

    // 3. 전체 도서 조회
    public ArrayList<BookBean> getAllbook() {
        getConnection();
        ArrayList<BookBean> lists = new ArrayList<BookBean>();
        try {
            String sql = "select * from book order by no asc";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            while(rs.next()) {
                BookBean bb = new BookBean();
                bb.setNo(rs.getInt("no"));
                bb.setTitle(rs.getString("title"));
                bb.setAuthor(rs.getString("author"));
                bb.setPublisher(rs.getString("publisher"));
                bb.setPrice(rs.getInt("price"));
                bb.setDay(String.valueOf(rs.getDate("day")));
                bb.setKind(rs.getString("kind"));
                bb.setBookstore(rs.getString("bookstore"));
                bb.setCount(rs.getInt("count"));

                lists.add(bb);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps != null) ps.close();
                if(rs != null) rs.close();
                if(conn != null) conn.close();
            } catch(SQLException e) {
                e.printStackTrace();
            }
        }
        return lists;
    }

    // 4. 도서 삽입
    public int insertBook(BookBean bb){
        getConnection();
        int cnt = -1;
        String sql = "insert into book values(bseq.nextval,?,?,?,?,?,?,?,?)";
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, bb.getTitle());
            ps.setString(2, bb.getAuthor());
            ps.setString(3, bb.getPublisher());
            ps.setInt(4, bb.getPrice());
            ps.setString(5, bb.getDay());
            ps.setString(6, bb.getKind());
            ps.setString(7, bb.getBookstore());
            ps.setInt(8, bb.getCount());

            cnt = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps != null) ps.close();
                if(conn != null) conn.close();
            } catch(SQLException e) {
                e.printStackTrace();
            }
        }
        return cnt;
    }
}

 

✔ 설명

• getAllbook() : DB → ResultSet → BookBean 변환 → ArrayList 반환
• insertBook() : BookBean에서 값 꺼내 DB insert 실행
• 공통 패턴: Connection → SQL 실행 → ResultSet/Update → close()

---

3. BookBean.java (데이터 객체)

package myPkg;

public class BookBean {
    private int no;
    private String title;
    private String author;
    private String publisher;
    private int price;
    private String day;
    private String kind;
    private String bookstore;
    private int count;

    public BookBean() {}

    // 생성자
    public BookBean(int no, String title, String author, String publisher, 
                    int price, String day, String kind,
                    String bookstore, int count) {
        this.no = no;
        this.title = title;
        this.author = author;
        this.publisher = publisher;
        this.price = price;
        this.day = day;
        this.kind = kind;
        this.bookstore = bookstore;
        this.count = count;
    }

    // getter/setter
    public int getNo() { return no; }
    public void setNo(int no) { this.no = no; }

    public String getTitle() { return title; }
    public void setTitle(String title) { this.title = title; }

    public String getAuthor() { return author; }
    public void setAuthor(String author) { this.author = author; }

    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; }

    public String getDay() { return day; }
    public void setDay(String day) { this.day = day; }

    public String getKind() { return kind; }
    public void setKind(String kind) { this.kind = kind; }

    public String getBookstore() { return bookstore; }
    public void setBookstore(String bookstore) { this.bookstore = bookstore; }

    public int getCount() { return count; }
    public void setCount(int count) { this.count = count; }
}

 

✔ 설명

• Book 테이블의 한 행(row)을 담는 객체
• DTO/VO 역할 → JSP와 DAO 사이 데이터 전달
• getter/setter 로 데이터 접근

---

사용한 sql

drop sequence bseq;
drop table book;

create sequence bseq
	increment by 1
	start with 1
	minvalue 1;
	
create table book(
	no number primary key,
	title varchar2(10) not null,
	author varchar2(10) not null,
	publisher varchar2(10) not null,
	price number,
	day date, 
	kind varchar2(10) not null,
	bookstore varchar2(50) not null, 
	count number	
);

insert into book
values(bseq.nextval,'JSP','윤아','metabuild',1000,'2000-1-1','무료','알라딘',3);

commit;

col no for 99
col title for a6
col author for a6
col publisher for a6
col kind for a6
col bookstore for a15
col count for 99
select * from book;

---

 

✅ 실행 순서 요약

1. 브라우저에서 select.jsp 요청
   → <jsp:useBean> 으로 BookDao 객체 생성
   → bdao.getAllbook() 호출

2. BookDao.getAllbook()
   → DB 접속
   → select * from book 실행
   → 결과를 BookBean 객체에 담고 ArrayList에 추가
   → ArrayList 반환

3. select.jsp
   → ArrayList<BookBean> 받아서 for-each 출력
   → HTML 테이블에 도서 목록 표시

4. insertBook()
   → insertForm.jsp에서 입력값 받음
   → BookBean에 담아서 insertBook() 호출
   → DB에 새로운 책 삽입