http://ezbuilder.tistory.com/93
[JSP] MySQL + JSP로 DB연결하여 결과 가져오기
1. MySQL 5.x용 JDBC 드라이버
mysql-connector-java-5.1.25-bin.jar
2. viewMemberList.jsp
---------------------------------------------------------------
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>회원 목록</title>
</head>
<body>
<caption>Member 테이블의 내용</caption>
<table width="100%" border="1">
<thead>
<tr>
<th>이름</th>
<th>아이디</th>
<th>이메일</th>
</tr>
</thead>
<tbody>
<%
Class.forName("com.mysql.jdbc.Driver");
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
String jdbcDriver = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";
String dbUser = "test";
String dbPwd = "test";
conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPwd);
pstmt = conn.prepareStatement("select * from ez_member");
rs = pstmt.executeQuery();
while(rs.next()){
%>
<tr>
<td><%= rs.getString("name") %></td>
<td><%= rs.getString("id") %></td>
<td><%= rs.getString("email") %></td>
</tr>
<%
}
}catch(SQLException se){
se.printStackTrace();
}finally{
if(rs != null) rs.close();
if(pstmt != null) pstmt.close();
if(conn != null) conn.close();
}
%>
</tbody>
</table>
</body>
</html>
---------------------------------------------------------------
http://ezbuilder.tistory.com/94
[JSP] MySQL jdbcDriver 자동로드 및 트랜잭션 처리하기.
1. MySQL JdbcDriver 자동로드시키기 위해 아래와 같이 web.xml 파일에 추가한다.
---------------------------------------------------------------
<servlet>
<servlet-name>jdbcDriver</servlet-name>
<servlet-class>utility.DriverLoader</servlet-class>
<init-param>
<param-name>jdbcDriver</param-name>
<param-value>com.mysql.jdbc.Driver</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
---------------------------------------------------------------
2. MySQL 트랜잭션 처리하기
test.jsp
---------------------------------------------------------------
try{
String jdbcDriver = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";
String dbUser = "test";
String dbPwd = "test";
conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPwd);
// 트랜잭션 시작
conn.setAutoCommit(false);
pstmt = conn.prepareStatement("insert into item_1 values (?)");
pstmt.setInt(1, 10);
pstmt.executeUpdate();
pstmt = conn.prepareStatement("insert into item_2 values (?)");
pstmt.setInt(1, 100);
pstmt.executeUpdate();
// 트랜잭션 작동여부 확인을 위한 예외오류 발생시키기
if(request.getParameter("error") != null){
throw new Exception("예외 발생!");
}
// 트랜잭션 종료
conn.commit}catch(Throwable e){
if(conn != null){
try{
// 오류발생시 롤백
conn.rollback();
}catch(SQLException se){}
}
}finally{
if(pstmt != null) pstmt.close();
if(conn != null) conn.close();
}
---------------------------------------------------------------
http://ezbuilder.tistory.com/95
[JSP] DBCP를 이용한 커넥션 풀(Connection Pool) 사용하기.
1. 필수 jar 파일( WEB-INF > lib 폴더에 복사)
2. 이클립스에서 pool.jocl 파일 작성하기(경로 : src > pool.jocl)
:: MySQL ::
---------------------------------------------------------------
<object class="org.apache.commons.dbcp.PoolableConnectionFactory" xmlns="http://apache.org/xml/xmlns/jakarta/commons/jocl">
<object class="org.apache.commons.dbcp.DriverManagerConnectionFactory">
<string value="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8"/>
<string value="test"/>
<string value="test"/>
</object>
<object class="org.apache.commons.pool.impl.GenericObjectPool">
<object class="org.apache.commons.pool.PoolableObjectFactory" null="true" />
</object>
<object class="org.apache.commons.pool.KeyedObjectPoolFactory" null="true"/>
<string null="true"/>
<boolean value="false"/>
<boolean value="true"/>
</object>
---------------------------------------------------------------
:: Oracle ::
---------------------------------------------------------------
<object class="org.apache.commons.dbcp.PoolableConnectionFactory" xmlns="http://apache.org/xml/xmlns/jakarta/commons/jocl">
<object class="org.apache.commons.dbcp.DriverManagerConnectionFactory">
<string value="jdbc:oracle:thin:@localhost:1521:XE"/>
<string value="test"/>
<string value="test"/>
</object>
<object class="org.apache.commons.pool.impl.GenericObjectPool">
<object class="org.apache.commons.pool.PoolableObjectFactory" null="true" />
</object>
<object class="org.apache.commons.pool.KeyedObjectPoolFactory" null="true"/>
<string null="true"/>
<boolean value="false"/>
<boolean value="true"/>
</object>
---------------------------------------------------------------
3. 커넥션 풀 초기화(경로 : src > DBCPInit.java)
---------------------------------------------------------------
import java.util.StringTokenizer;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
public class DBCPInit extends HttpServlet{
@Override
public void init(ServletConfig config) throws ServletException {
try{
String drivers = config.getInitParameter("jdbcDriver");
StringTokenizer st = new StringTokenizer(drivers, ",");
while(st.hasMoreTokens()){
String jdbcDriver = st.nextToken();
Class.forName(jdbcDriver);
}
Class.forName("org.apache.commons.dbcp.PoolingDriver");
}catch(Exception e){
throw new ServletException(e);
}
}
}
---------------------------------------------------------------
4. web.xml파일에 DBCPInit 등록하기
---------------------------------------------------------------
<servlet>
<servlet-name>DBCPInit</servlet-name>
<servlet-class>DBCPInit</servlet-class>
<init-param>
<param-name>jdbcDriver</param-name>
<param-value>com.mysql.jdbc.Driver</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
---------------------------------------------------------------
5. 커넥션 풀 사용하기
:: test.jsp ::
---------------------------------------------------------------
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>회원 목록</title>
</head>
<body>
<caption>Member 테이블의 내용</caption>
<table width="100%" border="1">
<thead>
<tr>
<th>이름</th>
<th>아이디</th>
<th>이메일</th>
</tr>
</thead>
<tbody>
<%
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
String jdbcDriver = "jdbc:apache:commons:dbcp:/pool";
conn = DriverManager.getConnection(jdbcDriver);
pstmt = conn.prepareStatement("select * from ez_member");
rs = pstmt.executeQuery();
while(rs.next()){
%>
<tr>
<td><%= rs.getString("name") %></td>
<td><%= rs.getString("id") %></td>
<td><%= rs.getString("email") %></td>
</tr>
<%
}
}catch(SQLException se){
se.printStackTrace();
}finally{
if(rs != null) rs.close();
if(pstmt != null) pstmt.close();
if(conn != null) conn.close();
}
%>
</tbody>
</table>
</body>
</html>
---------------------------------------------------------------
