Automating Database Connection Management
What can we do about this? One way is to thoroughly test the application for non closed connections. Yes this requires developer concentration and can be time consuming if you do not use proper tools. More effective solution is to use a database access platform that can automatically take care of closing the opened database connections. But what if our simple project requires use of direct database access technologies (such as pure JDBC or pure
Let’s look at a small code fragment which we can use to employ a very simple data access framework to take care of this problem. In short we are going to write a base database action class which can provide and manage database connection to its sub classes. We use sub classing to write data access code (data actions) we need. Below is an example for the base action class written on VB.NET.
Public MustInherit Class ActionBase(Of T)
Protected MustOverride Function Body(ByVal conn As DbConnection) As T
Public Function Execute() As T
' Create a database connection
Dim conn As DbConnection = ConnectionProvider.GetConnection()
' Call the body method by providing the connection
Body(conn)
' Close the connection
Conn.Close()
End Function
End Class
All the sub classes need to implement the abstract method “Body(ByVal conn As DbConnection)”. As a method parameter, a connection instance will be available to the implementing method and that can be used by the sub class method to perform data access logic. Above base class is written as a generic class and subclasses should provide the template class name which should be the expected return object type of the sub class. For an example, a data access action that retrieve a ‘User’ instance will like follows:
Friend Class RetrieveUserAction
Private _userId As String
Public Sub New(ByVal userId As String)
Me._userId = userId
End Sub
Protected Overrides Function Body(ByVal conn As DbConnection) As User
Dim user as User = new User()
' Use connection to fetch user info and populate ‘user’ instance
Return user
End Function
End Class
As you can see we write sub classes to execute database operation on the database. Developers will not have to look for database connections. It will be provided in the “Body()”method parameter. Also the developers are free from the burden of closing the connections as framework itself will take care of it.
Let’s look at how one can make use of the above written class to retrieve the user with id=12.
Dim action AS new RetrieveUserAction(12)
Dim user As User = Action.Execute()
Even though this simple framework is pretty useful in managing connection, this has some limitations also. Developers are not able to spawn threads and pass the connection in to it for further processing. Also the results will be always disconnected, you cant pass resultsets to upper layers for presessing (but I think this enforces a good practice).
Also one major thing to consider is whether actually database resources getting released by closing the connections. In Oracle with JDBC connection close will not release database resources and you need to close the ‘Statement’ instance to release database resources. In such situations we can ammend the framework to provide us statement objects instead of providing connections.
Labels: close, connection, database
