Database Connection Check

Mark J. Norton

I was recently given the task to create a uniform approach to checking the status of all running applications.  These checks use an HTTP listener to provide status information back to the caller. Currently, this information is returned in JSON format, but a shared formatter flow is called to provide a global method to transform it into another form, such as HTML.

The basic application check information includes a timestamp, the name of the application, what host, port, and cluster id it is running on, etc.  Additionally, checks are made for the status of any third party resources being used, such as a database, a REST or WSDL service, message queue, or others.  In particular, I’d like to share how the database connection check works.

The usual way to check to see if a particular database connection is up and running is to run a query against it.  Perhaps something like this:

<db:select config-ref=”MySQL_Configuration” doc:name=”Database”>

            <db:parameterized-query>

                          <![CDATA[select sysdate();]]>

            </db:parameterized-query>

</db:select>

If the connection is down, an exception is thrown that can be caught and status rendered appropriately.  However, there are times when this approach will fail even though the connection is up and running.  Databases containing sensitive information, such as those protected by HIPAA, may disallow select operations.  A more general approach can be developed by accessing the database connector object directly.

The simplest way to access the database connector object directly is to define it as a bean in a Mule global configuration file or a domain configuration file.

<spring:beans>

   <spring:bean id=”Connector” name=”conName”

                    class=”org.apache.commons.dbcp2.BasicDataSource”>

         <spring:property name=”driverClassName”    

                   value=”oracle.jdbc.driver.OracleDriver” />

         <spring:property name=”url” value=”${db.url}” />

         <spring:property name=”username” value=”${db.username}” />

         <spring:property name=”password” value=”${db.password}” />

   </spring:bean>

</spring:beans>

Once the bean is defined, we can access existing methods such as isClosed() to determine if the connection is open or closed.  An active database connection must be open to be used.  This example shows how to capture the database connector status using a MEL statement.

<set-variable doc:name=”isClosed”

value=”#[app.registry.conName.getConnection().isClosed() ? ‘Down’ : ‘Up’]”

variableName=”isClosed”/>

Depending on the database driver you are using, the method name might be slightly different.  Still, this is a more reliable and consistent way to determine if a database connection is viable