OpenJPA + Tomcat JDBC Connection Pooling = stale data
- by Julie MacNaught
I am using the Tomcat JDBC Connection Pool with OpenJPA in a web application. The application does not see updated data. Specifically, another java application adds or removes records from the database, but the web application never sees these updates. This is quite a serious issue. I must be missing something basic.
If I remove the Connection Pool from the implementation, the web application sees the updates. It's as if the web application's commits are never called on the Connection.
Version info:
Tomcat JDBC Connection Pool: org.apache.tomcat tomcat-jdbc 7.0.21
OpenJPA: org.apache.openjpa openjpa 2.0.1
Here is the code fragment that creates the DataSource (DataSourceHelper.findOrCreateDataSource method):
PoolConfiguration props = new PoolProperties();
props.setUrl(URL);
props.setDefaultAutoCommit(false);
props.setDriverClassName(dd.getClass().getName());
props.setUsername(username);
props.setPassword(pw);
props.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
"org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;"+
"org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReportJmx;"+
"org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer");
props.setLogAbandoned(true);
props.setSuspectTimeout(120);
props.setJmxEnabled(true);
props.setInitialSize(2);
props.setMaxActive(100);
props.setTestOnBorrow(true);
if (URL.toUpperCase().contains(DB2)) {
props.setValidationQuery("VALUES (1)");
} else if (URL.toUpperCase().contains(MYSQL)) {
props.setValidationQuery("SELECT 1");
props.setConnectionProperties("relaxAutoCommit=true");
} else if (URL.toUpperCase().contains(ORACLE)) {
props.setValidationQuery("select 1 from dual");
}
props.setValidationInterval(3000);
dataSource = new DataSource();
dataSource.setPoolProperties(props);
Here is the code that creates the EntityManagerFactory using the DataSource:
//props contains the connection url, user name, and password
DataSource dataSource = DataSourceHelper.findOrCreateDataSource("DATAMGT", URL, username, password);
props.put("openjpa.ConnectionFactory", dataSource);
emFactory = (OpenJPAEntityManagerFactory) Persistence.createEntityManagerFactory("DATAMGT", props);
If I comment out the DataSource like so, then it works. Note that OpenJPA has enough information in the props to configure the connection without using the DataSource.
//props contains the connection url, user name, and password
//DataSource dataSource = DataSourceHelper.findOrCreateDataSource("DATAMGT", URL, username, password);
//props.put("openjpa.ConnectionFactory", dataSource);
emFactory = (OpenJPAEntityManagerFactory) Persistence.createEntityManagerFactory("DATAMGT", props);
So somehow, the combination of OpenJPA and the Connection Pool is not working correctly.
