[웹/JSP]jsp model1 간단한 도서관리게시판-5(delete문)

마지막으로 살펴볼 내용은 jsp게시판 delete문인데요~

삭제라고 하죠~ 삭제는 두 가지로 처리해주었는데요~

테이블 안에 있는 삭제 버튼을 누르면 삭제되게해주는 것과 체크박스를 만들어서 체크를 한다음 삭제해주는 내용이죠~

그리고 jsp는 deleteProc.jsp와 deleteAll.jsp를 만들어주었는데요~

일단 select에서 자바스크립트 안에 내용을 연동시켜주도록 하겠습니다.

 

- select.jsp

<script type="text/javascript">
	function insert() {
		location.href = "insertForm.jsp";
	}

	function selectDelete() {
		var flag = false;
		var memberchk = document.getElementsByName("rowcheck");
		for (var i = 0; i < memberchk.length; i++) {
			if (memberchk[i].checked) {
				flag = memberchk[i].checked;
			}
		}
		if (!flag) {
			alert("삭제할 내용을 체크하세요");
			return;
		}
		document.userForm.submit();
	}

	function allDelete(obj) {
		var chkobj = document.getElementsByName("rowcheck");
		var rowcnt = chkobj.length - 1;
		
		var check = obj.checked;
		if (check) {
			for (var i = 0; i <= rowcnt; i++) {
				chkobj[i].checked = true;
			}
		} else {
			for (var i = 0; i <= rowcnt; i++) {
				chkobj[i].checked = false;
			}
		}
	}
</script>

 

rowcheck라는 데이터가 들어간 테이블안에 체크박스 name명이랑 onclick안에 들어가있는 function값명을

잘 살펴보시면요~ㅎㅎ

flag를 써서 값이 안들어가면 "삭제할 내용을 체크하세요"라는 alert창이 뜨게끔 해주었습니다~

그리고 check값을 if문을 주어서 전체체크하거나 부분체크가 되도록 설정을 해주었습니다.

이것들도 잘 외워두고 넘어가시길 바라겠습니다~!!

 

- deleteProc.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
deleteProc.jsp<br><br>

<jsp:useBean id="dao" class="book.BookDao"/>
<%
int bnum = Integer.parseInt(request.getParameter("bnum"));
System.out.println("deleteProc.jsp bnum:" + bnum);
int cnt = dao.deleteData(bnum);
if(cnt==1){
	response.sendRedirect("select.jsp");
}

%>

 

bnum으로 삭제가 되게끔 해었지요~ㅎㅎ

그리고 deleteAll도 한번 보겠습니다~!

 

- deleteAll.jsp

<%@page import="book.BookDao"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
deleteAll.jsp<br><br>

<jsp:useBean id="dao" class="book.BookDao"/>
<jsp:setProperty property="*" name="dao"/>
<%
    String[] str = request.getParameterValues("rowcheck");

    for(int i=0; i<str.length; i++){
    	System.out.println(str[i]+" ");
    }
    
    int cnt = dao.deleteCheckData(str);
    if(cnt>0){
    	response.sendRedirect("select.jsp");
    }
%>

 

str값으로 체크박스안에 값이 삭제되게끔 해주었고요

dao도 살펴보도록 할까요~!!!

 

- BeanDao.java

package book;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import book.BookBean;

public class BookDao {
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	String dbid="scott";
	String dbpw="1234";
	Connection conn = null;

	public BookDao() {

		try{
			Class.forName(driver);
			conn = DriverManager.getConnection(url,dbid,dbpw);
			
		}catch(SQLException e){
			e.printStackTrace();

		}catch(ClassNotFoundException e){
			e.printStackTrace();

		}catch(Exception e){
			e.printStackTrace();
		}
	}


	public Vector<BookBean> getAllBook(){
		String sql = "select * from book order by bnum";
		PreparedStatement pstmt=null;
		ResultSet rs = null;
		Vector<BookBean> lists = new Vector<BookBean>();
		try{
			pstmt=conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				int bnum = rs.getInt("bnum");
				String title = rs.getString("title");
				String author = rs.getString("author");
				String publisher = rs.getString("publisher");
				String price = rs.getString("price");
				String bdate = rs.getString("bdate");
				String kind = rs.getString("kind");
				String bookstore = rs.getString("bookstore");
				String count = rs.getString("count"); 
				BookBean bb = new BookBean(bnum,title,author,publisher,price,bdate,kind,bookstore,count);
				lists.add(bb);
			}
			

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

	public int insertData(BookBean bean) {
		PreparedStatement pstmt = null;
		String sql = "insert into book(bnum,title,author,publisher,price,bdate,kind,bookstore,count) "
				+ "values(b_seq.nextval,?,?,?,?,?,?,?,?)";
		int cnt=-1;
		try{
			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, bean.getTitle());
			pstmt.setString(2, bean.getAuthor());
			pstmt.setString(3, bean.getPublisher());
			pstmt.setString(4, bean.getPrice());
			pstmt.setString(5, bean.getBdate());
			pstmt.setString(6,bean.getKind());
			pstmt.setString(7, bean.getBookstore());
			pstmt.setString(8, bean.getCount());

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

	public BookBean getUpdateByBnum(int bnum){ 

		String sql = "select * from book where bnum=?";
		PreparedStatement pstmt=null;
		ResultSet rs = null;
		BookBean bean=null;
		try{
			pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1,bnum);
			rs = pstmt.executeQuery();

			if(rs.next()){ 
				String title = rs.getString("title");
				String author = rs.getString("author");
				String publisher = rs.getString("publisher");
				String price = rs.getString("price");
				String bdate = rs.getString("bdate");
				String kind = rs.getString("kind");
				String bookstore = rs.getString("bookstore");
				String count = rs.getString("count"); 
				bean = new BookBean(0,title,author,publisher,price,bdate,kind,bookstore,count);				
			}
				
		} catch (Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(rs!=null)
					rs.close();
				if(pstmt!=null)
					pstmt.close();
				if(conn!=null)
					conn.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		return bean;		
	}
	
	public int updateData(BookBean bean){
		PreparedStatement pstmt=null;

		String sql = "update book set  title=?, author=?, publisher=?, price=?, bdate=?, kind=?, bookstore=?, count=? where bnum=?";
		int cnt = -1;

		try{
			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, bean.getTitle());
			pstmt.setString(2, bean.getAuthor());
			pstmt.setString(3, bean.getPublisher());
			pstmt.setString(4, bean.getPrice());
			pstmt.setString(5, bean.getBdate());
			pstmt.setString(6,bean.getKind());
			pstmt.setString(7, bean.getBookstore());
			pstmt.setString(8, bean.getCount());
			pstmt.setInt(9, bean.getBnum());
			
			cnt = pstmt.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();

		}finally{
			try{
				if(pstmt!=null)
					pstmt.close();
				if(conn!=null)
					conn.close();
			}catch(Exception e2){
				e2.printStackTrace();
			}
		}
		return cnt;
	}

	public int deleteData(int bnum){
		PreparedStatement pstmt = null;
		String sql = "delete from book where bnum=?";
		int cnt=-1;
		try{
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, bnum);
			cnt = pstmt.executeUpdate();
			
		}catch(SQLException e){
			e.printStackTrace();
		}finally{ 
			try{
				if(pstmt!=null)
					pstmt.close();
				if(conn!=null)
					conn.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		return cnt;
	}
	
	public int deleteCheckData(String[] check){ 
		PreparedStatement pstmt = null;
		System.out.println("체크된 개수:"+check.length);
		
		for(int i=0;i<check.length;i++){
			System.out.println(check[i]);
		}
		String sql = "delete from book where bnum=?";
		for(int i=0;i<check.length-1;i++){
			sql += " or bnum=?";
		}
		int cnt=-1;
		try{
			pstmt = conn.prepareStatement(sql);
			for(int i=1;i<=check.length;i++){
				pstmt.setInt(i,Integer.parseInt(check[i-1]));
			}
			cnt = pstmt.executeUpdate();
			
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try{
				if(pstmt!=null)
					pstmt.close();
				if(conn!=null)
					conn.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		return cnt;
	}
	
	
}

 

자 이렇게 해주면 삭제는 말끔히 처리되게됩니다~!!!

혹시 에러가 난다면 띄어쓰기나 쉼표나 점이 오타가 났는지 잘 살펴보시고요~ 대소문자 구분도 잘해주시면

되겠습니다~ㅎㅎ

이것말고도 게시판들이 더 많게끔 올릴 수 있고 웹 디지인(퍼블리싱)도 꾸며줄 수 있는데요~^^ㅋ

 

마지막으로 웹 페이지로 띄운 결과창을 한번 올려보도록 할께요~!