Ideally How many connections I have to open ?

Question

Hi All,

Recently I attended interview in java, the interviewer asked a question like below:

I have a request which go throgh A,B,C modules and response go back throgh A , in module A I need to talk to database and again in module C I need to talk to database, so in this situation how many connections you will open and where do you close those connections?

My Answer: I said that in module A I will open a connection and I will close it then and there, then control go to module B then module C, in module C again I will open one more connection and i will close it again. then he asked me again another question I want to open one connection per one request processing, how can i do this?

Answer 1

Sharing the connection in the modules.

EDIT You can share the connection by passing it to the different modules:

class ModuleA {
    void process(){
        Connection c = getConnection();
        doA();
        new ModuleB( c ).doBs();
        new ModuleC( c ).doCs();
        doSomeMoreA();
        c.close();
    }
}

You can also have the connection in a external class and search for it when needed ( perhaps using a sessionid )

I'm not sure if this counts as "Connection pooling"

Here's it anyway.

class Db {
    private static Map<Integer, Connection> map;

    public static Connection getConnection( int sessionId ){

        if( !map.containsKey( sessionId ) ) {
            map.put( sessionId, createConnection());
        }
        return map.get( sessionId );
    }
}
class Main {
    void processs() {
        int sessionId = createSesionId();
        ModuleA a = new ModuleA( sessionId );
        a.doAsStuff();
        ModuleB b = new ModuleB( sessionId );
        b.doBsStuff();
        ModuleC c = new ModuleC( sessionId );
        b.doCsStuff();
        a.closeTransaction();

    }
}
class ModuleA{

    public doAsStuff() {
        Connection c = Db.getConnection(sessionId);
        doSomethingWith( c );
    }
    public closeTransaction() {
        Connection c = Db.getConnection(sessionId);
        c.close();
    }
}
class ModuleB{

    public doBsStuff() {
        Connection c = Db.getConnection(sessionId);
        doSomethingWith( c );
    }
}
class ModuleC{

    public doCsStuff() {
        Connection c = Db.getConnection(sessionId);
        doSomethingWith( c );
    }
}

Answer 2

For example by using an ORM solution like Hibernate, you can keep the current session (which is sort of the higher level Hibernate equivalent of connection) open throughout the whole request lifetime, and use it in all modules.

Answer 3

Use a database connection pool such as apache dbcp http://commons.apache.org/dbcp/

Answer 4

I would guess the answer he was looking for was to use a ThreadLocal and terminate it in a filter at the end of the request, but it's tough to decide on the "best" solution without asking more questions about the architecture said modules area wrapped up in. (and even the in context meaning of the word module.)

Answer 5

"Ideally How many connections I have to open ?"

Its very simple, open one connection per request ( no matter how many modules it goes through ) and then close the connection when the request is complete.

"then he asked me again another question I want to open one connection per one request processing, how can i do this?"

Make a Filter (request interceptor) that opens a connection before a request is processed, and closes afterward. Keep connection in a static ThreadLocal instance, so that it can be shared within current thread. Don't use simple static Connection, otherwise it will be shared across all your threads and render undesired results.