35: Servlet connection pool

1.위를 구현하시오.

- DAO 작성시 커넥션 풀을 사용 하여 구현할 것

//code1
//./src/emp/Print.java
package emp;
public class Print {
	private static StringBuilder print = new StringBuilder();
	private static void reset() {
		print.setLength(0);
	}
	public static <T> void append(T t) {
		if (t == null) {
			print.append("x");
		} else {
			print.append(t);
		};
	}
	public static String getAndReset() {
		String str = print.toString();
		reset();
		return str;
	}
}

//code2
//./src/emp/EmpDto.java
package emp;
import java.sql.*;
import java.util.ArrayList;
import emp.*;
public final class EmpDto {
	private String empno;
	private String ename;
	private String job;
	private String mgr;
	private Date hiredate;
	private String sal;
	private String comm;
	private String deptno;
	public EmpDto() {
	}
	public EmpDto(String empno, String ename, String job, String mgr, Date hiredate, String sal, String comm, String deptno) {
		this.empno = empno;
		this.ename = ename;
		this.job = job;
		this.mgr = mgr;
		this.hiredate = hiredate;
		this.sal = sal;
		this.comm = comm;
		this.deptno = deptno;
	}
	public String getEmpno() {
		return empno;
	}
	public void setEmpno(String empno) {
		this.empno = empno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public String getMgr() {
		return mgr;
	}
	public void setMgr(String mgr) {
		this.mgr = mgr;
	}
	public Date getHiredate() {
		return hiredate;
	}
	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}
	public String getSal() {
		return sal;
	}
	public void setSal(String sal) {
		this.sal = sal;
	}
	public String getComm() {
		return comm;
	}
	public void setComm(String comm) {
		this.comm = comm;
	}
	public String getDeptno() {
		return deptno;
	}
	public void setDeptno(String deptno) {
		this.deptno = deptno;
	}
	@Override
	public String toString() {
		Print.append("EMPNO: ");
		Print.append(this.empno);
		Print.append(",&nbsp ENAME: ");
		Print.append(this.ename);
		Print.append(",&nbsp JOB: ");
		Print.append(this.job);
		Print.append(",&nbsp MGR: ");
		Print.append(this.mgr);
		Print.append(",&nbsp HIREDATE: ");
		Print.append(this.hiredate);
		Print.append(",&nbsp SAL: ");
		Print.append(this.sal);
		Print.append(",&nbsp COMM: ");
		Print.append(this.comm);
		Print.append(",&nbsp DEPTNO: ");
		Print.append(this.deptno);
		Print.append("<br/>");
		return Print.getAndReset();
	}
	public String getResult() {
		EmpDao empDao = new EmpDao();
		ArrayList<EmpDto> dtos = empDao.empList();
		for(EmpDto emp : dtos){
			Print.append(emp);
			Print.append("<br>");
		}		
		return Print.getAndReset();
	}
}

//code3
//./src/emp/EmpDao.java
package emp;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import javax.naming.*;
import javax.sql.*;
import emp.*;
public class EmpDao {
	private DataSource dataSource = null;
	public EmpDao() {
		try {
			Context context = new InitialContext();
			dataSource = (DataSource)context.lookup("java:comp/env/jdbc/oracle");
		} catch(Exception e) {
			e.printStackTrace();
		};
	}
	public ArrayList<EmpDto> empList() {
		ArrayList<EmpDto> dtos = new ArrayList<EmpDto>();
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;
		String query = "select * from emp";
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			rs = statement.executeQuery(query);
			while (rs.next()) {
				String empno = rs.getString("empno");
				String ename = rs.getString("ename");
				String job = rs.getString("job");
				String mgr = rs.getString("mgr");
				Date hiredate = rs.getDate("hiredate");
				String sal = rs.getString("sal");
				String comm = rs.getString("comm");
				String deptno = rs.getString("deptno");
				EmpDto dto = new EmpDto(empno, ename, job, mgr, hiredate, sal, comm, deptno);
				dtos.add(dto);
			};
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				};
				if (statement != null) {
					statement.close();
				};
				if (connection != null) {
					connection.close();
				};
			} catch(Exception e2) {
				e2.printStackTrace();
			};
		};
		return dtos;
	}
	public int insert(EmpDto emp) {
		int result = -1;
		Connection connection = null;
		PreparedStatement pstmt = null;
		String query = "insert into emp01 values(?,?,?,?,?,?,?,?)";
		System.out.println("Check the query: " + query);
		try {
			connection = dataSource.getConnection();
			pstmt = connection.prepareStatement(query);
			pstmt.setString(1, emp.getEmpno());
			pstmt.setString(2, emp.getEname());
			pstmt.setString(3, emp.getJob());
			pstmt.setString(4, emp.getMgr());
			pstmt.setDate(5, emp.getHiredate());
			pstmt.setString(6, emp.getSal());
			pstmt.setString(7, emp.getComm());
			pstmt.setString(8, emp.getDeptno());
			result = pstmt.executeUpdate();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
				};
				if (connection != null) {
					connection.close();
				};
			} catch(Exception e2) {
				e2.printStackTrace();
			};
		};
		return result;
	}
	public List<String> getJobs() {
		List<String> jobs = new ArrayList<String>();
		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String query = "select distinct job from emp01";
		System.out.println("Check the query: " + query);
		try {
			connection = dataSource.getConnection();
			pstmt = connection.prepareStatement(query);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				jobs.add(rs.getString("job"));
			};
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				};
				if (pstmt != null) {
					pstmt.close();
				};
				if (connection != null) {
					connection.close();
				};
			} catch(Exception e2) {
				e2.printStackTrace();
			};
		};
		return jobs;
	}
	public List<String> getMgrNames() {
		List<String> names = new ArrayList<String>();
		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String query = "select ename from emp01 where empno in (select distinct mgr from emp01 where mgr is not null)";
		System.out.println("Check the query: " + query);
		try {
			connection = dataSource.getConnection();
			pstmt = connection.prepareStatement(query);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				names.add(rs.getString("ename"));
			};
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				};
				if (pstmt != null) {
					pstmt.close();
				};
				if (connection != null) {
					connection.close();
				};
			} catch(Exception e2) {
				e2.printStackTrace();
			};
		};
		return names;
	}
	public List<String> getDnames() {
		List<String> names = new ArrayList<String>();
		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String query = "select dname from dept";
		System.out.println("Check the query: " + query);
		try {
			connection = dataSource.getConnection();
			pstmt = connection.prepareStatement(query);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				names.add(rs.getString("dname"));
			};
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				};
				if (pstmt != null) {
					pstmt.close();
				};
				if (connection != null) {
					connection.close();
				};
			} catch(Exception e2) {
				e2.printStackTrace();
			};
		};
		return names;
	}
	public String getMgr(String mgrName) {
		String mgr = new String();
		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String query = "select empno from emp01 where ename = ?";
		System.out.println("Check the query: " + query);
		try {
			connection = dataSource.getConnection();
			pstmt = connection.prepareStatement(query);
			pstmt.setString(1, mgrName);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				mgr = rs.getString("empno");
			};
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				};
				if (pstmt != null) {
					pstmt.close();
				};
				if (connection != null) {
					connection.close();
				};
			} catch(Exception e2) {
				e2.printStackTrace();
			};
		};
		return mgr;
	}
	public String getDeptno(String dname) {
		String deptno = new String();
		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String query = "select deptno from dept where dname = ?";
		System.out.println("Check the query: " + query);
		try {
			connection = dataSource.getConnection();
			pstmt = connection.prepareStatement(query);
			pstmt.setString(1, dname);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				deptno = rs.getString("deptno");
			};
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				};
				if (pstmt != null) {
					pstmt.close();
				};
				if (connection != null) {
					connection.close();
				};
			} catch(Exception e2) {
				e2.printStackTrace();
			};
		};
		return deptno;
	}
}

<!-- code4 -->
<!-- ./WebContent/connection_pool_emp.jsp -->
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" import="emp.*" %>
<!DOCTYPE html>
<html>
<head>
<style>
input[type='number'], input[type='text']{
    width: 200px;
}
</style>
<meta charset="UTF-8">
<title>connection_pool_emp.jsp</title>
</head>
<body>
<h1>사원 정보 입력</h1>
<hr/>
<%EmpDao empDao = new EmpDao();%>
<form action="connection_pool_emp_insert.jsp" method="post">
	사원번호 <input type="number" name="empno" min="0" max="9999"><br/>
	사원이름 <input type="text" name="ename" size="10"><br/>
	직급 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <select name="job" id="job">
		<%
			for (String s : empDao.getJobs()) {
				Print.append("<option value=\"");
				Print.append(s);
				Print.append("\">");
				Print.append(s);
				Print.append("</option>");				
				out.println(Print.getAndReset());
			};
		%>	
	</select><br/>
	매니저 &nbsp&nbsp&nbsp <select name="mgr" id="mgr">
		<%
			for (String s : empDao.getMgrNames()) {
				Print.append("<option value=\"");
				Print.append(s);
				Print.append("\">");
				Print.append(s);
				Print.append("</option>");				
				out.println(Print.getAndReset());
			};
		%>		
	</select><br/>
	입사일 &nbsp&nbsp&nbsp <input type="date" name="hiredate"><br/>
	급여 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <input type="text" name="sal" size="10"><br/>
	커미션 &nbsp&nbsp&nbsp <input type="text" name="comm" size="10"><br/>
	부서명 &nbsp&nbsp&nbsp <select name="deptno" id="deptno">
		<%
			for (String s : empDao.getDnames()) {
				Print.append("<option value=\"");
				Print.append(s);
				Print.append("\">");
				Print.append(s);
				Print.append("</option>");				
				out.println(Print.getAndReset());
			};
		%>		
	</select><br/>
	<input type="submit" value="입력">
</form>
</body>
</html>

<!-- code5 -->
<!-- ./WebContent/connection_pool_emp_insert.jsp -->
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" import="emp.*" import="java.sql.Date"
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>connection_pool_emp_insert.jsp</title>
</head>
<body>
<%
	request.setCharacterEncoding("utf-8");
	String empno = request.getParameter("empno");
	String ename = request.getParameter("ename");
	String job = request.getParameter("job");
	String mgrName = request.getParameter("mgr");
	String hiredate = request.getParameter("hiredate");
	String sal = request.getParameter("sal");
	String comm = request.getParameter("comm");
	String dname = request.getParameter("deptno");
	EmpDao empDao = new EmpDao();
	String mgr = empDao.getMgr(mgrName);
	String deptno = empDao.getDeptno(dname);
	EmpDto emp = new EmpDto(
		empno, ename, job, mgr, Date.valueOf(hiredate), sal, comm, deptno
	);
	System.out.println(empDao.insert(emp));
%>
<a href="connection_pool_emp.jsp">Home</a>
</body>
</html>

2. 아래를 설명하시오.

• DAO(Data Access Object) : It can call data from sql with query.
• DTO(Data Transfer Object) : It just transfers like bean.
• 커넥션 풀(Connection pool) : DataSource.