20. JSP + JDBC Select 예제 정리 _ Movie

🎬 JSP + JDBC 예제 (Movie 테이블 조회)

이번 글에서는 JSP + JDBC를 활용하여 movie 테이블의 데이터를 조회

---

1. 사용한 sql문

drop table movie;
create table movie(
	num number not null  primary key,
	id varchar2(10) not null,
	name varchar2(10) not null,
	age number not null,
	genre varchar2(40),
	time varchar2(20),
	partner number default 1,
	memo varchar2(50)
);

drop sequence mv_seq;
create sequence mv_seq
	increment by 1
	start with 1 
	minvalue 1
	maxvalue 10000
	nocache;

insert into movie(num,id,name,age,genre,time,partner,memo)
values(mv_seq.nextval,'m1','김길동',30,'다큐','10~12',2,'좌석을 넓혀주세요.');

commit;
col num for 99
col id for a5
col name for a8
col age for 999
col genre for a16
col time for a10
col partner for 99
col memo for a30

select * from movie;

 

 

2. select.jsp (조회 목록 작성)

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

<jsp:useBean id="mdao" class="myPkg.MovieDao" />
<% 
    ArrayList<MovieBean> lists = mdao.getAllMovie();
%>
<form name="myform" action="multiDelete.jsp" method="post">
<input type="button" value="추가" onClick="location.href='insertForm.jsp'">
<input type="button" value="삭제" onClick="selectDelete()">

<table border="1">
	<tr>	
		<th><input type="checkbox" name="allcheck" onClick="allDelete(this)"></th>
	    <th>번호</th>
	    <th>아이디</th>
	    <th>이름</th>
	    <th>나이</th>
	    <th>좋아하는 장르</th>
	    <th>즐겨보는 시간대</th>
	    <th>동반 관객수</th>
	    <th>개선사항</th>
	    <th>수정</th>
	    <th>삭제</th>
	</tr>


    <% for(MovieBean mb : lists) {%>
        <tr>
        	<td><input type="checkbox" name="rowcheck" value="<%=mb.getNum() %>"></td>
            <td><%=mb.getNum() %></td>
            <td><%=mb.getId() %></td>
            <td><%=mb.getName() %></td>
            <td><%=mb.getAge() %></td>
            <td><%=mb.getGenre() %></td>
            <td><%=mb.getTime() %></td>
            <td><%=mb.getPartner()%></td>
            <td><%=mb.getMemo() %></td>
            <td><a href="updateForm.jsp?num=<%=mb.getNum()%>">수정</a></td>
            <td><a href="deleteProc.jsp?num=<%=mb.getNum()%>">삭제</a></td>
        </tr>

    <%} %>
</table>

</form>

 

 

3. MovieDao.jsp 작성

public ArrayList<MovieBean> getAllMovie(){
    getConnection();
    ArrayList<MovieBean> list = new ArrayList<MovieBean>();

    String sql = "select * from movie order by num";
    try {
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while(rs.next()) {
            int num = rs.getInt("num");
            String id  = rs.getString("id");
            String name  = rs.getString("name");
            int age = rs.getInt("age");
            String genre  = rs.getString("genre");
            String time  = rs.getString("time");
            int partner = rs.getInt("partner");
            String memo  = rs.getString("memo");

            MovieBean mb = new MovieBean(num,id,name,age,genre,time,partner,memo);
            list.add(mb);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        try {
            if(rs != null) rs.close();
            if(ps != null) ps.close();
            if(conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    System.out.println("list.size():" + list.size());
    return list;
}//getAllMovie

 

4. MovieBean.jsp

package myPkg;

public class MovieBean {
	private int num;
    private String id;
    private String name;
    private int age;
    private String genre;
    private String time;
    private int partner;
    private String memo;
    
    public MovieBean() {
    	
    }
	public MovieBean(int num, String id, String name, int age, String genre, String time, int partner, String memo) {
		super();
		this.num = num;
		this.id = id;
		this.name = name;
		this.age = age;
		this.genre = genre;
		this.time = time;
		this.partner = partner;
		this.memo = memo;
	}
	public int getNum() {
		return num;
	}
	public void setNum(int num) {
		this.num = num;
	}
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public String getGenre() {
		return genre;
	}
	public void setGenre(String genre) {
		this.genre = genre;
	}
	public String getTime() {
		return time;
	}
	public void setTime(String time) {
		this.time = time;
	}
	public int getPartner() {
		return partner;
	}
	public void setPartner(int partner) {
		this.partner = partner;
	}
	public String getMemo() {
		return memo;
	}
	public void setMemo(String memo) {
		this.memo = memo;
	}
}