Welcome!

IBM Cloud Authors: Reinhard Brandstädter, Liz McMillan, Yeshim Deniz, Cynthia Dunlop, Derek Weeks

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.

Background
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)
ctx.lookup("jdbc/
MyDataSource");

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);
...
con.close();

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 com.ibm.ejs.cm.portability; in 4.0 it's com.ibm.websphere.ce.cm (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 =
5000");
} catch(com.ibm.ejs.cm.portability.StaleConnectionException 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.

Resources
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:
www.ibm.com/software/webservers/appserv/library.html
www7b.boulder.ibm.com/wsdd and www.redbooks.ibm.com

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
The essence of data analysis involves setting up data pipelines that consist of several operations that are chained together – starting from data collection, data quality checks, data integration, data analysis and data visualization (including the setting up of interaction paths in that visualization). In our opinion, the challenges stem from the technology diversity at each stage of the data pipeline as well as the lack of process around the analysis.
The IoTs will challenge the status quo of how IT and development organizations operate. Or will it? Certainly the fog layer of IoT requires special insights about data ontology, security and transactional integrity. But the developmental challenges are the same: People, Process and Platform. In his session at @ThingsExpo, Craig Sproule, CEO of Metavine, will demonstrate how to move beyond today's coding paradigm and share the must-have mindsets for removing complexity from the development proc...
Designing IoT applications is complex, but deploying them in a scalable fashion is even more complex. A scalable, API first IaaS cloud is a good start, but in order to understand the various components specific to deploying IoT applications, one needs to understand the architecture of these applications and figure out how to scale these components independently. In his session at @ThingsExpo, Nara Rajagopalan is CEO of Accelerite, will discuss the fundamental architecture of IoT applications, ...
A strange thing is happening along the way to the Internet of Things, namely far too many devices to work with and manage. It has become clear that we'll need much higher efficiency user experiences that can allow us to more easily and scalably work with the thousands of devices that will soon be in each of our lives. Enter the conversational interface revolution, combining bots we can literally talk with, gesture to, and even direct with our thoughts, with embedded artificial intelligence, wh...
SYS-CON Events announced today that Tintri Inc., a leading producer of VM-aware storage (VAS) for virtualization and cloud environments, will exhibit at the 18th International CloudExpo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, New York, and the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA.
In his session at 18th Cloud Expo, Bruce Swann, Senior Product Marketing Manager at Adobe, will discuss how the Adobe Marketing Cloud can help marketers embrace opportunities for personalized, relevant and real-time customer engagement across offline (direct mail, point of sale, call center) and digital (email, website, SMS, mobile apps, social networks, connected objects). Bruce Swann has more than 15 years of experience working with digital marketing disciplines like web analytics, social med...
SYS-CON Events announced today that EastBanc Technologies will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. EastBanc Technologies has been working at the frontier of technology since 1999. Today, the firm provides full-lifecycle software development delivering flexible technology solutions that seamlessly integrate with existing systems – whether on premise or cloud. EastBanc Technologies partners with p...
SYS-CON Events announced today that BMC Software has been named "Siver Sponsor" of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2015 at the Javits Center in New York, New York. BMC is a global leader in innovative software solutions that help businesses transform into digital enterprises for the ultimate competitive advantage. BMC Digital Enterprise Management is a set of innovative IT solutions designed to make digital business fast, seamless, and optimized from mainframe to mo...
SYS-CON Events announced today that MangoApps will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. MangoApps provides modern company intranets and team collaboration software, allowing workers to stay connected and productive from anywhere in the world and from any device. For more information, please visit https://www.mangoapps.com/.
The IoT is changing the way enterprises conduct business. In his session at @ThingsExpo, Eric Hoffman, Vice President at EastBanc Technologies, discuss how businesses can gain an edge over competitors by empowering consumers to take control through IoT. We'll cite examples such as a Washington, D.C.-based sports club that leveraged IoT and the cloud to develop a comprehensive booking system. He'll also highlight how IoT can revitalize and restore outdated business models, making them profitable...
SYS-CON Events announced today that Commvault, a global leader in enterprise data protection and information management, has been named “Bronze Sponsor” of SYS-CON's 18th International Cloud Expo, which will take place on June 7–9, 2016, at the Javits Center in New York City, NY, and the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Commvault is a leading provider of data protection and information management...
Companies can harness IoT and predictive analytics to sustain business continuity; predict and manage site performance during emergencies; minimize expensive reactive maintenance; and forecast equipment and maintenance budgets and expenditures. Providing cost-effective, uninterrupted service is challenging, particularly for organizations with geographically dispersed operations.
Customer experience has become a competitive differentiator for companies, and it’s imperative that brands seamlessly connect the customer journey across all platforms. With the continued explosion of IoT, join us for a look at how to build a winning digital foundation in the connected era – today and in the future. In his session at @ThingsExpo, Chris Nguyen, Group Product Marketing Manager at Adobe, will discuss how to successfully leverage mobile, rapidly deploy content, capture real-time d...
SYS-CON Events announced today that ContentMX, the marketing technology and services company with a singular mission to increase engagement and drive more conversations for enterprise, channel and SMB technology marketers, has been named “Sponsor & Exhibitor Lounge Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2016, at the Javits Center in New York City, New York. “CloudExpo is a great opportunity to start a conversation with new prospects, but what happens after the...
SoftLayer operates a global cloud infrastructure platform built for Internet scale. With a global footprint of data centers and network points of presence, SoftLayer provides infrastructure as a service to leading-edge customers ranging from Web startups to global enterprises. SoftLayer's modular architecture, full-featured API, and sophisticated automation provide unparalleled performance and control. Its flexible unified platform seamlessly spans physical and virtual devices linked via a world...
IoT generates lots of temporal data. But how do you unlock its value? How do you coordinate the diverse moving parts that must come together when developing your IoT product? What are the key challenges addressed by Data as a Service? How does cloud computing underlie and connect the notions of Digital and DevOps What is the impact of the API economy? What is the business imperative for Cognitive Computing? Get all these questions and hundreds more like them answered at the 18th Cloud Expo...
SYS-CON Events announced today Object Management Group® has been named “Media Sponsor” of SYS-CON's 18th International Cloud Expo, which will take place on June 7–9, 2016, at the Javits Center in New York City, NY, and the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA.
What a difference a year makes. Organizations aren’t just talking about IoT possibilities, it is now baked into their core business strategy. With IoT, billions of devices generating data from different companies on different networks around the globe need to interact. From efficiency to better customer insights to completely new business models, IoT will turn traditional business models upside down. In the new customer-centric age, the key to success is delivering critical services and apps wit...
Join us at Cloud Expo | @ThingsExpo 2016 – June 7-9 at the Javits Center in New York City and November 1-3 at the Santa Clara Convention Center in Santa Clara, CA – and deliver your unique message in a way that is striking and unforgettable by taking advantage of SYS-CON's unmatched high-impact, result-driven event / media packages.
In his keynote at 18th Cloud Expo, Andrew Keys, Co-Founder of ConsenSys Enterprise, will provide an overview of the evolution of the Internet and the Database and the future of their combination – the Blockchain. Andrew Keys is Co-Founder of ConsenSys Enterprise. He comes to ConsenSys Enterprise with capital markets, technology and entrepreneurial experience. Previously, he worked for UBS investment bank in equities analysis. Later, he was responsible for the creation and distribution of life ...