CODEWITHSUNDEEP
javadesign-patternsjdbcconnection-pooling
Pawan
Pawan

Reputation: 32321

Why the Database connection is closed after every operation

I am using Connection pooling in TOmcat 6 and i have configued this way inside context.xml file 

<Resource name="jdbc/myoracle" auth="Container"
              type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
              url="jdbc:oracle:thin:@127.0.0.1:1521:ORCLE"
              username="scott" password="tiger" maxActive="20" maxIdle="10"
              maxWait="-1"/>

And this is my Factory class to obtain the Connection using DataSource 

public class ConnPoolFactory {
    private static DataSource dataSource;
    private static Connection connection;

    private ConnPoolFactory() {
    }

    public static synchronized Connection getConnection() throws SQLException {

        try {

            if (connection == null) {
                Context initContext = new InitialContext();
                Context envContext = (Context) initContext
                        .lookup("java:/comp/env");
                dataSource = (DataSource) envContext.lookup("jdbc/myoracle");
                connection = dataSource.getConnection();
            } else {
                return connection;
            }

        } catch (NamingException e) {
            e.printStackTrace();
        }

        return connection;

    }
}

And from my servlet inside finally block , i am closing it this way 

try {

connection = ConnPoolFactory.getConnection();
finally

{

if(conn!=null)
con.close();
}

From my User Interface i can give different commands (Button press) like Insert , Update , Delete , Select --.

The issue i am facing is that , the application runs only for one command , that is for example if i clicked Insert Button , it is inserting the record fine and after that , if once again i give any command there is a Exception on the Server console saying The Connection is closed .

If i remove that finally block code inside my servlet , then the application runs fine for any number of commands

Could anybody please let me know whats wrong with that finnaly block ??

Upvotes: 0

Views: 1364

Answers (2)

user207421
user207421

Reputation: 310850

The Connection variable in ConnPoolFactory should be method-local, not static. The variable you should be testing for null is not connection but dataSource. Once you have got a non-null value of that, you then return dataSource.getConnection(). The caller should then close that connection when he's finished with it.

Upvotes: 1

ptyx
ptyx

Reputation: 4164

Your factory is not aware that the connection is closed, and keeps handing it out. I'm assuming that's what you had in mind designing it.

You should either have your servlet turn back the connection after use, or the factory create a new connection every time.

Edit: trying to be more explicit:

Your servlet code will be called once per request. That's what servlet do. It seems to me that you close the connection after any request. Fine. However, your connection factory uses a static to store the connection it created. So on the second call, it will hand out a connection which has already been closed (in effect, it is a connection pool with a single connection rather than a factory).

BTW, you might encounter another nastier bug if you don't close your connection: you'll be sharing a connection between servlet threads if two requests come simultaneously, which might or might not work depending which DB operations you perform.

Upvotes: 0

Related Questions