3 Replies - 386 Views - Last Post: 18 June 2020 - 07:55 AM Rate Topic: -----

#1 Soumikbhat   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 85
  • Joined: 01-September 13

JDBC and Connection Pooling

Posted 18 June 2020 - 12:12 AM

I am trying to implement connection pooling with Apache DBCP BasicDataSource and JDBC but running into some issues. Here's what I have so far.

I have got a class DatabaseHandler which is a singleton class.
  • I have a getHandler() method that returns me the object of this class.
  • I have a getDataSource() method that returns me a BasicDataSource Object.
  • Also, I have a getResultSet() method that takes a String parameter (SQL query) and returns me the resultset



This is what I have in my DatabaseHandler

private static BasicDataSource ds = null;
private BasicDataSource getDataSource() {
        if (ds != null) {
            return ds;
        }
        String url = "jdbc:postgresql://" + hostname + dbName;
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("org.postgresql.Driver");
        dataSource.setUrl(url);
        dataSource.setUsername(userName);
        dataSource.setPassword(password);
        // initial number of connection when the pool is created
        dataSource.setInitialSize(0);
        // maximum number of connection of the pool
        dataSource.setMaxOpenPreparedStatements(35);
        ds = dataSource;
        return dataSource;
    }

public ResultSet getResultSet(String query) {
        try {
            BasicDataSource dataSource = this.getDataSource();
            Connection conn = dataSource.getConnection();
            PreparedStatement ptstmnt = conn.prepareStatement(query);
            ResultSet results = ptstmnt.executeQuery();
            return results;
        } catch (SQLException e) {
            System.out.print(e.getMessage());
        }
        System.out.println("ERROR IN QUERY EXE ====== " + query);
        return null;
    }




Now in my View components, this is how I use my DatabaseHandler methods.

ResultSet results = DatabaseHandler.getHandler().getResultSet("select * from XYZ");
while(results != null && results.next()){
//do something
}
if(results != null) results.close();


The problem is when I start my server (Spring) and run a query through the view components for the first time it works fine. From the second time onwards I keep getting "Cannot get a connection, general error".

Some help in unblocking this will be highly appreciated, happy to provide more details as required.

This post has been edited by Soumikbhat: 18 June 2020 - 01:11 AM


Is This A Good Question/Topic? 0
  • +

Replies To: JDBC and Connection Pooling

#2 g00se   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3704
  • View blog
  • Posts: 16,980
  • Joined: 20-September 08

Re: JDBC and Connection Pooling

Posted 18 June 2020 - 03:44 AM

You should probably be calling close() on that Connection or it will not be returned to the pool for reuse and hence the pool will empty instead of refilling
Also, you could possibly just use a Postgres connection pooling datasource directly:
https://www.postgres...datasource.html
Was This Post Helpful? 0
  • +
  • -

#3 Soumikbhat   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 85
  • Joined: 01-September 13

Re: JDBC and Connection Pooling

Posted 18 June 2020 - 07:22 AM

Will check that dataSource link, thanks.

Connections.close() will probably have to be called from the function that calls getResultSet() since otherwise if I close connection in the getResultSet() method, postgress throws an exception "Connection already closed" when I am iterating through the returned resultset. The caller of getResultSet() does not have any reference for this conn object.

Or should I not keep a method like this at all, and just create the connection, iterate through the resultset to do my job, and then close the connection from the methods directly in the View modules?

This post has been edited by Soumikbhat: 18 June 2020 - 07:23 AM

Was This Post Helpful? 0
  • +
  • -

#4 g00se   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3704
  • View blog
  • Posts: 16,980
  • Joined: 20-September 08

Re: JDBC and Connection Pooling

Posted 18 June 2020 - 07:55 AM

Quote

Or should I not keep a method like this at all, and just create the connection, iterate through the resultset to do my job, and then close the connection from the methods directly in the View modules?

Probably ;) Or you could design it so that it returns an object that encapsulates both ResultSet and Connection then you can also call Connection.close(). Of course, with a connection pool, that doesn't actually close it, but it returns it to the pool
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1