[Tomcat] JDBC pool + MySql

참고: http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html

MySQL JDBC 드라이버

• 다운로드 - http://dev.mysql.com/downloads/connector/j/
• 압축을 풀고 jar 파일을 반드시 Tomcat 설치 폴더의 lib 폴더에 복사 한다.
• 주의: 웹 앱 폴더의 WEB-INF/lib 에 복사하면 context.xml 파일에서 설정할 DataSource 객체의 JNDI 등록이 실패할 수 있다.
• 참고: http://stackoverflow.com/questions/13161747/jdbc-apache-tomcat-pooling-jar

결과물 폴더 구조
 - 테스트용 프로젝트는 결과적으로 아래와 같은 구조를 갖게 된다.

테스트를 위한 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