위로
아래
커넥션풀
커넥션풀(DBCP)
- DBCP (Database Connection Pool)
- 웹 컨테이너 (WAS)가 실행되면서 DB와 미리 Connection(연결)해놓은 객체들을 pool에 저장해두었다가 클라이언트 요청이 오면 Connection을 빌려주고, 처리가 끝나면 다시 Connection을 반납 받아 pool에 저장하는 방식
- 데이터베이스와 미리 연결해놓기 위해 사용
- 일반적인 방법으로 DB에 연결할 경우 매번 Driver를 로드하고 Connection 객체를 받아오려면 매번 thread를 생성해야하므로 부담이 생긴다. DBCP를 이용하면 만들어 놓은 Connection 객체들을 재사용하므로 이런 부담을 줄일 수 있다.
- 데이터베이스마다 Connection을 생성하는데 걸리는 시간이 다르다. 일일이 요청 때마다 Connection을 생성하면 클라이언트에데 응답하는 시간이 길어진다.
- ConnectionPool 객체를 구현할 떄에는 javax.sql.DataSource 클래스 이용
매커니즘
- getInstance로 계정 커넥션
- getConnection 메소드로 커넥션 풀에서 커넥션 꺼내오기
- releaseConnection 메소드로 커넥션 반환하기
- closeAll 메소드로 커넥션 풀 종료
JNDI
JNDI (Java Naming and Directory Interface)
- ConnectionPool 객체에 접근할 때 사용
- 외부에 있는 객체를 참고(lookup)하기 위한 API
- 필요한 자원을 key/value 쌍으로 저장한 수 필요할 때 키를 이용해 값을 얻는 방법
- 톰캣 컨테이너가 ConnectionPool 객체를 생성하면 이 객체에 대한 JNDI 이름(key)을 미리 설정한다. 그리고 이 이름(key)을 통해 데이터베이스와 연동 작업을 진행한다
- 사용하는 경우
- 웹 브라우저에서 name/value 쌍으로 전송한 후, 서블릿에서 getParameter로 값을 가져올 때
- 해시맵이나 해시테이블에 키/값으로 저장한 후, 키를 이용해 값을 가져올 때
- 웹 브라우저에서 도메인 이름으로 DNS 서버에 요청할 경우 도메인 이름에 대한 IP 주소를 가져올 때
DataSource 설정
- JDBC 드라이버를 /WEB-INF/lib 폴더에 설치
- ConnectionPool 기능 관련 DBCP.jar 파일을 /WEB-INF/lib 폴더에 설치
- context.xml 문서에 Connection 객체 생성 시 연결할 데이터베이스 정보를 JNDI 로 설정
- DAO 클래스에서 데이터베이스와 연동 시 미리 설정한 JDNI라는 이름으로 데이터베이스와 연결해서 작업
context.xml 파일 설정
<Resource> 를 이용해 톰캣 실행 시 연결할 데이터베이스 설정
<Resource auth="Container"
name="jdbc/oracle"
driverClassName="oracle.jdbc.driver.OracleDriver"
type="javax.sql.DataSource"
url="jdbc:oracle:thin:@127.0.0.1:1521:xe"
username="c##scott"
password="1234"
loginTimeout="100"
maxActive="500"
maxIdle="10"
maxWait="-1"
testOnBorrow="true" />- name : DataSource에 대한 JNDI 이름
- username : 데이터베이스 접속 ID
- password : 데이터베이스 접속 비밀번호
- type : 데이터베이스 종류별 DataSource
- url : 접속할 데이터베이스 주소와 포트 번호 및 SID
- driverClassName : 연결할 데이터베이스 종류에 따른 드라이버 클래스 이름
- factory : 연결할 데이터베이스 종류에 따른 ConnectionPool 생성 클래스 이름
- maxActive : 동시에 최대로 데이터베이스에 연결할 수 있는 Connection 수
- maxIdle : 동시에 idle 상태로 대기할 수 있는 최대 수
- maxWait : 새로운 연결이 생길 떄까지 기다릴 수 있는 최대 시간
전체 코드
더보기
<?xml version="1.0" encoding="UTF-8"?>
<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
--><!-- The contents of this file will be loaded for each web application --><Context>
<!-- Default set of monitored resources. If one of these changes, the -->
<!-- web application will be reloaded. -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<WatchedResource>WEB-INF/tomcat-web.xml</WatchedResource>
<WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>
<!-- Uncomment this to disable session persistence across Tomcat restarts -->
<!--
<Manager pathname="" />
-->
<Resource auth="Container"
name="jdbc/oracle"
driverClassName="oracle.jdbc.driver.OracleDriver"
type="javax.sql.DataSource"
url="jdbc:oracle:thin:@127.0.0.1:1521:xe"
username="c##scott"
password="1234"
loginTimeout="100"
maxActive="500"
maxIdle="10"
maxWait="-1"
testOnBorrow="true" />
</Context>
일반 커넥션 vs 커넥션풀
일반 커넥션
더보기
//MemberDAO
package sec01.ex01;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class MemberDAO {
private static final String driver = "oracle.jdbc.driver.OracleDriver";
private static final String url = "jdbc:oracle:thin:@localhost:1521:XE";
private static final String user = "c##scott";
private static final String pwd = "1234";
private Connection con;
private Statement stmt;
public List<MemberVO> listMembers() {
List<MemberVO> list = new ArrayList<MemberVO>();
try {
connDB();
String query = "select * from t_member ";
System.out.println(query);
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
String id = rs.getString("id");
String pwd = rs.getString("pwd");
String name = rs.getString("name");
String email = rs.getString("email");
Date joinDate = rs.getDate("joinDate");
MemberVO vo = new MemberVO();
vo.setId(id);
vo.setPwd(pwd);
vo.setName(name);
vo.setEmail(email);
vo.setJoinDate(joinDate);
list.add(vo);
}
rs.close();
stmt.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
private void connDB() {
try {
Class.forName(driver);
System.out.println("Oracle");
con = DriverManager.getConnection(url, user, pwd);
System.out.println("Connection");
stmt = con.createStatement();
System.out.println("Statement");
} catch (Exception e) {
e.printStackTrace();
}
}
}//MemberServlet
package sec01.ex01;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Date;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class MemberServlet
*/
@WebServlet("/mem")
public class MemberServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* Default constructor.
*/
public MemberServlet() {
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter();
MemberDAO dao = new MemberDAO();
List<MemberVO> list = dao.listMembers();
out.print("<html><body>");
out.print("<table border=1><tr align='center' bgcolor='lightgreen'>");
out.print("<td>아이디</td><td>비밀번호</td><td>이름</td><td>이메일</td><td>가입일</td></tr>");
for(int i=0;i<list.size();i++){
MemberVO memberVO = list.get(i);
String id = memberVO.getId();
String pwd = memberVO.getPwd();
String name = memberVO.getName();
String email = memberVO.getEmail();
Date joinDate = memberVO.getJoinDate();
out.print("<tr><td>" + id + "</td><td>" + pwd + "</td><td>" + name + "</td><td>" + email + "</td><td>" + joinDate + "</td></tr>");
}
out.print("</table></body></html>");
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}//MemberVO
package sec01.ex01;
import java.sql.Date;
public class MemberVO {
private String id;
private String pwd;
private String name;
private String email;
private Date joinDate;
public MemberVO() {
System.out.println("MemberVO");
}
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 getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getJoinDate() {
return joinDate;
}
public void setJoinDate(Date joinDate) {
this.joinDate = joinDate;
}
}
커넥션풀
더보기
//MemberDAO
package sec01.ex02;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class MemberDAO {
private Connection con;
private PreparedStatement pstmt;
private ResultSet rs;
private DataSource dataFactory;
public MemberDAO() {
try {
Context ctx = new InitialContext();
Context envContext = (Context) ctx.lookup("java:/comp/env");
dataFactory = (DataSource) envContext.lookup("jdbc/oracle");
} catch (Exception e) {
e.printStackTrace();
}
}
public List<MemberVO> listMembers(){
List<MemberVO> list = new ArrayList<MemberVO>();
try {
con = dataFactory.getConnection();
String query = "SELECT * FROM t_member";
pstmt = con.prepareStatement(query);
rs = pstmt.executeQuery();
while(rs.next()) {
String id = rs.getString("id");
String pwd = rs.getString("pwd");
String name = rs.getString("name");
String email = rs.getString("email");
Date JoinDate = rs.getDate("joinDate");
MemberVO vo = new MemberVO();
vo.setId(id);
vo.setPwd(pwd);
vo.setName(name);
vo.setEmail(email);
vo.setJoinDate(JoinDate);
list.add(vo);
}
rs.close();
pstmt.close();
con.close();
}catch (Exception e) {
e.printStackTrace();
}
return list;
}
}// MemberServlet
package sec01.ex02;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Date;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class MemberServlet
*/
@WebServlet("/mem2")
public class MemberServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* Default constructor.
*/
public MemberServlet() {
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter();
MemberDAO dao = new MemberDAO();
List<MemberVO> list = dao.listMembers();
out.print("<html><body>");
out.print("<table border=1><tr align='center' bgcolor='lightgreen'>");
out.print("<td>아이디</td><td>비밀번호</td><td>이름</td><td>이메일</td><td>가입일</td></tr>");
for(int i=0;i<list.size();i++){
MemberVO memberVO = list.get(i);
String id = memberVO.getId();
String pwd = memberVO.getPwd();
String name = memberVO.getName();
String email = memberVO.getEmail();
Date joinDate = memberVO.getJoinDate();
out.print("<tr><td>" + id + "</td><td>" + pwd + "</td><td>" + name + "</td><td>" + email + "</td><td>" + joinDate + "</td></tr>");
}
out.print("</table></body></html>");
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}//MemberVO
package sec01.ex02;
import java.sql.Date;
public class MemberVO {
private String id;
private String pwd;
private String name;
private String email;
private Date joinDate;
public MemberVO() {
System.out.println("MemberVO");
}
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 getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getJoinDate() {
return joinDate;
}
public void setJoinDate(Date joinDate) {
this.joinDate = joinDate;
}
}
커넥션풀 회원정보
MemberDAO
더보기
package sec01.ex03;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class MemberDAO {
private Connection con;
private PreparedStatement pstmt;
private ResultSet rs;
private DataSource dataFactory;
public MemberDAO() {
try {
Context ctx = new InitialContext();
Context envContext = (Context) ctx.lookup("java:/comp/env");
dataFactory = (DataSource) envContext.lookup("jdbc/oracle");
} catch (Exception e) {
e.printStackTrace();
}
}
public List<MemberVO> listMembers(){
List<MemberVO> list = new ArrayList<MemberVO>();
try {
con = dataFactory.getConnection();
String query = "SELECT * FROM t_member";
pstmt = con.prepareStatement(query);
rs = pstmt.executeQuery();
while(rs.next()) {
String id = rs.getString("id");
String pwd = rs.getString("pwd");
String name = rs.getString("name");
String email = rs.getString("email");
Date JoinDate = rs.getDate("joinDate");
MemberVO vo = new MemberVO();
vo.setId(id);
vo.setPwd(pwd);
vo.setName(name);
vo.setEmail(email);
vo.setJoinDate(JoinDate);
list.add(vo);
}
rs.close();
pstmt.close();
con.close();
}catch (Exception e) {
e.printStackTrace();
}
return list;
}
public void addMember(MemberVO memberVO) {
try {
con = dataFactory.getConnection();
String id = memberVO.getId();
String pwd = memberVO.getPwd();
String name = memberVO.getName();
String email = memberVO.getEmail();
String query = "INSERT INTO t_member ";
query += "(id,pwd,name,email) ";
query += "VALUES(?,?,?,?)";
System.out.println("prepareStatment: " +query);
pstmt=con.prepareStatement(query);
pstmt.setString(1, id);
pstmt.setString(2, pwd);
pstmt.setString(3, name);
pstmt.setString(4, email);
pstmt.executeUpdate();
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
System.out.println(1);
}
}
public void delMember(String id) {
try {
con = dataFactory.getConnection();
String query = "delete from t_member where id=?";
System.out.println("prepareStatement:"+query);
pstmt = con.prepareStatement(query);
pstmt.setString(1, id);
pstmt.executeUpdate();
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
MemberVO
더보기
package sec01.ex03;
import java.sql.Date;
public class MemberVO {
private String id;
private String pwd;
private String name;
private String email;
private Date joinDate;
public MemberVO() {
System.out.println("MemberVO ������ ȣ��");
}
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 getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getJoinDate() {
return joinDate;
}
public void setJoinDate(Date joinDate) {
this.joinDate = joinDate;
}
}
MemberServlet
더보기
package sec01.ex03;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Date;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class MemberServlet
*/
@WebServlet("/mem3")
public class MemberServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* Default constructor.
*/
public MemberServlet() {
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doHandle(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doHandle(request, response);
}
private void doHandle(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
MemberDAO dao = new MemberDAO();
PrintWriter out = response.getWriter();
String command = request.getParameter("command");
if (command != null && command.equals("addMember")) {
String _id = request.getParameter("id");
String _pwd = request.getParameter("pwd");
String _name = request.getParameter("name");
String _email = request.getParameter("email");
MemberVO vo = new MemberVO();
vo.setId(_id);
vo.setPwd(_pwd);
vo.setName(_name);
vo.setEmail(_email);
dao.addMember(vo);
} else if(command != null && command.equals("delMember")) {
String id = request.getParameter("id");
dao.delMember(id);
}
List<MemberVO> list = dao.listMembers();
out.print("<html><body>");
out.print("<table border=1><tr align='center' bgcolor='lightgreen'>");
out.print("<td>아이디</td><td>비밀번호</td><td>이름</td><td>이메일</td><td>가입일</td><td>삭제</td></tr>");
for(int i = 0; i < list.size(); i++) {
MemberVO memberVO = (MemberVO) list.get(i);
String id = memberVO.getId();
String pwd = memberVO.getPwd();
String name = memberVO.getName();
String email = memberVO.getEmail();
Date joinDate = memberVO.getJoinDate();
out.print("<tr><td>" + id + "</td><td>" + pwd + "</td><td>"
+ name + "</td><td>" + email + "</td><td>" + joinDate
+ "</td><td>" + "<a href='./mem3?command=delMember&id="
+ id + "'> 삭제 </a></td></tr>");
}
out.print("</table></body></html>");
out.print("<a href='ch07/memberForm.jsp'> 새 회원 등록하기 </a>");
}
}
memberFrom.jsp
더보기
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script src="../jquery/jquery-3.7.0.min.js"></script>
<script type="text/javascript">
function fn_sendMember(){
let frmMember = document.frmMember;
let id = frmMember.id.value;
let pwd = frmMember.pwd.value;
let name = frmMember.name.value;
let email = frmMember.email.value;
if(id.lenght == 0 || id == ""){
alert("아이디 필수");
} else if (pwd.length == 0 || pwd ==""){
alert("비밀번호 필수");
} else if (name.length == 0 || name == ""){
alert("이름 필수");
} else if (email.length == 0 || email == ""){
alert("이메일 필수");
} else {
frmMember.method = "post";
frmMember.action = "../mem3";
frmMember.submit();
}
}
</script>
</head>
<body>
<form name="frmMember">
<table>
<tr>
<th colspan="2">회원 가입창</th>
</tr>
<tr>
<td>아이디</td>
<td><input type="text" name="id"></td>
</tr>
<tr>
<td>비밀번호</td>
<td><input type="password" name="pwd"></td>
</tr>
<tr>
<td>이름</td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td>이메일</td>
<td><input type="text" name="email"></td>
</tr>
</table>
<input type="button" value="가입하기" onclick="fn_sendMember()">
<input type="reset" value="다시 입력">
<input type="hidden" name="command" value="addMember">
</form>
</body>
</html>