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이라고 됨 두번당함