참고:
http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html
MySQL JDBC 드라이버
결과물 폴더 구조
- 테스트용 프로젝트는 결과적으로 아래와 같은 구조를 갖게 된다.
테스트를 위한 DB 준비
- 테스트용 DB 계정 생성 및 DB Schema 생성
C:\> mysql -u root -p
Enter password: *******
mysql> grant all privileges on *.* to tester@localhost identified by 'changeit' with grant option;
Query OK, 0 rows affected (0.17 sec)
mysql> create database javatest;
Query OK, 1 row affected (0.00 sec)
mysql> use javatest;
Database changed
mysql> create table testdata (id int not null auto_increment primary key, foo varchar(25), bar int);
Query OK, 0 rows affected (0.24 sec)
mysql> insert into testdata values(null, 'hello', 12345);
Query OK, 1 row affected (0.02 sec)
mysql> select * from testdata;
+----+-------+-------+
| id | foo | bar |
+----+-------+-------+
| 1 | hello | 12345 |
+----+-------+-------+
1 row in set (0.00 sec)
web.xml
- 테스트용 서블릿 (com.javatest.MySqlTestServlet) 을 등록했다.
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
id="WebApp_ID" version="3.0">
<display-name>TomcatTest</display-name>
<servlet>
<servlet-name>MySqlTestServlet</servlet-name>
<servlet-class>com.javatest.MySqlTestServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>MySqlTestServlet</servlet-name>
<url-pattern>/MySqlTest</url-pattern>
</servlet-mapping>
<welcome-file-list>
<welcome-file>redirect.jsp</welcome-file>
</welcome-file-list>
</web-app>
redirect.jsp
- 테스트용 서블릿 (com.javatest.MySqlTestServlet) 으로 redirect 한다.
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%
response.sendRedirect("MySqlTest");
%>
context.xml 파일
- DataSource 를 생성한다.
<?xml version="1.0" encoding="UTF-8" ?>
<Context docBase="TomcatTest" path="/TomcatTest" reloadable="true">
<Resource name="jdbc/mySqlDb"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
testWhileIdle="true"
testOnBorrow="true"
testOnReturn="false"
validationQuery="SELECT 1"
validationInterval="30000"
timeBetweenEvictionRunsMillis="30000"
maxActive="100"
minIdle="10"
maxWait="10000"
initialSize="10"
removeAbandonedTimeout="60"
removeAbandoned="true"
logAbandoned="true"
minEvictableIdleTimeMillis="30000"
jmxEnabled="true"
jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
loginTimeout="10"
username="tester"
password="changeit"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/javatest?autoReconnect=true"
/>
</Context>
MySqlTestServlet.java
- 테스트용 서블릿 소스 코드
- javax.naming.InitialContext 객체를 생성하고 lookup() 함수로 context.xml 파일에서 설정한 DataSource 객체를 얻는다.
- 나머지는 일반적인 JDBC 사용법과 같다.
- JDBC pool 을 사용해도 Connection 객체는 close() 를 해주어야 한다.
> 참고:
http://stackoverflow.com/questions/4938517/closing-jdbc-connections-in-pool
package com.javatest;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
public class MySqlTestServlet extends HttpServlet {
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/plain");
PrintWriter writer = response.getWriter();
Connection conn = null;
try {
Context con = new InitialContext();
DataSource ds = (DataSource) con
.lookup("java:comp/env/jdbc/mySqlDb");
conn = ds.getConnection();
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("select * from testdata");
ResultSetMetaData rsmd = rs.getMetaData();
int cnt = rsmd.getColumnCount();
StringBuffer sb = new StringBuffer();
for (int i = 0; i < cnt; i++) {
sb.append(String.format("[%s]\t", rsmd.getColumnName(i + 1)));
}
writer.println(sb.toString());
writer.println("=======================================");
while (rs.next()) {
sb = new StringBuffer();
for (int i = 0; i < cnt; i++) {
sb.append(String.format("[%s]\t", rs.getString(i + 1)));
}
writer.println(sb.toString());
}
rs.close();
st.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
실행 결과
- http://localhost:8080/TomcatTest/MySqlTest
