[+/-]
This section describes how to use Connector/J in several contexts.
This section provides general background on J2EE concepts that pertain to use of Connector/J.
Connection pooling is a technique of creating and managing a pool of connections that are ready for use by any thread that needs them.
This technique of pooling connections is based on the fact that most applications only need a thread to have access to a JDBC connection when they are actively processing a transaction, which usually take only milliseconds to complete. When not processing a transaction, the connection would otherwise sit idle. Instead, connection pooling allows the idle connection to be used by some other thread to do useful work.
In practice, when a thread needs to do work against a MySQL or other database with JDBC, it requests a connection from the pool. When the thread is finished using the connection, it returns it to the pool, so that it may be used by any other threads that want to use it.
When the connection is loaned out from the pool, it is used
exclusively by the thread that requested it. From a
programming point of view, it is the same as if your thread
called DriverManager.getConnection()
every time it needed a JDBC connection, however with
connection pooling, your thread may end up using either a
new, or already-existing connection.
Connection pooling can greatly increase the performance of your Java application, while reducing overall resource usage. The main benefits to connection pooling are:
Reduced connection creation time
Although this is not usually an issue with the quick connection setup that MySQL offers compared to other databases, creating new JDBC connections still incurs networking and JDBC driver overhead that will be avoided if connections are recycled.
Simplified programming model
When using connection pooling, each individual thread can act as though it has created its own JDBC connection, allowing you to use straight-forward JDBC programming techniques.
Controlled resource usage
If you do not use connection pooling, and instead create a new connection every time a thread needs one, your application's resource usage can be quite wasteful and lead to unpredictable behavior under load.
Remember that each connection to MySQL has overhead (memory, CPU, context switches, and so forth) on both the client and server side. Every connection limits how many resources there are available to your application as well as the MySQL server. Many of these resources will be used whether or not the connection is actually doing any useful work!
Connection pools can be tuned to maximize performance, while keeping resource utilization below the point where your application will start to fail rather than just run slower.
Luckily, Sun has standardized the concept of connection pooling in JDBC through the JDBC-2.0 Optional interfaces, and all major application servers have implementations of these APIs that work fine with MySQL Connector/J.
Generally, you configure a connection pool in your application server configuration files, and access it via the Java Naming and Directory Interface (JNDI). The following code shows how you might use a connection pool from an application deployed in a J2EE application server:
Example 21.11. Connector/J: Using a connection pool with a J2EE application server
import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; import javax.naming.InitialContext; import javax.sql.DataSource; public class MyServletJspOrEjb { public void doSomething() throws Exception { /* * Create a JNDI Initial context to be able to * lookup the DataSource * * In production-level code, this should be cached as * an instance or static variable, as it can * be quite expensive to create a JNDI context. * * Note: This code only works when you are using servlets * or EJBs in a J2EE application server. If you are * using connection pooling in standalone Java code, you * will have to create/configure datasources using whatever * mechanisms your particular connection pooling library * provides. */ InitialContext ctx = new InitialContext(); /* * Lookup the DataSource, which will be backed by a pool * that the application server provides. DataSource instances * are also a good candidate for caching as an instance * variable, as JNDI lookups can be expensive as well. */ DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/MySQLDB"); /* * The following code is what would actually be in your * Servlet, JSP or EJB 'service' method...where you need * to work with a JDBC connection. */ Connection conn = null; Statement stmt = null; try { conn = ds.getConnection(); /* * Now, use normal JDBC programming to work with * MySQL, making sure to close each resource when you're * finished with it, which allows the connection pool * resources to be recovered as quickly as possible */ stmt = conn.createStatement(); stmt.execute("SOME SQL QUERY"); stmt.close(); stmt = null; conn.close(); conn = null; } finally { /* * close any jdbc instances here that weren't * explicitly closed during normal code path, so * that we don't 'leak' resources... */ if (stmt != null) { try { stmt.close(); } catch (sqlexception sqlex) { // ignore -- as we can't do anything about it here } stmt = null; } if (conn != null) { try { conn.close(); } catch (sqlexception sqlex) { // ignore -- as we can't do anything about it here } conn = null; } } } }
As shown in the example above, after obtaining the JNDI InitialContext, and looking up the DataSource, the rest of the code should look familiar to anyone who has done JDBC programming in the past.
The most important thing to remember when using connection pooling is to make sure that no matter what happens in your code (exceptions, flow-of-control, and so forth), connections, and anything created by them (such as statements or result sets) are closed, so that they may be re-used, otherwise they will be stranded, which in the best case means that the MySQL server resources they represent (such as buffers, locks, or sockets) may be tied up for some time, or worst case, may be tied up forever.
What Is the Best Size for my Connection Pool?
As with all other configuration rules-of-thumb, the answer is: it depends. Although the optimal size depends on anticipated load and average database transaction time, the optimum connection pool size is smaller than you might expect. If you take Sun's Java Petstore blueprint application for example, a connection pool of 15-20 connections can serve a relatively moderate load (600 concurrent users) using MySQL and Tomcat with response times that are acceptable.
To correctly size a connection pool for your application, you should create load test scripts with tools such as Apache JMeter or The Grinder, and load test your application.
An easy way to determine a starting point is to configure your connection pool's maximum number of connections to be unbounded, run a load test, and measure the largest amount of concurrently used connections. You can then work backward from there to determine what values of minimum and maximum pooled connections give the best performance for your particular application.
The following instructions are based on the instructions for Tomcat-5.x, available at http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html which is current at the time this document was written.
First, install the .jar file that comes with Connector/J in
$CATALINA_HOME/common/lib
so that it is
available to all applications installed in the container.
Next, Configure the JNDI DataSource by adding a declaration
resource to
$CATALINA_HOME/conf/server.xml
in the
context that defines your web application:
<Context ....> ... <Resource name="jdbc/MySQLDB" auth="Container" type="javax.sql.DataSource"/> <!-- The name you used above, must match _exactly_ here! The connection pool will be bound into JNDI with the name "java:/comp/env/jdbc/MySQLDB" --> <ResourceParams name="jdbc/MySQLDB"> <parameter> <name>factory</name> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value> </parameter> <!-- Don't set this any higher than max_connections on your MySQL server, usually this should be a 10 or a few 10's of connections, not hundreds or thousands --> <parameter> <name>maxActive</name> <value>10</value> </parameter> <!-- You don't want to many idle connections hanging around if you can avoid it, only enough to soak up a spike in the load --> <parameter> <name>maxIdle</name> <value>5</value> </parameter> <!-- Don't use autoReconnect=true, it's going away eventually and it's a crutch for older connection pools that couldn't test connections. You need to decide whether your application is supposed to deal with SQLExceptions (hint, it should), and how much of a performance penalty you're willing to pay to ensure 'freshness' of the connection --> <parameter> <name>validationQuery</name> <value>SELECT 1</value> <-- See discussion below for update to this option --> </parameter> <!-- The most conservative approach is to test connections before they're given to your application. For most applications this is okay, the query used above is very small and takes no real server resources to process, other than the time used to traverse the network. If you have a high-load application you'll need to rely on something else. --> <parameter> <name>testOnBorrow</name> <value>true</value> </parameter> <!-- Otherwise, or in addition to testOnBorrow, you can test while connections are sitting idle --> <parameter> <name>testWhileIdle</name> <value>true</value> </parameter> <!-- You have to set this value, otherwise even though you've asked connections to be tested while idle, the idle evicter thread will never run --> <parameter> <name>timeBetweenEvictionRunsMillis</name> <value>10000</value> </parameter> <!-- Don't allow connections to hang out idle too long, never longer than what wait_timeout is set to on the server...A few minutes or even fraction of a minute is sometimes okay here, it depends on your application and how much spikey load it will see --> <parameter> <name>minEvictableIdleTimeMillis</name> <value>60000</value> </parameter> <!-- Username and password used when connecting to MySQL --> <parameter> <name>username</name> <value>someuser</value> </parameter> <parameter> <name>password</name> <value>somepass</value> </parameter> <!-- Class name for the Connector/J driver --> <parameter> <name>driverClassName</name> <value>com.mysql.jdbc.Driver</value> </parameter> <!-- The JDBC connection url for connecting to MySQL, notice that if you want to pass any other MySQL-specific parameters you should pass them here in the URL, setting them using the parameter tags above will have no effect, you will also need to use & to separate parameter values as the ampersand is a reserved character in XML --> <parameter> <name>url</name> <value>jdbc:mysql://localhost:3306/test</value> </parameter> </ResourceParams> </Context>
Note that Connector/J 5.1.3 introduced a facility whereby,
rather than use a validationQuery
value of
SELECT 1
, it is possible to use
validationQuery
with a value set to
/* ping */
. This sends a ping to the server
which then returns a fake result set. This is a lighter weight
solution. It also has the advantage that if using
ReplicationConnection
or
LoadBalancedConnection
type connections,
the ping will be sent across all active connections. The
following XML snippet illustrates how to select this option:
<parameter> <name>validationQuery</name> <value>/* ping */</value> </parameter>
Note that /* ping */
has to be specified
exactly.
In general, you should follow the installation instructions that come with your version of Tomcat, as the way you configure datasources in Tomcat changes from time-to-time, and unfortunately if you use the wrong syntax in your XML file, you will most likely end up with an exception similar to the following:
Error: java.sql.SQLException: Cannot load JDBC driver class 'null ' SQL state: null
These instructions cover JBoss-4.x. To make the JDBC driver
classes available to the application server, copy the .jar
file that comes with Connector/J to the
lib
directory for your server
configuration (which is usually called
default
). Then, in the same configuration
directory, in the subdirectory named deploy, create a
datasource configuration file that ends with "-ds.xml", which
tells JBoss to deploy this file as a JDBC Datasource. The file
should have the following contents:
<datasources> <local-tx-datasource> <!-- This connection pool will be bound into JNDI with the name "java:/MySQLDB" --> <jndi-name>MySQLDB</jndi-name> <connection-url>jdbc:mysql://localhost:3306/dbname</connection-url> <driver-class>com.mysql.jdbc.Driver</driver-class> <user-name>user</user-name> <password>pass</password> <min-pool-size>5</min-pool-size> <!-- Don't set this any higher than max_connections on your MySQL server, usually this should be a 10 or a few 10's of connections, not hundreds or thousands --> <max-pool-size>20</max-pool-size> <!-- Don't allow connections to hang out idle too long, never longer than what wait_timeout is set to on the server...A few minutes is usually okay here, it depends on your application and how much spikey load it will see --> <idle-timeout-minutes>5</idle-timeout-minutes> <!-- If you're using Connector/J 3.1.8 or newer, you can use our implementation of these to increase the robustness of the connection pool. --> <exception-sorter-class-name> com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter </exception-sorter-class-name> <valid-connection-checker-class-name> com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker </valid-connection-checker-class-name> </local-tx-datasource> </datasources>
[+/-]
The Spring Framework is a Java-based application framework designed for assisting in application design by providing a way to configure components. The technique used by Spring is a well known design pattern called Dependency Injection (see Inversion of Control Containers and the Dependency Injection pattern). This article will focus on Java-oriented access to MySQL databases with Spring 2.0. For those wondering, there is a .NET port of Spring appropriately named Spring.NET.
Spring is not only a system for configuring components, but also includes support for aspect oriented programming (AOP). This is one of the main benefits and the foundation for Spring's resource and transaction management. Spring also provides utilities for integrating resource management with JDBC and Hibernate.
For the examples in this section the MySQL world sample database will be used. The first task is to set up a MySQL data source through Spring. Components within Spring use the "bean" terminology. For example, to configure a connection to a MySQL server supporting the world sample database you might use:
<util:map id="dbProps"> <entry key="db.driver" value="com.mysql.jdbc.Driver"/> <entry key="db.jdbcurl" value="jdbc:mysql://localhost/world"/> <entry key="db.username" value="myuser"/> <entry key="db.password" value="mypass"/> </util:map>
In the above example we are assigning values to properties that will be used in the configuration. For the datasource configuration:
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="${db.driver}"/> <property name="url" value="${db.jdbcurl}"/> <property name="username" value="${db.username}"/> <property name="password" value="${db.password}"/> </bean>
The placeholders are used to provide values for properties of this bean. This means that you can specify all the properties of the configuration in one place instead of entering the values for each property on each bean. We do, however, need one more bean to pull this all together. The last bean is responsible for actually replacing the placeholders with the property values.
<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="properties" ref="dbProps"/> </bean>
Now that we have our MySQL data source configured and ready to go, we write some Java code to access it. The example below will retrieve three random cities and their corresponding country using the data source we configured with Spring.
// Create a new application context. this processes the Spring config ApplicationContext ctx = new ClassPathXmlApplicationContext("ex1appContext.xml"); // Retrieve the data source from the application context DataSource ds = (DataSource) ctx.getBean("dataSource"); // Open a database connection using Spring's DataSourceUtils Connection c = DataSourceUtils.getConnection(ds); try { // retrieve a list of three random cities PreparedStatement ps = c.prepareStatement( "select City.Name as 'City', Country.Name as 'Country' " + "from City inner join Country on City.CountryCode = Country.Code " + "order by rand() limit 3"); ResultSet rs = ps.executeQuery(); while(rs.next()) { String city = rs.getString("City"); String country = rs.getString("Country"); System.out.printf("The city %s is in %s%n", city, country); } } catch (SQLException ex) { // something has failed and we print a stack trace to analyse the error ex.printStackTrace(); // ignore failure closing connection try { c.close(); } catch (SQLException e) { } } finally { // properly release our connection DataSourceUtils.releaseConnection(c, ds); }
This is very similar to normal JDBC access to MySQL with the main difference being that we are using DataSourceUtils instead of the DriverManager to create the connection.
While it may seem like a small difference, the implications are somewhat far reaching. Spring manages this resource in a way similar to a container managed data source in a J2EE application server. When a connection is opened, it can be subsequently accessed in other parts of the code if it is synchronized with a transaction. This makes it possible to treat different parts of your application as transactional instead of passing around a database connection.
Spring makes extensive use of the Template method design
pattern (see
Template
Method Pattern). Our immediate focus will be on the
JdbcTemplate
and related classes,
specifically NamedParameterJdbcTemplate
.
The template classes handle obtaining and releasing a
connection for data access when one is needed.
The next example shows how to use
NamedParameterJdbcTemplate
inside of a
DAO (Data Access Object) class to retrieve a random city
given a country code.
public class Ex2JdbcDao { /** * Data source reference which will be provided by Spring. */ private DataSource dataSource; /** * Our query to find a random city given a country code. Notice * the ":country" parameter towards the end. This is called a * named parameter. */ private String queryString = "select Name from City " + "where CountryCode = :country order by rand() limit 1"; /** * Retrieve a random city using Spring JDBC access classes. */ public String getRandomCityByCountryCode(String cntryCode) { // A template that allows using queries with named parameters NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource); // A java.util.Map is used to provide values for the parameters Map params = new HashMap(); params.put("country", cntryCode); // We query for an Object and specify what class we are expecting return (String)template.queryForObject(queryString, params, String.class); } /** * A JavaBean setter-style method to allow Spring to inject the data source. * @param dataSource */ public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; } }
The focus in the above code is on the
getRandomCityByCountryCode()
method. We
pass a country code and use the
NamedParameterJdbcTemplate
to query for a
city. The country code is placed in a Map with the key
"country", which is the parameter is named in the SQL query.
To access this code, you need to configure it with Spring by providing a reference to the data source.
<bean id="dao" class="code.Ex2JdbcDao"> <property name="dataSource" ref="dataSource"/> </bean>
At this point, we can just grab a reference to the DAO from
Spring and call
getRandomCityByCountryCode()
.
// Create the application context ApplicationContext ctx = new ClassPathXmlApplicationContext("ex2appContext.xml"); // Obtain a reference to our DAO Ex2JdbcDao dao = (Ex2JdbcDao) ctx.getBean("dao"); String countryCode = "USA"; // Find a few random cities in the US for(int i = 0; i < 4; ++i) System.out.printf("A random city in %s is %s%n", countryCode, dao.getRandomCityByCountryCode(countryCode));
This example shows how to use Spring's JDBC classes to
completely abstract away the use of traditional JDBC classes
including Connection
and
PreparedStatement
.
You might be wondering how we can add transactions into our code if we do not deal directly with the JDBC classes. Spring provides a transaction management package that not only replaces JDBC transaction management, but also allows declarative transaction management (configuration instead of code).
In order to use transactional database access, we will need to change the storage engine of the tables in the world database. The downloaded script explicitly creates MyISAM tables which do not support transactional semantics. The InnoDB storage engine does support transactions and this is what we will be using. We can change the storage engine with the following statements.
ALTER TABLE City ENGINE=InnoDB; ALTER TABLE Country ENGINE=InnoDB; ALTER TABLE CountryLanguage ENGINE=InnoDB;
A good programming practice emphasized by Spring is separating interfaces and implementations. What this means is that we can create a Java interface and only use the operations on this interface without any internal knowledge of what the actual implementation is. We will let Spring manage the implementation and with this it will manage the transactions for our implementation.
First you create a simple interface:
public interface Ex3Dao { Integer createCity(String name, String countryCode, String district, Integer population); }
This interface contains one method that will create a new city record in the database and return the id of the new record. Next you need to create an implementation of this interface.
public class Ex3DaoImpl implements Ex3Dao { protected DataSource dataSource; protected SqlUpdate updateQuery; protected SqlFunction idQuery; public Integer createCity(String name, String countryCode, String district, Integer population) { updateQuery.update(new Object[] { name, countryCode, district, population }); return getLastId(); } protected Integer getLastId() { return idQuery.run(); } }
You can see that we only operate on abstract query objects here and do not deal directly with the JDBC API. Also, this is the complete implementation. All of our transaction management will be dealt with in the configuration. To get the configuration started, we need to create the DAO.
<bean id="dao" class="code.Ex3DaoImpl"> <property name="dataSource" ref="dataSource"/> <property name="updateQuery">...</property> <property name="idQuery">...</property> </bean>
Now you need to set up the transaction configuration. The
first thing you must do is create transaction manager to
manage the data source and a specification of what
transaction properties are required for the
dao
methods.
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource"/> </bean> <tx:advice id="txAdvice" transaction-manager="transactionManager"> <tx:attributes> <tx:method name="*"/> </tx:attributes> </tx:advice>
The preceding code creates a transaction manager that
handles transactions for the data source provided to it. The
txAdvice
uses this transaction manager
and the attributes specify to create a transaction for all
methods. Finally you need to apply this advice with an AOP
pointcut.
<aop:config> <aop:pointcut id="daoMethods" expression="execution(* code.Ex3Dao.*(..))"/> <aop:advisor advice-ref="txAdvice" pointcut-ref="daoMethods"/> </aop:config>
This basically says that all methods called on the
Ex3Dao
interface will be wrapped in a
transaction. To make use of this, you only have to retrieve
the dao
from the application context and
call a method on the dao
instance.
Ex3Dao dao = (Ex3Dao) ctx.getBean("dao"); Integer id = dao.createCity(name, countryCode, district, pop);
We can verify from this that there is no transaction management happening in our Java code and it is all configured with Spring. This is a very powerful notion and regarded as one of the most beneficial features of Spring.
In many sitations, such as web applications, there will be a
large number of small database transactions. When this is
the case, it usually makes sense to create a pool of
database connections available for web requests as needed.
Although MySQL does not spawn an extra process when a
connection is made, there is still a small amount of
overhead to create and set up the connection. Pooling of
connections also alleviates problems such as collecting
large amounts of sockets in the TIME_WAIT
state.
Setting up pooling of MySQL connections with Spring is as
simple as changing the data source configuration in the
application context. There are a number of configurations
that we can use. The first example is based on the
Jakarta
Commons DBCP library. The example below replaces the
source configuration that was based on
DriverManagerDataSource
with DBCP's
BasicDataSource.
<bean id="dataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource"> <property name="driverClassName" value="${db.driver}"/> <property name="url" value="${db.jdbcurl}"/> <property name="username" value="${db.username}"/> <property name="password" value="${db.password}"/> <property name="initialSize" value="3"/> </bean>
The configuration of the two solutions is very similar. The
difference is that DBCP will pool connections to the
database instead of creating a new connection every time one
is requested. We have also set a parameter here called
initialSize
. This tells DBCP that we want
three connections in the pool when it is created.
Another way to configure connection pooling is to configure
a data source in our J2EE application server. Using JBoss as
an example, you can set up the MySQL connection pool by
creating a file called
mysql-local-ds.xml
and placing it in
the server/default/deploy directory in JBoss. Once we have
this setup, we can use JNDI to look it up. With Spring, this
lookup is very simple. The data source configuration looks
like this.
<jee:jndi-lookup id="dataSource" jndi-name="java:MySQL_DS"/>
User Comments
the above syntax is only good for tomcat 5.0.
tomcat 5.5 has different syntax than tomcat 5.0 for defining a jdbc connection pool (also called a datasource).
tomcat 5.5 has all of the parameters in the resource element:
<Resource name="jdbc/MySQLDB" auth="Container" driverClassName="com.mysql.jdbc.Driver" maxActive="10" maxIdle="1" maxWait="500" type="javax.sql.DataSource" url="jdbc:mysql://localhost:3306/test" username="username" password="password" />
In other words, the <ResourceParams> element is no longer used in tomcat 5.5.
Some of the parameters shown for Using Connector/J with Tomcat don't appear to be Tomcat parameters. I mean things like timeBetweenEvictionRunsMillis.
They appear to be Java Commons Pooling values. None of the other documentation on connection pooling in TOmcat show those values.
They do show params like MaxActive, MaxIdle.
THis needs clarification, especially as MySQL should be the expert on its own connection pooling.
Terry
Wasted several hours because the attribute 'username' is unknown - use 'user' with MysqlDatasource instead.
Look here as well:
http://dev.mysql.com/doc/refman/5.0/en/connector-j-installing-classpath.html
the tomcat resource requires no url attribute, rather it takes port, serverName and databaseName
How about an example using Sun's Glassfish server?
Also, it'd be nice to see examples combining Hibernate's XML-based configuration files (non-JPA) with JNDI lookups of the data source and connection pool to create Session Factories within Glassfish.
I am setting up connector-j with tomcat pooling and found this document handy but not complete. If you are using Apache Commons DBCP or Tomcat JDBC I also recommend reading Vigil Bose's blog article at: http://vigilbose.blogspot.com/2009/03/apache-commons-dbcp-and-tomcat-jdbc.html
I found the following context.xml settings worked well for my low traffic application:
<?xml version="1.0" encoding="UTF-8"?>
<Context antiJARLocking="true" path="/storageAPI">
<Resource name="jdbc/storage"
auth="Container"
type="javax.sql.DataSource"
driverClassName="com.mysql.jdbc.Driver"
maxActive="20"
maxIdle="2"
maxWait="10000"
validationQuery="/* ping */"
testWhileIdle="true"
timeBetweenEvictionRunsMillis="1800000"
minEvictableIdleTimeMillis="21600000"
password="password"
url="jdbc:mysql://localhost:3306/storage"
username="username"/>
</Context>
Add your own comment.