OpenFrameWork
오픈프레임워크_Day45
px
2015. 5. 18. 08:58
### : 목차 구분 기호
--- : 목차 내에 항목 구분 기호
@@@ : 태그 용도
--- : 목차 내에 항목 구분 기호
@@@ : 태그 용도
,,, : 같은 항목 내에 구분 기호
목차
1. 이론 및 정보
2. 설정 및 그 밖에
3. 소스코드 또는 실습
4. 과제
###################################
1. 이론 및 정보
-----------------------------------
8. DataBase
(1) DBCP(DataBase Connection Pool)
1) 기존 방식의 불편한 점
- 지속적인 연결 시도(성능악화)
- 중복된 코드 작성, 코드 변경 수정시 유지 보수 힘들어짐
- DB에 동시접속 제한
DBCP 원리
jsp와 DB 사이에 직접 처리 하던 방식에서 중간에 미들웨어로서 역할을 한다
DBCP는 미리 DB와 연결을 하고 적정수의 연결 객체를 미리 만들어 놓는다
jsp는 DBCP에게서 연결된 객체를 받아 쓰고 반납하는 방식
2) 사용 방법
- 직접 작성
- 외부 라이브러리
- WAS(Web Application Service, 컨테이너 포함한 비싼 웹서버)에서 제공
-----------------------------------
* DAO (Data Access Object)
데이터베이스에 접근하기 위한 용도로 만들어 놓은 객체
-----------------------------------
###################################
2. 설정 및 그 밖에
-----------------------------------
2. 설정 및 그 밖에
-----------------------------------
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/WebContent/v1을 복사
/DBApp/WebContent/v2로 붙여넣기
/DBApp/WebContent/v2를 수정 - 과제 해결
/DBApp/WebContent/v3로 복사 - DBCP 이용, 2가지 방식
/DBApp/WebContent/v3를 복사해서
/DBApp/WebContent/v4 - BEAN으로 만들기
-----------------------------------
* 카페에서 DBConnectionMgr.java 받음
-----------------------------------
* 톰캣의 DBCP Lib
~\apache-tomcat-8.0.21\lib\tomcat-dbcp.jar
위 라이브러리를 접근해서 가져가 쓸 수있음
자바에서는 멀리 떨어져 있는 객체를 찾아가서
내 것처럼 연결 해주는 방식, 찾아가는 방법 제공 -> JNDI
http://tomcat.apache.org/tomcat-8.0-doc/jndi-resources-howto.html
위 사이트에서 jndi 사용법 알려줌
JDBC Data Sources 클릭해서 이동
Configure Tomcat's Resource Factory
안에 내용 복사함
<Resource name="jdbc/EmployeeDB"
auth="Container" - 톰캣에게 맡기겠다는 의미
type="javax.sql.DataSource" - DBCP의 핵심 클래스
username="dbusername"
password="dbpassword"
driverClassName="org.hsql.jdbcDriver"
url="jdbc:HypersonicSQL:database"
maxTotal="8" - 동시 접속 갯수
maxIdle="4"/> - 접속 하지 않아도 4개를 준비 하겠다는 의미
auth="Container" - 톰캣에게 맡기겠다는 의미
type="javax.sql.DataSource" - DBCP의 핵심 클래스
username="dbusername"
password="dbpassword"
driverClassName="org.hsql.jdbcDriver"
url="jdbc:HypersonicSQL:database"
maxTotal="8" - 동시 접속 갯수
maxIdle="4"/> - 접속 하지 않아도 4개를 준비 하겠다는 의미
아래에 수정해서 추가
/Servers/Tomcat v8.0 Server at localhost-config/server.xml
<GlobalNamingResources>
...
<Resource name="jdbc/oracleDB"
auth="Container"
type="javax.sql.DataSource"
username="scott"
password="1111"
driverClassName="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:@localhost:1521:orcl"
maxTotal="8"
maxIdle="4"/>
auth="Container"
type="javax.sql.DataSource"
username="scott"
password="1111"
driverClassName="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:@localhost:1521:orcl"
maxTotal="8"
maxIdle="4"/>
</GlobalNamingResources>
위 내용을 원래는
<Context docBase="DBApp" path="/DBApp" reloadable="true" source="org.eclipse.jst.jee.server:DBApp">
</Context>
</Context>
안에 넣어 줘야 함
그러나 문제는 똑같은 DBCP를 다른 곳에서 사용한다고 하면
프로젝트마다 설정을 해줘야 하는데, 이러한 점이 불편해서
한군데에 설정을 해주는데, 대신 가져다 쓰겠다는 링크를 해줘야함
API는 Resource Links 를 참조
<ResourceLink name="linkToGlobalResource" - Resource의 name
global="simpleValue" - Resource의 name과 같게 해도 됨
type="java.lang.Integer"/> - Resource의 Type
global="simpleValue" - Resource의 name과 같게 해도 됨
type="java.lang.Integer"/> - Resource의 Type
아래와 같이 설정
...
<Context docBase="DBApp" path="/DBApp" reloadable="true" source="org.eclipse.jst.jee.server:DBApp">
<ResourceLink name="jdbc/oracleDB"
global="jdbc/oracleDB"
type="javax.sql.DataSource"/>
</Context>
<ResourceLink name="jdbc/oracleDB"
global="jdbc/oracleDB"
type="javax.sql.DataSource"/>
</Context>
...
/DBApp/WebContent/WEB-INF/web.xml
에 사용하겠다고 선언해야함
API는 web.xml configuration 의 내용을 참조해서 아래 내용을 추가
/DBApp/WebContent/WEB-INF/web.xml
...
<resource-ref>
<res-ref-name>jdbc/oracleDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
<res-ref-name>jdbc/oracleDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
...
-----------------------------------
###################################
3. 소스코드 또는 실습
3. 소스코드 또는 실습
-----------------------------------
3-1
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/WebContent/v2/addSawon_proc.jsp
/DBApp/WebContent/v2/addSawon_proc.jsp
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page contentType="text/html; charset=EUC-KR"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%
request.setCharacterEncoding("euc-kr");
String s_id = request.getParameter("s_id");
String s_name = request.getParameter("s_name");
String s_pass = request.getParameter("s_pass");
String s_age = request.getParameter("s_age");
String s_addr = request.getParameter("s_addr");
String s_dept = request.getParameter("s_dept");
String sql = "insert into "
+ "tblsawon(s_no,s_id,s_name,s_pass,s_age,s_addr,s_dept)"
+ " values(seq_no.nextVal,?,?,?,?,?,?)";
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(url, "scott","1111");
stmt = con.prepareStatement(sql);
stmt.setString(1, s_id);
stmt.setString(2, s_name);
stmt.setString(3, s_pass);
stmt.setString(4, s_age);
stmt.setString(5, s_addr);
stmt.setString(6, s_dept);
int result = stmt.executeUpdate();
if(result > 0){
%>
<script>
alert("사원이 잘 추가 되었습니다.");
location.href="index.jsp";
</script>
<%
}
}catch(Exception e){
System.out.println("addSawon_proc.jsp : "+e);
}finally{
if(rs!=null)try{rs.close();}catch(Exception e){}
if(stmt!=null)try{stmt.close();}catch(Exception e){}
if(con!=null)try{con.close();}catch(Exception e){}
}
%>
</body>
</html>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page contentType="text/html; charset=EUC-KR"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%
request.setCharacterEncoding("euc-kr");
String s_id = request.getParameter("s_id");
String s_name = request.getParameter("s_name");
String s_pass = request.getParameter("s_pass");
String s_age = request.getParameter("s_age");
String s_addr = request.getParameter("s_addr");
String s_dept = request.getParameter("s_dept");
String sql = "insert into "
+ "tblsawon(s_no,s_id,s_name,s_pass,s_age,s_addr,s_dept)"
+ " values(seq_no.nextVal,?,?,?,?,?,?)";
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(url, "scott","1111");
stmt = con.prepareStatement(sql);
stmt.setString(1, s_id);
stmt.setString(2, s_name);
stmt.setString(3, s_pass);
stmt.setString(4, s_age);
stmt.setString(5, s_addr);
stmt.setString(6, s_dept);
int result = stmt.executeUpdate();
if(result > 0){
%>
<script>
alert("사원이 잘 추가 되었습니다.");
location.href="index.jsp";
</script>
<%
}
}catch(Exception e){
System.out.println("addSawon_proc.jsp : "+e);
}finally{
if(rs!=null)try{rs.close();}catch(Exception e){}
if(stmt!=null)try{stmt.close();}catch(Exception e){}
if(con!=null)try{con.close();}catch(Exception e){}
}
%>
</body>
</html>
-----------------------------------
3-2
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/WebContent/v2/addSawon.html
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
<h1>사원 추가</h1>
<form action="addSawon_proc.jsp" method="post">
<table border="1">
<tr>
<td align="center">아이디</td>
<td><input type="text" name="s_id" /></td>
</tr>
<tr>
<td align="center">이름</td>
<td><input type="text" name="s_name" /></td>
</tr>
<tr>
<td align="center">나이</td>
<td><input type="text" name="s_age" /></td>
</tr>
<tr>
<td align="center">패스워드</td>
<td><input type="password" name="s_pass" /></td>
</tr>
<tr>
<td align="center">근무지</td>
<td>
<select name="s_addr">
<option value="서울">서울</option>
<option value="광주">광주</option>
<option value="부산">부산</option>
<option value="제주">제주</option>
</select>
</td>
</tr>
<tr>
<td align="center">부서명</td>
<td>
<select name="s_dept">
<option value="영업">영업</option>
<option value="기술">기술</option>
<option value="기획">기획</option>
</select>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="추가" />
<input type="reset" value="다시 작성" />
</td>
</tr>
</table>
</form>
</body>
</html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
<h1>사원 추가</h1>
<form action="addSawon_proc.jsp" method="post">
<table border="1">
<tr>
<td align="center">아이디</td>
<td><input type="text" name="s_id" /></td>
</tr>
<tr>
<td align="center">이름</td>
<td><input type="text" name="s_name" /></td>
</tr>
<tr>
<td align="center">나이</td>
<td><input type="text" name="s_age" /></td>
</tr>
<tr>
<td align="center">패스워드</td>
<td><input type="password" name="s_pass" /></td>
</tr>
<tr>
<td align="center">근무지</td>
<td>
<select name="s_addr">
<option value="서울">서울</option>
<option value="광주">광주</option>
<option value="부산">부산</option>
<option value="제주">제주</option>
</select>
</td>
</tr>
<tr>
<td align="center">부서명</td>
<td>
<select name="s_dept">
<option value="영업">영업</option>
<option value="기술">기술</option>
<option value="기획">기획</option>
</select>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="추가" />
<input type="reset" value="다시 작성" />
</td>
</tr>
</table>
</form>
</body>
</html>
-----------------------------------
3-3
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/WebContent/v2/editSawon_proc.jsp
<%@ page contentType="text/html; charset=EUC-KR"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%
request.setCharacterEncoding("euc-kr");
String s_no = request.getParameter("s_no");
String s_id = request.getParameter("s_id");
String s_name = request.getParameter("s_name");
String s_pass = request.getParameter("s_pass");
String s_age = request.getParameter("s_age");
String s_addr = request.getParameter("s_addr");
String s_dept = request.getParameter("s_dept");
String sql = "update tblsawon set "
+ "s_id=?, s_name=?, s_pass=?,"
+ "s_age=?, s_addr=?, s_dept=? "
+ "where s_no = ?";
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(url, "scott","1111");
stmt = con.prepareStatement(sql);
stmt.setString(1, s_id);
stmt.setString(2, s_name);
stmt.setString(3, s_pass);
stmt.setString(4, s_age);
stmt.setString(5, s_addr);
stmt.setString(6, s_dept);
stmt.setString(7, s_no);
int result = stmt.executeUpdate();
if(result > 0){
%>
<script>
alert("사원이 잘 수정 되었습니다.");
location.href="index.jsp";
</script>
<%
}
}catch(Exception e){
System.out.println("editSawon_proc.jsp : "+e);
}finally{
if(rs!=null)try{rs.close();}catch(Exception e){}
if(stmt!=null)try{stmt.close();}catch(Exception e){}
if(con!=null)try{con.close();}catch(Exception e){}
}
%>
</body>
</html>
<%@ page import="java.sql.*"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%
request.setCharacterEncoding("euc-kr");
String s_no = request.getParameter("s_no");
String s_id = request.getParameter("s_id");
String s_name = request.getParameter("s_name");
String s_pass = request.getParameter("s_pass");
String s_age = request.getParameter("s_age");
String s_addr = request.getParameter("s_addr");
String s_dept = request.getParameter("s_dept");
String sql = "update tblsawon set "
+ "s_id=?, s_name=?, s_pass=?,"
+ "s_age=?, s_addr=?, s_dept=? "
+ "where s_no = ?";
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(url, "scott","1111");
stmt = con.prepareStatement(sql);
stmt.setString(1, s_id);
stmt.setString(2, s_name);
stmt.setString(3, s_pass);
stmt.setString(4, s_age);
stmt.setString(5, s_addr);
stmt.setString(6, s_dept);
stmt.setString(7, s_no);
int result = stmt.executeUpdate();
if(result > 0){
%>
<script>
alert("사원이 잘 수정 되었습니다.");
location.href="index.jsp";
</script>
<%
}
}catch(Exception e){
System.out.println("editSawon_proc.jsp : "+e);
}finally{
if(rs!=null)try{rs.close();}catch(Exception e){}
if(stmt!=null)try{stmt.close();}catch(Exception e){}
if(con!=null)try{con.close();}catch(Exception e){}
}
%>
</body>
</html>
-----------------------------------
3-4
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/WebContent/v2/editSawon.jsp
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page contentType="text/html; charset=EUC-KR"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%
String s_no = request.getParameter("s_no");
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String sql = "select * from tblsawon where s_no = ?";
String s_id=null,s_name=null,s_pass=null,
s_addr=null,s_dept=null,s_age=null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(url, "scott","1111");
stmt = con.prepareStatement(sql);
stmt.setString(1, s_no);
rs = stmt.executeQuery();
if(rs.next()){
s_no = rs.getString("s_no");
s_id = rs.getString("s_id");
s_name = rs.getString("s_name");
s_pass = rs.getString("s_pass");
s_age = rs.getString("s_age");
s_addr = rs.getString("s_addr");
s_dept = rs.getString("s_dept");
}
}catch(Exception e){
System.out.println("editSawon.jsp : "+e);
}finally{
if(rs!=null)try{rs.close();}catch(Exception e){}
if(stmt!=null)try{stmt.close();}catch(Exception e){}
if(con!=null)try{con.close();}catch(Exception e){}
}
%>
<form action="editSawon_proc.jsp" method="post">
<input type="hidden" name="s_no" value="<%=s_no%>"/>
<table border="1">
<tr>
<td align="center">아이디</td>
<td><input type="text" name="s_id" value="<%=s_id%>"/></td>
</tr>
<tr>
<td align="center">이름</td>
<td><input type="text" name="s_name" value="<%=s_name%>"/></td>
</tr>
<tr>
<td align="center">나이</td>
<td><input type="text" name="s_age" value="<%=s_age%>"/></td>
</tr>
<tr>
<td align="center">패스워드</td>
<td><input type="password" name="s_pass" value="<%=s_pass%>"/></td>
</tr>
<tr>
<td align="center">근무지</td>
<td>
<select name="s_addr">
<option value="서울"<%if(s_addr.equals("서울")){%>selected="selected"<%}%>>서울</option>
<option value="광주"<%if(s_addr.equals("광주")){%>selected="selected"<%}%>>광주</option>
<option value="부산"<%if(s_addr.equals("부산")){%>selected="selected"<%}%>>부산</option>
<option value="제주"<%if(s_addr.equals("제주")){%>selected="selected"<%}%>>제주</option>
</select>
</td>
</tr>
<tr>
<td align="center">부서명</td>
<td>
<select name="s_dept">
<option value="영업"<%if(s_dept.equals("영업")){%>selected="selected"<%}%>>영업</option>
<option value="기술"<%if(s_dept.equals("기술")){%>selected="selected"<%}%>>기술</option>
<option value="기획"<%if(s_dept.equals("기획")){%>selected="selected"<%}%>>기획</option>
</select>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="수정" />
<input type="reset" value="다시 작성" />
</td>
</tr>
</table>
</form>
</body>
</html>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page contentType="text/html; charset=EUC-KR"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%
String s_no = request.getParameter("s_no");
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String sql = "select * from tblsawon where s_no = ?";
String s_id=null,s_name=null,s_pass=null,
s_addr=null,s_dept=null,s_age=null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(url, "scott","1111");
stmt = con.prepareStatement(sql);
stmt.setString(1, s_no);
rs = stmt.executeQuery();
if(rs.next()){
s_no = rs.getString("s_no");
s_id = rs.getString("s_id");
s_name = rs.getString("s_name");
s_pass = rs.getString("s_pass");
s_age = rs.getString("s_age");
s_addr = rs.getString("s_addr");
s_dept = rs.getString("s_dept");
}
}catch(Exception e){
System.out.println("editSawon.jsp : "+e);
}finally{
if(rs!=null)try{rs.close();}catch(Exception e){}
if(stmt!=null)try{stmt.close();}catch(Exception e){}
if(con!=null)try{con.close();}catch(Exception e){}
}
%>
<form action="editSawon_proc.jsp" method="post">
<input type="hidden" name="s_no" value="<%=s_no%>"/>
<table border="1">
<tr>
<td align="center">아이디</td>
<td><input type="text" name="s_id" value="<%=s_id%>"/></td>
</tr>
<tr>
<td align="center">이름</td>
<td><input type="text" name="s_name" value="<%=s_name%>"/></td>
</tr>
<tr>
<td align="center">나이</td>
<td><input type="text" name="s_age" value="<%=s_age%>"/></td>
</tr>
<tr>
<td align="center">패스워드</td>
<td><input type="password" name="s_pass" value="<%=s_pass%>"/></td>
</tr>
<tr>
<td align="center">근무지</td>
<td>
<select name="s_addr">
<option value="서울"<%if(s_addr.equals("서울")){%>selected="selected"<%}%>>서울</option>
<option value="광주"<%if(s_addr.equals("광주")){%>selected="selected"<%}%>>광주</option>
<option value="부산"<%if(s_addr.equals("부산")){%>selected="selected"<%}%>>부산</option>
<option value="제주"<%if(s_addr.equals("제주")){%>selected="selected"<%}%>>제주</option>
</select>
</td>
</tr>
<tr>
<td align="center">부서명</td>
<td>
<select name="s_dept">
<option value="영업"<%if(s_dept.equals("영업")){%>selected="selected"<%}%>>영업</option>
<option value="기술"<%if(s_dept.equals("기술")){%>selected="selected"<%}%>>기술</option>
<option value="기획"<%if(s_dept.equals("기획")){%>selected="selected"<%}%>>기획</option>
</select>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="수정" />
<input type="reset" value="다시 작성" />
</td>
</tr>
</table>
</form>
</body>
</html>
-----------------------------------
3-5
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/WebContent/v2/deleteSawon.jsp
<%@ page contentType="text/html; charset=EUC-KR"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%
request.setCharacterEncoding("euc-kr");
String s_no = request.getParameter("s_no");
String sql = "delete from tblsawon where s_no = ?";
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(url, "scott","1111");
stmt = con.prepareStatement(sql);
stmt.setString(1, s_no);
int result = stmt.executeUpdate();
if(result > 0){
%>
<script>
alert("사원이 잘 삭제 되었습니다.");
location.href="index.jsp";
</script>
<%
}
}catch(Exception e){
System.out.println("deleteSawon_proc.jsp : "+e);
}finally{
if(rs!=null)try{rs.close();}catch(Exception e){}
if(stmt!=null)try{stmt.close();}catch(Exception e){}
if(con!=null)try{con.close();}catch(Exception e){}
}
%>
</body>
</html>
<%@ page import="java.sql.*"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%
request.setCharacterEncoding("euc-kr");
String s_no = request.getParameter("s_no");
String sql = "delete from tblsawon where s_no = ?";
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(url, "scott","1111");
stmt = con.prepareStatement(sql);
stmt.setString(1, s_no);
int result = stmt.executeUpdate();
if(result > 0){
%>
<script>
alert("사원이 잘 삭제 되었습니다.");
location.href="index.jsp";
</script>
<%
}
}catch(Exception e){
System.out.println("deleteSawon_proc.jsp : "+e);
}finally{
if(rs!=null)try{rs.close();}catch(Exception e){}
if(stmt!=null)try{stmt.close();}catch(Exception e){}
if(con!=null)try{con.close();}catch(Exception e){}
}
%>
</body>
</html>
-----------------------------------
3-6
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/WebContent/v2/index.jsp
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page contentType="text/html; charset=EUC-KR"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
<script>
function delCheck(s_no) {
var result = confirm("정말 삭제하시겠습니까?");
if(result == true){
location.href="deleteSawon.jsp?s_no="+s_no;
}
}
</script>
</head>
<body>
<h1>직원 정보 리스트</h1>
<a href="addSawon.html">사원추가</a>
<br/>
<%
request.setCharacterEncoding("euc-kr");
String sql = "select * from tblsawon order by s_no";
String search = request.getParameter("search");
String searchText = request.getParameter("searchText");
if(searchText != null){
sql = "select * from tblsawon where "+search+" like ? order by s_no";
}
%>
<form method="post" action="index.jsp">
<select name="search">
<option value="s_id">id</option>
<option value="s_addr">근무지</option>
<option value="s_dept">부서명</option>
</select>
<input type="text" name="searchText"/>
<input type="submit" name="검색"/>
</form>
<table border="1">
<tr>
<th>사번</th>
<th>아이디</th>
<th>이름</th>
<th>패스워드</th>
<th>나이</th>
<th>근무지</th>
<th>부서명</th>
<th>수정</th>
<th>삭제</th>
</tr>
<%
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(url, "scott","1111");
stmt = con.prepareStatement(sql);
if(searchText != null){
stmt.setString(1, "%"+searchText+"%");
}
rs = stmt.executeQuery();
while(rs.next()){
int s_no = rs.getInt("s_no");
String s_id = rs.getString("s_id");
String s_name = rs.getString("s_name");
String s_pw = rs.getString("s_pass");
int s_age = rs.getInt("s_age");
String s_addr = rs.getString("s_addr");
String s_dept = rs.getString("s_dept");
%>
<tr>
<th><%=s_no%></th>
<th><%=s_id%></th>
<th><%=s_name%></th>
<th><%=s_pw%></th>
<th><%=s_age%></th>
<th><%=s_addr%></th>
<th><%=s_dept%></th>
<th><a href="editSawon.jsp?s_no=<%=s_no%>">수정</a></th>
<th><a href="javascript:delCheck('<%=s_no%>')">삭제</a></th>
</tr>
<%
}
}catch(Exception e){
System.out.println("index.jsp : "+e);
}finally{
if(rs!=null)try{rs.close();}catch(Exception e){}
if(stmt!=null)try{stmt.close();}catch(Exception e){}
if(con!=null)try{con.close();}catch(Exception e){}
}
%>
</table>
</body>
</html>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page contentType="text/html; charset=EUC-KR"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
<script>
function delCheck(s_no) {
var result = confirm("정말 삭제하시겠습니까?");
if(result == true){
location.href="deleteSawon.jsp?s_no="+s_no;
}
}
</script>
</head>
<body>
<h1>직원 정보 리스트</h1>
<a href="addSawon.html">사원추가</a>
<br/>
<%
request.setCharacterEncoding("euc-kr");
String sql = "select * from tblsawon order by s_no";
String search = request.getParameter("search");
String searchText = request.getParameter("searchText");
if(searchText != null){
sql = "select * from tblsawon where "+search+" like ? order by s_no";
}
%>
<form method="post" action="index.jsp">
<select name="search">
<option value="s_id">id</option>
<option value="s_addr">근무지</option>
<option value="s_dept">부서명</option>
</select>
<input type="text" name="searchText"/>
<input type="submit" name="검색"/>
</form>
<table border="1">
<tr>
<th>사번</th>
<th>아이디</th>
<th>이름</th>
<th>패스워드</th>
<th>나이</th>
<th>근무지</th>
<th>부서명</th>
<th>수정</th>
<th>삭제</th>
</tr>
<%
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(url, "scott","1111");
stmt = con.prepareStatement(sql);
if(searchText != null){
stmt.setString(1, "%"+searchText+"%");
}
rs = stmt.executeQuery();
while(rs.next()){
int s_no = rs.getInt("s_no");
String s_id = rs.getString("s_id");
String s_name = rs.getString("s_name");
String s_pw = rs.getString("s_pass");
int s_age = rs.getInt("s_age");
String s_addr = rs.getString("s_addr");
String s_dept = rs.getString("s_dept");
%>
<tr>
<th><%=s_no%></th>
<th><%=s_id%></th>
<th><%=s_name%></th>
<th><%=s_pw%></th>
<th><%=s_age%></th>
<th><%=s_addr%></th>
<th><%=s_dept%></th>
<th><a href="editSawon.jsp?s_no=<%=s_no%>">수정</a></th>
<th><a href="javascript:delCheck('<%=s_no%>')">삭제</a></th>
</tr>
<%
}
}catch(Exception e){
System.out.println("index.jsp : "+e);
}finally{
if(rs!=null)try{rs.close();}catch(Exception e){}
if(stmt!=null)try{stmt.close();}catch(Exception e){}
if(con!=null)try{con.close();}catch(Exception e){}
}
%>
</table>
</body>
</html>
-----------------------------------
3-7
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/src/dbcp/custom/DBConnectionMgr.java
package dbcp.custom;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Vector;
public class DBConnectionMgr {
private Vector connections = new Vector(10);
private String _driver = "oracle.jdbc.driver.OracleDriver",
_url = "jdbc:oracle:thin:@localhost:1521:orcl",
_user = "scott",
_password = "1111";
private boolean _traceOn = false;
private boolean initialized = false;
private int _openConnections = 10;
private static DBConnectionMgr instance = null;
public DBConnectionMgr() {
}
/** Use this method to set the maximum number of open connections before
unused connections are closed.
*/
public static DBConnectionMgr getInstance() {
if (instance == null) {
synchronized (DBConnectionMgr.class) {
if (instance == null) {
instance = new DBConnectionMgr();
}
}
}
return instance;
}
public void setOpenConnectionCount(int count) {
_openConnections = count;
}
public void setEnableTrace(boolean enable) {
_traceOn = enable;
}
/** Returns a Vector of java.sql.Connection objects */
public Vector getConnectionList() {
return connections;
}
/** Opens specified "count" of connections and adds them to the existing pool */
public synchronized void setInitOpenConnections(int count)
throws SQLException {
Connection c = null;
ConnectionObject co = null;
for (int i = 0; i < count; i++) {
c = createConnection();
co = new ConnectionObject(c, false);
connections.addElement(co);
trace("ConnectionPoolManager: Adding new DB connection to pool (" + connections.size() + ")");
}
}
/** Returns a count of open connections */
public int getConnectionCount() {
return connections.size();
}
/** Returns an unused existing or new connection. */
public synchronized Connection getConnection()
throws Exception {
if (!initialized) {
Class c = Class.forName(_driver);
DriverManager.registerDriver((Driver) c.newInstance());
initialized = true;
}
Connection c = null;
ConnectionObject co = null;
boolean badConnection = false;
for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.elementAt(i);
// If connection is not in use, test to ensure it's still valid!
if (!co.inUse) {
try {
badConnection = co.connection.isClosed();
if (!badConnection)
badConnection = (co.connection.getWarnings() != null);
} catch (Exception e) {
badConnection = true;
e.printStackTrace();
}
// Connection is bad, remove from pool
if (badConnection) {
connections.removeElementAt(i);
trace("ConnectionPoolManager: Remove disconnected DB connection #" + i);
continue;
}
c = co.connection;
co.inUse = true;
trace("ConnectionPoolManager: Using existing DB connection #" + (i + 1));
break;
}
}
if (c == null) {
c = createConnection();
co = new ConnectionObject(c, true);
connections.addElement(co);
trace("ConnectionPoolManager: Creating new DB connection #" + connections.size());
}
return c;
}
/** Marks a flag in the ConnectionObject to indicate this connection is no longer in use */
public synchronized void freeConnection(Connection c) {
if (c == null)
return;
ConnectionObject co = null;
for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.elementAt(i);
if (c == co.connection) {
co.inUse = false;
break;
}
}
for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.elementAt(i);
if ((i + 1) > _openConnections && !co.inUse)
removeConnection(co.connection);
}
}
public void freeConnection(Connection c, PreparedStatement p, ResultSet r) {
try {
if (r != null) r.close();
if (p != null) p.close();
freeConnection(c);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void freeConnection(Connection c, Statement s, ResultSet r) {
try {
if (r != null) r.close();
if (s != null) s.close();
freeConnection(c);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void freeConnection(Connection c, PreparedStatement p) {
try {
if (p != null) p.close();
freeConnection(c);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void freeConnection(Connection c, Statement s) {
try {
if (s != null) s.close();
freeConnection(c);
} catch (SQLException e) {
e.printStackTrace();
}
}
/** Marks a flag in the ConnectionObject to indicate this connection is no longer in use */
public synchronized void removeConnection(Connection c) {
if (c == null)
return;
ConnectionObject co = null;
for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.elementAt(i);
if (c == co.connection) {
try {
c.close();
connections.removeElementAt(i);
trace("Removed " + c.toString());
} catch (Exception e) {
e.printStackTrace();
}
break;
}
}
}
private Connection createConnection()
throws SQLException {
Connection con = null;
try {
if (_user == null)
_user = "";
if (_password == null)
_password = "";
Properties props = new Properties();
props.put("user", _user);
props.put("password", _password);
con = DriverManager.getConnection(_url, props);
} catch (Throwable t) {
throw new SQLException(t.getMessage());
}
return con;
}
/** Closes all connections and clears out the connection pool */
public void releaseFreeConnections() {
trace("ConnectionPoolManager.releaseFreeConnections()");
Connection c = null;
ConnectionObject co = null;
for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.elementAt(i);
if (!co.inUse)
removeConnection(co.connection);
}
}
/** Closes all connections and clears out the connection pool */
public void finalize() {
trace("ConnectionPoolManager.finalize()");
Connection c = null;
ConnectionObject co = null;
for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.elementAt(i);
try {
co.connection.close();
} catch (Exception e) {
e.printStackTrace();
}
co = null;
}
connections.removeAllElements();
}
private void trace(String s) {
if (_traceOn)
System.err.println(s);
}
}
class ConnectionObject {
public java.sql.Connection connection = null;
public boolean inUse = false;
public ConnectionObject(Connection c, boolean useFlag) {
connection = c;
inUse = useFlag;
}
}
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Vector;
public class DBConnectionMgr {
private Vector connections = new Vector(10);
private String _driver = "oracle.jdbc.driver.OracleDriver",
_url = "jdbc:oracle:thin:@localhost:1521:orcl",
_user = "scott",
_password = "1111";
private boolean _traceOn = false;
private boolean initialized = false;
private int _openConnections = 10;
private static DBConnectionMgr instance = null;
public DBConnectionMgr() {
}
/** Use this method to set the maximum number of open connections before
unused connections are closed.
*/
public static DBConnectionMgr getInstance() {
if (instance == null) {
synchronized (DBConnectionMgr.class) {
if (instance == null) {
instance = new DBConnectionMgr();
}
}
}
return instance;
}
public void setOpenConnectionCount(int count) {
_openConnections = count;
}
public void setEnableTrace(boolean enable) {
_traceOn = enable;
}
/** Returns a Vector of java.sql.Connection objects */
public Vector getConnectionList() {
return connections;
}
/** Opens specified "count" of connections and adds them to the existing pool */
public synchronized void setInitOpenConnections(int count)
throws SQLException {
Connection c = null;
ConnectionObject co = null;
for (int i = 0; i < count; i++) {
c = createConnection();
co = new ConnectionObject(c, false);
connections.addElement(co);
trace("ConnectionPoolManager: Adding new DB connection to pool (" + connections.size() + ")");
}
}
/** Returns a count of open connections */
public int getConnectionCount() {
return connections.size();
}
/** Returns an unused existing or new connection. */
public synchronized Connection getConnection()
throws Exception {
if (!initialized) {
Class c = Class.forName(_driver);
DriverManager.registerDriver((Driver) c.newInstance());
initialized = true;
}
Connection c = null;
ConnectionObject co = null;
boolean badConnection = false;
for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.elementAt(i);
// If connection is not in use, test to ensure it's still valid!
if (!co.inUse) {
try {
badConnection = co.connection.isClosed();
if (!badConnection)
badConnection = (co.connection.getWarnings() != null);
} catch (Exception e) {
badConnection = true;
e.printStackTrace();
}
// Connection is bad, remove from pool
if (badConnection) {
connections.removeElementAt(i);
trace("ConnectionPoolManager: Remove disconnected DB connection #" + i);
continue;
}
c = co.connection;
co.inUse = true;
trace("ConnectionPoolManager: Using existing DB connection #" + (i + 1));
break;
}
}
if (c == null) {
c = createConnection();
co = new ConnectionObject(c, true);
connections.addElement(co);
trace("ConnectionPoolManager: Creating new DB connection #" + connections.size());
}
return c;
}
/** Marks a flag in the ConnectionObject to indicate this connection is no longer in use */
public synchronized void freeConnection(Connection c) {
if (c == null)
return;
ConnectionObject co = null;
for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.elementAt(i);
if (c == co.connection) {
co.inUse = false;
break;
}
}
for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.elementAt(i);
if ((i + 1) > _openConnections && !co.inUse)
removeConnection(co.connection);
}
}
public void freeConnection(Connection c, PreparedStatement p, ResultSet r) {
try {
if (r != null) r.close();
if (p != null) p.close();
freeConnection(c);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void freeConnection(Connection c, Statement s, ResultSet r) {
try {
if (r != null) r.close();
if (s != null) s.close();
freeConnection(c);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void freeConnection(Connection c, PreparedStatement p) {
try {
if (p != null) p.close();
freeConnection(c);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void freeConnection(Connection c, Statement s) {
try {
if (s != null) s.close();
freeConnection(c);
} catch (SQLException e) {
e.printStackTrace();
}
}
/** Marks a flag in the ConnectionObject to indicate this connection is no longer in use */
public synchronized void removeConnection(Connection c) {
if (c == null)
return;
ConnectionObject co = null;
for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.elementAt(i);
if (c == co.connection) {
try {
c.close();
connections.removeElementAt(i);
trace("Removed " + c.toString());
} catch (Exception e) {
e.printStackTrace();
}
break;
}
}
}
private Connection createConnection()
throws SQLException {
Connection con = null;
try {
if (_user == null)
_user = "";
if (_password == null)
_password = "";
Properties props = new Properties();
props.put("user", _user);
props.put("password", _password);
con = DriverManager.getConnection(_url, props);
} catch (Throwable t) {
throw new SQLException(t.getMessage());
}
return con;
}
/** Closes all connections and clears out the connection pool */
public void releaseFreeConnections() {
trace("ConnectionPoolManager.releaseFreeConnections()");
Connection c = null;
ConnectionObject co = null;
for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.elementAt(i);
if (!co.inUse)
removeConnection(co.connection);
}
}
/** Closes all connections and clears out the connection pool */
public void finalize() {
trace("ConnectionPoolManager.finalize()");
Connection c = null;
ConnectionObject co = null;
for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.elementAt(i);
try {
co.connection.close();
} catch (Exception e) {
e.printStackTrace();
}
co = null;
}
connections.removeAllElements();
}
private void trace(String s) {
if (_traceOn)
System.err.println(s);
}
}
class ConnectionObject {
public java.sql.Connection connection = null;
public boolean inUse = false;
public ConnectionObject(Connection c, boolean useFlag) {
connection = c;
inUse = useFlag;
}
}
-----------------------------------
3-8
Workspace : ~\JSP\EclipseWork
/Servers/Tomcat v8.0 Server at localhost-config/server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
--><!-- Note: A "Server" is not itself a "Container", so you may not
define subcomponents such as "Valves" at this level.
Documentation at /docs/config/server.html
--><Server port="8005" shutdown="SHUTDOWN">
<Listener className="org.apache.catalina.startup.VersionLoggerListener"/>
<!-- Security listener. Documentation at /docs/config/listeners.html
<Listener className="org.apache.catalina.security.SecurityListener" />
-->
<!--APR library loader. Documentation at /docs/apr.html -->
<Listener SSLEngine="on" className="org.apache.catalina.core.AprLifecycleListener"/>
<!-- Prevent memory leaks due to use of particular java/javax APIs-->
<Listener className="org.apache.catalina.core.JreMemoryLeakPreventionListener"/>
<Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener"/>
<Listener className="org.apache.catalina.core.ThreadLocalLeakPreventionListener"/>
<!-- Global JNDI resources
Documentation at /docs/jndi-resources-howto.html
-->
<GlobalNamingResources>
<!-- Editable user database that can also be used by
UserDatabaseRealm to authenticate users
-->
<Resource auth="Container" description="User database that can be updated and saved" factory="org.apache.catalina.users.MemoryUserDatabaseFactory" name="UserDatabase" pathname="conf/tomcat-users.xml" type="org.apache.catalina.UserDatabase"/>
<Resource name="jdbc/oracleDB"
auth="Container"
type="javax.sql.DataSource"
username="scott"
password="1111"
driverClassName="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:@localhost:1521:orcl"
maxTotal="8"
maxIdle="4"/>
</GlobalNamingResources>
<!-- A "Service" is a collection of one or more "Connectors" that share
a single "Container" Note: A "Service" is not itself a "Container",
so you may not define subcomponents such as "Valves" at this level.
Documentation at /docs/config/service.html
-->
<Service name="Catalina">
<!--The connectors can use a shared executor, you can define one or more named thread pools-->
<!--
<Executor name="tomcatThreadPool" namePrefix="catalina-exec-"
maxThreads="150" minSpareThreads="4"/>
-->
<!-- A "Connector" represents an endpoint by which requests are received
and responses are returned. Documentation at :
Java HTTP Connector: /docs/config/http.html (blocking & non-blocking)
Java AJP Connector: /docs/config/ajp.html
APR (HTTP/AJP) Connector: /docs/apr.html
Define a non-SSL/TLS HTTP/1.1 Connector on port 8080
-->
<Connector connectionTimeout="20000" port="8080" protocol="HTTP/1.1" redirectPort="8443"/>
<!-- A "Connector" using the shared thread pool-->
<!--
<Connector executor="tomcatThreadPool"
port="8080" protocol="HTTP/1.1"
connectionTimeout="20000"
redirectPort="8443" />
-->
<!-- Define a SSL/TLS HTTP/1.1 Connector on port 8443
This connector uses the NIO implementation that requires the JSSE
style configuration. When using the APR/native implementation, the
OpenSSL style configuration is required as described in the APR/native
documentation -->
<!--
<Connector port="8443" protocol="org.apache.coyote.http11.Http11NioProtocol"
maxThreads="150" SSLEnabled="true" scheme="https" secure="true"
clientAuth="false" sslProtocol="TLS" />
-->
<!-- Define an AJP 1.3 Connector on port 8009 -->
<Connector port="8009" protocol="AJP/1.3" redirectPort="8443"/>
<!-- An Engine represents the entry point (within Catalina) that processes
every request. The Engine implementation for Tomcat stand alone
analyzes the HTTP headers included with the request, and passes them
on to the appropriate Host (virtual host).
Documentation at /docs/config/engine.html -->
<!-- You should set jvmRoute to support load-balancing via AJP ie :
<Engine name="Catalina" defaultHost="localhost" jvmRoute="jvm1">
-->
<Engine defaultHost="localhost" name="Catalina">
<!--For clustering, please take a look at documentation at:
/docs/cluster-howto.html (simple how to)
/docs/config/cluster.html (reference documentation) -->
<!--
<Cluster className="org.apache.catalina.ha.tcp.SimpleTcpCluster"/>
-->
<!-- Use the LockOutRealm to prevent attempts to guess user passwords
via a brute-force attack -->
<Realm className="org.apache.catalina.realm.LockOutRealm">
<!-- This Realm uses the UserDatabase configured in the global JNDI
resources under the key "UserDatabase". Any edits
that are performed against this UserDatabase are immediately
available for use by the Realm. -->
<Realm className="org.apache.catalina.realm.UserDatabaseRealm" resourceName="UserDatabase"/>
</Realm>
<Host appBase="webapps" autoDeploy="true" name="localhost" unpackWARs="true">
<!-- SingleSignOn valve, share authentication between web applications
Documentation at: /docs/config/valve.html -->
<!--
<Valve className="org.apache.catalina.authenticator.SingleSignOn" />
-->
<!-- Access log processes all example.
Documentation at: /docs/config/valve.html
Note: The pattern used is equivalent to using pattern="common" -->
<Valve className="org.apache.catalina.valves.AccessLogValve" directory="logs" pattern="%h %l %u %t "%r" %s %b" prefix="localhost_access_log" suffix=".txt"/>
<!-- http://localhost:8080/ 의 주소를 줄여서 '/' 슬래시로 표시 -->
<!-- <Context path="/ServletApp2" docBase="F:\study\JSP\ManualWork\ServletApp2" workDir="F:\study\JSP\ManualWork\ServletApp2\work"/> -->
<Context docBase="ServletApp3" path="/ServletApp3" reloadable="true" source="org.eclipse.jst.jee.server:ServletApp3"/>
<Context docBase="ImplicitObjectApp" path="/ImplicitObjectApp" reloadable="true" source="org.eclipse.jst.jee.server:ImplicitObjectApp"/>
<Context docBase="ActionTagApp" path="/ActionTagApp" reloadable="true" source="org.eclipse.jst.jee.server:ActionTagApp"/>
<Context docBase="BeanApp" path="/BeanApp" reloadable="true" source="org.eclipse.jst.jee.server:BeanApp"/>
<Context docBase="DBApp" path="/DBApp" reloadable="true" source="org.eclipse.jst.jee.server:DBApp">
<ResourceLink name="jdbc/oracleDB"
global="jdbc/oracleDB"
type="javax.sql.DataSource"/>
</Context>
</Host>
</Engine>
</Service>
</Server>
<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
--><!-- Note: A "Server" is not itself a "Container", so you may not
define subcomponents such as "Valves" at this level.
Documentation at /docs/config/server.html
--><Server port="8005" shutdown="SHUTDOWN">
<Listener className="org.apache.catalina.startup.VersionLoggerListener"/>
<!-- Security listener. Documentation at /docs/config/listeners.html
<Listener className="org.apache.catalina.security.SecurityListener" />
-->
<!--APR library loader. Documentation at /docs/apr.html -->
<Listener SSLEngine="on" className="org.apache.catalina.core.AprLifecycleListener"/>
<!-- Prevent memory leaks due to use of particular java/javax APIs-->
<Listener className="org.apache.catalina.core.JreMemoryLeakPreventionListener"/>
<Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener"/>
<Listener className="org.apache.catalina.core.ThreadLocalLeakPreventionListener"/>
<!-- Global JNDI resources
Documentation at /docs/jndi-resources-howto.html
-->
<GlobalNamingResources>
<!-- Editable user database that can also be used by
UserDatabaseRealm to authenticate users
-->
<Resource auth="Container" description="User database that can be updated and saved" factory="org.apache.catalina.users.MemoryUserDatabaseFactory" name="UserDatabase" pathname="conf/tomcat-users.xml" type="org.apache.catalina.UserDatabase"/>
<Resource name="jdbc/oracleDB"
auth="Container"
type="javax.sql.DataSource"
username="scott"
password="1111"
driverClassName="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:@localhost:1521:orcl"
maxTotal="8"
maxIdle="4"/>
</GlobalNamingResources>
<!-- A "Service" is a collection of one or more "Connectors" that share
a single "Container" Note: A "Service" is not itself a "Container",
so you may not define subcomponents such as "Valves" at this level.
Documentation at /docs/config/service.html
-->
<Service name="Catalina">
<!--The connectors can use a shared executor, you can define one or more named thread pools-->
<!--
<Executor name="tomcatThreadPool" namePrefix="catalina-exec-"
maxThreads="150" minSpareThreads="4"/>
-->
<!-- A "Connector" represents an endpoint by which requests are received
and responses are returned. Documentation at :
Java HTTP Connector: /docs/config/http.html (blocking & non-blocking)
Java AJP Connector: /docs/config/ajp.html
APR (HTTP/AJP) Connector: /docs/apr.html
Define a non-SSL/TLS HTTP/1.1 Connector on port 8080
-->
<Connector connectionTimeout="20000" port="8080" protocol="HTTP/1.1" redirectPort="8443"/>
<!-- A "Connector" using the shared thread pool-->
<!--
<Connector executor="tomcatThreadPool"
port="8080" protocol="HTTP/1.1"
connectionTimeout="20000"
redirectPort="8443" />
-->
<!-- Define a SSL/TLS HTTP/1.1 Connector on port 8443
This connector uses the NIO implementation that requires the JSSE
style configuration. When using the APR/native implementation, the
OpenSSL style configuration is required as described in the APR/native
documentation -->
<!--
<Connector port="8443" protocol="org.apache.coyote.http11.Http11NioProtocol"
maxThreads="150" SSLEnabled="true" scheme="https" secure="true"
clientAuth="false" sslProtocol="TLS" />
-->
<!-- Define an AJP 1.3 Connector on port 8009 -->
<Connector port="8009" protocol="AJP/1.3" redirectPort="8443"/>
<!-- An Engine represents the entry point (within Catalina) that processes
every request. The Engine implementation for Tomcat stand alone
analyzes the HTTP headers included with the request, and passes them
on to the appropriate Host (virtual host).
Documentation at /docs/config/engine.html -->
<!-- You should set jvmRoute to support load-balancing via AJP ie :
<Engine name="Catalina" defaultHost="localhost" jvmRoute="jvm1">
-->
<Engine defaultHost="localhost" name="Catalina">
<!--For clustering, please take a look at documentation at:
/docs/cluster-howto.html (simple how to)
/docs/config/cluster.html (reference documentation) -->
<!--
<Cluster className="org.apache.catalina.ha.tcp.SimpleTcpCluster"/>
-->
<!-- Use the LockOutRealm to prevent attempts to guess user passwords
via a brute-force attack -->
<Realm className="org.apache.catalina.realm.LockOutRealm">
<!-- This Realm uses the UserDatabase configured in the global JNDI
resources under the key "UserDatabase". Any edits
that are performed against this UserDatabase are immediately
available for use by the Realm. -->
<Realm className="org.apache.catalina.realm.UserDatabaseRealm" resourceName="UserDatabase"/>
</Realm>
<Host appBase="webapps" autoDeploy="true" name="localhost" unpackWARs="true">
<!-- SingleSignOn valve, share authentication between web applications
Documentation at: /docs/config/valve.html -->
<!--
<Valve className="org.apache.catalina.authenticator.SingleSignOn" />
-->
<!-- Access log processes all example.
Documentation at: /docs/config/valve.html
Note: The pattern used is equivalent to using pattern="common" -->
<Valve className="org.apache.catalina.valves.AccessLogValve" directory="logs" pattern="%h %l %u %t "%r" %s %b" prefix="localhost_access_log" suffix=".txt"/>
<!-- http://localhost:8080/ 의 주소를 줄여서 '/' 슬래시로 표시 -->
<!-- <Context path="/ServletApp2" docBase="F:\study\JSP\ManualWork\ServletApp2" workDir="F:\study\JSP\ManualWork\ServletApp2\work"/> -->
<Context docBase="ServletApp3" path="/ServletApp3" reloadable="true" source="org.eclipse.jst.jee.server:ServletApp3"/>
<Context docBase="ImplicitObjectApp" path="/ImplicitObjectApp" reloadable="true" source="org.eclipse.jst.jee.server:ImplicitObjectApp"/>
<Context docBase="ActionTagApp" path="/ActionTagApp" reloadable="true" source="org.eclipse.jst.jee.server:ActionTagApp"/>
<Context docBase="BeanApp" path="/BeanApp" reloadable="true" source="org.eclipse.jst.jee.server:BeanApp"/>
<Context docBase="DBApp" path="/DBApp" reloadable="true" source="org.eclipse.jst.jee.server:DBApp">
<ResourceLink name="jdbc/oracleDB"
global="jdbc/oracleDB"
type="javax.sql.DataSource"/>
</Context>
</Host>
</Engine>
</Service>
</Server>
-----------------------------------
3-9
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/WebContent/WEB-INF/web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
id="WebApp_ID" version="3.1">
<display-name>DBApp</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.htm</welcome-file>
<welcome-file>default.jsp</welcome-file>
</welcome-file-list>
<resource-ref>
<res-ref-name>jdbc/oracleDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
id="WebApp_ID" version="3.1">
<display-name>DBApp</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.htm</welcome-file>
<welcome-file>default.jsp</welcome-file>
</welcome-file-list>
<resource-ref>
<res-ref-name>jdbc/oracleDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
-----------------------------------
3-10
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/WebContent/JNDITest.jsp
<%@page import="java.sql.Connection"%>
<%@page import="javax.sql.DataSource"%>
<%@page import="javax.naming.InitialContext"%>
<%@page import="javax.naming.Context"%>
<%@ page contentType="text/html; charset=EUC-KR"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<h1>JNDI를 이용한 DBCP 사용 테스트</h1>
<%
try {
Context ctx = new InitialContext();
// 외부에 접근하기 위한 가상 프로토콜
// java:comp/env/
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/oracleDB");
Connection con = ds.getConnection();
if(con!=null){
out.println("연결 성공");
con.close();
}
} catch (Exception e) {
System.out.println("연결 실패 : " + e);
}
%>
</body>
</html>
<%@page import="javax.sql.DataSource"%>
<%@page import="javax.naming.InitialContext"%>
<%@page import="javax.naming.Context"%>
<%@ page contentType="text/html; charset=EUC-KR"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<h1>JNDI를 이용한 DBCP 사용 테스트</h1>
<%
try {
Context ctx = new InitialContext();
// 외부에 접근하기 위한 가상 프로토콜
// java:comp/env/
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/oracleDB");
Connection con = ds.getConnection();
if(con!=null){
out.println("연결 성공");
con.close();
}
} catch (Exception e) {
System.out.println("연결 실패 : " + e);
}
%>
</body>
</html>
-----------------------------------
3-11
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/WebContent/v3/index.jsp
<%@page import="javax.sql.DataSource"%>
<%@page import="javax.naming.InitialContext"%>
<%@page import="javax.naming.Context"%>
<%@page import="dbcp.custom.DBConnectionMgr"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page contentType="text/html; charset=EUC-KR"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
<script>
function delCheck(s_no) {
var result = confirm("정말 삭제하시겠습니까?");
if(result == true){
location.href="deleteSawon.jsp?s_no="+s_no;
}
}
</script>
</head>
<body>
<h1>직원 정보 리스트</h1>
<a href="addSawon.html">사원추가</a>
<br/>
<%
request.setCharacterEncoding("euc-kr");
String sql = "select * from tblsawon order by s_no";
String search = request.getParameter("search");
String searchText = request.getParameter("searchText");
if(searchText != null){
sql = "select * from tblsawon where "+search+" like ? order by s_no";
}
%>
<form method="post" action="index.jsp">
<select name="search">
<option value="s_id">id</option>
<option value="s_addr">근무지</option>
<option value="s_dept">부서명</option>
</select>
<input type="text" name="searchText"/>
<input type="submit" name="검색"/>
</form>
<table border="1">
<tr>
<th>사번</th>
<th>아이디</th>
<th>이름</th>
<th>패스워드</th>
<th>나이</th>
<th>근무지</th>
<th>부서명</th>
<th>수정</th>
<th>삭제</th>
</tr>
<%
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
Context ctx = null;
try{
ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/oracleDB");
con = ds.getConnection();
stmt = con.prepareStatement(sql);
if(searchText != null){
stmt.setString(1, "%"+searchText+"%");
}
rs = stmt.executeQuery();
while(rs.next()){
int s_no = rs.getInt("s_no");
String s_id = rs.getString("s_id");
String s_name = rs.getString("s_name");
String s_pw = rs.getString("s_pass");
int s_age = rs.getInt("s_age");
String s_addr = rs.getString("s_addr");
String s_dept = rs.getString("s_dept");
%>
<tr>
<th><%=s_no%></th>
<th><%=s_id%></th>
<th><%=s_name%></th>
<th><%=s_pw%></th>
<th><%=s_age%></th>
<th><%=s_addr%></th>
<th><%=s_dept%></th>
<th><a href="editSawon.jsp?s_no=<%=s_no%>">수정</a></th>
<th><a href="javascript:delCheck('<%=s_no%>')">삭제</a></th>
</tr>
<%
}
}catch(Exception e){
System.out.println("index.jsp : "+e);
}finally{
if(rs!=null)try{rs.close();}catch(Exception e){}
if(stmt!=null)try{stmt.close();}catch(Exception e){}
if(con!=null)try{con.close();}catch(Exception e){}
}
%>
</table>
</body>
</html>
<%@page import="javax.naming.InitialContext"%>
<%@page import="javax.naming.Context"%>
<%@page import="dbcp.custom.DBConnectionMgr"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page contentType="text/html; charset=EUC-KR"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
<script>
function delCheck(s_no) {
var result = confirm("정말 삭제하시겠습니까?");
if(result == true){
location.href="deleteSawon.jsp?s_no="+s_no;
}
}
</script>
</head>
<body>
<h1>직원 정보 리스트</h1>
<a href="addSawon.html">사원추가</a>
<br/>
<%
request.setCharacterEncoding("euc-kr");
String sql = "select * from tblsawon order by s_no";
String search = request.getParameter("search");
String searchText = request.getParameter("searchText");
if(searchText != null){
sql = "select * from tblsawon where "+search+" like ? order by s_no";
}
%>
<form method="post" action="index.jsp">
<select name="search">
<option value="s_id">id</option>
<option value="s_addr">근무지</option>
<option value="s_dept">부서명</option>
</select>
<input type="text" name="searchText"/>
<input type="submit" name="검색"/>
</form>
<table border="1">
<tr>
<th>사번</th>
<th>아이디</th>
<th>이름</th>
<th>패스워드</th>
<th>나이</th>
<th>근무지</th>
<th>부서명</th>
<th>수정</th>
<th>삭제</th>
</tr>
<%
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
Context ctx = null;
try{
ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/oracleDB");
con = ds.getConnection();
stmt = con.prepareStatement(sql);
if(searchText != null){
stmt.setString(1, "%"+searchText+"%");
}
rs = stmt.executeQuery();
while(rs.next()){
int s_no = rs.getInt("s_no");
String s_id = rs.getString("s_id");
String s_name = rs.getString("s_name");
String s_pw = rs.getString("s_pass");
int s_age = rs.getInt("s_age");
String s_addr = rs.getString("s_addr");
String s_dept = rs.getString("s_dept");
%>
<tr>
<th><%=s_no%></th>
<th><%=s_id%></th>
<th><%=s_name%></th>
<th><%=s_pw%></th>
<th><%=s_age%></th>
<th><%=s_addr%></th>
<th><%=s_dept%></th>
<th><a href="editSawon.jsp?s_no=<%=s_no%>">수정</a></th>
<th><a href="javascript:delCheck('<%=s_no%>')">삭제</a></th>
</tr>
<%
}
}catch(Exception e){
System.out.println("index.jsp : "+e);
}finally{
if(rs!=null)try{rs.close();}catch(Exception e){}
if(stmt!=null)try{stmt.close();}catch(Exception e){}
if(con!=null)try{con.close();}catch(Exception e){}
}
%>
</table>
</body>
</html>
-----------------------------------
3-12
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/WebContent/v3/editSawon.jsp
<%@page import="dbcp.custom.DBConnectionMgr"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page contentType="text/html; charset=EUC-KR"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%
String s_no = request.getParameter("s_no");
DBConnectionMgr pool = null;
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String sql = "select * from tblsawon where s_no = ?";
String s_id=null,s_name=null,s_pass=null,
s_addr=null,s_dept=null,s_age=null;
try{
pool = DBConnectionMgr.getInstance();
con = pool.getConnection();
stmt = con.prepareStatement(sql);
stmt.setString(1, s_no);
rs = stmt.executeQuery();
if(rs.next()){
s_no = rs.getString("s_no");
s_id = rs.getString("s_id");
s_name = rs.getString("s_name");
s_pass = rs.getString("s_pass");
s_age = rs.getString("s_age");
s_addr = rs.getString("s_addr");
s_dept = rs.getString("s_dept");
}
}catch(Exception e){
System.out.println("editSawon.jsp : "+e);
}finally{
pool.freeConnection(con, stmt, rs);
}
%>
<form action="editSawon_proc.jsp" method="post">
<input type="hidden" name="s_no" value="<%=s_no%>"/>
<table border="1">
<tr>
<td align="center">아이디</td>
<td><input type="text" name="s_id" value="<%=s_id%>"/></td>
</tr>
<tr>
<td align="center">이름</td>
<td><input type="text" name="s_name" value="<%=s_name%>"/></td>
</tr>
<tr>
<td align="center">나이</td>
<td><input type="text" name="s_age" value="<%=s_age%>"/></td>
</tr>
<tr>
<td align="center">패스워드</td>
<td><input type="password" name="s_pass" value="<%=s_pass%>"/></td>
</tr>
<tr>
<td align="center">근무지</td>
<td>
<select name="s_addr">
<option value="서울"<%if(s_addr.equals("서울")){%>selected="selected"<%}%>>서울</option>
<option value="광주"<%if(s_addr.equals("광주")){%>selected="selected"<%}%>>광주</option>
<option value="부산"<%if(s_addr.equals("부산")){%>selected="selected"<%}%>>부산</option>
<option value="제주"<%if(s_addr.equals("제주")){%>selected="selected"<%}%>>제주</option>
</select>
</td>
</tr>
<tr>
<td align="center">부서명</td>
<td>
<select name="s_dept">
<option value="영업"<%if(s_dept.equals("영업")){%>selected="selected"<%}%>>영업</option>
<option value="기술"<%if(s_dept.equals("기술")){%>selected="selected"<%}%>>기술</option>
<option value="기획"<%if(s_dept.equals("기획")){%>selected="selected"<%}%>>기획</option>
</select>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="수정" />
<input type="reset" value="다시 작성" />
</td>
</tr>
</table>
</form>
</body>
</html>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page contentType="text/html; charset=EUC-KR"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%
String s_no = request.getParameter("s_no");
DBConnectionMgr pool = null;
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String sql = "select * from tblsawon where s_no = ?";
String s_id=null,s_name=null,s_pass=null,
s_addr=null,s_dept=null,s_age=null;
try{
pool = DBConnectionMgr.getInstance();
con = pool.getConnection();
stmt = con.prepareStatement(sql);
stmt.setString(1, s_no);
rs = stmt.executeQuery();
if(rs.next()){
s_no = rs.getString("s_no");
s_id = rs.getString("s_id");
s_name = rs.getString("s_name");
s_pass = rs.getString("s_pass");
s_age = rs.getString("s_age");
s_addr = rs.getString("s_addr");
s_dept = rs.getString("s_dept");
}
}catch(Exception e){
System.out.println("editSawon.jsp : "+e);
}finally{
pool.freeConnection(con, stmt, rs);
}
%>
<form action="editSawon_proc.jsp" method="post">
<input type="hidden" name="s_no" value="<%=s_no%>"/>
<table border="1">
<tr>
<td align="center">아이디</td>
<td><input type="text" name="s_id" value="<%=s_id%>"/></td>
</tr>
<tr>
<td align="center">이름</td>
<td><input type="text" name="s_name" value="<%=s_name%>"/></td>
</tr>
<tr>
<td align="center">나이</td>
<td><input type="text" name="s_age" value="<%=s_age%>"/></td>
</tr>
<tr>
<td align="center">패스워드</td>
<td><input type="password" name="s_pass" value="<%=s_pass%>"/></td>
</tr>
<tr>
<td align="center">근무지</td>
<td>
<select name="s_addr">
<option value="서울"<%if(s_addr.equals("서울")){%>selected="selected"<%}%>>서울</option>
<option value="광주"<%if(s_addr.equals("광주")){%>selected="selected"<%}%>>광주</option>
<option value="부산"<%if(s_addr.equals("부산")){%>selected="selected"<%}%>>부산</option>
<option value="제주"<%if(s_addr.equals("제주")){%>selected="selected"<%}%>>제주</option>
</select>
</td>
</tr>
<tr>
<td align="center">부서명</td>
<td>
<select name="s_dept">
<option value="영업"<%if(s_dept.equals("영업")){%>selected="selected"<%}%>>영업</option>
<option value="기술"<%if(s_dept.equals("기술")){%>selected="selected"<%}%>>기술</option>
<option value="기획"<%if(s_dept.equals("기획")){%>selected="selected"<%}%>>기획</option>
</select>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="수정" />
<input type="reset" value="다시 작성" />
</td>
</tr>
</table>
</form>
</body>
</html>
-----------------------------------
3-13
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/WebContent/v3/editSawon_proc.jsp
<%@page import="javax.naming.InitialContext"%>
<%@page import="javax.sql.DataSource"%>
<%@page import="javax.naming.Context"%>
<%@ page contentType="text/html; charset=EUC-KR"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%
request.setCharacterEncoding("euc-kr");
String s_no = request.getParameter("s_no");
String s_id = request.getParameter("s_id");
String s_name = request.getParameter("s_name");
String s_pass = request.getParameter("s_pass");
String s_age = request.getParameter("s_age");
String s_addr = request.getParameter("s_addr");
String s_dept = request.getParameter("s_dept");
String sql = "update tblsawon set "
+ "s_id=?, s_name=?, s_pass=?,"
+ "s_age=?, s_addr=?, s_dept=? "
+ "where s_no = ?";
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
Context ctx = null;
try{
ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/oracleDB");
con = ds.getConnection();
stmt = con.prepareStatement(sql);
stmt.setString(1, s_id);
stmt.setString(2, s_name);
stmt.setString(3, s_pass);
stmt.setString(4, s_age);
stmt.setString(5, s_addr);
stmt.setString(6, s_dept);
stmt.setString(7, s_no);
int result = stmt.executeUpdate();
if(result > 0){
%>
<script>
alert("사원이 잘 수정 되었습니다.");
location.href="index.jsp";
</script>
<%
}
}catch(Exception e){
System.out.println("editSawon_proc.jsp : "+e);
}finally{
if(rs!=null)try{rs.close();}catch(Exception e){}
if(stmt!=null)try{stmt.close();}catch(Exception e){}
if(con!=null)try{con.close();}catch(Exception e){}
}
%>
</body>
</html>
<%@page import="javax.sql.DataSource"%>
<%@page import="javax.naming.Context"%>
<%@ page contentType="text/html; charset=EUC-KR"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%
request.setCharacterEncoding("euc-kr");
String s_no = request.getParameter("s_no");
String s_id = request.getParameter("s_id");
String s_name = request.getParameter("s_name");
String s_pass = request.getParameter("s_pass");
String s_age = request.getParameter("s_age");
String s_addr = request.getParameter("s_addr");
String s_dept = request.getParameter("s_dept");
String sql = "update tblsawon set "
+ "s_id=?, s_name=?, s_pass=?,"
+ "s_age=?, s_addr=?, s_dept=? "
+ "where s_no = ?";
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
Context ctx = null;
try{
ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/oracleDB");
con = ds.getConnection();
stmt = con.prepareStatement(sql);
stmt.setString(1, s_id);
stmt.setString(2, s_name);
stmt.setString(3, s_pass);
stmt.setString(4, s_age);
stmt.setString(5, s_addr);
stmt.setString(6, s_dept);
stmt.setString(7, s_no);
int result = stmt.executeUpdate();
if(result > 0){
%>
<script>
alert("사원이 잘 수정 되었습니다.");
location.href="index.jsp";
</script>
<%
}
}catch(Exception e){
System.out.println("editSawon_proc.jsp : "+e);
}finally{
if(rs!=null)try{rs.close();}catch(Exception e){}
if(stmt!=null)try{stmt.close();}catch(Exception e){}
if(con!=null)try{con.close();}catch(Exception e){}
}
%>
</body>
</html>
-----------------------------------
3-14
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/WebContent/v3/deleteSawon.jsp
<%@page import="dbcp.custom.DBConnectionMgr"%>
<%@ page contentType="text/html; charset=EUC-KR"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%
request.setCharacterEncoding("euc-kr");
String s_no = request.getParameter("s_no");
String sql = "delete from tblsawon where s_no = ?";
DBConnectionMgr pool = null;
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
try{
pool = DBConnectionMgr.getInstance();
con = pool.getConnection();
stmt = con.prepareStatement(sql);
stmt.setString(1, s_no);
int result = stmt.executeUpdate();
if(result > 0){
%>
<script>
alert("사원이 잘 삭제 되었습니다.");
location.href="index.jsp";
</script>
<%
}
}catch(Exception e){
System.out.println("deleteSawon_proc.jsp : "+e);
}finally{
pool.freeConnection(con, stmt, rs);
}
%>
</body>
</html>
<%@ page contentType="text/html; charset=EUC-KR"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%
request.setCharacterEncoding("euc-kr");
String s_no = request.getParameter("s_no");
String sql = "delete from tblsawon where s_no = ?";
DBConnectionMgr pool = null;
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
try{
pool = DBConnectionMgr.getInstance();
con = pool.getConnection();
stmt = con.prepareStatement(sql);
stmt.setString(1, s_no);
int result = stmt.executeUpdate();
if(result > 0){
%>
<script>
alert("사원이 잘 삭제 되었습니다.");
location.href="index.jsp";
</script>
<%
}
}catch(Exception e){
System.out.println("deleteSawon_proc.jsp : "+e);
}finally{
pool.freeConnection(con, stmt, rs);
}
%>
</body>
</html>
-----------------------------------
3-15
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/WebContent/v3/addSawon_proc.jsp
<%@page import="dbcp.custom.DBConnectionMgr"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page contentType="text/html; charset=EUC-KR"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%
request.setCharacterEncoding("euc-kr");
String s_id = request.getParameter("s_id");
String s_name = request.getParameter("s_name");
String s_pass = request.getParameter("s_pass");
String s_age = request.getParameter("s_age");
String s_addr = request.getParameter("s_addr");
String s_dept = request.getParameter("s_dept");
String sql = "insert into "
+ "tblsawon(s_no,s_id,s_name,s_pass,s_age,s_addr,s_dept)"
+ " values(seq_no.nextVal,?,?,?,?,?,?)";
DBConnectionMgr pool = null;
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
try{
pool = DBConnectionMgr.getInstance();
con = pool.getConnection();
stmt = con.prepareStatement(sql);
stmt.setString(1, s_id);
stmt.setString(2, s_name);
stmt.setString(3, s_pass);
stmt.setString(4, s_age);
stmt.setString(5, s_addr);
stmt.setString(6, s_dept);
int result = stmt.executeUpdate();
if(result > 0){
%>
<script>
alert("사원이 잘 추가 되었습니다.");
location.href="index.jsp";
</script>
<%
}
}catch(Exception e){
System.out.println("addSawon_proc.jsp : "+e);
}finally{
pool.freeConnection(con, stmt, rs);
}
%>
</body>
</html>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page contentType="text/html; charset=EUC-KR"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%
request.setCharacterEncoding("euc-kr");
String s_id = request.getParameter("s_id");
String s_name = request.getParameter("s_name");
String s_pass = request.getParameter("s_pass");
String s_age = request.getParameter("s_age");
String s_addr = request.getParameter("s_addr");
String s_dept = request.getParameter("s_dept");
String sql = "insert into "
+ "tblsawon(s_no,s_id,s_name,s_pass,s_age,s_addr,s_dept)"
+ " values(seq_no.nextVal,?,?,?,?,?,?)";
DBConnectionMgr pool = null;
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
try{
pool = DBConnectionMgr.getInstance();
con = pool.getConnection();
stmt = con.prepareStatement(sql);
stmt.setString(1, s_id);
stmt.setString(2, s_name);
stmt.setString(3, s_pass);
stmt.setString(4, s_age);
stmt.setString(5, s_addr);
stmt.setString(6, s_dept);
int result = stmt.executeUpdate();
if(result > 0){
%>
<script>
alert("사원이 잘 추가 되었습니다.");
location.href="index.jsp";
</script>
<%
}
}catch(Exception e){
System.out.println("addSawon_proc.jsp : "+e);
}finally{
pool.freeConnection(con, stmt, rs);
}
%>
</body>
</html>
-----------------------------------
3-16
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/src/bean/dao/EmpDao.java
package bean.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.sun.org.apache.regexp.internal.recompile;
import bean.dto.EmpDto;
import dbcp.custom.DBConnectionMgr;
public class EmpDao {
private Connection con = null;
private PreparedStatement stmt = null;
private ResultSet rs = null;
private DBConnectionMgr pool = null;
public EmpDao() {
try {
pool = DBConnectionMgr.getInstance();
} catch (Exception e) {
System.out.println("DB연결객체 생성실패 : " + e);
}
}
// index.jsp
public List getList(){
ArrayList list = new ArrayList();
String sql = "select * from tblsawon order by s_no";
try{
pool = DBConnectionMgr.getInstance();
con = pool.getConnection();
stmt = con.prepareStatement(sql);
rs = stmt.executeQuery();
while(rs.next()){
EmpDto dto = new EmpDto();
dto.setS_no(rs.getInt("s_no"));
dto.setS_id(rs.getString("s_id"));
dto.setS_name(rs.getString("s_name"));
dto.setS_pass(rs.getString("s_pass"));
dto.setS_age(rs.getInt("s_age"));
dto.setS_addr(rs.getString("s_addr"));
dto.setS_dept(rs.getString("s_dept"));
list.add(dto);
}
}catch(Exception e){
System.out.println("getList() : " + e);
}finally{
pool.freeConnection(con, stmt, rs);
}
return list;
}
public List getList(String condition, String param){
ArrayList list = new ArrayList();
String sql = "select * from tblsawon where "+condition+" like ? order by s_no";
try{
pool = DBConnectionMgr.getInstance();
con = pool.getConnection();
stmt = con.prepareStatement(sql);
stmt.setString(1, "%"+param+"%");
rs = stmt.executeQuery();
while(rs.next()){
EmpDto dto = new EmpDto();
dto.setS_no(rs.getInt("s_no"));
dto.setS_id(rs.getString("s_id"));
dto.setS_name(rs.getString("s_name"));
dto.setS_pass(rs.getString("s_pass"));
dto.setS_age(rs.getInt("s_age"));
dto.setS_addr(rs.getString("s_addr"));
dto.setS_dept(rs.getString("s_dept"));
list.add(dto);
}
}catch(Exception e){
System.out.println("getList(String condition, String param) : " + e);
}finally{
pool.freeConnection(con, stmt, rs);
}
return list;
}
public EmpDto getSawon(String s_no){
EmpDto dto = new EmpDto();
String sql = "select * from tblsawon where s_no = ?";
try{
pool = DBConnectionMgr.getInstance();
con = pool.getConnection();
stmt = con.prepareStatement(sql);
stmt.setString(1, s_no);
rs = stmt.executeQuery();
if(rs.next()){
dto.setS_no(rs.getInt("s_no"));
dto.setS_id(rs.getString("s_id"));
dto.setS_name(rs.getString("s_name"));
dto.setS_pass(rs.getString("s_pass"));
dto.setS_age(rs.getInt("s_age"));
dto.setS_addr(rs.getString("s_addr"));
dto.setS_dept(rs.getString("s_dept"));
}
}catch(Exception e){
System.out.println("getSawon(String s_no) : " + e);
}finally{
pool.freeConnection(con, stmt, rs);
}
return dto;
}
// 사원 추가
public int addSawon(EmpDto dto){
int result = 0;
String sql = "insert into "
+ "tblsawon(s_no,s_id,s_name,s_pass,s_age,s_addr,s_dept)"
+ " values(seq_no.nextVal,?,?,?,?,?,?)";
try{
pool = DBConnectionMgr.getInstance();
con = pool.getConnection();
stmt = con.prepareStatement(sql);
stmt.setString(1, dto.getS_id());
stmt.setString(2, dto.getS_name());
stmt.setString(3, dto.getS_pass());
stmt.setInt(4, dto.getS_age());
stmt.setString(5, dto.getS_addr());
stmt.setString(6, dto.getS_dept());
result = stmt.executeUpdate();
}catch(Exception e){
System.out.println("addSawon(EmpDto dto) : " + e);
}finally{
pool.freeConnection(con, stmt);
}
return result;
}
// 사원 삭제
public int deleteSawon(String s_no){
int result = 0;
String sql = "delete from tblsawon where s_no = ?";
try{
pool = DBConnectionMgr.getInstance();
con = pool.getConnection();
stmt = con.prepareStatement(sql);
stmt.setString(1, s_no);
result = stmt.executeUpdate();
}catch(Exception e){
System.out.println("removeSawon(String s_no) : " + e);
}finally{
pool.freeConnection(con, stmt);
}
return result;
}
// 사원 수정
public int updateSawon(EmpDto dto){
int result = 0;
String sql = "update tblsawon set "
+ "s_id=?, s_name=?, s_pass=?,"
+ "s_age=?, s_addr=?, s_dept=? "
+ "where s_no = ?";
try{
pool = DBConnectionMgr.getInstance();
con = pool.getConnection();
stmt = con.prepareStatement(sql);
stmt.setString(1, dto.getS_id());
stmt.setString(2, dto.getS_name());
stmt.setString(3, dto.getS_pass());
stmt.setInt(4, dto.getS_age());
stmt.setString(5, dto.getS_addr());
stmt.setString(6, dto.getS_dept());
stmt.setInt(7, dto.getS_no());
result = stmt.executeUpdate();
}catch(Exception e){
System.out.println("updateSawon(EmpDto dto) : " + e);
}finally{
pool.freeConnection(con, stmt);
}
return result;
}
}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.sun.org.apache.regexp.internal.recompile;
import bean.dto.EmpDto;
import dbcp.custom.DBConnectionMgr;
public class EmpDao {
private Connection con = null;
private PreparedStatement stmt = null;
private ResultSet rs = null;
private DBConnectionMgr pool = null;
public EmpDao() {
try {
pool = DBConnectionMgr.getInstance();
} catch (Exception e) {
System.out.println("DB연결객체 생성실패 : " + e);
}
}
// index.jsp
public List getList(){
ArrayList list = new ArrayList();
String sql = "select * from tblsawon order by s_no";
try{
pool = DBConnectionMgr.getInstance();
con = pool.getConnection();
stmt = con.prepareStatement(sql);
rs = stmt.executeQuery();
while(rs.next()){
EmpDto dto = new EmpDto();
dto.setS_no(rs.getInt("s_no"));
dto.setS_id(rs.getString("s_id"));
dto.setS_name(rs.getString("s_name"));
dto.setS_pass(rs.getString("s_pass"));
dto.setS_age(rs.getInt("s_age"));
dto.setS_addr(rs.getString("s_addr"));
dto.setS_dept(rs.getString("s_dept"));
list.add(dto);
}
}catch(Exception e){
System.out.println("getList() : " + e);
}finally{
pool.freeConnection(con, stmt, rs);
}
return list;
}
public List getList(String condition, String param){
ArrayList list = new ArrayList();
String sql = "select * from tblsawon where "+condition+" like ? order by s_no";
try{
pool = DBConnectionMgr.getInstance();
con = pool.getConnection();
stmt = con.prepareStatement(sql);
stmt.setString(1, "%"+param+"%");
rs = stmt.executeQuery();
while(rs.next()){
EmpDto dto = new EmpDto();
dto.setS_no(rs.getInt("s_no"));
dto.setS_id(rs.getString("s_id"));
dto.setS_name(rs.getString("s_name"));
dto.setS_pass(rs.getString("s_pass"));
dto.setS_age(rs.getInt("s_age"));
dto.setS_addr(rs.getString("s_addr"));
dto.setS_dept(rs.getString("s_dept"));
list.add(dto);
}
}catch(Exception e){
System.out.println("getList(String condition, String param) : " + e);
}finally{
pool.freeConnection(con, stmt, rs);
}
return list;
}
public EmpDto getSawon(String s_no){
EmpDto dto = new EmpDto();
String sql = "select * from tblsawon where s_no = ?";
try{
pool = DBConnectionMgr.getInstance();
con = pool.getConnection();
stmt = con.prepareStatement(sql);
stmt.setString(1, s_no);
rs = stmt.executeQuery();
if(rs.next()){
dto.setS_no(rs.getInt("s_no"));
dto.setS_id(rs.getString("s_id"));
dto.setS_name(rs.getString("s_name"));
dto.setS_pass(rs.getString("s_pass"));
dto.setS_age(rs.getInt("s_age"));
dto.setS_addr(rs.getString("s_addr"));
dto.setS_dept(rs.getString("s_dept"));
}
}catch(Exception e){
System.out.println("getSawon(String s_no) : " + e);
}finally{
pool.freeConnection(con, stmt, rs);
}
return dto;
}
// 사원 추가
public int addSawon(EmpDto dto){
int result = 0;
String sql = "insert into "
+ "tblsawon(s_no,s_id,s_name,s_pass,s_age,s_addr,s_dept)"
+ " values(seq_no.nextVal,?,?,?,?,?,?)";
try{
pool = DBConnectionMgr.getInstance();
con = pool.getConnection();
stmt = con.prepareStatement(sql);
stmt.setString(1, dto.getS_id());
stmt.setString(2, dto.getS_name());
stmt.setString(3, dto.getS_pass());
stmt.setInt(4, dto.getS_age());
stmt.setString(5, dto.getS_addr());
stmt.setString(6, dto.getS_dept());
result = stmt.executeUpdate();
}catch(Exception e){
System.out.println("addSawon(EmpDto dto) : " + e);
}finally{
pool.freeConnection(con, stmt);
}
return result;
}
// 사원 삭제
public int deleteSawon(String s_no){
int result = 0;
String sql = "delete from tblsawon where s_no = ?";
try{
pool = DBConnectionMgr.getInstance();
con = pool.getConnection();
stmt = con.prepareStatement(sql);
stmt.setString(1, s_no);
result = stmt.executeUpdate();
}catch(Exception e){
System.out.println("removeSawon(String s_no) : " + e);
}finally{
pool.freeConnection(con, stmt);
}
return result;
}
// 사원 수정
public int updateSawon(EmpDto dto){
int result = 0;
String sql = "update tblsawon set "
+ "s_id=?, s_name=?, s_pass=?,"
+ "s_age=?, s_addr=?, s_dept=? "
+ "where s_no = ?";
try{
pool = DBConnectionMgr.getInstance();
con = pool.getConnection();
stmt = con.prepareStatement(sql);
stmt.setString(1, dto.getS_id());
stmt.setString(2, dto.getS_name());
stmt.setString(3, dto.getS_pass());
stmt.setInt(4, dto.getS_age());
stmt.setString(5, dto.getS_addr());
stmt.setString(6, dto.getS_dept());
stmt.setInt(7, dto.getS_no());
result = stmt.executeUpdate();
}catch(Exception e){
System.out.println("updateSawon(EmpDto dto) : " + e);
}finally{
pool.freeConnection(con, stmt);
}
return result;
}
}
-----------------------------------
3-17
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/src/bean/dto/EmpDto.java
package bean.dto;
public class EmpDto {
private int s_no;
private String s_id;
private String s_name;
private String s_pass;
private int s_age;
private String s_addr;
private String s_dept;
public int getS_no() {
return s_no;
}
public void setS_no(int s_no) {
this.s_no = s_no;
}
public String getS_id() {
return s_id;
}
public void setS_id(String s_id) {
this.s_id = s_id;
}
public String getS_name() {
return s_name;
}
public void setS_name(String s_name) {
this.s_name = s_name;
}
public String getS_pass() {
return s_pass;
}
public void setS_pass(String s_pass) {
this.s_pass = s_pass;
}
public int getS_age() {
return s_age;
}
public void setS_age(int s_age) {
this.s_age = s_age;
}
public String getS_addr() {
return s_addr;
}
public void setS_addr(String s_addr) {
this.s_addr = s_addr;
}
public String getS_dept() {
return s_dept;
}
public void setS_dept(String s_dept) {
this.s_dept = s_dept;
}
}
public class EmpDto {
private int s_no;
private String s_id;
private String s_name;
private String s_pass;
private int s_age;
private String s_addr;
private String s_dept;
public int getS_no() {
return s_no;
}
public void setS_no(int s_no) {
this.s_no = s_no;
}
public String getS_id() {
return s_id;
}
public void setS_id(String s_id) {
this.s_id = s_id;
}
public String getS_name() {
return s_name;
}
public void setS_name(String s_name) {
this.s_name = s_name;
}
public String getS_pass() {
return s_pass;
}
public void setS_pass(String s_pass) {
this.s_pass = s_pass;
}
public int getS_age() {
return s_age;
}
public void setS_age(int s_age) {
this.s_age = s_age;
}
public String getS_addr() {
return s_addr;
}
public void setS_addr(String s_addr) {
this.s_addr = s_addr;
}
public String getS_dept() {
return s_dept;
}
public void setS_dept(String s_dept) {
this.s_dept = s_dept;
}
}
-----------------------------------
3-18
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/WebContent/v4/index.jsp
<%@page import="java.util.List"%>
<%@page import="bean.dto.EmpDto"%>
<%@ page contentType="text/html; charset=EUC-KR"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
<script>
function delCheck(s_no) {
var result = confirm("정말 삭제하시겠습니까?");
if(result == true){
location.href="deleteSawon.jsp?s_no="+s_no;
}
}
</script>
</head>
<body>
<h1>직원 정보 리스트</h1>
<a href="addSawon.html">사원추가</a>
<br/>
<%
request.setCharacterEncoding("euc-kr");
String search = request.getParameter("search");
String searchText = request.getParameter("searchText");
%>
<form method="post" action="index.jsp">
<select name="search">
<option value="s_id">id</option>
<option value="s_addr">근무지</option>
<option value="s_dept">부서명</option>
</select>
<input type="text" name="searchText"/>
<input type="submit" name="검색"/>
</form>
<br/>
<jsp:useBean id="dao" class="bean.dao.EmpDao" />
<table border="1">
<tr>
<th>사번</th>
<th>아이디</th>
<th>이름</th>
<th>패스워드</th>
<th>나이</th>
<th>근무지</th>
<th>부서명</th>
<th>수정</th>
<th>삭제</th>
</tr>
<%
List list = null;
if(searchText == null){
list = dao.getList();
}else if(searchText != null){
list = dao.getList(search,searchText);
}
for(int i=0;i<list.size();i++){
EmpDto dto = (EmpDto)list.get(i);
%>
<tr>
<th><%=dto.getS_no()%></th>
<th><%=dto.getS_id()%></th>
<th><%=dto.getS_name()%></th>
<th><%=dto.getS_pass()%></th>
<th><%=dto.getS_age()%></th>
<th><%=dto.getS_addr()%></th>
<th><%=dto.getS_dept()%></th>
<th><a href="editSawon.jsp?s_no=<%=dto.getS_no()%>">수정</a></th>
<th><a href="javascript:delCheck('<%=dto.getS_no()%>')">삭제</a></th>
</tr>
<%
}
%>
</table>
</body>
</html>
<%@page import="bean.dto.EmpDto"%>
<%@ page contentType="text/html; charset=EUC-KR"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
<script>
function delCheck(s_no) {
var result = confirm("정말 삭제하시겠습니까?");
if(result == true){
location.href="deleteSawon.jsp?s_no="+s_no;
}
}
</script>
</head>
<body>
<h1>직원 정보 리스트</h1>
<a href="addSawon.html">사원추가</a>
<br/>
<%
request.setCharacterEncoding("euc-kr");
String search = request.getParameter("search");
String searchText = request.getParameter("searchText");
%>
<form method="post" action="index.jsp">
<select name="search">
<option value="s_id">id</option>
<option value="s_addr">근무지</option>
<option value="s_dept">부서명</option>
</select>
<input type="text" name="searchText"/>
<input type="submit" name="검색"/>
</form>
<br/>
<jsp:useBean id="dao" class="bean.dao.EmpDao" />
<table border="1">
<tr>
<th>사번</th>
<th>아이디</th>
<th>이름</th>
<th>패스워드</th>
<th>나이</th>
<th>근무지</th>
<th>부서명</th>
<th>수정</th>
<th>삭제</th>
</tr>
<%
List list = null;
if(searchText == null){
list = dao.getList();
}else if(searchText != null){
list = dao.getList(search,searchText);
}
for(int i=0;i<list.size();i++){
EmpDto dto = (EmpDto)list.get(i);
%>
<tr>
<th><%=dto.getS_no()%></th>
<th><%=dto.getS_id()%></th>
<th><%=dto.getS_name()%></th>
<th><%=dto.getS_pass()%></th>
<th><%=dto.getS_age()%></th>
<th><%=dto.getS_addr()%></th>
<th><%=dto.getS_dept()%></th>
<th><a href="editSawon.jsp?s_no=<%=dto.getS_no()%>">수정</a></th>
<th><a href="javascript:delCheck('<%=dto.getS_no()%>')">삭제</a></th>
</tr>
<%
}
%>
</table>
</body>
</html>
-----------------------------------
3-19
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/WebContent/v4/addSawon_proc.jsp
<%@ page contentType="text/html; charset=EUC-KR"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%request.setCharacterEncoding("euc-kr");%>
<jsp:useBean id="dao" class="bean.dao.EmpDao"/>
<jsp:useBean id="dto" class="bean.dto.EmpDto">
<jsp:setProperty property="*" name="dto"/>
</jsp:useBean>
<%
int result = dao.addSawon(dto);
if (result > 0) {
%>
<script>
alert("사원이 잘 추가 되었습니다.");
location.href = "index.jsp";
</script>
<%
}
%>
</body>
</html>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%request.setCharacterEncoding("euc-kr");%>
<jsp:useBean id="dao" class="bean.dao.EmpDao"/>
<jsp:useBean id="dto" class="bean.dto.EmpDto">
<jsp:setProperty property="*" name="dto"/>
</jsp:useBean>
<%
int result = dao.addSawon(dto);
if (result > 0) {
%>
<script>
alert("사원이 잘 추가 되었습니다.");
location.href = "index.jsp";
</script>
<%
}
%>
</body>
</html>
-----------------------------------
3-20
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/WebContent/v4/addSawon.html
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
<h1>사원 추가</h1>
<form action="addSawon_proc.jsp" method="post">
<table border="1">
<tr>
<td align="center">아이디</td>
<td><input type="text" name="s_id" /></td>
</tr>
<tr>
<td align="center">이름</td>
<td><input type="text" name="s_name" /></td>
</tr>
<tr>
<td align="center">나이</td>
<td><input type="text" name="s_age" /></td>
</tr>
<tr>
<td align="center">패스워드</td>
<td><input type="password" name="s_pass" /></td>
</tr>
<tr>
<td align="center">근무지</td>
<td>
<select name="s_addr">
<option value="서울">서울</option>
<option value="광주">광주</option>
<option value="부산">부산</option>
<option value="제주">제주</option>
</select>
</td>
</tr>
<tr>
<td align="center">부서명</td>
<td>
<select name="s_dept">
<option value="영업">영업</option>
<option value="기술">기술</option>
<option value="기획">기획</option>
</select>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="추가" />
<input type="reset" value="다시 작성" />
</td>
</tr>
</table>
</form>
</body>
</html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
<h1>사원 추가</h1>
<form action="addSawon_proc.jsp" method="post">
<table border="1">
<tr>
<td align="center">아이디</td>
<td><input type="text" name="s_id" /></td>
</tr>
<tr>
<td align="center">이름</td>
<td><input type="text" name="s_name" /></td>
</tr>
<tr>
<td align="center">나이</td>
<td><input type="text" name="s_age" /></td>
</tr>
<tr>
<td align="center">패스워드</td>
<td><input type="password" name="s_pass" /></td>
</tr>
<tr>
<td align="center">근무지</td>
<td>
<select name="s_addr">
<option value="서울">서울</option>
<option value="광주">광주</option>
<option value="부산">부산</option>
<option value="제주">제주</option>
</select>
</td>
</tr>
<tr>
<td align="center">부서명</td>
<td>
<select name="s_dept">
<option value="영업">영업</option>
<option value="기술">기술</option>
<option value="기획">기획</option>
</select>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="추가" />
<input type="reset" value="다시 작성" />
</td>
</tr>
</table>
</form>
</body>
</html>
-----------------------------------
3-21
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/WebContent/v4/deleteSawon.jsp
<%@ page contentType="text/html; charset=EUC-KR"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%request.setCharacterEncoding("euc-kr");%>
<jsp:useBean id="dao" class="bean.dao.EmpDao"/>
<%
String s_no = request.getParameter("s_no");
int result = dao.deleteSawon(s_no);
if(result > 0){
%>
<script>
alert("사원이 잘 삭제 되었습니다.");
location.href="index.jsp";
</script>
<%
}
%>
</body>
</html>
<%@ page import="java.sql.*"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%request.setCharacterEncoding("euc-kr");%>
<jsp:useBean id="dao" class="bean.dao.EmpDao"/>
<%
String s_no = request.getParameter("s_no");
int result = dao.deleteSawon(s_no);
if(result > 0){
%>
<script>
alert("사원이 잘 삭제 되었습니다.");
location.href="index.jsp";
</script>
<%
}
%>
</body>
</html>
-----------------------------------
3-22
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/WebContent/v4/editSawon_proc.jsp
<%@page import="dbcp.custom.DBConnectionMgr"%>
<%@ page contentType="text/html; charset=EUC-KR"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%request.setCharacterEncoding("euc-kr");%>
<jsp:useBean id="dao" class="bean.dao.EmpDao"/>
<jsp:useBean id="dto" class="bean.dto.EmpDto">
<jsp:setProperty property="*" name="dto"/>
</jsp:useBean>
<%
int result = dao.updateSawon(dto);
if(result > 0){
%>
<script>
alert("사원이 잘 수정 되었습니다.");
location.href="index.jsp";
</script>
<%
}
%>
</body>
</html>
<%@ page contentType="text/html; charset=EUC-KR"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<%request.setCharacterEncoding("euc-kr");%>
<jsp:useBean id="dao" class="bean.dao.EmpDao"/>
<jsp:useBean id="dto" class="bean.dto.EmpDto">
<jsp:setProperty property="*" name="dto"/>
</jsp:useBean>
<%
int result = dao.updateSawon(dto);
if(result > 0){
%>
<script>
alert("사원이 잘 수정 되었습니다.");
location.href="index.jsp";
</script>
<%
}
%>
</body>
</html>
-----------------------------------
3-23
Project :DBApp
Workspace : ~\JSP\EclipseWork
/DBApp/WebContent/v4/editSawon.jsp
<%@ page contentType="text/html; charset=EUC-KR"%>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<jsp:useBean id="dao" class="bean.dao.EmpDao"/>
<jsp:useBean id="dto" class="bean.dto.EmpDto"/>
<%
String s_no = request.getParameter("s_no");
dto = dao.getSawon(s_no);
%>
<form action="editSawon_proc.jsp" method="post">
<input type="hidden" name="s_no" value="<%=dto.getS_no()%>"/>
<table border="1">
<tr>
<td align="center">아이디</td>
<td><input type="text" name="s_id" value="<%=dto.getS_id()%>"/></td>
</tr>
<tr>
<td align="center">이름</td>
<td><input type="text" name="s_name" value="<%=dto.getS_name()%>"/></td>
</tr>
<tr>
<td align="center">나이</td>
<td><input type="text" name="s_age" value="<%=dto.getS_age()%>"/></td>
</tr>
<tr>
<td align="center">패스워드</td>
<td><input type="password" name="s_pass" value="<%=dto.getS_pass()%>"/></td>
</tr>
<tr>
<td align="center">근무지</td>
<td>
<select name="s_addr">
<option value="서울"<%if(dto.getS_addr().equals("서울")){%>selected="selected"<%}%>>서울</option>
<option value="광주"<%if(dto.getS_addr().equals("광주")){%>selected="selected"<%}%>>광주</option>
<option value="부산"<%if(dto.getS_addr().equals("부산")){%>selected="selected"<%}%>>부산</option>
<option value="제주"<%if(dto.getS_addr().equals("제주")){%>selected="selected"<%}%>>제주</option>
</select>
</td>
</tr>
<tr>
<td align="center">부서명</td>
<td>
<select name="s_dept">
<option value="영업"<%if(dto.getS_dept().equals("영업")){%>selected="selected"<%}%>>영업</option>
<option value="기술"<%if(dto.getS_dept().equals("기술")){%>selected="selected"<%}%>>기술</option>
<option value="기획"<%if(dto.getS_dept().equals("기획")){%>selected="selected"<%}%>>기획</option>
</select>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="수정" />
<input type="reset" value="다시 작성" />
</td>
</tr>
</table>
</form>
</body>
</html>
<!DOCTYPE html >
<html>
<head>
<title>Insert title here</title>
</head>
<body>
<jsp:useBean id="dao" class="bean.dao.EmpDao"/>
<jsp:useBean id="dto" class="bean.dto.EmpDto"/>
<%
String s_no = request.getParameter("s_no");
dto = dao.getSawon(s_no);
%>
<form action="editSawon_proc.jsp" method="post">
<input type="hidden" name="s_no" value="<%=dto.getS_no()%>"/>
<table border="1">
<tr>
<td align="center">아이디</td>
<td><input type="text" name="s_id" value="<%=dto.getS_id()%>"/></td>
</tr>
<tr>
<td align="center">이름</td>
<td><input type="text" name="s_name" value="<%=dto.getS_name()%>"/></td>
</tr>
<tr>
<td align="center">나이</td>
<td><input type="text" name="s_age" value="<%=dto.getS_age()%>"/></td>
</tr>
<tr>
<td align="center">패스워드</td>
<td><input type="password" name="s_pass" value="<%=dto.getS_pass()%>"/></td>
</tr>
<tr>
<td align="center">근무지</td>
<td>
<select name="s_addr">
<option value="서울"<%if(dto.getS_addr().equals("서울")){%>selected="selected"<%}%>>서울</option>
<option value="광주"<%if(dto.getS_addr().equals("광주")){%>selected="selected"<%}%>>광주</option>
<option value="부산"<%if(dto.getS_addr().equals("부산")){%>selected="selected"<%}%>>부산</option>
<option value="제주"<%if(dto.getS_addr().equals("제주")){%>selected="selected"<%}%>>제주</option>
</select>
</td>
</tr>
<tr>
<td align="center">부서명</td>
<td>
<select name="s_dept">
<option value="영업"<%if(dto.getS_dept().equals("영업")){%>selected="selected"<%}%>>영업</option>
<option value="기술"<%if(dto.getS_dept().equals("기술")){%>selected="selected"<%}%>>기술</option>
<option value="기획"<%if(dto.getS_dept().equals("기획")){%>selected="selected"<%}%>>기획</option>
</select>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="수정" />
<input type="reset" value="다시 작성" />
</td>
</tr>
</table>
</form>
</body>
</html>
-----------------------------------
###################################
4. 과제
-----------------------------------
4. 과제
-----------------------------------
-----------------------------------
###################################
5. 과제 해결
-----------------------------------
5. 과제 해결
-----------------------------------
-----------------------------------
###################################
6. 기타
----------------------------------- -----------------------------------