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 enables 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 through 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 22.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 permits 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.
Validating Connections
MySQL Connector/J has the ability to execute a lightweight ping against a server, in order to validate the connection. In the case of load-balanced connections, this is performed against all active pooled internal connections that are retained. This is beneficial to Java applications using connection pools, as the pool can use this feature to validate connections. Depending on your connection pool and configuration, this validation can be carried out at different times:
Before the pool returns a connection to the application.
When the application returns a connection to the pool.
During periodic checks of idle connections.
In order to use this feature you need to specify a
validation query in your connection pool that starts with
/* ping */
. Note the syntax must be
exactly as specified. This will cause the driver send a ping
to the server and return a fake, light-weight, result set.
When using a ReplicationConnection
or
LoadBalancedConnection
, the ping will be
sent across all active connections.
It is critical that the syntax be specified correctly. For example, consider the following snippets:
sql = "/* PING */ SELECT 1"; sql = "SELECT 1 /* ping*/"; sql = "/*ping*/ SELECT 1"; sql = " /* ping */ SELECT 1"; sql = "/*to ping or not to ping*/ SELECT 1";
None of the above statements will work. This is because the ping syntax is sensitive to whitespace, capitalization, and placement. The syntax needs to be exact for reasons of efficiency, as this test is done for every statement that is executed:
protected static final String PING_MARKER = "/* ping */"; ... if (sql.charAt(0) == '/') { if (sql.startsWith(PING_MARKER)) { doPingInstead(); ...
All of the previous statements will issue a normal
SELECT
statement and will
not be transformed into the
lightweight ping. Further, for load-balanced connections the
statement will be executed against one connection in the
internal pool, rather than validating each underlying
physical connection. This results in the non-active physical
connections assuming a stale state, and they may die. If
Connector/J then re-balances it may select a dead
connection, resulting in an exception being passed to the
application. To help prevent this you can use
loadBalanceValidateConnectionOnSwapServer
to validate the connection before use.
If your Connector/J deployment uses a connection pool that
allows you to specify a validation query, this should be
taken advantage of, but ensure that the query starts
exactly with /* ping
*/
. This is particularly important if you are
using the load-balancing or replication-aware features of
Connector/J, as it will help keep alive connections which
otherwise will go stale and die, causing problems later.
Connector/J has long provided an effective means to distribute read/write load across multiple MySQL server instances for Cluster or master-master replication deployments, but until version 5.1.13, managing such deployments frequently required a service outage to redeploy a new configuration. Given that the ease of scaling out by adding additional MySQL Cluster (server) instances is a key element in that product offering, which is also naturally targeted at deployments with very strict availability requirements, it was necessary to add support for online changes of this nature. This is also critical for online upgrades, as the alternative is to take a MySQL Cluster server instance down hard, which will lose any in-process transactions and will also generate application exceptions, if any application is trying to use that particular server instance. Connector/J now has the ability to dynamically configure load-balanced connections.
There are two connection string options associated with this functionality:
loadBalanceConnectionGroup
– This provides the ability to group connections from different sources. This allows you to manage these JDBC sources within a single class-loader in any combination you choose. If they use the same configuration, and you want to manage them as a logical single group, give them the same name. This is the key property for management, if you do not define a name (string) forloadBalanceConnectionGroup
, you cannot manage the connections. All load-balanced connections sharing the sameloadBalanceConnectionGroup
value, regardless of how the application creates them, will be managed together.loadBalanceEnableJMX
– The ability to manage the connections is exposed when you define aloadBalanceConnectionGroup
, but if you want to manage this externally, it is necessary to enable JMX by setting this property totrue
. This enables a JMX implementation, which exposes the management and monitoring operations of a connection group. Further, you need to start your application with the-Dcom.sun.management.jmxremote
JVM flag. You can then perform connect and perform operations using a JMX client such asjconsole
.
Once a connection has been made using the correct connection string options, a number of monitoring properties are available:
Current active host count
Current active physical connection count
Current active logical connection count
Total logical connections created
Total transaction count
The following management operations can also be performed:
Add host
Remove host
The JMX interface,
com.mysql.jdbc.jmx.LoadBalanceConnectionGroupManagerMBean
,
has the following methods:
int getActiveHostCount(String group);
int getTotalHostCount(String group);
long getTotalLogicalConnectionCount(String group);
long getActiveLogicalConnectionCount(String group);
long getActivePhysicalConnectionCount(String group);
long getTotalPhysicalConnectionCount(String group);
long getTotalTransactionCount(String group);
void removeHost(String group, String host) throws SQLException;
void stopNewConnectionsToHost(String group, String host) throws SQLException;
void addHost(String group, String host, boolean forExisting);
String getActiveHostsList(String group);
String getRegisteredConnectionGroups();
The getRegisteredConnectionGroups()
method will return the names of all connection groups
defined in that class-loader.
You can test this setup with the following code:
public class Test { private static String URL = "jdbc:mysql:loadbalance://" + "localhost:3306,localhost:3310/test?" + "loadBalanceConnectionGroup=first&loadBalanceEnableJMX=true"; public static void main(String[] args) throws Exception { new Thread(new Repeater()).start(); new Thread(new Repeater()).start(); new Thread(new Repeater()).start(); } static Connection getNewConnection() throws SQLException, ClassNotFoundException { Class.forName("com.mysql.jdbc.Driver"); return DriverManager.getConnection(URL, "root", ""); } static void executeSimpleTransaction(Connection c, int conn, int trans){ try { c.setAutoCommit(false); Statement s = c.createStatement(); s.executeQuery("SELECT SLEEP(1) /* Connection: " + conn + ", transaction: " + trans + " */"); c.commit(); } catch (SQLException e) { e.printStackTrace(); } } public static class Repeater implements Runnable { public void run() { for(int i=0; i < 100; i++){ try { Connection c = getNewConnection(); for(int j=0; j < 10; j++){ executeSimpleTransaction(c, i, j); Thread.sleep(Math.round(100 * Math.random())); } c.close(); Thread.sleep(100); } catch (Exception e) { e.printStackTrace(); } } } } }
After compiling, the application can be started with the
-Dcom.sun.management.jmxremote
flag, to
enable remote management. jconsole
can
then be started. The Test main class will be listed by
jconsole. Select this and click
. You can then navigate to the
com.mysql.jdbc.jmx.LoadBalanceConnectionGroupManager
bean. At this point you can click on various operations and
examine the returned result.
If you now had an additional instance of MySQL running on
port 3309, you could ensure that Connector/J starts using it
by using the addHost()
, which is exposed
in jconsole
. Note that these operations
can be performed dynamically without having to stop the
application running.
Connector/J provides a useful load-balancing implementation
for Cluster or multi-master deployments. As of Connector/J
5.1.12, this same implementation is used for balancing load
between read-only slaves with
ReplicationDriver
. When trying to balance
workload between multiple servers, the driver has to
determine when it is safe to swap servers, doing so in the
middle of a transaction, for example, could cause problems.
It is important not to lose state information. For this
reason, Connector/J will only try to pick a new server when
one of the following happens:
At transaction boundaries (transactions are explicitly committed or rolled back).
A communication exception (SQL State starting with "08") is encountered.
When a
SQLException
matches conditions defined by user, using the extension points defined by theloadBalanceSQLStateFailover
,loadBalanceSQLExceptionSubclassFailover
orloadBalanceExceptionChecker
properties.
The third condition revolves around three new properties
introduced with Connector/J 5.1.13. It allows you to control
which SQLException
s trigger failover.
loadBalanceExceptionChecker
- TheloadBalanceExceptionChecker
property is really the key. This takes a fully-qualified class name which implements the newcom.mysql.jdbc.LoadBalanceExceptionChecker
interface. This interface is very simple, and you only need to implement the following method:public boolean shouldExceptionTriggerFailover(SQLException ex)
A
SQLException
is passed in, and a boolean returned.True
triggers a failover,false
does not.You can use this to implement your own custom logic. An example where this might be useful is when dealing with transient errors when using MySQL Cluster, where certain buffers may become overloaded. The following code snippet illustrates this:
public class NdbLoadBalanceExceptionChecker extends StandardLoadBalanceExceptionChecker { public boolean shouldExceptionTriggerFailover(SQLException ex) { return super.shouldExceptionTriggerFailover(ex) || checkNdbException(ex); } private boolean checkNdbException(SQLException ex){ // Have to parse the message since most NDB errors // are mapped to the same DEMC. return (ex.getMessage().startsWith("Lock wait timeout exceeded") || (ex.getMessage().startsWith("Got temporary error") && ex.getMessage().endsWith("from NDB"))); } }
The code above extends
com.mysql.jdbc.StandardLoadBalanceExceptionChecker
, which is the default implementation. There are a few convenient shortcuts built into this, for those who want to have some level of control using properties, without writing Java code. This default implementation uses the two remaining properties:loadBalanceSQLStateFailover
andloadBalanceSQLExceptionSubclassFailover
.loadBalanceSQLStateFailover
- allows you to define a comma-delimited list ofSQLState
code prefixes, against which aSQLException
is compared. If the prefix matches, failover is triggered. So, for example, the following would trigger a failover if a givenSQLException
starts with "00", or is "12345":loadBalanceSQLStateFailover=00,12345
loadBalanceSQLExceptionSubclassFailover
- can be used in conjunction withloadBalanceSQLStateFailover
or on its own. If you want certain subclasses ofSQLException
to trigger failover, simply provide a comma-delimited list of fully-qualified class or interface names to check against. For example, if you want allSQLTransientConnectionExceptions
to trigger failover, you would specify:loadBalanceSQLExceptionSubclassFailover=java.sql.SQLTransientConnectionException
While the three fail-over conditions enumerated earlier suit
most situations, if auto-commit
is
enabled, Connector/J never re-balances, and continues using
the same physical connection. This can be problematic,
particularly when load-balancing is being used to distribute
read-only load across multiple slaves. However, Connector/J
can be configured to re-balance after a certain number of
statements are executed, when auto-commit
is enabled. This functionality is dependent upon the
following properties:
loadBalanceAutoCommitStatementThreshold
– defines the number of matching statements which will trigger the driver to potentially swap physical server connections. The default value, 0, retains the behavior that connections withauto-commit
enabled are never balanced.loadBalanceAutoCommitStatementRegex
– the regular expression against which statements must match. The default value, blank, matches all statements. So, for example, using the following properties will cause Connector/J to re-balance after every third statement that contains the string “test”:loadBalanceAutoCommitStatementThreshold=3 loadBalanceAutoCommitStatementRegex=.*test.*
loadBalanceAutoCommitStatementRegex
can prove useful in a number of situations. Your application may use temporary tables, server-side session state variables, or connection state, where letting the driver arbitrarily swap physical connections before processing is complete could cause data loss or other problems. This allows you to identify a trigger statement that is only executed when it is safe to swap physical connections.
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 toward 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 permits 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 enables declarative transaction management (configuration instead of code).
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"/>
This section explains how to use MySQL Connector/J with Glassfish ™ Server Open Source Edition 3.0.1. Glassfish can be downloaded from the Glassfish website.
Once Glassfish is installed you will need to make sure it can
access MySQL Connector/J. To do this copy the MySQL Connector/J JAR file to the
directory
.
For example, copy
GLASSFISH_INSTALL
/glassfish/libmysql-connector-java-5.1.12-bin.jar
to
C:\glassfishv3\glassfish\lib
. Restart the
Glassfish Application Server.
You are now ready to create JDBC Connection Pools and JDBC Resources.
Creating a Connection Pool
In the Glassfish Administration Console, using the navigation tree navigate to Resources, JDBC, Connection Pools.
In the JDBC Connection Pools frame click . You will enter a two step wizard.
In the Name field under General Settings enter the name for the connection pool, for example enter
MySQLConnPool
.In the Resource Type field, select
javax.sql.DataSource
from the drop-down listbox.In the Database Vendor field, select
MySQL
from the drop-down listbox. Click to go to the next page of the wizard.You can accept the default settings for General Settings, Pool Settings and Transactions for this example. Scroll down to Additional Properties.
In Additional Properties you will need to ensure the following properties are set:
ServerName - The server you wish to connect to. For local testing this will be
localhost
.User - The user name with which to connect to MySQL.
Password - The corresponding password for the user.
DatabaseName - The database you wish to connect to, for example the sample MySQL database
World
.
Click JDBC Connection Pools page where all current connection pools, including the one you just created, will be displayed.
to exit the wizard. You will be taken to theIn the JDBC Connection Pools frame click on the connection pool you just created. Here you can review and edit information about the connection pool.
To test your connection pool click the
button at the top of the frame. A message will be displayed confirming correct operation or otherwise. If an error message is received recheck the previous steps, and ensure that MySQL Connector/J has been correctly copied into the previously specified location.
Now that you have created a connection pool you will also need to create a JDBC Resource (data source) for use by your application.
Creating a JDBC Resource
Your Java application will usually reference a data source object to establish a connection with the database. This needs to be created first using the following procedure.
Using the navigation tree in the Glassfish Administration Console, navigate to Resources, JDBC, JDBC Resources. A list of resources will be displayed in the JDBC Resources frame.
Click New JDBC Resource frame will be displayed.
. TheIn the JNDI Name field, enter the JNDI name that will be used to access this resource, for example enter
jdbc/MySQLDataSource
.In the Pool Name field, select a connection pool you want this resource to use from the drop-down listbox.
Optionally, you can enter a description into the Description field.
Additional properties can be added if required.
Click JDBC Resources frame will list all available JDBC Resources.
to create the new JDBC resource. The
This section shows how to deploy a simple JSP application on Glassfish, that connects to a MySQL database.
This example assumes you have already set up a suitable
Connection Pool and JDBC Resource, as explained in the
preceding sections. It is also assumed you have a sample
database installed, such as world
.
The main application code, index.jsp
is
presented here:
<%@ page import="java.sql.*, javax.sql.*, java.io.*, javax.naming.*" %> <html> <head><title>Hello world from JSP</title></head> <body> <% InitialContext ctx; DataSource ds; Connection conn; Statement stmt; ResultSet rs; try { ctx = new InitialContext(); ds = (DataSource) ctx.lookup("java:comp/env/jdbc/MySQLDataSource"); //ds = (DataSource) ctx.lookup("jdbc/MySQLDataSource"); conn = ds.getConnection(); stmt = conn.createStatement(); rs = stmt.executeQuery("SELECT * FROM Country"); while(rs.next()) { %> <h3>Name: <%= rs.getString("Name") %></h3> <h3>Population: <%= rs.getString("Population") %></h3> <% } } catch (SQLException se) { %> <%= se.getMessage() %> <% } catch (NamingException ne) { %> <%= ne.getMessage() %> <% } %> </body> </html>
In addition two XML files are required:
web.xml
, and
sun-web.xml
. There may be other files
present, such as classes and images. These files are
organized into the directory structure as follows:
index.jsp WEB-INF | - web.xml - sun-web.xml
The code for web.xml
is:
<?xml version="1.0" encoding="UTF-8"?> <web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"> <display-name>HelloWebApp</display-name> <distributable/> <resource-ref> <res-ref-name>jdbc/MySQLDataSource</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> <res-sharing-scope>Shareable</res-sharing-scope> </resource-ref> </web-app>
The code for sun-web.xml
is:
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sun-web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Application Server 8.1 Servlet 2.4//EN" "http://www.sun.com/software/appserver/dtds/sun-web-app_2_4-1.dtd"> <sun-web-app> <context-root>HelloWebApp</context-root> <resource-ref> <res-ref-name>jdbc/MySQLDataSource</res-ref-name> <jndi-name>jdbc/MySQLDataSource</jndi-name> </resource-ref> </sun-web-app>
These XML files illustrate a very important aspect of
running JDBC applications on Glassfish. On Glassfish it is
important to map the string specified for a JDBC resource to
its JNDI name, as set up in the Glassfish administration
console. In this example, the JNDI name for the JDBC
resource, as specified in the Glassfish Administration
console when creating the JDBC Resource, was
jdbc/MySQLDataSource
. This must be mapped
to the name given in the application. In this example the
name specified in the application,
jdbc/MySQLDataSource
, and the JNDI name,
happen to be the same, but this does not necessarily have to
be the case. Note that the XML element <res-ref-name>
is used to specify the name as used in the application
source code, and this is mapped to the JNDI name specified
using the <jndi-name> element, in the file
sun-web.xml
. The resource also has to
be created in the web.xml
file,
although the mapping of the resource to a JNDI name takes
place in the sun-web.xml
file.
If you do not have this mapping set up correctly in the XML files you will not be able to lookup the data source using a JNDI lookup string such as:
ds = (DataSource) ctx.lookup("java:comp/env/jdbc/MySQLDataSource");
You will still be able to access the data source directly using:
ds = (DataSource) ctx.lookup("jdbc/MySQLDataSource");
With the source files in place, in the correct directory structure, you are ready to deploy the application:
In the navigation tree, navigate to Applications - the Applications frame will be displayed. Click .
You can now deploy an application packaged into a single WAR file from a remote client, or you can choose a packaged file or directory that is locally accessible to the server. If you are simply testing an application locally you can simply ponit Glassfish at the directory that contains your application, without needing to package the application into a WAR file.
Now select the application type from the Type drop-down listbox, which in this example is
Web application
.Click OK.
Now, when you navigate to the
Applications frame, you will have the
option to Launch,
Redeploy, or
Restart your application. You can test
your application by clicking Launch.
The application will connection to the MySQL database and
display the Name and Population of countries in the
Country
table.
This section describes a simple servlet that can be used in
the Glassfish environment to access a MySQL database. As
with the previous section, this example assumes the sample
database world
is installed.
The project is set up with the following directory structure:
index.html WEB-INF | - web.xml - sun-web.xml - classes | - HelloWebServlet.java - HelloWebServlet.class
The code for the servlet, located in
HelloWebServlet.java
, is as follows:
import javax.servlet.http.*; import javax.servlet.*; import java.io.*; import java.sql.*; import javax.sql.*; import javax.naming.*; public class HelloWebServlet extends HttpServlet { InitialContext ctx = null; DataSource ds = null; Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; String sql = "SELECT Name, Population FROM Country WHERE Name=?"; public void init () throws ServletException { try { ctx = new InitialContext(); ds = (DataSource) ctx.lookup("java:comp/env/jdbc/MySQLDataSource"); conn = ds.getConnection(); ps = conn.prepareStatement(sql); } catch (SQLException se) { System.out.println("SQLException: "+se.getMessage()); } catch (NamingException ne) { System.out.println("NamingException: "+ne.getMessage()); } } public void destroy () { try { if (rs != null) rs.close(); if (ps != null) ps.close(); if (conn != null) conn.close(); if (ctx != null) ctx.close(); } catch (SQLException se) { System.out.println("SQLException: "+se.getMessage()); } catch (NamingException ne) { System.out.println("NamingException: "+ne.getMessage()); } } public void doPost(HttpServletRequest req, HttpServletResponse resp){ try { String country_name = req.getParameter("country_name"); resp.setContentType("text/html"); PrintWriter writer = resp.getWriter(); writer.println("<html><body>"); writer.println("<p>Country: "+country_name+"</p>"); ps.setString(1, country_name); rs = ps.executeQuery(); if (!rs.next()){ writer.println("<p>Country does not exist!</p>"); } else { rs.beforeFirst(); while(rs.next()) { writer.println("<p>Name: "+rs.getString("Name")+"</p>"); writer.println("<p>Population: "+rs.getString("Population")+"</p>"); } } writer.println("</body></html>"); writer.close(); } catch (Exception e) { e.printStackTrace(); } } public void doGet(HttpServletRequest req, HttpServletResponse resp){ try { resp.setContentType("text/html"); PrintWriter writer = resp.getWriter(); writer.println("<html><body>"); writer.println("<p>Hello from servlet doGet()</p>"); writer.println("</body></html>"); writer.close(); } catch (Exception e) { e.printStackTrace(); } } }
In the preceding code a basic doGet()
method is implemented, but is not used in the example. The
code to establish the connection with the database is as
shown in the previous example,
Section 22.3.5.2.5.1, “A Simple JSP Application with Glassfish, Connector/J and MySQL”,
and is most conveniently located in the servlet
init()
method. The corresponding freeing
of resources is located in the destroy method. The main
functionality of the servlet is located in the
doPost()
method. If the user enters nto
the input form a country name that can be located in the
database, the population of the country is returned. The
code is invoked using a POST action associated with the
input form. The form is defined in the file
index.html
:
<html> <head><title>HelloWebServlet</title></head> <body> <h1>HelloWebServlet</h1> <p>Please enter country name:</p> <form action="HelloWebServlet" method="POST"> <input type="text" name="country_name" length="50" /> <input type="submit" value="Submit" /> </form> </body> </html>
The XML files web.xml
and
sun-web.xml
are as for the example in
the preceding section,
Section 22.3.5.2.5.1, “A Simple JSP Application with Glassfish, Connector/J and MySQL”,
no additional changes are required.
Whe compiling the Java source code, you will need to specify
the path to the file javaee.jar
. On
Windows, this can be done as follows:
shell> javac -classpath c:\glassfishv3\glassfish\lib\javaee.jar HelloWebServlet.java
Once the code is correctly located within its directory structure, and compiled, the application can be deployed in Glassfish. This is done in exactly the same way as described in the preceding section, Section 22.3.5.2.5.1, “A Simple JSP Application with Glassfish, Connector/J and MySQL”.
Once deployed the application can be launched from within the Glassfish Administration Console. Enter a country name such as “England”, and the application will return “Country does not exist!”. Enter “France”, and the application will return a population of 59225700.