Click here to close now.


Java IoT Authors: SmartBear Blog, Mike Kavis, Liz McMillan, Pat Romanski, AppDynamics Blog

Related Topics: IBM Cloud

IBM Cloud: Article

Managing JDBC Connections

Managing JDBC Connections

If application servers like WebSphere are the heart of e-business, then the database is surely the lifeblood. Whether your firm is managing thousands of airline flights a day or simply exposing a library's card catalog to the Web, you must deal with database connectivity.

Java developers have a simple way to manage database connectivity at their disposal: JDBC. It provides a convenient interface to access a variety of vendors' databases via JDBC drivers. Simply plug in a new driver and your application can talk to DB2, Oracle, Sybase, Informix, and others.

The JDBC version 2.0 extensions introduced a feature called connection pooling, providing better performance and scalability. IBM implemented a JDBC 2.0-compliant connection pool in WebSphere 3.5, and continues to enhance it in version 4.0.

In addition to connection pooling, the WebSphere connection manager offers many additional qualities to enhance an application's performance and scalability for the savvy Web developer. This article will highlight those features and show how they can be used effectively for building better Web applications. In addition, this article will describe many best practices and potential pitfalls for developing with WebSphere's connection manager.

While the following information will provide an improvement to applications, note that this is meant to aid in application design, not replace it. No amount of "tweaking" can make up for a poor design, but a well-designed application with poor implementation of database connectivity logic can be improved. This article will address the latter case.

The WebSphere connection manager has many facets. At the core, however, it's simply a connection pool. Object pools are nothing new.

The basic reason for a pool is that creation of certain objects is expensive in performance terms (time, memory allocation, etc.). Rather than creating these objects over and over again, it's more efficient to create a pool of already initialized objects that can be allocated to application components, and then returned to the pool when no longer needed. Thus, the hit to performance is only incurred once, when the object is actually created.

Anyone doing performance-tuning of JDBC applications knows that creating database connections is an expensive operation. Connection pooling is a natural fit for Web applications that utilize servlets or EJBs that require many simultaneous threads to connect to databases. Figure 1 provides a more visual example. In this diagram, three application components (a servlet, JSP, and session bean) have each received a Connection object from the pool (whose current size is six).

In addition to pooling connections, the WebSphere connection manager monitors the health of a database connection. If a connection has been allocated from the pool but hasn't been used in a specified amount of time (orphan timeout), the connection manager will mark the connection as "stale" and will return it to the pool (preempt the connection).

In the event of a connectivity failure (database error, network connection dropped, etc.), the connection manager will throw a StaleConnectionException, a subclass of SQLExcep-tion, on the next JDBC operation. This allows application developers to handle failure conditions more flexibly, such as allowing the application to retry the database operation. Further, the connection manager removes all Connection objects from the pool.

WebSphere's connection manager supports a variety of databases, including DB2, Oracle, Sybase, Microsoft SQL Server, and Informix (only available in WebSphere 4.0). SQL Server and Oracle (for two-phase commit operations) require the use of the Merant SequeLink 5.1 JDBC Driver, which ships with WebSphere Application Server, Advanced Edition.

Configuration of the connection manager is simple and painless (see Figures 2 and 3). Each data source represents a connection pool to a specified database, and is linked to a single JDBC driver.

Accessing a data source programmatically requires a JNDI lookup such as:


Context ctx = new InitialContext();
DataSource ds = (DataSource)

The string, "jdbc/MyDataSource", represents the JNDI name of the datasource. In version 4.0, you may use a resource reference such as "java:comp/env/jdbc/MyDataSource", as per the J2EE specification. The DataSource object contains the methods getConnection() and getConnection(String username, String password) to allocate a Connection object from the pool.

Simply close the Connection object to return it to the pool. For example:


Connection con = ds.getConnection(
username, password);

Now that you understand the basics of the WebSphere connection manager, let's take a look at some of the best practices.

Keep the Connection Life Span Minimized
Ideally, an application should never hold on to a Connection object beyond a single method. Nor should a single thread acquire more than one Connection. The longer one part of an application holds onto a Connection, the longer other parts cannot use that same Connection.
An application designed to acquire more than one Connection per thread and hold them for very long periods of time could easily come to a screeching halt when multiple threads collectively request more Connection objects than the maximum pool size.

The temptation here is to simply increase the size of the pool, but that will impact performance. Each Connection, whether it's in use or not, can cost over two megabytes of JVM memory. Furthermore, a higher pool size means creating more Connection objects which, as previously mentioned, is expensive. Efficient usage of fewer Connection objects is a far better design.

Additionally, when an orphaned Connection times out, the connection manager will reclaim the Connection for the pool, throwing a StaleConnectionException in the offending client on the next JDBC operation. While this is costly for the single thread, it does at least allow the Connection object to be freed. However, inside the bounds of a transaction (e.g., inside an EJB or when using UserTransaction objects), a Connection will never be orphaned. Thus, long-running transactions could keep Connection objects out of commission for a long time, and could lead to deadlock-like conditions.

While keeping connectivity life span minimized, a developer should be careful not to compromise functionality. There are times when holding a ResultSet open for an extended operation is necessary to preserve transaction isolation (row locks). Closing a ResultSet (or its creating Connection or Statement object) too early might result in your application making decisions that assumed the state of the data in the database was X when another thread has modified it to be Y.

From an application development standpoint, the best alternative is to use fewer connections in your pool, but to use them wisely. Once a method has finished its database utilization, it should free the Connection object by calling its close() method.

Cache the Datasource, Not the Connection
To take full advantage of the connection pool and make limited calls to expensive operations, an application developer should cache a datasource object when database connectivity may be utilized often. Caching the Connection has the negative effects described above and defeats the purpose of pooling.

Also, be aware that declaring Connection objects as static class variables can have unexpected and undesirable effects. For example:


public class MyJDBCClass {
static Connection con;

When a Connection is declared static, it's shared by all instances of the declaring class. Therefore, it's possible that two threads might attempt to utilize the same Connection object simultaneously. The results could include unexpected table locking, incorrect ResultSets, and serious data integrity problems.

Also, keep in mind that all global variables of a servlet are static by default (even if you don't declare them static). If you decide to cache a Connection, make sure not to declare it static.

Caching the datasource means creating a global instance variable in one of your classes. That class will maintain the reference to the datasource object throughout its life. When a member method needs a Connection, it creates one by calling the datasource's getConnection() method.

The advantage of caching the datasource is that you prevent multiple JNDI lookups, another expensive operation. WebSphere provides additional caching in this area, but caching it locally is advantageous.

Make Sure to Clean Up!
The WebSphere connection manager does a good job of caring for your connections, but nothing makes up for careless coding. Make sure to clean up the open database resource objects properly.

The rule of thumb when cleaning up JDBC objects is to close them in the reverse order they were created. As an example, most database read operations encompass a Connection, which creates or prepares a Statement (or PreparedStatement), which produces a ResultSet. In this case, close the ResultSet first, the Statement second, the Connection last. The order of the cleanup is important to prevent database-specific annoyances, like running out of cursors.

The best way to ensure proper cleanup is to place all cleanup logic in a finally clause of a try-catch-finally block. The code in a finally clause is guaranteed to run in all cases short of a JVM crash, so a Connection.close() in a finally clause is sure to return the Connection to the pool. Example:


try {
con = ds.getConnection();
ps = con.prepareStatement
("select * from mytable where mycolumn = ?");
ps.setString(1, someString);
rs = ps.executeQuery();
// do something with the ResultSet
} catch (SQLException ex) {
// handle the SQLException
} finally {
try { rs.close(); } catch(SQLException ex) {rs = null;}
try { ps.close(); } catch (SQLException ex) {ps = null;}
try { con.close(); } catch (SQLException ex) {con = null;}

One alternative to closing Connection objects in finally clauses is to close them in a finalize() method of an object. This approach is not recommended, as the finalize() method is only called prior to garbage collection, an asynchronous operation. Also, the only reason to postpone closing a Connection in this manner would imply that you're caching it in the object, another poor practice. Cleaning up JDBC objects correctly can prevent many unnecessary headaches.

When Good Connections Go Bad
Occasionally database connectivity breaks unexpectedly. Network cables get pulled, electricity goes out, or sometimes a database server just gets pegged. Although nothing can be done until the database is brought back online, these sorts of outages don't have to permanently bring down your application's functionality.

The WebSphere connection manager provides a convenient manner of handling temporary connectivity outages. Proper handling of the StaleConnectionException allows developers to create more fault-tolerant applications.

The StaleConnectionException is an exception that extends SQLException. This is necessary in order to follow the JDBC specification (most JDBC methods may only throw a SQLException). This is important to note because if an application is to catch a StaleConnectionException, it must be caught before catching the SQLException. The actual package name for the StaleConnectionException in version 3.5 is; in 4.0 it's (the 3.5 package name will work in 4.0 but is deprecated).

Just for clarification, when a Connection is marked "stale," that means that on its next operation it will throw a StaleConnectionException.

A Connection can "go bad" in any number of situations:

1.   The application attempts to acquire a Connection during a database failure or when the database has not yet been started.
2.   All of the Connection objects in the pool have gone bad due to a database failure. In this case, any new calls to getConnection() will return a stale Connection object, and all Connection objects in use would throw this exception when the application attempts to use them.
3.   The application using the Connection has previously called its close() method, but later tries to use the connection again.
4.   The application acquired a Connection but didn't use it within the orphan timeout period. In this case, the connection manager orphans (preempts) the Connection and the next time the application attempts to use it (or a JDBC resource that it created; e.g. Statement, ResultSet, etc.) a StaleConnectionException will be thrown.

Handling a StaleConnection-Exception is entirely up to the application designer. For more fault tolerance, one recommended approach is to catch the StaleConnection-Exception and retry the database operation up to a predefined number of tries. Here's an example:


boolean retry = false;
do {
try {
retry = false;
con = ds.getConnection();
stmt = con.createStatement();
rowsUpdated = stmt.execute("update myTable set myColumn =
} catch( sce) {
if (retryAttempts++ < MAX_RETRIES)retry = true;
} catch(SQLException sqle) {
/* handle a more severe failure and exit loop */
} finally {/*Close stmt and con */cleanupJDBCResources();
} while (retry);

Normal execution of this code will only execute the logic in the loop once; only in error conditions will the thread retry the operation. The connection manager could throw a StaleConnectionException on any of the operations on lines four through six. In this event, the thread will check to see if it's reached the maximum number of retries (set prior to execution), and if so retry the entire operation, starting by obtaining a new Connection object.

Handling the StaleConnectionException in this manner will allow your Web application to recover from a number of temporary database outages that would normally return an error page to your Internet customer.

Wrapping Up
The practices outlined here are recommended as best practices, and in general are good advice for building WebSphere applications. Nevertheless, none of these tips will replace a poor design; they are meant to complement a well-designed application. They're also meant to provide insight into how the connection manager functions. This information should help in future development projects as well as in maintaining your current applications.

Managing JDBC connections can be tricky, but with a good application design and by following these recommendations, you should be well on your way to delivering high-performing and scalable enterprise solutions.

Cuomo, G., A Methodology for Production Performance Tuning
Lauzon, S., and Schommer, P., Handling Database Failures
Erickson, D., Connection Manager Whitepaper
WebSphere 3.5 Handbook Redbook

These articles and whitepapers can be found online at: and

More Stories By J. Andrew McCright

Andy McCright is a software engineer for IBM software group. His>responsibilities include development of the WebSphere Application Server>and customer engagement. Recently, he has been researching web services and how they will play into the next generation of application servers.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.

@ThingsExpo Stories
As more intelligent IoT applications shift into gear, they’re merging into the ever-increasing traffic flow of the Internet. It won’t be long before we experience bottlenecks, as IoT traffic peaks during rush hours. Organizations that are unprepared will find themselves by the side of the road unable to cross back into the fast lane. As billions of new devices begin to communicate and exchange data – will your infrastructure be scalable enough to handle this new interconnected world?
SYS-CON Events announced today that Super Micro Computer, Inc., a global leader in high-performance, high-efficiency server, storage technology and green computing, will exhibit at the 17th International Cloud Expo®, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. Supermicro (NASDAQ: SMCI), the leading innovator in high-performance, high-efficiency server technology is a premier provider of advanced server Building Block Solutions® for Data Center, Cloud Computing, Enterprise IT, Hadoop/Big Data, HPC and Embedded Systems worldwide. Supermi...
Who are you? How do you introduce yourself? Do you use a name, or do you greet a friend by the last four digits of his social security number? Assuming you don’t, why are we content to associate our identity with 10 random digits assigned by our phone company? Identity is an issue that affects everyone, but as individuals we don’t spend a lot of time thinking about it. In his session at @ThingsExpo, Ben Klang, Founder & President of Mojo Lingo, will discuss the impact of technology on identity. Should we federate, or not? How should identity be secured? Who owns the identity? How is identity ...
This week, the team assembled in NYC for @Cloud Expo 2015 and @ThingsExpo 2015. For the past four years, this has been a must-attend event for MetraTech. We were happy to once again join industry visionaries, colleagues, customers and even competitors to share and explore the ways in which the Internet of Things (IoT) will impact our industry. Over the course of the show, we discussed the types of challenges we will collectively need to solve to capitalize on the opportunity IoT presents.
Today’s connected world is moving from devices towards things, what this means is that by using increasingly low cost sensors embedded in devices we can create many new use cases. These span across use cases in cities, vehicles, home, offices, factories, retail environments, worksites, health, logistics, and health. These use cases rely on ubiquitous connectivity and generate massive amounts of data at scale. These technologies enable new business opportunities, ways to optimize and automate, along with new ways to engage with users.
WebRTC converts the entire network into a ubiquitous communications cloud thereby connecting anytime, anywhere through any point. In his session at WebRTC Summit,, Mark Castleman, EIR at Bell Labs and Head of Future X Labs, will discuss how the transformational nature of communications is achieved through the democratizing force of WebRTC. WebRTC is doing for voice what HTML did for web content.
Through WebRTC, audio and video communications are being embedded more easily than ever into applications, helping carriers, enterprises and independent software vendors deliver greater functionality to their end users. With today’s business world increasingly focused on outcomes, users’ growing calls for ease of use, and businesses craving smarter, tighter integration, what’s the next step in delivering a richer, more immersive experience? That richer, more fully integrated experience comes about through a Communications Platform as a Service which allows for messaging, screen sharing, video...
SYS-CON Events announced today that Dyn, the worldwide leader in Internet Performance, will exhibit at SYS-CON's 17th International Cloud Expo®, which will take place on November 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. Dyn is a cloud-based Internet Performance company. Dyn helps companies monitor, control, and optimize online infrastructure for an exceptional end-user experience. Through a world-class network and unrivaled, objective intelligence into Internet conditions, Dyn ensures traffic gets delivered faster, safer, and more reliably than ever.
SYS-CON Events announced today that Sandy Carter, IBM General Manager Cloud Ecosystem and Developers, and a Social Business Evangelist, will keynote at the 17th International Cloud Expo®, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA.
The Internet of Things (IoT) is growing rapidly by extending current technologies, products and networks. By 2020, Cisco estimates there will be 50 billion connected devices. Gartner has forecast revenues of over $300 billion, just to IoT suppliers. Now is the time to figure out how you’ll make money – not just create innovative products. With hundreds of new products and companies jumping into the IoT fray every month, there’s no shortage of innovation. Despite this, McKinsey/VisionMobile data shows "less than 10 percent of IoT developers are making enough to support a reasonably sized team....
The IoT market is on track to hit $7.1 trillion in 2020. The reality is that only a handful of companies are ready for this massive demand. There are a lot of barriers, paint points, traps, and hidden roadblocks. How can we deal with these issues and challenges? The paradigm has changed. Old-style ad-hoc trial-and-error ways will certainly lead you to the dead end. What is mandatory is an overarching and adaptive approach to effectively handle the rapid changes and exponential growth.
With major technology companies and startups seriously embracing IoT strategies, now is the perfect time to attend @ThingsExpo in Silicon Valley. Learn what is going on, contribute to the discussions, and ensure that your enterprise is as "IoT-Ready" as it can be! Internet of @ThingsExpo, taking place Nov 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 17th Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The Internet of Things (IoT) is the most profound change in personal an...
The IoT is upon us, but today’s databases, built on 30-year-old math, require multiple platforms to create a single solution. Data demands of the IoT require Big Data systems that can handle ingest, transactions and analytics concurrently adapting to varied situations as they occur, with speed at scale. In his session at @ThingsExpo, Chad Jones, chief strategy officer at Deep Information Sciences, will look differently at IoT data so enterprises can fully leverage their IoT potential. He’ll share tips on how to speed up business initiatives, harness Big Data and remain one step ahead by apply...
There will be 20 billion IoT devices connected to the Internet soon. What if we could control these devices with our voice, mind, or gestures? What if we could teach these devices how to talk to each other? What if these devices could learn how to interact with us (and each other) to make our lives better? What if Jarvis was real? How can I gain these super powers? In his session at 17th Cloud Expo, Chris Matthieu, co-founder and CTO of Octoblu, will show you!
Developing software for the Internet of Things (IoT) comes with its own set of challenges. Security, privacy, and unified standards are a few key issues. In addition, each IoT product is comprised of at least three separate application components: the software embedded in the device, the backend big-data service, and the mobile application for the end user's controls. Each component is developed by a different team, using different technologies and practices, and deployed to a different stack/target - this makes the integration of these separate pipelines and the coordination of software upd...
As a company adopts a DevOps approach to software development, what are key things that both the Dev and Ops side of the business must keep in mind to ensure effective continuous delivery? In his session at DevOps Summit, Mark Hydar, Head of DevOps, Ericsson TV Platforms, will share best practices and provide helpful tips for Ops teams to adopt an open line of communication with the development side of the house to ensure success between the two sides.
Today air travel is a minefield of delays, hassles and customer disappointment. Airlines struggle to revitalize the experience. GE and M2Mi will demonstrate practical examples of how IoT solutions are helping airlines bring back personalization, reduce trip time and improve reliability. In their session at @ThingsExpo, Shyam Varan Nath, Principal Architect with GE, and Dr. Sarah Cooper, M2Mi's VP Business Development and Engineering, will explore the IoT cloud-based platform technologies driving this change including privacy controls, data transparency and integration of real time context w...
The Internet of Everything is re-shaping technology trends–moving away from “request/response” architecture to an “always-on” Streaming Web where data is in constant motion and secure, reliable communication is an absolute necessity. As more and more THINGS go online, the challenges that developers will need to address will only increase exponentially. In his session at @ThingsExpo, Todd Greene, Founder & CEO of PubNub, will explore the current state of IoT connectivity and review key trends and technology requirements that will drive the Internet of Things from hype to reality.
"Matrix is an ambitious open standard and implementation that's set up to break down the fragmentation problems that exist in IP messaging and VoIP communication," explained John Woolf, Technical Evangelist at Matrix, in this interview at @ThingsExpo, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
Nowadays, a large number of sensors and devices are connected to the network. Leading-edge IoT technologies integrate various types of sensor data to create a new value for several business decision scenarios. The transparent cloud is a model of a new IoT emergence service platform. Many service providers store and access various types of sensor data in order to create and find out new business values by integrating such data.