Problem with Java Spring - db connection timesout

[kminev]

Hi,

I have two web application running on apache tomcat. I have my front end build on Flex and the back-end is java blaze ds and of course Spring.

When my web apps are sitting unused for a while few hour up to day(s) I also have the same error on both at page load. After I refresh the browser the error/exception disappears until the application is left to sit for a while.

In a java forum people suggested my db connections times out and I should change it from static to non.


Here is part of my Spring Beans configuration file:

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
"springframework.org/dtd/spring-beans.dtd">
<beans>
<bean class="org.springframework.beans.factory.config.Pr opertyPlaceholderConfigurer">
<property name="location">
<value>WEB-INF/jdbc.properties</value>
</property>
</bean>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="${db.driver}"/>
<property name="jdbcUrl" value="${db.jdbcurl}"/>
<property name="user" value="${db.username}"/>
<property name="password" value="${db.password}"/>
<property name="minPoolSize" value="0"/>
<property name="maxPoolSize" value="15"/>
<property name="initialPoolSize" value="3"/>
</bean>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSou rceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>

Thanks in advance.

This is the Exception:

[RPC Fault faultString="org.springframework.dao.DataAccessRes ourceFailureException : PreparedStatementCallback; SQL []; Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: Software caused connection abort: socket write error

STACKTRACE:

java.net.SocketException: Software caused connection abort: socket write error

at java.net.SocketOutputStream.socketWrite0(Native Method)

at java.net.SocketOutputStream.socketWrite(Unknown Source)

at java.net.SocketOutputStream.write(Unknown Source)

at java.io.BufferedOutputStream.flushBuffer(Unknown Source)

at java.io.BufferedOutputStream.flush(Unknown Source)

at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2637)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:15 54)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java :1665)

at com.mysql.jdbc.Connection.execSQL(Connection.java: 3176)

at com.mysql.jdbc.PreparedStatement.executeInternal(P reparedStatement.java:1153)

at com.mysql.jdbc.PreparedStatement.executeUpdate(Pre paredStatement.java:1404)

at com.mysql.jdbc.PreparedStatement.executeUpdate(Pre paredStatement.java:1318)

at com.mysql.jdbc.PreparedStatement.executeUpdate(Pre paredStatement.java:1303)

at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement .executeUpdate(NewProxyPreparedStatement.java:105)

at org.springframework.jdbc.core.simple.AbstractJdbcI nsert.doExecuteAndReturnKey(AbstractJdbcInsert.jav a:356)

at org.springframework.jdbc.core.simple.SimpleJdbcIns ert.executeAndReturnKey(SimpleJdbcInsert.java:106)

at com.seventicks.re.dao.jdbc.LogMessageImpl.add(LogM essageImpl.java:110)

at com.seventicks.re.service.DefaultLogonServiceImpl. accessSystem(DefaultLogonServiceImpl.java:72)

at sun.reflect.GeneratedMethodAccessor126.invoke(Unkn own Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at flex.messaging.services.remoting.adapters.JavaAdap ter.invoke(JavaAdapter.java:421)

at flex.messaging.services.RemotingService.serviceMes sage(RemotingService.java:183)

at flex.messaging.MessageBroker.routeMessageToService (MessageBroker.java:1503)

at flex.messaging.endpoints.AbstractEndpoint.serviceM essage(AbstractEndpoint.java:884)

at flex.messaging.endpoints.amf.MessageBrokerFilter.i nvoke(MessageBrokerFilter.java:121)

at flex.messaging.endpoints.amf.LegacyFilter.invoke(L egacyFilter.java:158)

at flex.messaging.endpoints.amf.SessionFilter.invoke( SessionFilter.java:44)

at flex.messaging.endpoints.amf.BatchProcessFilter.in voke(BatchProcessFilter.java:67)

at flex.messaging.endpoints.amf.SerializationFilter.i nvoke(SerializationFilter.java:146)

at flex.messaging.endpoints.BaseHTTPEndpoint.service( BaseHTTPEndpoint.java:278)

at flex.messaging.MessageBrokerServlet.service(Messag eBrokerServlet.java:322)

at javax.servlet.http.HttpServlet.service(HttpServlet .java:717)

at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:290)

at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:206)

at org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.java:233)

at org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.java:191)

at org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:128)

at org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:102)

at org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java:109)

at org.apache.catalina.connector.CoyoteAdapter.servic e(CoyoteAdapter.java:286)

at org.apache.coyote.http11.Http11AprProcessor.proces s(Http11AprProcessor.java:857)

at org.apache.coyote.http11.Http11AprProtocol$Http11C onnectionHandler.process(Http11AprProtocol.java:56 5)

at org.apache.tomcat.util.net.AprEndpoint$Worker.run( AprEndpoint.java:1509)

at java.lang.Thread.run(Unknown Source)



** END NESTED EXCEPTION **



Last packet sent to the server was 0 ms ago.; nested exception is com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: Software caused connection abort: socket write error

STACKTRACE:

java.net.SocketException: Software caused connection abort: socket write error

at java.net.SocketOutputStream.socketWrite0(Native Method)

at java.net.SocketOutputStream.socketWrite(Unknown Source)

at java.net.SocketOutputStream.write(Unknown Source)

at java.io.BufferedOutputStream.flushBuffer(Unknown Source)

at java.io.BufferedOutputStream.flush(Unknown Source)

at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2637)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:15 54)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java :1665)

at com.mysql.jdbc.Connection.execSQL(Connection.java: 3176)

at com.mysql.jdbc.PreparedStatement.executeInternal(P reparedStatement.java:1153)

at com.mysql.jdbc.PreparedStatement.executeUpdate(Pre paredStatement.java:1404)

at com.mysql.jdbc.PreparedStatement.executeUpdate(Pre paredStatement.java:1318)

at com.mysql.jdbc.PreparedStatement.executeUpdate(Pre paredStatement.java:1303)

at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement .executeUpdate(NewProxyPreparedStatement.java:105)

at org.springframework.jdbc.core.JdbcTemplate$3.doInP reparedStatement(JdbcTemplate.java:826)

at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:591)

at org.springframework.jdbc.core.JdbcTemplate.update( JdbcTemplate.java:824)

at org.springframework.jdbc.core.simple.AbstractJdbcI nsert.executeInsertAndReturnKeyHolderInternal(Abst ractJdbcInsert.java:421)

at org.springframework.jdbc.core.simple.AbstractJdbcI nsert.executeInsertAndReturnKeyInternal(AbstractJd bcInsert.java:402)

at org.springframework.jdbc.core.simple.AbstractJdbcI nsert.doExecuteAndReturnKey(AbstractJdbcInsert.jav a:356)

at org.springframework.jdbc.core.simple.SimpleJdbcIns ert.executeAndReturnKey(SimpleJdbcInsert.java:106)

at com.seventicks.re.dao.jdbc.LogMessageImpl.add(LogM essageImpl.java:110)

at com.seventicks.re.service.DefaultLogonServiceImpl. accessSystem(DefaultLogonServiceImpl.java:72)

at sun.reflect.GeneratedMethodAccessor126.invoke(Unkn own Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at flex.messaging.services.remoting.adapters.JavaAdap ter.invoke(JavaAdapter.java:421)

at flex.messaging.services.RemotingService.serviceMes sage(RemotingService.java:183)

at flex.messaging.MessageBroker.routeMessageToService (MessageBroker.java:1503)

at flex.messaging.endpoints.AbstractEndpoint.serviceM essage(AbstractEndpoint.java:884)

at flex.messaging.endpoints.amf.MessageBrokerFilter.i nvoke(MessageBrokerFilter.java:121)

at flex.messaging.endpoints.amf.LegacyFilter.invoke(L egacyFilter.java:158)

org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:128)

at org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:102)

at org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java:109)

at org.apache.catalina.connector.CoyoteAdapter.servic e(CoyoteAdapter.java:286)

at org.apache.coyote.http11.Http11AprProcessor.proces s(Http11AprProcessor.java:857)

at org.apache.coyote.http11.Http11AprProtocol$Http11C onnectionHandler.process(Http11AprProtocol.java:56 5)

at org.apache.tomcat.util.net.AprEndpoint$Worker.run( AprEndpoint.java:1509)

at java.lang.Thread.run(Unknown Source)



** END NESTED EXCEPTION **



Last packet sent to the server was 0 ms ago." faultCode="Server.Processing" faultDetail="null"]

[serjant]

Man use BasicDataSource, which will read the configs from the configration.propertie file, the use this dataSource whenever you want by calling it from the context:

 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
  		<property name="driverClassName"><value>${db.driverClassName}</value></property>
  		<property name="url"><value>${db.url}</value></property>
  		<property name="username"><value>${db.username}</value></property>
  		<property name="password"><value>${db.password}</value></property>
  </bean>

 	

  <bean id="placeholderConfig" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
  		<property name="location"><value>classpath:configuration.properties</value></property>
  </bean>

[serjant]

${db.driverClassName} = will indicate the proper JDBC driver in the configuration.properties file, the same with other properties

[kminev]

Thank you very much for your post.

Where do I place this line and what is the purpose of it?

<bean id="placeholderConfig" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
  		<property name="location"><value>classpath:configuration.properties</value></property>
  </bean>

Thanks

[kminev]

Here is some my new configuration:

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE beans PUBLIC   "-//SPRING//DTD BEAN//EN"
"springframework.org/dtd/spring-beans.dtd">
<beans>
    <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="location">
            <value>WEB-INF/jdbc.properties</value>
        </property>
    </bean>
    <!--
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
        <property name="driverClass" value="${db.driver}"/>
        <property name="jdbcUrl" value="${db.jdbcurl}"/>
        <property name="user" value="${db.username}"/>
        <property name="password" value="${db.password}"/>
        <property name="minPoolSize" value="0"/>
        <property name="maxPoolSize" value="15"/>
        <property name="initialPoolSize" value="3"/>
    </bean>
    -->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClass" value="${db.driver}"/>
        <property name="jdbcUrl" value="${db.jdbcurl}"/>
        <property name="user" value="${db.username}"/>
        <property name="password" value="${db.password}"/>
        <property name="minPoolSize" value="0"/>
        <property name="maxPoolSize" value="15"/>
        <property name="initialPoolSize" value="3"/>
    </bean>
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <bean id="authorizationDAO" class=" com.seventicks.re.dao.jdbc.AuthorizationImpl">
        <property name="transactionManager">
            <ref local="transactionManager"/>
        </property>
    </bean>
    <bean id="companyDAO" class=" com.seventicks.re.dao.jdbc.CompanyImpl">
        <property name="transactionManager">
            <ref local="transactionManager"/>
        </property>
    </bean>

[kminev]

Hopefully this will clear things out and help find the solution.

I tried switching to BasicDataSource in my config file, but now tomcat can't compile my jar with the update applicaiton context file. I am guessing the issue is in m transaction bean configuration as shown below.

Here is the config once again:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClass" value="${db.driver}"/>
        <property name="jdbcUrl" value="${db.jdbcurl}"/>
        <property name="user" value="${db.username}"/>
        <property name="password" value="${db.password}"/>
        <property name="minPoolSize" value="0"/>
        <property name="maxPoolSize" value="15"/>
        <property name="initialPoolSize" value="3"/>
    </bean>
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <bean id="authorizationDAO" class=" com.seventicks.re.dao.jdbc.AuthorizationImpl">
        <property name="transactionManager">
            <ref local="transactionManager"/>
        </property>
    </bean>
here I have bunch of other dao objects declaration nothing else

This is my implementation for my DAO objects:

public class CompanyImpl implements CompanyDAO {

    private static final String TABLENAME = "company";
    private DataSourceTransactionManager transactionManager;
    private SimpleJdbcTemplate simpleJdbcTemplate;
    private SimpleJdbcInsert insert;
    private DataSource ds;

    public void setTransactionManager(DataSourceTransactionManager transactionManager) {
        this.transactionManager = transactionManager;
        ds = transactionManager.getDataSource();
        simpleJdbcTemplate = new SimpleJdbcTemplate(ds);
        this.insert = new SimpleJdbcInsert(ds).withTableName(TABLENAME).usingGeneratedKeyColumns("objectId");
    }

    public Company getById(Long objId) {
        StringBuffer sb = new StringBuffer();
        sb.append(" AND c.objectId=:oid");

        MapSqlParameterSource msps = new MapSqlParameterSource();
        msps.addValue("oid", objId);

        List<Company> results = getCore(sb.toString(), msps);
        if (results.size() > 0) {
            return results.get(0);
        }
        return null;
    }

Any ideas!!! Thank you