jdbc - Creating Connection Pool In Standalone Java Application -

i need use connection pool in standalone (as in non-web) java application. work, not allowed use apis without going through layers of security, , job needs completed soon. below attempt @ creating connection pool.

i have unit tested code , tested within context of overall application hundred times , in cases tests passed 0 errors, , in addition performance of each run under 3 thousand times faster simple connect, retrieve data, disconnect in serial approach; however, still have nagging concerns there issues approach haven't unearthed yet. appreciate advice has concerning below code. first post on site; please let me know if i've made errors in etiquette. did search site problem before posting. please see below code invocation example. thanks. --jr

package mypackage;  import java.sql.connection; import java.sql.sqlexception; import java.util.map; import java.util.concurrent.arrayblockingqueue; import java.util.concurrent.blockingqueue; import java.util.concurrent.timeunit;  /**  * note: class instantiated once per application run.  *       multiple instantiations, specified in release notes,  *       not supported.        */ public class connectionmanager {      // use blocking queue store database connections.     // application called once, single user,     // within application many threads require      // connection.      private blockingqueue<connection> connectionqueue = null;      // load connection queue user-defined number of connections.     // params contains map of non hard-coded variables in     // application.     public connectionmanager(int howmany, map<string, object> params) {         database database = new database();         connectionqueue = new arrayblockingqueue<connection>(howmany);         for(int = 0; < howmany; i++) {             connectionqueue.add(database.getconn(params));         }     }      // return connection queue, waiting 15 minutes so.     // 15 minutes hard-coded because standard time-out     // processes @ our agency.  application must complete in less     // fifteen minutes (is completing in thirty 5 seconds).     public connection getconnection() {         connection conn = null;         try {             conn = connectionqueue.poll(15, timeunit.minutes);         }         catch(interruptedexception e) {             e.printstacktrace();         }         catch(sqlexception e) {             e.printstacktrace();         }         return conn;     }      // returns connection connection queue.       public void returnconnectiontomanager(connection conn) {         connectionqueue.add(conn);     }      // called on last line of application program's dispatcher.     // closes active connections (which exist if there     // failure within 1 of worker threads).     public void closeallconnections() {         for(connection conn : connectionqueue) {             try {                 conn.close();             }             catch(sqlexception e) {                 e.printstacktrace();             }         }     } } 

invocation example:

...

private connectionmanager cm; 

...

public table(map<string, object> params, string method) {   ...   cm = (connectionmanager) params.get("cm"); }  // execute chunk of sql code without requiring processing of  // result set.  acquires connection pool via cm.getconnection // , releases connection via cm.returnconnectiontomanager. // (database helper class simple methods  // closing prepared statement, result sets, etc.)   private void execute(string sql) {   preparedstatement ps = null;   connection conn = null;   try {     conn = cm.getconnection();     ps = conn.preparestatement(sql);     ps.execute();   }   catch (sqlexception e) {     e.printstacktrace();   }   {     database.closepreparedstatement(ps);     cm.returnconnectiontomanager(conn);    } } 

your code looks good, there 1 serious problem, clients of api needs take care of getting , releasing connection, 1 of them forget, , memory/resource leak ready.

make 1 place in posts queries execute, in place take connection, execute query , return connection pool. secure connections returned. if need invoke multiple queries 1 after in single connection make method accept array or list of sql queries execute in order. idea encapsulate each request db, manage connections. donethat write interface has en execute(connection conn) need implement, , have service takes such object gives connection , releases resources connection pool. like:

interface sqlwork {  execute(connection conn); }  sqlwork mywork = new sqlwork () {   execute(connection conn) {     // work conn here   } }  class sqlexecutionservice {    connectionmanager cm = ...;     public void execute(sqlwork sqlwork) {        connection conn = null;        try {          conn = cm.getconnection();          sqlwork.execute(conn);        } catch (your exceptions here) {          //serve or rethrow them        }                {          if (conn!=null) {             cm.returnconnectiontomanager(conn);           }        }    } } 

example of use:

sqlexecutionservice sqlexecservice = ...; sqlexecservice.execute(mywork);