본문 바로가기
[2020]KIC 캠퍼스 복습/JAVA SCRIPT(5,6,7)

[코드리뷰] JSP) Connection pooling (상품자료)

by 두블두블 2020. 8. 27.

JSP의 JDBC Connection pooling기능에 대해 알아보자. 

여러 사용자가 필요할 때 마다 하나씩 꺼내서 사용하고 다시 집어넣는(Pooling) 방식

학습목표

1. DTO와 JDBC를 작성할 수 있다. 

2. FORM BEANS를 작성할 수 있다.

3. SQL의 보안을 올리고, 데이터들을 마음대로 핸들링할 수 있다. 

<코드 1 : beandb2.jsp>

<%@page import="pack3.SangpumDto"%>
<%@page import="java.util.ArrayList"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<jsp:useBean id="cdp" class = "pack3.ConnDbPooling" scope="page"/>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
function funcUp(){
	//alert("a");
	var code = prompt("수정할 코드 입력","");
	//alert("code");
	if(code !== "" && code !== null){
		location.href="beandb2_up.jsp?code=" + code;
		//response.send..?
	}
}


function funcDel(){
	//alert("b");
	var code = prompt("삭제할 코드 입력","");
	if(code !== "" && code !== null){
		if(confirm("정말 삭제할까요?") == true){
			location.href="beandb2_del.jsp?code=" + code;
		}
	}	
}

</script>
</head>
<body>
<h2>* 상품자료(beans + dbConnection Cooling/dbcp 사용) *</h2>
<a href = "beandb2_ins.html">추가</a>&nbsp;
<a href = "javascript:funcUp()">수정</a>&nbsp;
<a href = "javascript:funcDel()">삭제</a><br>
<table border='1'>
   <tr><td>코드</td><td>품명</td><td>수량</td><td>단가</td></tr>
	<%
	ArrayList<SangpumDto> list = cdp.getDataAll();
	for(SangpumDto s:list){
	%>
	<tr>
		<td><%=s.getCode() %> </td>
		<td><%=s.getSang() %> </td>
		<td><%=s.getSu() %> </td>
		<td><%=s.getDan() %> </td>
	</tr>
	<%	
	}	
	%>   
</table>
</body>
</html>

 

<코드 2 : beandb2_ins.html>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script src="http://code.jquery.com/jquery-latest.js"></script>
<script type="text/javascript">
$(document).ready(function(){
	$("#btnIns").click(function(){
		//alert("c");
		if($("#sang").val()=== ""){
			alert("상품명이 공란입니다.");
			return;
		}
		//...
		$("#frm").submit();
	});	

	$("#btnList").bind("click", listFunc);
});

function listFunc(){
	//alert("d");
	//history.back();
	location.href="beandb2.jsp"
}
</script>
</head>
<body>
* 상품추가 *<br>
<form action="beandb2_ins.jsp" method="post" id="frm">
품명 : <input type=text name="sang" id="sang"><br>
수량 : <input type=text name="su" id="su"><br>
단가 : <input type=text name="dan" id="dan"><br>
<br>
<br>
<input type="button" value="자료추가" id="btnIns">
<input type="reset" value="입력취소">
<input type="button" value="목록보기" id="btnList">
 
</form>
</body>
</html>

 

<코드 3 : SangpumDto.java>

package pack3;

public class SangpumDto {
	private String code, sang;
	private int su, dan;
	
	public String getCode() {
		return code;
	}
	public void setCode(String code) {
		this.code = code;
	}
	public String getSang() {
		return sang;
	}
	public void setSang(String sang) {
		this.sang = sang;
	}
	public int getSu() {
		return su;
	}
	public void setSu(int su) {
		this.su = su;
	}
	public int getDan() {
		return dan;
	}
	public void setDan(int dan) {
		this.dan = dan;
	}
}

<코드 4 : SangpumBean.java>

package pack3;

public class SangpumBean {
	private String code, sang, su, dan;

	public String getCode() {
		return code;
	}

	public void setCode(String code) {
		this.code = code;
	}

	public String getSang() {
		return sang;
	}

	public void setSang(String sang) {
		this.sang = sang;
	}

	public String getSu() {
		return su;
	}

	public void setSu(String su) {
		this.su = su;
	}

	public String getDan() {
		return dan;
	}

	public void setDan(String dan) {
		this.dan = dan;
	}	
}

<코드 5 : ★★★★★★ConnDbPooling.java>

package pack3;

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

import javax.naming.Context; //추가
import javax.naming.InitialContext;
import javax.sql.DataSource;


import pack3.SangpumDto;

public class ConnDbPooling {
	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;
	private DataSource ds; // 추가

	public ConnDbPooling() {
		try {
			Context context = new InitialContext(); // JDNI
			ds = (DataSource) context.lookup("java:comp/env/jdbc_maria");
		} catch (Exception e) {
			System.out.println("ConnDbPooling err: " + e);
		}
	}

	public ArrayList<SangpumDto> getDataAll() {
		ArrayList<SangpumDto> list = new ArrayList<SangpumDto>();

		try {
			String sql = "select * from sangdata";
			conn = ds.getConnection(); //보안도 강화되고, 코드도 줄이고 
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				SangpumDto dto = new SangpumDto();
				dto.setCode(rs.getString("code"));
				dto.setSang(rs.getString("sang"));
				dto.setSu(rs.getInt("su"));
				dto.setDan(rs.getInt("dan"));
				list.add(dto);
			}
			System.out.println("list :" + list.size());
		} catch (Exception e) {
			System.out.println("getDataAll err: " + e);
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception e2) {

			}
		}
		return list;
	}
	//=======================Insert===================
	public boolean insertData(SangpumBean bean) {
		boolean b = false;
		String sql= "";
		try {
			//새 상품 코드 구하기
			sql="select max(code) as max from sangdata";
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			int maxCode = 0;
			if(rs.next()) {
				maxCode = rs.getInt("max");
				
			}
			maxCode += 1;
			//추가 
			pstmt.close();
			sql = "insert into sangdata values(?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, maxCode);
			pstmt.setString(2, bean.getSang());
			pstmt.setString(3, bean.getSu());
			pstmt.setString(4, bean.getDan());
			int re = pstmt.executeUpdate();
			if(re == 1) b = true;
		} catch (Exception e) {
			System.out.println("insertData err: " + e);
		} finally {
			try {
				if (rs != null)	rs.close();
				if (pstmt != null)	pstmt.close();
				if (conn != null)	conn.close();
			} catch (Exception e2) {

			}
		}
		return b;
	}
	//==================================================
	public SangpumDto updateDataRead(String code) {
		SangpumDto dto = null;
		String sql= "";
		try {
			sql = "select * from sangdata where code=?";
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, code);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				dto = new SangpumDto();
				dto.setCode(rs.getString(1));
				dto.setSang(rs.getString(2));
				dto.setSu(rs.getInt(3));
				dto.setDan(rs.getInt(4));
			}			
			
		} catch (Exception e) {
			System.out.println("updateDataRead err: " + e);
		} finally {
			try {
				if (rs != null)	rs.close();
				if (pstmt != null)	pstmt.close();
				if (conn != null)	conn.close();
			} catch (Exception e2) {

			}
		}
		return dto;		
	}
	//=========================Update==========================
	public boolean updateData(SangpumBean bean) {
		boolean b = false;
		try {
			conn = ds.getConnection(); 
			//DML문
			String sql = "update sangdata set sang=?, su=?, dan=? where code=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bean.getSang());
			pstmt.setString(2, bean.getSu());
			pstmt.setString(3, bean.getDan());
			pstmt.setString(4, bean.getCode());
			if(pstmt.executeUpdate() > 0) b = true;
			
		} catch (Exception e) {
			System.out.println("updateData err: " + e);
		} finally {
			try {
				if (rs != null)	rs.close();
				if (pstmt != null)	pstmt.close();
				if (conn != null)	conn.close();
			} catch (Exception e2) {

			}
		}
		return b;
	}
	//================================deleteData
	public boolean deleteData(String code) {
		boolean b = false;
		try {
			conn = ds.getConnection(); 
			//DML문
			String sql = "delete from sangdata where code=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, code);
			if(pstmt.executeUpdate() > 0) b = true;
			
		} catch (Exception e) {
			System.out.println("deleteData err: " + e);
		} finally {
			try {
				if (rs != null)	rs.close();
				if (pstmt != null)	pstmt.close();
				if (conn != null)	conn.close();
			} catch (Exception e2) {

			}
		}
		return b;
	}
}

<코드 6 : beandb2_up.jsp>

<%@page import="pack3.SangpumDto"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
request.setCharacterEncoding("UTF-8");
String code = request.getParameter("code");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>상품수정</title>
</head>
<body>
<jsp:useBean id="cdp" class="pack3.ConnDbPooling" />

<% 
SangpumDto dto = cdp.updateDataRead(code);
if(dto ==null){
%>
	<script> 
	alert("중복된 상품 코드가 아닙니다. \n 수정불가");
	location.href="beandb2.jsp";
	</script> 
<% 
	return;
}
%>

** 상품수정 ** <br>
<form action="beandb2_upok.jsp" method="post">
코드 : <%=dto.getCode() %><br>
<input type="hidden" name="code" value="<%=dto.getCode() %>">
품명 : <input type="text" name="sang" value="<%=dto.getSang() %>"><br>
수량 : <input type="text" name="su" value="<%=dto.getSu() %>"><br>
단가 : <input type="text" name="dan" value="<%=dto.getDan() %>"><br>
<br>
<input type="submit" value = "자료수정">
<input type="button" value = "목록보기" onclick="javascript:location.href='beandb2.jsp'"> 
</form>
</body>
</html>

<코드 7 : beandb2_upok.jsp>

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

<%
request.setCharacterEncoding("utf-8");
%>
<jsp:useBean id="bean" class="pack3.SangpumBean"/>
<jsp:setProperty property="*" name="bean"/>
<jsp:useBean id="cdp" class="pack3.ConnDbPooling"/>

<%
if(cdp.updateData(bean)){
	response.sendRedirect("beandb2.jsp"); // 수정 후 목록보기
}else{
	response.sendRedirect("beandb2_fail.html"); // 에러페이지
}
%>

<코드 8 : beandb2_del.jsp>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
request.setCharacterEncoding("utf-8");
String code = request.getParameter("code"); %>
<jsp:useBean id="cdp" class="pack3.ConnDbPooling"/>

<%
if(cdp.deleteData(code)){
	response.sendRedirect("beandb2.jsp"); // 삭제 후 목록보기
}else{
	response.sendRedirect("beandb2_fail.html"); // 에러페이지
}
%>

<코드 9 : beandb2_ins.jsp>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
request.setCharacterEncoding("UTF-8");
%>
<jsp:useBean id="bean" class="pack3.SangpumBean"/>
<jsp:setProperty property="*" name="bean"/>
<jsp:useBean id="cdp" class="pack3.ConnDbPooling"/>

<%
boolean b = cdp.insertData(bean);
if(b)
	response.sendRedirect("beandb2.jsp"); // 추가 후 목록보기
else
	response.sendRedirect("beandb2_fail.html"); // 에러페이지
%>

<코드 10 : beandb2_fail.html>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<b style="font-size: 30px; color: red;">작업 실패!! </b>
<br>
<a href="beandb2.jsp">목록보기 </a>
</body>
</html>

 

결과화면

 

2. 상품자료 (1) 메인화면

2. 상품자료 (2) 상품추가

2. 상품자료 (3) 추가된 데이터와 이를 수정하기위한

2. 상품자료 (3_2) 마리아 DB에도 저장이 됨

2. 상품자료 (4) 상품수정

2. 상품자료 (5) 상품수정 완료

2. 상품자료 (6) 추가된 데이터를 삭제하기위한

2. 상품자료 (7) 삭제전 무조건 한번은 더 물어보게

2. 상품자료 (8) 상품삭제 완료

2. 상품자료 (8_2) DB에서도 상품삭제 완료

 

 

 

저작자표시 비영리 변경금지

'[2020]KIC 캠퍼스 복습 > JAVA SCRIPT(5,6,7)' 카테고리의 다른 글

[수업 D-33 ] JSP)Connection pooling,로그인, 게시판  (0) 2020.08.27
[수업 D-32 ] Servlet)배운내용  (0) 2020.08.26
[코드리뷰] Servlet) Form Beans 예제 1  (0) 2020.08.26
[수업 D-31 ] JSP) 지시어, 내장객체  (0) 2020.08.25
[수업 D-30 ] SERVLET) 쿠키, 세션, 쇼핑몰, 방명록  (0) 2020.08.24

관련글

티스토리툴바