[JSP] DBCP + Bean을 사용한 데이터 조회
30 Nov 2021 -
8 minute read
DBCP(DataBase Connection Pool)와 Bean을 사용해 데이터 조회하기
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_Drill.RegisterBean.java
package dbcp_Drill;
public class RegisterBean {
private String uid;
private String upw;
private String uname;
private int num1;
private int num2;
private String uemail;
private String uphone;
private String zipcode;
private String address;
private String job;
public String getUid() {
return uid;
}
public void setUid(String uid) {
this.uid = uid;
}
public String getUpw() {
return upw;
}
public void setUpw(String upw) {
this.upw = upw;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
public int getNum1() {
return num1;
}
public void setNum1(int num1) {
this.num1 = num1;
}
public int getNum2() {
return num2;
}
public void setNum2(int num2) {
this.num2 = num2;
}
public String getUemail() {
return uemail;
}
public void setUemail(String uemail) {
this.uemail = uemail;
}
public String getUphone() {
return uphone;
}
public void setUphone(String uphone) {
this.uphone = uphone;
}
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;
}
}
dbcp_Drill.DBConnectionMgr.java
package dbcp_Drill;
/**
* 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_Drill.RegisterMgr.java
package dbcp_Drill;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
public class RegisterMgr {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
public RegisterMgr() {
try {
DBConnectionMgr pool = DBConnectionMgr.getInstance();
conn = pool.getConnection();
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
public Vector getMemlist() {
Vector vector = new Vector();
try {
stmt = conn.createStatement();
String sql = "select * from memlist order by uname";
rs = stmt.executeQuery(sql);
if (rs != null) {
while (rs.next()) {
RegisterBean regBean = new RegisterBean();
regBean.setUid(rs.getString("uid"));
regBean.setUpw(rs.getString("upw"));
regBean.setUname(rs.getString("uname"));
regBean.setNum1(rs.getInt("num1"));
regBean.setNum2(rs.getInt("num2"));
regBean.setUemail(rs.getString("uemail"));
regBean.setUphone(rs.getString("uphone"));
regBean.setZipcode(rs.getString("zipcode"));
regBean.setAddress(rs.getString("address"));
regBean.setJob(rs.getString("job"));
vector.add(regBean);
}
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return vector;
}
}
dbcp_Bean.View.jsp
<%@page import="dbcp_Drill.RegisterBean"%>
<%@page import="java.util.Vector"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<jsp:useBean id="bean" class="dbcp_Drill.RegisterMgr" scope="page" />
<!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>JSP DBCP BEAN</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>Bean과 Connection Pool을 사용한 DB 연동</h1>
<h3>회원정보</h3>
<table>
<tr>
<th>아이디</th>
<th>비밀번호</th>
<th>이름</th>
<th>주민번호</th>
<th>이메일</th>
<th>연락처</th>
<th>주소</th>
<th>직업</th>
</tr>
<%
Vector veclist = bean.getMemlist();
for (int i = 0; i < veclist.size(); i++){
RegisterBean regBean = (RegisterBean)veclist.get(i);
%>
<tr>
<td><%=regBean.getUid() %></td>
<td><%=regBean.getUpw() %></td>
<td><%=regBean.getUname() %></td>
<td><%=regBean.getNum1() %> - <%=regBean.getNum2() %></td>
<td><%=regBean.getUemail() %></td>
<td><%=regBean.getUphone() %></td>
<td><%="(" + regBean.getZipcode() + ")" %> <%=regBean.getAddress() %></td>
<td><%=regBean.getJob() %></td>
</tr>
<%
}
%>
</table>
</div>
</body>
</html>
-
useBean 액션태그 > 객체를 만드는 것이므로 RegisterMgr를 import 하고
RegisterMgr regMgr = new RegisterMgr();와 같이 사용 가능 -
그렇다면 아래
Vector veclist = bean.getMemlist();대신Vector veclist = regMgr.getMemlist();로 작성
