Database connection management in Spring

Question

Do we have to explicitly manage database resources when using Spring Framework.. liking closing all open connections etc?

I have read that Spring relieves developer from such boiler plate coding...

This is to answer an error that I am getting in a Spring web app:

org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: ORA-00020: maximum number of processes (150) exceeded

The jdbcTemplate is configured in the xml file and the DAO implementation has reference to this jdbcTemplate bean which is used to query the database.

Answer 1

It could be due to connections not being closed. How are you accessing your connections within spring? Are you are using JdbcTemplate to query the database? Or just getting the connection from spring?

Answer 2

Do we have to explicitly manage database resources when using Spring Framework, like closing all open connections etc?

If you are using Spring abstraction like JbdcTemplate, Spring handles that for you and it is extremely unlikely that that there is a bug in that part.

Now, without more information on your configuration (your applicationContext.xml), on the context (how do you create your application context, when does this happen exactly?), it is a hard to say anything. So this is a shot in the dark: do you have the attribute destroy-method="close" set on your datasource configuration? Something like that:

 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">

In certain circumstances, not using the destroy-method combined with some other bad practices may eventually end up with exhausting resources.

Answer 3

I have read that Spring relieves developer from such boiler plate coding

That depends which level of Spring you operate at. JdbcTemplate provides many different operations, some of which are fire-and-forget, some of which still require you to manage your JDBC resources (connections, resultsets, statements, etc) properly. The rule of thumb is that if you find yourself calling getConnection(), then at some point you need to call releaseConnection() also.

ORA-00020: maximum number of processes (150) exceeded

Are you using a connection pool? If so, then make sure that it isn't configured with a larger number of max connections than your database is capable of handling (150, in this case). If you're not using a connection pool, then you really, really should be.

Answer 4

you say "The jdbcTemplate is configured in the xml file". You should normally create a new instance of the jdbcTemplate for each usage, not have it managed by spring.

I would guess that each time you request a new jdbcTemplate bean from spring, it is creating a new one with a new connection to the database, but after it falls out of scope in your code it is still referenced by spring's applicationContext, and so does not close the connection.

Answer 5

Hi all,

I have the same problem with DB connections. How I can solve it? Any idea? My pool connection don't work well.

I have Java Web application, that I invoke with Java Web Start. The application is configure with Spring+Hibernate+MySQL. I Have problem with DB connection. I have probed with dbcp, c3p0 and finally with bonetcp, but always it generate more conection than maxConnection configure.

My applicationContext.xml content is:

… .hbm.xml -->

<!-- Transaction manager for a single JDBC DataSource -->
<bean id="transactionManager"
    class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource" />
</bean>
<!--
    ========================= DAO DEFINITIONS =========================
-->
<bean id="functionsDao" class="….model.dao.TSysFunctionsDao">
    <property name="dataSource" ref="dataSource" />
</bean>
…

DAO class example:

import java.sql.Connection; import java.sql.PreparedStatement; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.HashSet; import java.util.List; import java.util.Map;

import javax.naming.InitialContext; import javax.sql.DataSource;

public class TSysFuncionsDao {

  private DataSource dataSource;

  public void setDataSource(DataSource dataSource) {
      this.dataSource = dataSource;
  }


  public TDsDsourceDao() {
  }

  public Response getTSysFunctionssList(GridParams gridParams) throws DataAccessException {
        Connection conn = null;
        try {
          conn = dataSource.getConnection();
        …
          return res; 
        }
        catch (Exception ex) {
          return new ErrorResponse(ex.getMessage());
        }
        finally {
          try {
            conn.close();               
          }
          catch (Exception ex1) {
          }
        }
  }

}

I think that the system not release the close data source connection. Any Idea? How I can solve it?

Thanks in advances.