Posted In: Server, Tomcat

Tomcat 9 – Why you should start using org.apache.tomcat.jdbc.pool

The JDBC Connection Pool org.apache.tomcat.jdbc.pool is a replacement or an alternative to the Apache Commons DBCP connection pool.
So why do we need a new connection pool?
Here are a few of the reasons:
1. Multi threaded. Support for highly concurrent environments and multi core/cpu systems.
2. Tomcat jdbc pool implements the ability retrieve a connection asynchronously, without adding additional threads to the library itself.
3. Starvation proof. If a pool is empty, and threads are waiting for a connection, when a connection is returned, the pool will awake the correct thread waiting. Most pools will simply starve.
4. Connections can be retrieved from a java.sql.Driver, javax.sql.DataSource or javax.sql.XADataSource This is achieved using the dataSource and dataSourceJNDI attributes.
5. XA connection support
6. Close connections after they have been connected for a certain time. Age based close upon return to the pool.

How to use org.apache.tomcat.jdbc.pool through context.xml

<Resource name="jdbc/javausecasedb" 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"
	username="root" password="password" driverClassName="com.mysql.jdbc.Driver"
	url="jdbc:mysql://localhost:3306/javausecasedb" />

How to use org.apache.tomcat.jdbc.pool through Java code

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

PoolProperties p = new PoolProperties();
p.setUrl("jdbc:mysql://localhost:3306/javausecase_db");
p.setDriverClassName("com.mysql.jdbc.Driver");
p.setUsername("root");
p.setPassword("password");
DataSource datasource = new DataSource();
datasource.setPoolProperties(p);

Connection conn = datasource.getConnection();

How to use XADataSource org.apache.tomcat.jdbc.pool through context.xml

<Resource factory="org.apache.tomcat.jdbc.naming.GenericNamingResourcesFactory"
	name="jdbc/xajavausecasedb" type="com.mysql.cj.jdbc.MysqlXADataSource"
	databaseName="javausecase_db" serverName="localhost" portNumber="3306"
	user="root" password="password" />

<Resource factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
	dataSourceJNDI="xajavausecasedb" name="jdbc/xajavausecasedb" auth="Container"
	type="javax.sql.XADataSource" testWhileIdle="true" testOnBorrow="true"
	testOnReturn="false" validationQuery="SELECT 1" validationInterval="30000"
	timeBetweenEvictionRunsMillis="5000" maxActive="100" minIdle="10"
	maxIdle="20" maxWait="10000" initialSize="10" removeAbandonedTimeout="60"
	removeAbandoned="true" logAbandoned="true" minEvictableIdleTimeMillis="30000"
	jmxEnabled="true"
	jdbcInterceptors="ConnectionState;StatementFinalizer;SlowQueryReportJmx(threshold=10000)"
	abandonWhenPercentageFull="75" />

import javax.sql.XAConnection;
import javax.sql.XADataSource;
.....
@Resource(name = "jdbc/xajavausecasedb")
private XADataSource ds;
XAConnection conn = ds.getXAConnection();

COMMON ERRORS

Check type class

java.sql.SQLException: com.mysql.cj.jdbc.MysqlXADataSource cannot be cast to java.sql.Driver

by , on August 27th, 2017

  • Categories