210514_5(연동프로그램)
3) jsp + pool 이용
커넥션 풀사용
ConnectionPool => 연동 객체를 여러개 미리 생성해서 풀에 담아놓고 필요할때마다 꺼내서 사용 후 다시 담아 놓는것
클라------------>디비서버------------>웹서버
연동<-------------서버에찾아가서, 3306나와라,나는 jsp유저인데,암호는~~~고,
jspdb를사용할 권한을 줘라. 이작업의시간이 생각보다 걸린다
--->비용이 증가
그래서 매번 연결하는 것보다, 미리 연결객체를 많이 만들어서
풀pool(Vector)에 담아 놓고 필요할때마다 하나씩 꺼내서
사용하고 돌려주는 개념이다. 사용할것을 권장함.
장점 : 연결속도가 빠름. 단점 : 풀을 생성할 메모리공간이 필요하다
쿼리
실행
결과처리
/**
* Copyright(c) 2001 iSavvix Corporation (http://www.isavvix.com/)
*
* All rights reserved
*
* Permission to use, copy, modify and distribute this material for
* any purpose and without fee is hereby granted, provided that the
* above copyright notice and this permission notice appear in all
* copies, and that the name of iSavvix Corporation not be used in
* advertising or publicity pertaining to this material without the
* specific, prior written permission of an authorized representative of
* iSavvix Corporation.
*
* ISAVVIX CORPORATION MAKES NO REPRESENTATIONS AND EXTENDS NO WARRANTIES,
* EXPRESS OR IMPLIED, WITH RESPECT TO THE SOFTWARE, INCLUDING, BUT
* NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
* FITNESS FOR ANY PARTICULAR PURPOSE, AND THE WARRANTY AGAINST
* INFRINGEMENT OF PATENTS OR OTHER INTELLECTUAL PROPERTY RIGHTS. THE
* SOFTWARE IS PROVIDED "AS IS", AND IN NO EVENT SHALL ISAVVIX CORPORATION OR
* ANY OF ITS AFFILIATES BE LIABLE FOR ANY DAMAGES, INCLUDING ANY
* LOST PROFITS OR OTHER INCIDENTAL OR CONSEQUENTIAL DAMAGES RELATING
* TO THE SOFTWARE.
*
*/
package chapter9;
import java.sql.*;
import java.util.Properties;
import java.util.Vector;
/**
* Manages a java.sql.Connection pool.
*
* @author Anil Hemrajani
*/
public class DBConnectionMgr {
private Vector connections = new Vector(10);
private String _driver = "org.gjt.mm.mysql.Driver",
_url = "jdbc:mysql://127.0.0.1:3306/jspdb?useUnicode=true&characterEncoding=EUCKR",
_user = "jspuser",
_password = "1234";
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;
}
}
<%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR" import="java.sql.*, chapter9.*"%>
<%
DBConnectionMgr pool = DBConnectionMgr.getInstance();
//커넥션 풀 얻어오는 방법
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String id = "",
passwd ="",
name="",
mem_num1="",
mem_num2="",
e_mail="",
phone="",
zipcode="",
address="",
job="";
int counter =0;
try{
conn = pool.getConnection();
//conn = DriverManager.getConnection("jdbc:mysql://localhost:3306:/디비명","계정","암호");
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT * FROM MEMBER");
%>
<html>
<head>
<title>JSP에서 데이터베이스 연동</title>
<link href ="style.css" rel="stylesheet" type="text/css">
<script language="JavaScript" src="script.js">
</script>
</head>
<body bgcolor="#FFFFCC">
<h2> JSP 스크립틀릿에서 Connection Pool을 이용한 데이터베이스 연동 예제입니다...</h2>
<br>
<br>
<h3>회원정보</h3>
<table bordercolor ="#0000ff" border="1">
<tr>
<td><strong>ID</strong></td>
<td><strong>PASSWD</strong></td>
<td><strong>MAME</strong></td>
<td><strong>MEM_NUM1</strong></td>
<td><strong>MEM_NUM2</strong></td>
<td><strong>E_MAIL</strong></td>
<td><strong>PHONE</strong></td>
<td><strong>ZIPCODE/ADDRESS</strong></td>
<td><strong>JOP</strong></td>
</tr>
<%
if(rs!=null){
while(rs.next()){
id = rs.getString("id");
passwd =rs.getString("passwd");
name = rs.getString("name");
mem_num1 = rs.getString("mem_num1");
mem_num2 = rs.getString("mem_num2");
e_mail = rs.getString("e_mail");
phone = rs.getString("phone");
zipcode = rs.getString("zipcode");
address = rs.getString("address");
job = rs.getString("job");
%>
<tr>
<td><%= id %></td>
<td><%= passwd %></td>
<td><%= name %></td>
<td><%= mem_num1 %></td>
<td><%= mem_num2 %></td>
<td><%= e_mail %></td>
<td><%= phone %></td>
<td><%= zipcode %>/<%= address %></td>
<td><%= job %></td>
<%
counter++;
}//end while
}//end if
%>
</tr>
</table>
<br>
<br>
total record : <%= counter %>
<%
}catch(SQLException sqlException){
System.out.println("sql exception");
}catch(Exception exception){
System.out.println("exception");
}finally{
if(rs != null)
try{ rs.close();}
catch(SQLException ex){}
if( stmt != null)
try { rs.close();}
catch(SQLException ex){}
if(conn != null)
try{ pool.freeConnection(conn);}
//conn을 돌려줘라
catch(Exception ex){}
}
%>
</body>
</html>

4) jsp + pool + bean 이용
1. JSP : 컨트롤기능(요청 수신과 + 비지니스메서드 호출), 뷰로직을 담당
2. DTO : jsp와 DAO 사이에서 데이타 전달을 당담
3. DAO : 쿼리 실행후 결과 처리하여 빈에 저장후 리턴
4. DBCP : 연동을 따로 분리하여 풀을 생성하고 연동객체를 별도로 관리함
mvc패턴 model1 구조임 : 특징 jsp가 Host 역할을 하고, 뷰역할도 함.
컨트롤의 기능이 완전 분화되지 않음. 하지만 model부분은 완전히 분리되어 있음.
Data Tansfer Object
Data Access Object
DataBase Connection Pool
-- 왜 분리 되는지, 어떻게 연결되는지 이해하고 설명 할 수 있어야 함. --

RegisterBean.java
DBConnectionMgr.java
재사용
package chapter9;
import java.sql.*;
import java.util.*;
public class MemberMgrPool {
private DBConnectionMgr pool = null;
public MemberMgrPool() { //생성자
try {
pool = DBConnectionMgr.getInstance();
//풀을 얻어온다
} catch (Exception e) {
System.out.println("Error : 커넥션 얻어오기 실패");
}
}// MemberMgrPool()
public Vector getMemberList() { //비즈니스 메소드
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
Vector vecList = new Vector();
try {
conn = pool.getConnection();
String strQuery = "select * from member";
stmt = conn.createStatement();
rs = stmt.executeQuery(strQuery);
while (rs.next()) {
RegisterBean regBean = new RegisterBean();
//rs에서 bean으로 데이터를 담는다.
regBean.setMem_id(rs.getString("id"));
regBean.setMem_passwd(rs.getString("passwd"));
regBean.setMem_name(rs.getString("name"));
regBean.setMem_num1(rs.getString("mem_num1"));
regBean.setMem_num2(rs.getString("mem_num2"));
regBean.setMem_email(rs.getString("e_mail"));
regBean.setMem_phone(rs.getString("phone"));
regBean.setMem_zipcode(rs.getString("zipcode"));
regBean.setMem_address(rs.getString("address"));
regBean.setMem_job(rs.getString("job"));
vecList.add(regBean);
}
} catch (Exception ex) {
System.out.println("Exception" + ex);
} finally {
pool.freeConnection(conn, stmt, rs);
}
return vecList;
}
}// class
<%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR" import="java.util.*, chapter9.*"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>JSP에서 데이터베이스 연동</title>
<link href="style.css" rel="stylesheet" type="text/css">
</head>
<body bgcolor="#FFFFCC">
<h2>Bean과 커넥션 풀을 사용한<br>
데이터베이스 연동 예제입니다...</h2>
<br>
<br>
<h3>회원정보</h3>
<table bordercolor="#0000ff" border="1">
<tr>
<td><strong>ID</strong></td>
<td><strong>PASSWD</strong></td>
<td><strong>NAME</strong></td>
<td><strong>MEM_NUM1</strong></td>
<td><strong>MEM_NUM2</strong></td>
<td><strong>E_MAIL</strong></td>
<td><strong>PHONE</strong></td>
<td><strong>ZIPCODE/ADDRESS</strong></td>
<td><strong>JOB</strong></td>
</tr>
<jsp:useBean id="memMgr" class="chapter9.MemberMgrPool" scope="page"/>
<%
Vector vlist = memMgr.getMemberList();
int counter = vlist.size();
for(int i=0; i<vlist.size(); i++){ //뷰로직
RegisterBean regBean = (RegisterBean) vlist.elementAt(i);
%>
<tr>
<td><%=regBean.getMem_id() %></td>
<td><%=regBean.getMem_passwd() %></td>
<td><%=regBean.getMem_name() %></td>
<td><%=regBean.getMem_num1() %></td>
<td><%=regBean.getMem_num2() %></td>
<td><%=regBean.getMem_email() %></td>
<td><%=regBean.getMem_phone() %></td>
<td><%=regBean.getMem_zipcode() %>/<%=regBean.getMem_address() %></td>
<td><%=regBean.getMem_job() %></td>
<%
}
%>
</tr>
</table>
<br>
<br>
total records : <%= counter %>
</body>
</html>

cf.regBean.setMem_id(rs.getString("id")); //자동완성 사용주의 NString이라고 됨 두번당함