I use connection pooling to fix a hibernate problem
Yesterday i had a problem with hibernate envers and a sql-server database and the solution was to switch from a “non pooled” to a “pooled” datasource with JPA/hibernate/spring.
The problem
I got this wonderfull stacktrace:
Caused by: org.hibernate.exception.JDBCConnectionException: Cannot open connection at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:97) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52) at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449) at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167) at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161) at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1573) at org.hibernate.loader.Loader.doQuery(Loader.java:696) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259) at org.hibernate.loader.Loader.doList(Loader.java:2228) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125) at org.hibernate.loader.Loader.list(Loader.java:2120) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) at org.hibernate.envers.entities.mapper.relation.lazy.initializor.AbstractCollectionInitializor.initialize(AbstractCollectionInitializor.java:62) at org.hibernate.envers.entities.mapper.relation.lazy.proxy.CollectionProxy.checkInit(CollectionProxy.java:47) at org.hibernate.envers.entities.mapper.relation.lazy.proxy.CollectionProxy.toString(CollectionProxy.java:118) at org.apache.commons.beanutils.converters.StringConverter.convert(StringConverter.java:57) at org.apache.commons.beanutils.ConvertUtilsBean.convert(ConvertUtilsBean.java:400) at org.apache.commons.beanutils.BeanUtilsBean.getNestedProperty(BeanUtilsBean.java:699) at org.apache.commons.beanutils.BeanUtilsBean.getProperty(BeanUtilsBean.java:723) at org.apache.commons.beanutils.BeanUtilsBean.describe(BeanUtilsBean.java:504) at org.apache.commons.beanutils.BeanUtils.describe(BeanUtils.java:145) at com.siemens.spice.common.persistence.revision.BeanDiff.getAllProperties(BeanDiff.java:170)
The background story
We use envers for revision-management of persisted objects. Our application have a “changelog-view” for change-statistics (which object changed from version A to version B, differences between these versions for each object etc.):
During the creation of the table data our code find out the changed objects (with a envers query). We use our RevisionsDaoImpl for that, i.e. the getRevisions method:
@Repository
@Transactional
class RevisionsDaoImpl<T,KEY, REVINFO> {
protected final Class<T> classOfT;
protected EntityManager entityManager;
public RevisionsDaoImpl(final Class<T> clazz) {
super(clazz);
}
public List<Revision<T, REVINFO>> getRevisions(final int theRevisionFrom, final int theRevisionTo) {
final AuditReader reader = ExtendedAuditReaderFactory.get(entityManager);
AuditQuery query = reader.createQuery().forRevisionsOfEntity(classOfT, false, true);
query = query.addOrder(AuditEntity.revisionNumber().asc());
// all items newer or equal than theRevisionFrom
query = query.add(AuditEntity.revisionNumber().ge(Integer.valueOf(theRevisionFrom)));
// all items older or equal than theRevisionTo
query = query.add(AuditEntity.revisionNumber().le(Integer.valueOf(theRevisionTo)));
return fetchRevisions(query, reader);
}
protected List<Revision<T, REVINFO>> fetchRevisions(final AuditQuery query, final AuditReader reader) {
try {
final Object resultList = query.getResultList();
final List<Object[]> queryResult = (List<Object[]>) resultList;
final List<Revision<T, REVINFO>> result = new ArrayList<Revision<T, REVINFO>>(queryResult.size());
for (final Object[] array : queryResult) {
result.add(new RevisionImpl<T, REVINFO>((T) array[0], (REVINFO) array[1], (RevisionType) array[2]));
}
return result;
} catch (final RevisionDoesNotExistException ex) {
return null;
} catch (final NoResultException ex) {
return null;
}
}
...
}
In the datatable bean we are loading all revisions (from version A to version B) . Then we use org.apache.commons.beanutils.BeanUtils to get all properties from version A and B and then we compare these properties for each object. The result is the list of changed objects and all differences for each object (diffRevision(T versionA, T version B)):
protected List<T> getChangeLog() {
final List<T> result = new ArrayList<T>();
final VersionedDao<T, Integer, RevisionInfo> dao = getRevisionsDao();
// get all revisions from A to B for objects T
final List<Revision<T, RevisionInfo>> revs = dao.getRevisions(fromRevision.intValue(), toRevision.intValue());
// get A revision and B revision for each object T with revisions
final Map<Integer, List<Revision<T, RevisionInfo>>> oldNewRevisions = dao.getOldNewRevisions(revs, fromRevision, toRevision);
// build diff for each object
for (final Revision rev : revs) {
final T entity = rev.getEntity();
final Revision<T, RevisionInfo> oldestRevision = oldNewRevisions.get(entity.getId()).get(0);
final Revision<T, RevisionInfo> newestRevision = oldNewRevisions.get(entity.getId()).get(1);
result.add(diffRevision(newestRevision, oldestRevision));
}
In the ui layer we are using rich:dataTable which have sortable columns. The above exception occured if a column-sort was triggered.
Maybe inside hibernate or envers too many transactions or connections created and not fast enough released during the table data creation or the table rendering (lazy loaded properties may play a role in this game). Or the jdbc driver we are using is the bad guy (net.sourceforge.jtds:jtds:1.2).
The solution
Anyway! A workaround was to use a pooled datasource instead of a standard datasource. I found a list of pooling frameworks for hibernate here. The Hibernate documentation contains some information too. I decide to try the 3CPO framework. Its very easy to add a new dependency:
<dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-c3p0</artifactId> <version>3.3.1.GA</version> <scope>runtime</scope> </dependency>
And change the datasource:
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${jpa.driver}" />
<property name="url" value="${jpa.url}" />
<property name="username" value="${jpa.username}" />
<property name="password" value="${jpa.password}" />
</bean>
<bean id="pooledDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="jdbcUrl" value="${jpa.url}" />
<property name="user" value="${jpa.username}" />
<property name="password" value="${jpa.password}" />
<property name="initialPoolSize" value="1" />
<property name="minPoolSize" value="1" />
<property name="maxPoolSize" value="3" />
<property name="idleConnectionTestPeriod" value="500" />
<property name="acquireIncrement" value="1" />
<property name="maxStatements" value="50" />
<property name="numHelperThreads" value="1" />
</bean>
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="loadTimeWeaver">
<bean class="org.springframework.instrument.classloading.InstrumentationLoadTimeWeaver" />
</property>
<property name="dataSource" ref="pooledDataSource" />
<property name="persistenceUnitName" value="sid-admin-web-persistence" />
<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
<property name="showSql" value="false" />
<property name="generateDdl" value="true" />
<property name="databasePlatform" value="${jpa.hibernate.dialect}" />
</bean>
</property>
</bean>
<property name=”driverClassName” value=”${jpa.driver}” />
<property name=”url” value=”${jpa.url}” />
<property name=”username” value=”${jpa.username}” />
<property name=”password” value=”${jpa.password}” />
</bean>
<bean id=”pooledDataSource” class=”com.mchange.v2.c3p0.ComboPooledDataSource” destroy-method=”close”>
<description>
Wir muessen eine “pooled data source” benutzen weil im changelog massiv transaktionen/connections
erzeugt werden, dies fuehrt in verbindung mit dem sql-server zu einem knappwerden der sockets und
zu einem jdbc-error
</description>
<property name=”driverClass” value=”${jpa.driver}” />
<property name=”jdbcUrl” value=”${jpa.url}” />
<property name=”user” value=”${jpa.username}” />
<property name=”password” value=”${jpa.password}” />
<property name=”initialPoolSize” value=”1″ />
<property name=”minPoolSize” value=”1″ />
<property name=”maxPoolSize” value=”3″ />
<property name=”idleConnectionTestPeriod” value=”500″ />
<property name=”acquireIncrement” value=”1″ />
<property name=”maxStatements” value=”50″ />
<property name=”numHelperThreads” value=”1″ />
</bean>
<bean id=”entityManagerFactory” class=”org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean”>
<property name=”loadTimeWeaver”>
<bean class=”org.springframework.instrument.classloading.InstrumentationLoadTimeWeaver” />
</property>
<property name=”dataSource” ref=”dataSource” />
<property name=”persistenceUnitName” value=”sid-admin-web-persistence” />
<property name=”jpaVendorAdapter”>
<bean class=”org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter”>
<property name=”showSql” value=”false” />
<property name=”generateDdl” value=”true” />
<property name=”databasePlatform” value=”${jpa.hibernate.dialect}” />
</bean>
</property>
</bean>
