[JSP] JDBC로 데이터베이스 연동 - 2 (자바빈즈, Connection Pool)

 

 

 

자바 빈즈와 Connection Pool을 이용한
데이터베이스 연동방법을 알아본다.

 

 

 

 

자바 빈즈를 이용한 데이터베이스 연동

 

자바 빈즈를 사용하여 데이터베이스와 연동

자바빈즈를 이용한 구조

데이터베이스에 관련된 자바 코드를 JSP 페이지에 스크립트릿으로부터 분리한다.
예를 들어 JDBC 코드들은 RegisterMgr.java에 레코드들은 RegisterBean.java에 분리했다.

 

 

자바 빈즈로 데이터베이스 연동 예제

[JSP] JDBC로 데이터베이스 연동 - 1 (스크립트릿) 에서 사용했던 usingJDBCjsp.jsp를 분리하려 한다.

RegisterBean.java

package ch11;
 
public class RegisterBean {
    private String id;
    private String pwd;
    private String name;
    private String num1;
    private String num2;
    private String email;
    private String phone;
    private String zipcode;
    private String address;
    private String job;
    
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getPwd() {
        return pwd;
    }
    public void setPwd(String pwd) {
        this.pwd = pwd;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getNum1() {
        return num1;
    }
    public void setNum1(String num1) {
        this.num1 = num1;
    }
    public String getNum2() {
        return num2;
    }
    public void setNum2(String num2) {
        this.num2 = num2;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public String getPhone() {
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }
    public String getZipcode() {
        return zipcode;
    }
    public void setZipcode(String zipcode) {
        this.zipcode = zipcode;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public String getJob() {
        return job;
    }
    public void setJob(String job) {
        this.job = job;
    }
}
 

 

RegisterMgr.java

package ch11;
 
import java.sql.*;
import java.util.Vector;
 
public class RegisterMgr {
    
    private final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    private final String JDBC_URL = "jdbc:mysql://localhost:3306/mydb?serverTimezone=UTC";
    private final String USER = "root";
    private final String PASS = "1234";
    
    public RegisterMgr() {
        try {
        Class.forName(JDBC_DRIVER);
        } catch(Exception ex) {
            System.out.println("JDBC 드라이버 로딩 실패");
            ex.printStackTrace();
        }
    }
    
    public Vector<RegisterBean> getRegisterList(){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        Vector<RegisterBean> vlist = new Vector<RegisterBean>();
        try {
            conn = DriverManager.getConnection(JDBC_URL, USER, PASS);
            stmt = conn.createStatement();
            String strQuery = "select * from tblRegister";
            rs = stmt.executeQuery(strQuery);
            
            while(rs.next()) {
                RegisterBean bean = new RegisterBean();
                bean.setId(rs.getString("id"));
                bean.setPwd(rs.getString("pwd"));
                bean.setName(rs.getString("name"));
                bean.setNum1(rs.getString("num1"));
                bean.setNum2(rs.getString("num2"));
                bean.setEmail(rs.getString("email"));
                bean.setPhone(rs.getString("phone"));
                bean.setZipcode(rs.getString("zipcode"));
                bean.setAddress(rs.getString("address"));
                bean.setJob(rs.getString("job"));
                vlist.addElement(bean);
            }
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            if(rs!=null) {
                try {
                    rs.close();
                }catch(Exception e) {}
            }
            if(stmt!=null) {
                try {
                    rs.close();
                }catch(Exception e) {}
            }
            if(conn!=null) {
                try {
                    rs.close();
                }catch(Exception e) {}
            }
        }        
        return vlist;
    }
}
 

 

usingJDBCBean.jsp

<%@ page contentType="text/html;charset=EUC-KR"%>
<%@ page import="java.util.*, ch11.*" %>
<jsp:useBean id="regMgr" class="ch11.RegisterMgr"/>
<!DOCTYPE html>
<html>
<head>
<title>JSP에서 데이터베이스 연동</title>
<link href="style.css" rel="stylesheet" type="text/css">
</head>
<body bgcolor="#FFFFCC">
<div align="center">
    <h2>JSP 스크립틀릿에서 데이터베이스 연동 예제</h2><br/>
    <h3>회원정보</h3>
    <table>
        <tr>
           <th>ID</th>
           <th>PASSWD</th>
           <th>NAME</th>
           <th>NUM1</th>
           <th>NUM2</th>
           <th>EMAIL</th>
           <th>PHONE</th>
           <th>ZIPCODE/ADDRESS</th>
           <th>JOB</th>
        </tr>
<%
    Vector<RegisterBean> vlist = regMgr.getRegisterList();
    int counter = vlist.size();
    for(int i=0; i<vlist.size(); i++){
        RegisterBean regBean = vlist.get(i);
%>
<tr>
    <td><%=regBean.getId() %></td>
    <td><%=regBean.getPwd() %></td>
    <td><%=regBean.getName() %></td>
    <td><%=regBean.getNum1() %></td>
    <td><%=regBean.getNum2() %></td>
    <td><%=regBean.getEmail() %></td>
    <td><%=regBean.getPhone() %></td>
    <td><%=regBean.getZipcode() %>/<%=regBean.getAddress() %></td>
    <td><%=regBean.getJob() %></td>
</tr>
<%} %>
    </table>
    <br/><br/>
    total records : <%=counter %>
</div>
</body>
</html>

실행화면은 동일하게 나온다.

 

 

---

 

 

 

Connection Pool을 사용한 데이터베이스 연결

기존 데이터베이스 연동 방법의 문제점 : Connection 객체는 새롭게 생성될 때 많은 시스템 자원이 요구된다. 메모리에 객체를 할당할 자리를 만들고, 객체가 사용할 여러 자원들에 대한 초기화 작업 등에 시간이 많이 소모된다.

Connection Pool 을 사용해서 보다 효율적으로 데이터베이스에 연동이 가능하다. (Pooling 기법)
Connection 객체를 연결 요청이 있을 때마다 매번 생성, 사용, 해제하지 않고 미리 Connection 객체들을 여러 개 만들어 두고 계속 재사용한다.

JSP 페이지와 DBConnectionMgr을 통한 데이터베이스 연동

 

ConnectionPool을 이용한 데이터베이스 프로그램

프로그래밍 방법

• 1단계: 데이터베이스 연결 pool 객체를 얻는다.
  DBConnectionMgr pool = DBConnectionMgr.getInstance();
• 2단계: 얻어진 pool 객체를 이용해 Connection 객체를 얻는다.
  Connection conn = pool.getConnection();
• 3단계: 얻어진 Connection 객체를 사용해 DB에 질의를 던지고, 질의 결과를 처리
• 4단계: 사용된 Connection 객체를 pool에 반환, Statement객체(stmt), ResultSet 객체(rs)
  pool.freeConnection(conn, stmt, rs);

 

ConnectionPool을 이용한 데이터베이스 연결 예제

DBConnectionMgr.java 

public class DBConnectionMgr {
    private Vector connections = new Vector(10);
    private String _driver = "com.mysql.cj.jdbc.Driver",
    _url = "jdbc:mysql://localhost/mydb?serverTimezone=UTC",
    _user = "root",
    _password = "1234";
...
...
    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 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.get(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;
    }
...
...
    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();
        }
    }
...

코드가 길어서 주요부분만 작성해두었다.

 

ConnectionPool과 Bean을 이용한 데이터베이스 연결

JSP 페이지와 DBConnectionMgr, usingJDBCPoolBean.jsp, RegisterMgrPool.java를 사용한 연동

 

RegisterMgrPool.java

package ch11;
 
import java.sql.*;
import java.util.Vector;
 
public class RegisterMgrPool {
    
    private DBConnectionMgr pool = null;
    
    public RegisterMgrPool() {
        try {
            pool = DBConnectionMgr.getInstance();
        } catch(Exception ex) {
            ex.printStackTrace();
        }
    }
    
    public Vector<RegisterBean> getRegisterList(){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        Vector<RegisterBean> vlist = new Vector<RegisterBean>();
        try {
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String strQuery = "select * from tblRegister";
            rs = stmt.executeQuery(strQuery);
            
            while(rs.next()) {
                RegisterBean bean = new RegisterBean();
                bean.setId(rs.getString("id"));
                bean.setPwd(rs.getString("pwd"));
                bean.setName(rs.getString("name"));
                bean.setNum1(rs.getString("num1"));
                bean.setNum2(rs.getString("num2"));
                bean.setEmail(rs.getString("email"));
                bean.setPhone(rs.getString("phone"));
                bean.setZipcode(rs.getString("zipcode"));
                bean.setAddress(rs.getString("address"));
                bean.setJob(rs.getString("job"));
                vlist.addElement(bean);
            }
        }catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            pool.freeConnection(conn,stmt,rs);    
        }        
        return vlist;
    }
}
 

 

usingJDBCPoolBean.jsp

<%@ page contentType="text/html;charset=EUC-KR"%>
<%@ page import="java.util.*, ch11.*" %>
<jsp:useBean id="regMgr" class="ch11.RegisterMgrPool"/>
<!DOCTYPE html>
<html>
<head>
<title>JSP에서 데이터베이스 연동</title>
<link href="style.css" rel="stylesheet" type="text/css">
</head>
<body bgcolor="#FFFFCC">
<div align="center">
    <h2>JSP 스크립틀릿에서 데이터베이스 연동 예제</h2><br/>
    <h3>회원정보</h3>
    <table>
        <tr>
           <th>ID</th>
           <th>PASSWD</th>
           <th>NAME</th>
           <th>NUM1</th>
           <th>NUM2</th>
           <th>EMAIL</th>
           <th>PHONE</th>
           <th>ZIPCODE/ADDRESS</th>
           <th>JOB</th>
        </tr>
<%
    Vector<RegisterBean> vlist = regMgr.getRegisterList();
    int counter = vlist.size();
    for(int i=0; i<vlist.size(); i++){
        RegisterBean regBean = vlist.get(i);
%>
<tr>
    <td><%=regBean.getId() %></td>
    <td><%=regBean.getPwd() %></td>
    <td><%=regBean.getName() %></td>
    <td><%=regBean.getNum1() %></td>
    <td><%=regBean.getNum2() %></td>
    <td><%=regBean.getEmail() %></td>
    <td><%=regBean.getPhone() %></td>
    <td><%=regBean.getZipcode() %>/<%=regBean.getAddress() %></td>
    <td><%=regBean.getJob() %></td>
</tr>
<%} %>
    </table>
    <br/><br/>
    total records : <%=counter %>
</div>
</body>
</html>