Tomcat JDBC Connection Pool

Tomcat JDBC Connection Pool의 

일반 속성 ( Common Attributes )을 사용 하기

JDBC 연결하고 사용중(서비스중)에 갑자기 발생하는 단절 현상과 같이 Query시에 발생하는 PSQLException 중에서 에러 메시지 종류가 1. ERROR : could not establish connection,2. Detail : could not connect to server 인 경우에 testOnBorrow, validationQuery를 사용하면, 연결 단절시에 대한 조치를 할 수 있다. 

설정하는 옵션에 대한 설명은 아래 표를 참고 한다.

Attribute	Description
defaultAutoCommit	(boolean) The default auto-commit state of connections created by this pool. If not set, default is JDBC driver default (If not set then the setAutoCommit method will not be called.)
testOnBorrow	(boolean) The indication of whether objects will be validated before being borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another. NOTE - for a true value to have any effect, the validationQuery or validatorClassName parameter must be set to a non-null string. In order to have a more efficient validation, see validationInterval. Default value is false
validationQuery	(String) The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query does not have to return any data, it just can't throw a SQLException. The default value is null. Example values are SELECT 1(mysql), select 1 from dual(oracle), SELECT 1(MS Sql Server)
validationQueryTimeout	(int) The timeout in seconds before a connection validation queries fail. This works by calling java.sql.Statement.setQueryTimeout(seconds) on the statement that executes the validationQuery. The pool itself doesn't timeout the query, it is still up to the JDBC driver to enforce query timeouts. A value less than or equal to zero will disable this feature. The default value is -1.
validatorClassName	(String) The name of a class which implements the org.apache.tomcat.jdbc.pool.Validator interface and provides a no-arg constructor (may be implicit). If specified, the class will be used to create a Validator instance which is then used instead of any validation query to validate connections. The default value is null. An example value iscom.mycompany.project.SimpleValidator.

참조 원문 : https://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html

for high-concurrency (높은 동시성)

Validating Connections

데이타베이스 풀링은 문제점을 갖고 있고, 이는 연결된 풀들이 끊어질 수(신선하지 않은) 있다.

<Resource type="javax.sql.DataSource"
            name="jdbc/TestDB"
            factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/mysql"
            username="mysql_user"
            password="mypassword123"
            testOnBorrow="true"
            validationQuery="SELECT 1"
            />

Validation Queries는 몇가지 단점도 있다.

1. 자주 사용하면, 시스템의 성능을 저하시킨다.

2. 멀리 떨어져 있는 상태에서 호출하면, 결과는 실패일 수있다

 

http://www.tomcatexpert.com/blog/2010/04/01/configuring-jdbc-pool-high-concurrency

자바에서 사용하는 경우를 예로 들면 아래와 같다.

Code Example - Plain Java

import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.Statement;

  import org.apache.tomcat.jdbc.pool.DataSource;
  import org.apache.tomcat.jdbc.pool.PoolProperties;

  public class SimplePOJOExample {

      public static void main(String[] args) throws Exception {
          PoolProperties p = new PoolProperties();
          p.setUrl("jdbc:mysql://localhost:3306/mysql");
          p.setDriverClassName("com.mysql.jdbc.Driver");
          p.setUsername("root");
          p.setPassword("password");
          p.setJmxEnabled(true);
          p.setTestWhileIdle(false);
          p.setTestOnBorrow(true);
          p.setValidationQuery("SELECT 1");
          p.setTestOnReturn(false);
          p.setValidationInterval(30000);
          p.setTimeBetweenEvictionRunsMillis(30000);
          p.setMaxActive(100);
          p.setInitialSize(10);
          p.setMaxWait(10000);
          p.setRemoveAbandonedTimeout(60);
          p.setMinEvictableIdleTimeMillis(30000);
          p.setMinIdle(10);
          p.setLogAbandoned(true);
          p.setRemoveAbandoned(true);
          p.setJdbcInterceptors(
            "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
            "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
          DataSource datasource = new DataSource();
          datasource.setPoolProperties(p);

          Connection con = null;
          try {
            con = datasource.getConnection();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select * from user");
            int cnt = 1;
            while (rs.next()) {
                System.out.println((cnt++)+". Host:" +rs.getString("Host")+
                  " User:"+rs.getString("User")+" Password:"+rs.getString("Password"));
            }
            rs.close();
            st.close();
          } finally {
            if (con!=null) try {con.close();}catch (Exception ignore) {}
          }
      }

  }