0720 JSP JDBC로 만든 칼럼 추가 삭제

Develop/JavaScript JQuery JSP

0720 JSP JDBC로 만든 칼럼 추가 삭제

포페PostFace 2022. 7. 22. 19:37

1.memo.txt

create table shoes(
   num number primary key,
   id varchar2(40) not null,
   year number not null,
   month number not null,
   day number not null,
   brand varchar2(50),
   feel number default 0,
   design number default 0,
   premium number default 0,
   sum number default 0
);

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

insert into shoes(num,id,year,month,day,brand,feel,design,premium,sum)
values(sho_seq.nextval,'1 bred',1987,7,9,'jordan',20,100,70,190);

commit;

2.ShoesBean.java

package myPkg;

public class ShoesBean {
	private int num;
	private String id;
	private int year;
	private int month;
	private int day;
	private String brand;
	private int feel;
	private int design;
	private int premium;
	private int sum;
	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 int getYear() {
		return year;
	}
	public void setYear(int year) {
		this.year = year;
	}
	public int getMonth() {
		return month;
	}
	public void setMonth(int month) {
		this.month = month;
	}
	public int getDay() {
		return day;
	}
	public void setDay(int day) {
		this.day = day;
	}
	public String getBrand() {
		return brand;
	}
	public void setBrand(String brand) {
		this.brand = brand;
	}
	public int getFeel() {
		return feel;
	}
	public void setFeel(int feel) {
		this.feel = feel;
	}
	public int getDesign() {
		return design;
	}
	public void setDesign(int design) {
		this.design = design;
	}
	public int getPremium() {
		return premium;
	}
	public void setPremium(int premium) {
		this.premium = premium;
	}
	public int getSum() {
		return sum;
	}
	public void setSum(int sum) {
		this.sum = sum;
	}
	
}

3.ShoesDao.java

package myPkg;

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

public class ShoesDao {
	String driver="oracle.jdbc.driver.OracleDriver";
	String url="jdbc:oracle:thin:@localhost:1521:orcl";
	String id="sqlid";
	String pw="sqlpw";
	Connection conn=null;
	public ShoesDao() {
		try {
			Class.forName(driver);
			System.out.println("드라이버 로드");
		} catch (ClassNotFoundException e) {
			System.out.println("드라이버 로드 실패");
			e.printStackTrace();
		}
	}
	public void connect() {
		try {
			conn=DriverManager.getConnection(url,id,pw);
			System.out.println("계정 접속");
		} catch (SQLException e) {
			System.out.println("접속 실패");
			e.printStackTrace();
		}
	}
	public ArrayList<ShoesBean> getAllShoes(){
		connect();
		String sql="select * from shoes order by num";
		PreparedStatement ps=null;
		ResultSet rs=null;
		ArrayList<ShoesBean> lists=new ArrayList<ShoesBean>();
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				ShoesBean sb=new ShoesBean();
				sb.setNum(rs.getInt("num"));
				sb.setId(rs.getString("id"));
				sb.setYear(rs.getInt("year"));
				sb.setMonth(rs.getInt("month"));
				sb.setDay(rs.getInt("day"));
				sb.setBrand(rs.getString("brand"));
				sb.setFeel(rs.getInt("feel"));
				sb.setDesign(rs.getInt("design"));
				sb.setPremium(rs.getInt("premium"));
				sb.setSum(rs.getInt("sum"));
				lists.add(sb);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(conn!=null)
					conn.close();
				if(ps!=null)
					ps.close();
				if(rs!=null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lists;
	}
	public int insertShoes(ShoesBean sb) {
		connect();
		String sql="insert into shoes values(sho_seq.nextval,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps=null;
		int result=-1;
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,sb.getId());
			ps.setInt(2, sb.getYear());
			ps.setInt(3, sb.getMonth());
			ps.setInt(4, sb.getDay());
			ps.setString(5, sb.getBrand());
			ps.setInt(6, sb.getFeel());
			ps.setInt(7, sb.getDesign());
			ps.setInt(8, sb.getPremium());
			ps.setInt(9, sb.getSum());
			result=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(conn!=null)
					conn.close();
				if(ps!=null)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
		return result;
	}
	public ShoesBean getShoesByNum(int num) {
		connect();
		String sql="select * from shoes where num="+num;
		PreparedStatement ps=null;
		ResultSet rs=null;
		ShoesBean sb=null;
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()) {
				sb= new ShoesBean();
				sb.setNum(rs.getInt("num"));
				sb.setId(rs.getString("id"));
				sb.setYear(rs.getInt("year"));
				sb.setMonth(rs.getInt("month"));
				sb.setDay(rs.getInt("day"));
				sb.setBrand(rs.getString("brand"));
				sb.setFeel(rs.getInt("feel"));
				sb.setDesign(rs.getInt("design"));
				sb.setPremium(rs.getInt("premium"));
				sb.setSum(rs.getInt("sum"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(conn!=null)
					conn.close();
				if(ps!=null)
					ps.close();
				if(rs!=null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return sb;
	}
	public int updateShoes(ShoesBean sb) {
		connect();
		String sql="update shoes set id=?,year=?,month=?,day=?,brand=?,"+
				"feel=?,design=?,premium=?,sum=? where num=?";
		PreparedStatement ps=null;
		int result=-1;
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1,sb.getId());
			ps.setInt(2, sb.getYear());
			ps.setInt(3, sb.getMonth());
			ps.setInt(4, sb.getDay());
			ps.setString(5, sb.getBrand());
			ps.setInt(6, sb.getFeel());
			ps.setInt(7, sb.getDesign());
			ps.setInt(8, sb.getPremium());
			ps.setInt(9, sb.getSum());
			ps.setInt(10, sb.getNum());
			result=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(conn!=null)
					conn.close();
				if(ps!=null)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}
	public int deleteShoes(int num) {
		connect();
		String sql="delete shoes where num="+num;
		int result=-1;
		PreparedStatement ps=null;
		try {
			ps=conn.prepareStatement(sql);
			result=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(conn!=null)
					conn.close();
				if(ps!=null)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
		return result;
	}
	public boolean searchId(String id) {
		connect();
		String sql="select id from shoes where id=?";
		PreparedStatement ps=null;
		ResultSet rs=null;
		boolean flag=false;
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()) {
				flag=true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(conn!=null)
					conn.close();
				if(ps!=null)
					ps.close();
				if(rs!=null)
					rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}
	public int deleteShoesByNum(String[] deleteArr) {
		connect();
		String sql="delete shoes where num=? ";
		for(int i=0;i<deleteArr.length-1;i++) {
			sql += " or num=?";
		}
		int result=-1;
		PreparedStatement ps=null;
		try {
			ps=conn.prepareStatement(sql);
			for(int i=0;i<deleteArr.length;i++) {
				ps.setString(i+1,deleteArr[i]);
			}
			result=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(conn!=null)
					conn.close();
				if(ps!=null)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
		return result;
	}
}

4.select.jsp

<%@page import="myPkg.ShoesBean"%>
<%@page import="java.util.ArrayList"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<style>
   table {
      width: 80%;
      margin: 0 auto;
      border: 3px solid pink;
   }
   
   table th {
      background: grey;
   }
   
   table td {
      text-align: center;
   }
   
   table td, table th {
      border: 1px solid black;
   }
   img {
   		width: 50%;
   }
</style>
select.jsp<br>
<%request.setCharacterEncoding("UTF-8"); %>
<jsp:useBean id="sb" class="myPkg.ShoesBean"/>
<jsp:useBean id="sdao" class='myPkg.ShoesDao'/>
<form name="myform" action="deleteAll.jsp">
   <table border='1'>
      <tr>
         <th><input type="checkbox" name="allcheck" onclick="allDelete()"></th>
         <th>번호</th>
         <th>제품명</th>
         <th>발매일자</th>
         <th>브랜드</th>
         <th>착화감</th>
         <th>디자인</th>
         <th>희소성</th>
         <th>합계</th>
         <th>수정</th>
         <th>삭제</th>
      </tr>
      <%
      ArrayList<ShoesBean> lists=sdao.getAllShoes();   
      for(int i=0;i<lists.size();i++){ //for(ShoesBean sb : lists){
         sb=lists.get(i);%>
      <tr>
         <td><input type="checkbox" name="rowcheck" value="<%=sb.getNum()%>"></td>
         <td><%=sb.getNum()%></td>
         <td><%=sb.getId() %></td>
         <td><%=sb.getYear()+"-"+sb.getMonth()+"-"+sb.getDay()%></td>
         <td><img src="images/<%=sb.getBrand() %>.png"> <br><%=sb.getBrand() %></td>
         <td><%=sb.getFeel() %></td>
         <td><%=sb.getDesign() %></td>
         <td><%=sb.getPremium() %></td>
         <td><%=sb.getSum() %></td>
         <td><a href="updateForm.jsp?num=<%=sb.getNum()%>">수정</a></td>      
         <td><a href="deleteProc.jsp?num=<%=sb.getNum()%>">삭제</a></td>      
      </tr>
      <%}   %>
   </table>
   <br><br>
   <input type="button" value="삽입" onclick=insert()>
   <input type="button" value="삭제" onclick=selectDelete()>
</form>
<script type="text/javascript">
   function insert(){
      location.href="insertForm.jsp";
   }
   function allDelete(){
      
      //alert(1);   
      rc =document.myform.rowcheck;
   
      if(document.myform.allcheck.checked==true){
         for(i=0;i<rc.length;i++){
            rc[i].checked=true;   
         }
      } else {
         for(i=0;i<rc.length;i++){
            rc[i].checked=false;   
         }                     
      }      
   }
   function selectDelete(){
      rc=document.myform.rowcheck;
      flag=false;
      for(i=0;i<rc.length;i++){
         if(rc[i].checked==true){
            flag=true;   
         }
      }
      if(flag==false){
         alert("삭제할 체크박스를 선택하세요.");
         return;
      }
      document.myform.submit();               
   }   
</script>

5.insertForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
   pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<style type="text/css">
	img{
		height:25%;
		width:25%;
	}
</style>
<script type="text/javascript" src="js/jquery.js"></script>
<script type="text/javascript">
   $(document).ready(function(){
      //alert(1);
      var use;
      var isCheck=false;
      var isChange=false;
      var isBlank=false;
      $('#id_check').click(function(){
         isChange=false;
         isCheck=true;
         if($('input[name="id"]').val()==""){
            alert("입력하세요");
            isBlank=true;
            return;
         }
         isBlank=false;
         $.ajax({
            url : 'id_check_proc.jsp', 
            data : {
               userid : $('input[name="id"]').val() 
            }  ,
            success : function(data){ 
               if($.trim(data)=='YES'){
                  $('#idmessage').html("<font color=green>사용 가능한 아이디입니다.</font>");
                  $('#idmessage').show();
                  use='possible';
                  
               }
               else{
                  $('#idmessage').html("<font color=red>이미 사용중인 아이디입니다.</font>");
                  $('#idmessage').show();
                  use='impossible';                  
               }
            }//success
         });//ajax
      });//click
      $('input[id="sub"]').click(function(){
         //alert(1);
         
         if(use=="impossible"){
            alert("이미 등록된 제품입니다.");            
            return false;
         }
         else if(isCheck==false||isChange==true){
            alert("중복체크 먼저 하세요.");
            return false;
         }
         else if(isBlank==true){
            alert("제품명을 입력하세요.");
            return false;
         }
      });//sub/click
      $('input[name="id"]').keydown(function(){
         //alert(4);
         isCheck=false;
         isChange=true;
         use=null;
         $('#idmessage').css('display','none');
      })
   });//ready
</script>
</head>
<body>
   <h2>신발 추가 insertForm.jsp</h2>
   <form action="insertProc.jsp" method="post" name='myform'>
      제품명 : <input type="text" name="id" value="Masyard 2.0">
      <input type="button" value='중복체크' id="id_check">
      <span id='idmessage'></span>
      <br><br>  
      
      발매일자 :
      <select name="year">
         <%for(int i=2022;i>1970;i--){%>
            <option value="<%=i%>"><%=i%></option>   
         <%}   %>
         
      </select> 년 
      
      <select name="month">
         <%for(int i=1;i<=12;i++){%>
            <option value="<%=i%>"><%=i%></option>   
         <% }%>
         
      </select> 월
      
      <select name="day">
         <%for(int i=1;i<=31;i++){%>
            <option value="<%=i%>"><%=i%></option>   
         <% }%>
        </select> 일 <br>
      <br> 브랜드 : <br> 
      <input type="radio" name="brand" value="nike">나이키<br> <img src="images/nike.png"><br> 
      <input type="radio" name="brand" value="adidas">아디다스<br> <img src="images/adidas.png"> <br> 
      <input type="radio" name="brand" value="jordan">조던<br> <img src="images/jordan.png"><br> 
      <input type="radio" name="brand" value="yeezy">이지<br> <img src="images/yeezy.png"><br> <br><br> 

      [점수입력]<br> 
      착용감 :    <input type="text" name="feel" size="10">
      디자인 : <input type="text" name="design" size="10">
      희소성 : <input type="text" name="premium" size="10"><br><br>  
      <input type="submit" value="가입하기" id="sub">
   </form>

</body>
</html>

6.insertProc.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
insertProc.jsp<br>
<%request.setCharacterEncoding("UTF-8"); %>
<jsp:useBean id="sb" class="myPkg.ShoesBean"/>
<jsp:useBean id="sdao" class="myPkg.ShoesDao"/>
<jsp:setProperty property="*" name="sb"/>
<% 
	sb.setSum(sb.getFeel()+sb.getDesign()+sb.getPremium());
	int result = sdao.insertShoes(sb);
	String msg;
	String url;
	if(result>0){
		msg="삽입성공";
		url="select.jsp";
	} else {
		msg="삽입실패";
		url="insertForm.jsp";		
	}
%>
<script type="text/javascript">
	alert('<%=msg%>');
	location.href="<%=url%>"
</script>

7.updateForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <style type="text/css">
	img{
		height:125px;
		width:125px;
	}
</style>
<%request.setCharacterEncoding("UTF-8"); %>
<jsp:useBean id="sb" class="myPkg.ShoesBean"/>
<jsp:useBean id="sdao" class="myPkg.ShoesDao"/>
<%
	int num=Integer.parseInt(request.getParameter("num"));
	sb=sdao.getShoesByNum(num);
%>

updateForm.jsp <br>
<h2>신발 추가 insertForm.jsp</h2>
   <form action="updateProc.jsp" method="post" name='myform'>
   	<input type="hidden" name="num" value="<%=sb.getNum() %>">
      제품명 : <input type="text" name="id" value="<%=sb.getId()%>">
      <br><br>  
      
      발매일자 :
      <select name="year">
         <%for(int i=2022;i>1970;i--){%>
            <option value="<%=i%>"<%if(sb.getYear()==i){%> selected<%} %>><%=i %></option>   
         <%}   %>
         
      </select> 년 
      
      <select name="month">
         <%for(int i=1;i<=12;i++){%>
            <option value="<%=i%>" <%if(sb.getMonth()==i){%> selected<%} %>><%=i%></option>   
         <% }%>
         
      </select> 월
      
      <select name="day">
         <%for(int i=1;i<=31;i++){%>
            <option value="<%=i%>" <%if(sb.getDay()==i){%> selected<%} %>><%=i%></option>   
         <% }%>
        </select> 일 <br>
      <br> 브랜드 : <br> 
      <input type="radio" name="brand" value="nike"
      <%if(sb.getBrand().equals("nike")){ %> checked<%} %>>나이키<br> <img src="images/nike.png"><br> 
      <input type="radio" name="brand" value="adidas"
      <%if(sb.getBrand().equals("adidas")){ %> checked<%} %>>아디다스<br> <img src="images/adidas.png"> <br> 
      <input type="radio" name="brand" value="jordan"
      <%if(sb.getBrand().equals("jordan")){ %> checked<%} %>>조던<br> <img src="images/jordan.png"><br> 
      <input type="radio" name="brand" value="yeezy"
      <%if(sb.getBrand().equals("yeezy")){ %> checked<%} %>>이지<br> <img src="images/yeezy.png"><br> <br><br> 

      [점수입력]<br> 
      착용감 :    <input type="text" name="feel" size="10" value="<%=sb.getFeel()%>">
      디자인 : <input type="text" name="design" size="10" value="<%=sb.getDesign() %>">
      희소성 : <input type="text" name="premium" size="10" value="<%=sb.getPremium()%>"><br><br>  
      <input type="submit" value="수정하기" id="sub">
   </form>

8.updateProc.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
updateProc.jsp<br>
<%request.setCharacterEncoding("UTF-8"); %>
<jsp:useBean id="sb" class="myPkg.ShoesBean"/>
<jsp:useBean id="sdao" class="myPkg.ShoesDao"/>
<jsp:setProperty property="*" name="sb"/>
<% 
	sb.setSum(sb.getFeel()+sb.getDesign()+sb.getPremium());
	int result = sdao.updateShoes(sb);
	String msg;
	String url;
	if(result>0){
		msg="수정성공";
		url="select.jsp";
	} else {
		msg="수정실패";
		url="updateForm.jsp?num="+sb.getNum();		
	}
%>
<script type="text/javascript">
	alert('<%=msg%>');
	location.href="<%=url%>"
</script>

9.deleteProc.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
deleteProc.jsp<br>
<jsp:useBean id="sdao" class="myPkg.ShoesDao"/>
<% 
	int num=Integer.parseInt(request.getParameter("num"));
	int result=sdao.deleteShoes(num);
	String msg;
	if(result>0){
		msg="삭제성공";
	} else {
		msg="삭제실패";	
	}
%>
<script type="text/javascript">
	alert('<%=msg%>');
	location.href="select.jsp";
</script>

10.deleteAll.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
deleteAll.jsp<br>
<jsp:useBean id="sdao" class="myPkg.ShoesDao"/>
<% 
	String[] deleteArr=request.getParameterValues("rowcheck");
	int result=sdao.deleteShoesByNum(deleteArr);
	String msg= result+"개 삭제 완료";
%>
<script type="text/javascript">
	alert('<%=msg%>');
	location.href='select.jsp';
</script>

11.id_check_proc.jsp(ajax)

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%request.setCharacterEncoding("UTF-8");%>
<jsp:useBean id="sdao" class="myPkg.ShoesDao"/>
<% 
	String userid=request.getParameter("userid");
	boolean isCheck=sdao.searchId(userid);  
	System.out.println("isCheck:"+isCheck);
	String str;
	if(isCheck){ 
		str="NO";
		System.out.println("str:"+str);
		out.print(str); 
	} else { 
		str="YES";
		System.out.println("str:"+str);
		out.print(str);
	}
%>

'Develop > JavaScript JQuery JSP' 카테고리의 다른 글

0805 XML,EL,JSTL (0)	2022.08.10
0721 게시판 만들기 (0)	2022.07.22
0721 게시판의 구조 + ip주소 방식 설정 (0)	2022.07.22
0720 JSP (0)	2022.07.22
0719 DBCP 커넥션풀 (0)	2022.07.22

현재글0720 JSP JDBC로 만든 칼럼 추가 삭제

포페의 개발공부

Today :
Yesterday :

일	월	화	수	목	금	토
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

포페의 개발공부