Implementing Connection Pooling : Java
Asked Answered
B

4

20

In one of the interviews that I faced,I was asked to implement connection pooling. So approach was this:

  1. Create a List or HashMap
  2. Create predefined number of connections
  3. Add them to the collection.
  4. Now when the ConnectionImpl getConnection() method of ConnectionPoolingImpl class is invoked return a connection reference.

Now when someone returns the connection (releaseConnection(ConnectionImpl O)) how can I ensure that when the same application again tries to reuse the connection object, my implementation throws an exception?

The same connection object might have been returned to a new application and that should be able to use it.

My point of view would be to maintain a flag variable in another array kind of structure for each Connectionimpl object and set that variable to a valid value. When user returns the connection object I would make that some invalid value. For every operation in my ConnectionImpl, I will have to verify if the user had a valid flag.

What would you say to that approach?

Bigener answered 6/7, 2011 at 10:59 Comment(7)
I would try to use proper English for a start. ;) The problem with a flag is that the connection could be valid as it is being used by another thread when you attempt to reuse it.Molybdate
Now .. u need better than the above detailed explanation .. :-) .. it wud be an article not a question ..Bigener
@Joachim, has done a good job of translating into English without turning it into an article. ;) I have explained why the approach wouldn't work. Have you any other suggestions as to how the problem could be solved?Molybdate
The outline of my solution: never return the actual connection, always return a wrapper. And return each wrapper object only once.Atlante
@Joachim sourcemaking.com/design_patterns/object_pool/java# .. its implemented here ,but the connection object is returned directly without any wrapper .. In this case I think the user can reuse the connection even after releasing it ?Bigener
@whokares: that is correct. It's not a good implementation of a connection pool. By the way: unless you're doing this as homework and/or for learning, I would suggest you don't implement your own connection pool, but use an existing one instead (such as Apache DBCP, or c3p0).Atlante
@Joachim .thanks for the info btw this is just to understand the internals and not for any custom use ..CheersBigener
D
27

I would not return the "real" connection object from the pool, but a wrapper which gives the pool control of connection life cycle, instead of the client.

Assume you have a really simple connection, which you can read int values from:

interface Connection {
    int read(); // reads an int from the connection
    void close(); // closes the connection
}

An implementation reading from a stream could look like this (ignoring exceptions, EOF handling, etc):

class StreamConnection implements Connection {
    private final InputStream input;
    int read(){ return input.read(); }
    void close(){ input.close(); }
}

Furthermore, let's assume you have a pool for StreamConnections that looks like this (again, ignoring exceptions, concurrency, etc):

class StreamConnectionPool {
    List<StreamConnection> freeConnections = openSomeConnectionsSomehow();
    StreamConnection borrowConnection(){ 
        if (freeConnections.isEmpty()) throw new IllegalStateException("No free connections");
        return freeConnections.remove(0); 
    }
    void returnConnection(StreamConnection conn){
        freeConnections.add(conn);
    }
}

The basic idea here is OK, but we can't be sure the connections are returned, and we can't be sure they aren't closed and then returned, or that you don't return a connection which came from another source altogether.

The solution is (of course) another layer of indirection: Make a pool which returns a wrapper Connection which, instead of closing the underlying connection when close() is called, returns it to the pool:

class ConnectionPool {

    private final StreamConnectionPool streamPool = ...;

    Connection getConnection() {
        final StreamConnection realConnection = streamPool.borrowConnection();
        return new Connection(){
            private boolean closed = false;
            int read () {
                if (closed) throw new IllegalStateException("Connection closed"); 
                return realConnection.read();
            }
            void close() {
                if (!closed) {
                    closed = true;
                    streamPool.returnConnection(realConnection);
                }
            }
            protected void finalize() throws Throwable {
                try {
                    close();
                } finally {
                    super.finalize();
                }
            }
        };
    }

}

This ConnectionPool would be the only thing the client code ever sees. Assuming it is the sole owner of the StreamConnectionPool, this approach has several advantages:

Reduced complexity and minimal impact on client code - the only difference between opening connections yourself and using the pool is that you use a factory to get hold of Connections (which you might already do, if you're using dependency injection). Most importantly, you always clean up your resources in the same way, i.e., by calling close(). Just like you don't care what read does, as long as it gives you the data you need, you don't care what close() does, as long as it releases the resources you've claimed. You shouldn't have to think whether this connection is from a pool or not.

Protection against malicious/incorrect usage - clients can only return resources they've retrieved from the pool; they can't close the underlying connections; they can't use connections they've already returned... etc.

"Guaranteed" returning of resources - thanks to our finalize implementation, even if all references to a borrowed Connection is lost, it is still returned to the pool (or does at least stand a chance to be returned). The connection will of course be held longer than necessary this way - possibly indefinitely, since finalization isn't guaranteed to ever run - but it's a small improvement.

Descender answered 6/7, 2011 at 11:54 Comment(7)
Anywayz we wont close the connection as its a pool except in rare cases .. But how are u handlng the logic() where in you are revoking the acces to a connection object once its returned ,,,,Bigener
It's in read(), the part that says if (closed) throw new IllegalStateException("Connection closed"). Basically, this is your idea of flags checking if the connection is returned or not, just that it happens to be in another place (the wrapper instead of the pool).Descender
The other problem wud be that when your are returning a wrapper ,you will have to mock all the methods of the real connection object by overiding,,which is a little cumbersome....Bigener
Can be cumbersome, but it's also the only safe thing to do.Descender
I think this wud not work as .. there will be many threads accessing the connection objects .. so after a thread releases a connection object if you set closed = true,how will the other thread once it acquires the connection be able to read ..and you are anywayz creating new connection objects than reusing ..Bigener
There is a new wrapper created every time you borrow a connection. We don't care about the wrappers, they are ridiculously cheap to create. The underlying connections we do care about, and they are reused. I'd advise you to play around with my code for a bit, get it running in a debugger and see how it works in runtime.Descender
As @whokares points out correctly, the solution above ignores concurrency issues.Olympie
S
6

I'd just tell them I'd use the JdbcConnectionPool class (here) that comes with H2 (you can probably copy it out). Screw trying to implement one :) It could be a trick question.

Secretin answered 6/7, 2011 at 11:56 Comment(0)
S
3

ConnectionPool implemenation

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;



/** A Connection Pool with 5 Available Connections **/
class ConnectionPool {

    private List<Connection>availableConnections = 
                                new ArrayList<Connection>();
    private List<Connection>usedConnections = new ArrayList<Connection>();
    private final int MAX_CONNECTIONS = 5;

    private String URL;
    private String USERID;
    private String PASSWORD;


    /** Initialize all 5 Connections and put them in the Pool **/
    public ConnectionPool(String Url, String UserId, String password)
            throws SQLException {
        this.URL = Url;
        this.USERID = UserId;
        this.PASSWORD = password;

        for (int count = 0; count <MAX_CONNECTIONS; count++) {
            availableConnections.add(this.createConnection());
        }

    }






/** Private function, 
    used by the Pool to create new connection internally **/

    private Connection createConnection() throws SQLException {
        return DriverManager
                .getConnection(this.URL, this.USERID, this.PASSWORD);
    }




    /** Public function, used by us to get connection from Pool **/
    public Connection getConnection() {
        if (availableConnections.size() == 0) {
            System.out.println("All connections are Used !!");
            return null;
        } else {
            Connection con = 
            availableConnections.remove(
                availableConnections.size() - 1);
            usedConnections.add(con);
            return con;
        }
    }



    /** Public function, to return connection back to the Pool **/
    public boolean releaseConnection(Connection con) {
        if (null != con) {
            usedConnections.remove(con);
            availableConnections.add(con);
            return true;
        }
        return false;
    }





    /** Utility function to check the number of Available Connections **/
    public int getFreeConnectionCount() {
        return availableConnections.size();
    }
}
Sanctum answered 27/5, 2018 at 7:40 Comment(0)
E
0

OK, so if I understand correctly, your question is basically "how can we can ensure that a thread doesn't return a connection to the pool and then carry on using it?". Provided you don't pass back the "raw" Connection object to the caller, then the answer is essentially "you can put some control in somewhere if you want".

The actual check could involve marking each connection with which Thread "owns" it at a given moment, then making sure this is always Thread.currentThread() during any call to use the connection.

It doesn't matter terribly much what object you do pass back to user of the connection to represent the connection: it could be your own wrapper implementation of Connection, or just some other wrapper object with your methods for executing queries. Whichever you use, you just need to make the abovementioned check before executing any query. Bear in mind that for security you generally shouldn't be allowing "raw" arbitrary SQL to be executed, but that all queries should be based on a well-defined PreparedStatement. So there's no particular compulsion to return an actual implementation of Connection, other than this might in some circumstances help you migrate existing code (and/or if you've decided you really do want to permit execution of arbitrary SQL).

In many circumstances, you could also not bother making this check. You're passing a caller a means to access your database, so it's a bit like trying to stop pilots from crashing planes into buildings by scanning them for explosives at airports: they all ready have a means of messing up your system whether or not you make the additional check.

Ensor answered 6/7, 2011 at 12:22 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.