Database connection management in Spring

Do we have to explicitly开发者_如何学Go 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.

---

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.

---

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?

---

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.

---

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.

---

My hosing, provide only 20 connection. I done by manually close the connection on every request to db. I not declared a destory-method in bean(this not worked "i dont know why"), but i done in every requst call. (Hint : extends JdbcDaoSupport in dao class).

public void cleanUp() {
        try {
            if (!this.getJdbcTemplate().getDataSource().getConnection().isClosed()) {
                this.getJdbcTemplate().getDataSource().getConnection().close();
            }
        } catch (Exception e) {
            Logger.getLogger(myDAOImpl.class.getName()).log(Level.SEVERE, null, e);
        }
    }

IMPORTANT: Here I mention that, what I have done to solve my problem. You should not use this code directly. You should use only one connection. Alter if as per your code.