connection.createStatement in Spring-managed datasource never return

Question

This is very strange in my RESTful WS development. I am using Tomcat 7 with Jersey 1.8, Spring 2.5 and MySQL database.

I defined two data sources like this:

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean" scope="singleton">
  <property name="jndiName" value="jdbc/MAINDB" />
  <property name="resourceRef" value="true" />
</bean>
<bean id="orderDataSource" class="org.springframework.jndi.JndiObjectFactoryBean" scope="singleton">
  <property name="jndiName" value="jdbc/ORDERS" />
  <property name="resourceRef" value="true" />
</bean>

and inject one data source into the servlet context like this:

<bean class="org.springframework.web.context.support.ServletContextAttributeExporter">
  <property name="attributes">
    <map>
      <entry key="orderData">
        <ref bean="orderDataSource" />
      </entry>
    </map>
  </property>
</bean>

and this code:

orderDataSource = (DataSource) sc.getServletContext().getAttribute("orderData");
Order ord = new Order();
Statement stmt = null;
try {
    stmt = orderDataSource.getConnection().createStatement();
} catch (SQLException ex) {
    Logger.getLogger(OrdersResource.class.getName()).log(Level.SEVERE, null, ex);
}

gets executed 6 or 7 times flawlessly and from the 8th request, it gets stall at createStatement() line and never return or throw any exception!

However, all other resources API from MAINDB still works very well.

Anyone knows what's happening?

Answer 1

Why are you bothering with such code yourself? If you are using Spring already, use the JdbcTemplate instead. It can be initialized with your DataSource and will manage all that for you.

You are requiring a connection from the pool but you never closes it (same for the statement actually). Using JdbcTemplate prevents you from having those issues in the first place.

Something like

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.execute(...)

See the javadoc for more details.