java - Frequency of obtaining and releasing JDBC connections from ConnectionPools? -

i in process of refactoring legacy system has been designed in mid-1990s. days, jdbc connection scarce resource, there no reliable connection pool implementations , therefore connection held long possible. lead constructs these:

class clienthandler {   connection conn=drivermanager.createconnection(...);   statement stmt=conn.createstatement();   statement stmt2=conn.createstatement();    public replytype handlecommand(requesttype req) {     if (req.requesttype==requesttype.login)       return new loginmanager(stmt,stmt2).login(req.requestdata);     ...   } }  class loginmanager {   statement stmt,stmt2;   public loginmanager(statement stmtx,statement stmt2x) { stmt=stmtx; stmt2=stmt2x; }   public login(requestdata data) {     resultset rs=stmt.executequery("select count(*) users name="+data.getname()+" , pw="+data.getpassword());     if (!rs.next()) throw new illegalstateexception();     if (rs.getint(1)==0)       throw new whateverexception("error.wrongpassword");   } } 

this example show database connections in structure

• are opened early

• are kept open long times

• are passed around has access database

now, being in process of refactoring, want remove mode of operation. thinking of connection pooling , operation stateless possible, imagine remove every passing around of database connections , obtaining database connection (from pool) need them , throw them away (back pool) within same scope. same code above rewritten (java 7 try-with-resource style):

class clienthandler {    public replytype handlecommand(requesttype req) {     if (req.requesttype==requesttype.login)       return new loginmanager().login(req.requestdata);     ...   } }  class loginmanager {   public login(requestdata data) {     try (connection conn=staticconnectionpoolbridge.createconnection) {       resultset rs=conn.createstatement().executequery("select count(*) users name="+data.getname()+" , pw="+data.getpassword());       if (!rs.next()) throw new illegalstateexception();       if (rs.getint(1)==0)         throw new whateverexception("error.wrongpassword");     }   } } 

my question: sensible refactor way? mean, connection pools meant make database connection creation lightwight operation. so lightwight can , throw away in such fine-grained way? or should try restrict number of obtain-release cycles connection pool not-too-large number, e.g. moving around obtained connections , statements @ least (utility) methods? there rules of thumb working connection pools in such systems?

fwiw: backbone of whole system play 2.3 uses bonecp connection pool internally. of course, system in question features layers of abstraction in database access code left out here make example code simple possible.

obtaining , releasing connections connection pool lightweight because thing you're doing taking/putting connection in queue. wouldn't talking db @ in operations.