Subject Hibernate with Apache DBCP and Prepared Statement Pooling
Author Bill Oliver
Hi all!

A coworker of mine is trying to use Hibernate with Apache's DBCP
connection pool. He has turned prepared statement pooling ON. His
settings are:

<property name="hibernate.connection.pool_size">20</property>
<property name="hibernate.dbcp.initialSize">5</property>
<property name="hibernate.dbcp.maxActive">100</property>
<property name="hibernate.dbcp.maxIdle">10</property>
<property name="">10</property>
<property name="">10</property>

This is Firebird 1.5.3 with Jaybird 2.1.1 on Windows, BTW.

The last 2 settings (ps.maxActive, ps.maxIdle) appear to be
problematic. His test job fails with these settings with an
integrity constraint violation. A wild guess is that with the
prepared statement cache, one of the later prepared statement
doesn't see a previous insert into the master table, thus resulting
in a failure when we insert into child. But that is just a guess.

Test job runs fine with these properties absent - thus turning off
prepared statement caching. A larger value of 100 for maxActive
works, too, but that is probably masking the problem, as very little
recycling is done in this case.

This page says to always set prepared statement cache to 0 when
using Firebird with Hibernate - but doesn't explicitly refer to
Apache DBCP. It also doesn't say why to do this.

A confirmation that I should turn off prepared statement caching
with Apache DBCP or any further information on this issue would be
appreicated very much!