This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .
Today, I worked on a service request that our customer reported several disconnections with ETL process that maintains the connection open for a long time. Improving the resilient for this type of workload I would like to share my lessons learned using isClosed() and isValid() for JDBC Driver.
This article provides an in-depth technical analysis of the differences and advantages of using JDBC's isClosed() and isValid() functions to manage database connections in Java applications, especially when using connection poolers.
Introduction:
Java Database Connectivity (JDBC) is a Java API that facilitates connecting with databases. Two of the most important functions provided by JDBC are isClosed() and isValid(). While both are used to manage the state of connections, there are key differences that might make one more suitable than the other in certain situations.
isClosed() Function:
isClosed() checks whether a database connection is closed. It returns true if the connection is closed; otherwise, it returns false.
isValid() Function:
isValid(int timeout) checks whether a database connection is valid or not within the specified time in seconds.
Comparative Analysis between isClosed() and isValid():
-
Detection of Invalid Connections:
- isClosed() only detects if a connection is closed, but it cannot determine if an open connection is valid or not.
- isValid() checks if a connection is valid, which includes not only if it is open but also if it is in a state that allows for database operations.
-
Handling Timeouts:
- isClosed() lacks the capability to handle timeouts.
- isValid() allows specifying a timeout, providing more granular control over the time waited to determine the validity of a connection.
-
Resource Usage:
- isClosed() generally uses fewer resources because it simply checks the internal state of the connection without interacting with the database.
- isValid() may consume more resources because it performs a validation operation that may involve communication with the database.
Usage in Connection Pooling:
In the context of connection pooling, isValid() is generally more useful because it provides a more comprehensive check of a connection's validity before delivering it from the pool. This ensures that the connections delivered from the pool are ready to be used and capable of handling database operations.
Internals of isValid() :
Unfortunately, the exact implementation of isValid() can vary between different JDBC drivers, as it is driver-dependent. In general, isValid() might perform actions such as sending a simple SQL query to the database and waiting for a response to confirm that the connection is alive and ready to process commands. However, the specific SQL query used, and the way the response is handled, can differ between drivers.
Conclusion:
While both isClosed() and isValid() are essential JDBC functions for managing database connections, isValid() offers a more comprehensive check of a connection's validity. This makes it especially useful in the context of connection pooling, where it is crucial to ensure that connections delivered from the pool are valid and in a state that allows for database operations. In summary, while isClosed() is useful for a quick check of a connection's state, isValid() is the more powerful tool to ensure the resilience and reliability of database connections in Java applications.
In this situation, including this piece of code we were able to make more resilient our connection.
if(oConnRetryLogic.getConn().isClosed() || !oConnRetryLogic.getConn().isValid(2000))
{
... Open a new connection
}