[JSP] DBCP를 사용한 데이터 조회(SELECT)
27 Nov 2021 -
7 minute read
DBCP(DataBase Connection Pool) 사용, Bean 없이 JSP에서 바로 실행 해 데이터 조회하기
DB
create database mydb;
use mydb;
create table memlist(
uid char(10) primary key,
upw char(10),
uname char(10),
num1 int,
num2 int,
uemail char(20),
uphone char(20),
zipcode char(10),
address char(20),
job char(10)
);
insert into memlist
values
('vidv', '1234', '김준형', 941125, 1234567, 'vjun@naver.com',
'010-123-4567', 26547, '서울시 은평구', '개발자'),
('abcddd', '45456', '박동석', 901215, 1634567, 'pds@naver.com',
'010-1432-5656', 15678, '서울시 강남구', '개발자');
(DBCP)package_DBCP.DBonnectionMgr.java
package pack_DBCP;
/**
* 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.
*
*/
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 = "com.mysql.cj.jdbc.Driver",
_url = "jdbc:mysql://127.0.0.1:3306/myDB?useSSL=false&serverTimezone=Asia/Seoul&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true",
_user = "root",
_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.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;
}
/** 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.get(i);
if (c == co.connection) {
co.inUse = false;
break;
}
}
for (int i = 0; i < connections.size(); i++) {
co = (ConnectionObject) connections.get(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.get(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.get(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.get(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;
}
}
(DBCP)package_DBCP.DBonnectionMgr.java
<!-- DBCP와 JSP를 직접 연결 -->
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@page import="pack_DBCP.DBConnectionMgr"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<jsp:useBean class="pack_DBCP.DBConnectionMgr" id="dbcp" scope="page" />
<!-- class : 객체가 만들어지는것, id : 객체명 -->
<%-- <%@ page import="pack_DBCP.DBConnectionMgr" %> --%>
<!-- 액션태그나 import 중 아무거나 사용 가능 -->
<%
DBConnectionMgr pool = DBConnectionMgr.getInstance();
Connection conn = pool.getConnection();
Statement stmt = conn.createStatement();
String sql = "select * from memlist order by uname";
ResultSet rs = stmt.executeQuery(sql);
%>
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>DBCP를 사용한 JDBC</title>
<style>
div#wrap{
border: 1px solid #000;
padding: 10px;
width: 1000px;
text-align: center;
}
table {
border: 1px solid #000;
border-collapse: collapse;
margin: 0 auto;
}
th,td {
border: 1px solid #000;
padding: 12px;
}
</style>
</head>
<body>
<div id="wrap">
<h1>DBCP를 사용한 데이터 조회</h1>
<table>
<tbody>
<tr>
<th>아이디</th>
<th>비밀번호</th>
<th>이름</th>
<th>주민번호</th>
<th>이메일</th>
<th>연락처</th>
<th>주소</th>
<th>직업</th>
</tr>
<%
if(rs!=null) {
while(rs.next()){
%>
<tr>
<td><%=rs.getString("uid") %></td>
<td><%=rs.getString("upw") %></td>
<td><%=rs.getString("uname") %></td>
<td><%=rs.getInt("num1") %> - <%=rs.getInt("num2") %></td>
<td><%=rs.getString("uemail") %></td>
<td><%=rs.getString("uphone") %></td>
<td><%="(" + rs.getString("zipcode") + ")" %> <%=rs.getString("address") %></td>
<td><%=rs.getString("job") %></td>
</tr>
<%
}
}
%>
</tbody>
</table>
</div>
</body>
</html>
