RSS Feed for This PostCurrent Article

Avoid Connection Pool Error after Database Restart for Java Application


This is one of the common configuration that I noticed most developers either do not configure or not configured properly. I normally use Hibernate or iBatis as the persistence layer and Apache DBCP for the connection pool. There are times that database are restarted without bouncing the application. When this happens, all the database connections in the pool become invalid, and user will encounter error in the application whenever there is any database access.

To avoid this error, you need to validate the connection whenever the application requests for it. E.g., in iBatis, if I am using Apache DBCP, I need to set the following properties.

  • validationQuery – The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query MUST be an SQL SELECT statement that returns at least one row.
  • testOnBorrow – The indication of whether objects will be validated before being borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another.
    NOTE – for a true value to have any effect, the validationQuery parameter must be set to a non-null string.

For example, if I am using Oracle database, I need to set the following properties.

<property name=”initialSize” value=”5″></property>
<property name=”maxActive” value=”30″></property>
<property name=”maxIdle” value=”20″></property>
<property name=”maxWait” value=”60000″></property>
<property name=”poolPreparedStatements” value=”true”></property>
<property name=”validationQuery” value=”select 0 from dual”></property>
<property name=”testOnBorrow” value=”true”></property>

Of course this would add a bit of overhead but in my opinion it is worth it.


Trackback URL


RSS Feed for This Post3 Comment(s)

  1. hrm2100 | Oct 3, 2007 | Reply

    nice tip

  2. Durgesh | Oct 16, 2007 | Reply

    hi
    in my website when i am login than it not any permission an d no any error come.
    Database work properly becasue some point of database show.
    when i am login wrong username and password than show error of wrong “username and password”.

    Pls tell me what type of Error?????????

  3. admin | Oct 16, 2007 | Reply

    What web and db framework u r using ? Can you post your detailed error here ?

Sorry, comments for this entry are closed at this time.