java,jsp,spring/JSP
DBCP 기법
프루트
2022. 8. 26. 20:23
DBCP 기법(Database Connection Pool)
- 정의 : 데이터베이스 커넥션 객체를 여러개 만들어서 커넥션 풀에 넣어놓고 클라이언트 객체가 필요할 때 마다 커넥션(Connection) 객체를 가져다 쓰고 다 쓴 후에는 다시 풀에 돌려주는 기법 커넥션 풀(Connection Pool)에 돌아간 커넥션 객체는 다른 클라이언트가 요청하면 재사용
- 장점 : 속도 향상, 자원 공유
- server.xml의 <context>태그(작업 경로에 맞는 태그)에 추가
<Resource name=“jdbc/oracle” auth=”container” type=”javax.sql.DataSource” driverClassName=“oracle.jdbc.OracleDriver” url=”jdbc:oracle:thin:@localhost:1521:orcl” username=“scott” password=”tiger” maxActive=”100” maxIdle=”30” maxWait=”10000” />
- DataSource를 통한 사용
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup(”java:comp/env/jdbc/oracle”);
Connection con = ds.getConnection();
PreparedStatement 인터페이스
- 파라미터를 분리할 수 있음 setString(1,???) > 첫번째에는 ??? 입력이라는 뜻
StringBuffer selectQuery = new StringBuffer();
selectQuery.append(“select name, class, tel from member where id =?”);
Connection con = ((DataSource)(new InitialContext().lookup(”java:comp/env/jdbc/oracle”))).getConnection(); PreparedStatement pstmt = con.prepareStatement(selectQuery);
pstmt.setString(1, id);
pstmt.executeQuery();
[데이터 업데이트 예제]
[테이블 조회]
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<% request.setCharacterEncoding("UTF-8"); %>
<%!
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "scott";
String password = "tiger";
String selectQuery = "SELECT * FROM MEMBER2";
%>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<table width="400" border="1">
<tr>
<td>아이디</td>
<td>이름</td>
<td>등급</td>
<td>전화번호</td>
</tr>
<%
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, user, password);
stmt = conn.createStatement();
rs = stmt.executeQuery(selectQuery);
while(rs.next()){
%>
<tr>
<td>
<a href="updateMember.jsp?id=<%= rs.getString("id") %>">
<%= rs.getString("id") %>
</a>
</td>
<td><%= rs.getString("name") %></td>
<td>
<%
int n_class = rs.getInt("class");
if(n_class == 1){
out.print("일반회원");
} else {
out.print("교수님");
}
%>
</td>
<td><%= rs.getString("tel") %></td>
</tr>
<%
}
} catch(SQLException ex){
ex.getMessage();
} finally{
try {
if(rs != null) rs.close();
if(stmt != null) stmt.close();
if(conn != null) conn.close();
} catch(SQLException ex){
ex.getMessage();
}
}
%>
</table>
</body>
</html>[테이블 수정]
<%@page import="javax.naming.InitialContext"%>
<%@page import="javax.naming.Context"%>
<%@page import="java.sql.SQLException"%>
<%@page import="javax.naming.NamingException"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="javax.sql.DataSource"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<% request.setCharacterEncoding("UTF-8"); %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%!
Connection conn = null;
DataSource ds = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
%>
<%
try{
Context ctx = new InitialContext();
ds = (DataSource)ctx.lookup("java:comp/env/jdbc/oracle");
conn = ds.getConnection();
StringBuffer selectQuery = new StringBuffer();
selectQuery.append("SELECT name, class, tel FROM MEMBER2 WHERE ID = ?");
String id = request.getParameter("id");
pstmt = conn.prepareStatement(selectQuery.toString());
pstmt.setString(1, id);
rs = pstmt.executeQuery();
if(rs.next()) {
%>
<form method="post" action="updateProcess.jsp">
아이디 : <input type="text" name="id" readonly value="<%= id %>"><br>
이름 : <input type="text" name="name" value="<%= rs.getString("name") %>"><br>
회원등급 : <input type="text" name="mclass" value="<%= rs.getString("class") %>"><br>
전화번호 : <input type="text" name="tel" value="<%= rs.getString("tel") %>"><br>
<input type="submit" value="수정">
<a href="viewMember.jsp">목록보기</a>
</form>
<%
} else {
%>
<font color="red">아이디가 존재하지 않습니다.</font>
<a href="viewMember.jsp">목록보기</a>
<%
}
}catch(NamingException ne){
ne.printStackTrace();
}catch(SQLException ex){
out.print("데이터베이스 연결이 실패했습니다.<br>");
out.print("SQLException : "+ex.getMessage());
} finally {
try{
if(rs != null) {
rs.close();
}
if(pstmt != null) {
pstmt.close();
}
if(conn != null) {
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
%>
</body>
</html>[테이블 수정 process]
<%@page import="javax.naming.InitialContext"%>
<%@page import="java.sql.SQLException"%>
<%@page import="javax.naming.NamingException"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="javax.sql.DataSource"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<% request.setCharacterEncoding("UTF-8"); %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%!
Connection conn = null;
PreparedStatement pstmt = null;
%>
<%
try{
conn = ((DataSource)(new InitialContext().lookup("java:comp/env/jdbc/oracle"))).getConnection();
}catch(NamingException ne){
ne.printStackTrace();
}catch(SQLException ex){
ex.getMessage();
}
String id="", name="", vclass="",tel="";
id = request.getParameter("id");
name = request.getParameter("name");
vclass = request.getParameter("mclass");
tel = request.getParameter("tel");
StringBuffer updateQuery = new StringBuffer();
updateQuery.append("UPDATE MEMBER2 SET NAME = ?, CLASS = ?, TEL = ? WHERE ID = ?");
try{
pstmt = conn.prepareStatement(updateQuery.toString());
pstmt.setString(1, name);
pstmt.setInt(2, Integer.parseInt(vclass));
pstmt.setString(3, tel);
pstmt.setString(4, id);
int re = pstmt.executeUpdate();
if(re==1){
%>
<%= id %>정보가 수정되었습니다.<br>
[<a href="viewMember.jsp">목록보기</a>]
<%
} else {
%>
변경 실패
<%
}
}catch(SQLException ex){
out.print("데이터베이스 연결이 실패했습니다.<br>");
out.print("SQLException : "+ex.getMessage());
} finally {
try{
if(conn != null) {
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
%>
</body>
</html>