If you get "Last packet sent to the server was XX ms ago." error in your application that want to access MySQL database or you want to know, how to create a connection pool using Tomcat server and DataSource then this is your post.Connection pooling with MySQLUsing connection pool is a good way to increase your web application speed. If you create a connection pool in your application, using a
MySQl server, in the first morning you will see, the database server drops all of the connections in the pool.
This is because
MySQL drops connections after 8 hours idle period. There are many solution in the web, some are working, some aren't working, but the most of them are hacking.
I present an elegant solution to this typically problem, caused by
MySQL server.
The best solution - Commons DBCP
Download Commons DBCPThe
Apache Commons project has a very useful connection pool implementation, called
Commons DBCP.
To use
Commons DBCP,
- download the required jar file from the project page (DBCP Download),
- copy it into the lib directory in your Tomcat server and add to your project, where you want to use the connection pool.
Configuring datasource - Tomcat server, server.xmlIn my solution, the datasource is defined as a global resource in the
server.xml of the Tomcat. To do this, edit the
server.xml file in the
conf directory in your Tomcat server and add some code line like this between the
<GlobalNamingResources> </GlobalNamingResources> tags:
<resource name="jdbc/sampleDB" auth="Container" type="javax.sql.DataSource" maxActive="15" maxidle="10" maxwait="10000" timeBetweenEvictionRunsMillis="10000" minEvictableIdleTimeMillis="60000" factory="org.apache.commons.dbcp.BasicDataSourceFactory" username="XXXXXXXX" password="XXXXXXX" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost/sampledatabase" testOnBorrow="true" testOnReturn="true" validationQuery="select count(*) from TABLENAME" />
Parameters descriptions are availables
here, in nutshell:
name - The datasource will be registred with this name in the naming context.
auth - The authentication type of the resource. Set to Container.
type - The resource type. Set to
javax.sql.DataSource to create a datasource.
maxAcive - The maximum number of active connections that can be allocated from this pool at the same time, or negative for no limit.
maxIdele - The maximum number of connections that can remain idle in the pool, without extra ones being released, or negative for no limit.
maxWait - The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception, or -1 to wait indefinitely.
timeBetweenEvictionRunsMillis - The number of milliseconds to sleep between runs of the idle object evictor thread. When non-positive, no idle object evictor thread will be run.
minEvictableIdleTimeMillis - The minimum amount of time an object may sit idle in the pool before it is eligable for eviction by the idle object evictor (if any).
factory - The factory, that provide the connections, to use
Commons DBCP, use
org.apache.commons.dbcp.BasicDataSourceFactory.
userName - The user name to access your database.
password - The password to access your database.
driverClassName - The driver class to access the database, in this case
com.mysql.jdbc.Driver. The driver file must be copied into the
lib directory of the Tomcat server.
url - The database url.
testOnBorrow - 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. Set to true.
testOnReturn - The indication of whether objects will be validated before being returned to the pool. Set to true.
validationQuery - The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query must be an SQL SELECT statement that returns at least one row.
Configuring datasource - Web application, context.xmlTo access the defined global resource, modify the
context.xml file in your web application. Open the
context.xml with your favorite editor and put the following code between the
<Context> </Context> tags:
<ResourceLink global="jdbc/sampleDB" name="jdbc/sampleDB" type="javax.sql.DataSource"/>
The
global parameter is the global name of the defined resource in the
server.xml, the
name parameter is the name of the resource in our context, type is like in
server.xml, the type of the resource, in this case
javax.sql.DataSource.
Getting a connection from the poolWe're ready to get a connection from the pool and use it in a Hibernate transaction. The code:
...
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.sql.Connection;
...
public void someMethod() throws Exception {
//Getting the initial context.
Context initCtx = new InitialContext();
//Getting the env context from the initial context,
// where the resources are defined.
Context envCtx = (Context) initCtx.lookup("java:comp/env");
//Getting the datasource from the env context.
//The lookup parameter must be the name parameter
// of the ResourceLink tag in the application's context,xml.
DataSource dataSource = (DataSource) envCtx.lookup("jdbc/sampleDB");
//Getting a connection from the datasource.
Connection connection = dataSource.getConnection();
//sessionFactory must be set before use here, setting
// sessiobFactory is depend on the type of Hibernate
// type and version.
try {
Session session = sessionFactory.openSession(connection);
...
//Use the session
...
} catch(Exception ex) {
//Very important exception handling methods comes here
...
} finally {
//finally statement is very important if you use connection
// from datasource. if you don't use then the connection
// can be stuck and it will be not more available.
try {
//If you use datasource, closing the sessions are as important as
// opeining them.
session.close();
} catch(Excedption ex) { ... }
}
...
TipsYou must restart your Tomcat server after you copy something into lib directory or edit the server.xml file.
