In Applicationcontext.xml we are configuring the datasource for the db connection
<bean id="dataSource" class="xx.datasource.ConfigureDataSource">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="${notification.central.ds.url}" />
<property name="username" value="${notification.central.ds.username}" />
<property name="encryptedPassword" value="di.tomcat.enc.NOTIFICATION_CENTRAL_DS" />
<property name="maxActive" value="200"/>
<property name="minIdle" value="5"/>
<property name="maxIdle" value="200"/>
<property name="initialSize" value="0"/>
<property name="removeAbandonedTimeout" value="180"/>
<property name="removeAbandoned" value="true"/>
<property name="logAbandoned" value="true"/>
<property name="minEvictableIdleTimeMillis" value="180000"/>
<property name="jmxEnabled" value="true"/>
<property name="testWhileIdle" value="true"/>
<property name="testOnBorrow" value="false"/>
<property name="testOnConnect" value="true"/>
<property name="validationQuery" value="select 1 from dual"/>
<property name="validationInterval" value="20000"/>
</bean>
ConfigureDataSource is a custom class used for the connection pool.
public class ConfigureDataSource extends DataSource implements InitializingBean {
final static XLogger logger = XLoggerFactory.getXLogger(ConfigureDataSource.class);
private PropertyReplacerUtil util = null;
private String encryptedPassword;
public ConfigureDataSource() throws TomcatutilException {
util = new PropertyReplacerUtil();
}
public String getEncryptedPassword() {
return encryptedPassword;
}
public void setEncryptedPassword(String encryptedPassword) {
this.encryptedPassword = encryptedPassword;
}
@Override
public void afterPropertiesSet() {
logger.entry();
try {
logger.info("password for decryption ", this.encryptedPassword);
String pass = util.decryptFromKeyStore(this.encryptedPassword);
setPassword(pass);
logger.info("password decrypted and set successfully");
String jdbcInterceptors = new StringBuilder("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer(trace=true);StatementCache(prepared=true,callable=false,max=100)").toString();
setJdbcInterceptors(jdbcInterceptors);
} catch (Exception e) {
logger.error("exception while decrypting the encrypted string in application.properties", e.getLocalizedMessage() + e.getCause());
}
}
}
i have used the tomcat-jdbc:9.05 and tomcat-utili:9.05 version in pom.xml
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-jdbc</artifactId>
<version>9.0.5</version>
</dependency>
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-juli</artifactId>
<version>9.0.5</version>
</dependency>
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-util</artifactId>
<version>9.0.5</version>
</dependency>
when i tried first with update API then it worked fine, but 2nd time it throws the below error. Not sure why connection pool is getting closed or connection getting closed. please let me know any help.
Caused by: java.sql.SQLException: PooledConnection has already been closed. at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:87) at com.sun.proxy.$Proxy90.getMetaData(Unknown Source) at org.springframework.jdbc.core.StatementCreatorUtils.setNull(StatementCreatorUtils.java:259) at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:229) at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:163) at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.doSetValue(ArgumentPreparedStatementSetter.java:69) at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.setValues(ArgumentPreparedStatementSetter.java:50) at org.springframework.jdbc.core.JdbcTemplate.lambda$update$0(JdbcTemplate.java:865) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)