■ 準備
* 以下のjarファイルをインポートする
[1] /lib/tomcat-jdbc.jar
[2] /bin/tomcat-juli.jar
[3] mysql-connector-java-X.X.XX-bin.jar (今回は「mysql-connector-java-5.1.40-bin.jar」)
補足：[2] tomcat-juli.jar について
『[2] tomcat-juli.jar』をインポートしないと、コンパイルは通るが、以下の例外が発生する
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/juli/logging/LogFactory
at org.apache.tomcat.jdbc.pool.PoolProperties.<clinit>(PoolProperties.java:36)
at com.sample.TomcatJdbcConnectionPoolDemo.main(TomcatJdbcConnectionPoolDemo.java:12)
Caused by: java.lang.ClassNotFoundException: org.apache.juli.logging.LogFactory
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
... 2 more
■ サンプル
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;
public class TomcatJdbcConnectionPoolDemo {
public static void main(String[] args) {
PoolProperties dbPool = new PoolProperties();
dbPool.setUrl("jdbc:mysql://localhost:3306/sampledb?useSSL=false");
dbPool.setDriverClassName("org.gjt.mm.mysql.Driver");
dbPool.setUsername("root");
dbPool.setPassword("password");
dbPool.setJmxEnabled(true);
dbPool.setTestWhileIdle(false);
dbPool.setTestOnBorrow(true);
dbPool.setValidationQuery("SELECT 1");
dbPool.setTestOnReturn(false);
dbPool.setValidationInterval(30000);
dbPool.setTimeBetweenEvictionRunsMillis(30000);
dbPool.setMaxActive(100);
dbPool.setInitialSize(10);
dbPool.setMaxWait(10000);
dbPool.setRemoveAbandonedTimeout(60);
dbPool.setMinEvictableIdleTimeMillis(30000);
dbPool.setMinIdle(10);
dbPool.setLogAbandoned(true);
dbPool.setRemoveAbandoned(true);
dbPool.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"
+ "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
DataSource datasource = new DataSource();
datasource.setPoolProperties(dbPool);
try (Connection connection = datasource.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from person");) {
while (resultSet.next()) {
System.out.println("Id:" + resultSet.getString("id") + " Name:" + resultSet.getString("name"));
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
補足：「useSSL=false」について
* 以下のログが出るため。
警告ログ
Thu Dec 28 23:54:28 JST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
■ パフォーマンス検証
http://symfoware.blog68.fc2.com/blog-entry-1364.html
のように検証。対象はMySQL。
【１】Poolなし版の検証プログラム
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class NormalDemo {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/sampledb?useSSL=false";
String user = "root";
String password = "password";
long start = System.currentTimeMillis();
for (int i = 0; i < 101; i++) {
long lap = System.currentTimeMillis();
try (Connection connection = DriverManager.getConnection(url, user, password);) {
} catch (SQLException ex) {
ex.printStackTrace();
}
System.out.println("[" + i + "]" + (System.currentTimeMillis() - lap));
}
long end = System.currentTimeMillis();
System.out.println("[Done]" + (end - start));
}
}
【２】Poolあり版の検証プログラム
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;
public class TomcatJdbcConnectionPoolDemo {
public static void main(String[] args) {
PoolProperties dbPool = new PoolProperties();
dbPool.setUrl("jdbc:mysql://localhost:3306/sampledb?useSSL=false");
dbPool.setDriverClassName("org.gjt.mm.mysql.Driver");
dbPool.setUsername("root");
dbPool.setPassword("password");
dbPool.setJmxEnabled(true);
dbPool.setTestWhileIdle(false);
dbPool.setTestOnBorrow(true);
dbPool.setValidationQuery("SELECT 1");
dbPool.setTestOnReturn(false);
dbPool.setValidationInterval(30000);
dbPool.setTimeBetweenEvictionRunsMillis(30000);
dbPool.setMaxActive(100);
dbPool.setInitialSize(10);
dbPool.setMaxWait(10000);
dbPool.setRemoveAbandonedTimeout(60);
dbPool.setMinEvictableIdleTimeMillis(30000);
dbPool.setMinIdle(10);
dbPool.setLogAbandoned(true);
dbPool.setRemoveAbandoned(true);
dbPool.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"
+ "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
DataSource datasource = new DataSource();
datasource.setPoolProperties(dbPool);
long start = System.currentTimeMillis();
for (int i = 0; i < 101; i++) {
long lap = System.currentTimeMillis();
try (Connection connection = datasource.getConnection();) {
} catch (SQLException ex) {
ex.printStackTrace();
}
System.out.println("[" + i + "]" + (System.currentTimeMillis() - lap));
}
long end = System.currentTimeMillis();
System.out.println("[Done]" + (end - start));
}
}
検証結果
* パフォーマンスは確かにあがる
【１】Poolなし版の検証プログラム
1回目：769ms（うち初回接続：395ms）
2回目：744ms（うち初回接続：379ms）
3回目：750ms（うち初回接続：390ms）
【２】Poolあり版の検証プログラム
1回目：397ms（うち初回接続：374ms）
2回目：415ms（うち初回接続：392ms）
3回目：396ms（うち初回接続：373ms）
