java - Proper Implementation of JDBC Multithreading with JNDI -

i'm working on project i'm doing lot of queries , time consideration want try , implement jdbc multithreading. i'm not sure proper way is.

here's first draft implementation:

spring datasource bean:

private datasource ds; @resource(name="jdbc") public void setdatasource(datasource ds) {     this.ds = ds; } 

initialization method:

  public void checkusersmulti(list<user> users) throws exception {     if(users!= null || users.size() != 0) {         executorservice executorservice = executors.newfixedthreadpool(20);         queue<user> queue = new concurrentlinkedqueue<>();         queue.addall(users);         (useruser: users) {             executorservice.submit(new processuser(queue));         }         executorservice.shutdown();         try {             executorservice.awaittermination(1, timeunit.hours);         } catch (interruptedexception e) {             e.printstacktrace();         }      } } 

runnable class:

class processuser implements runnable {     private final queue<user> queue;      public processuser(queue<user> queue) {         this.queue = queue;     }      public void run() {         try {             connection conn = ds.getconnection();             user user = null;             while((user = queue.poll()) != null) {                 userdao.getuser(user , conn));             }             dbutils.closequietly(conn);         } catch (exception e) {             e.printstacktrace();         }     } } 

user dao method:

public user retrieveuser(user user, connection conn)  {     preparedstatement st = null;     resultset rs = null;     try {         string sql = "select firstname, lastname users id= ?;          st = conn.preparestatement(sql);         st.setstring(1, user.getid());         rs = st.executequery();          while(rs.next()) {             user.setfirstname(rs.getstring("firstname"));             user.setlastname(rs.getstring("lastname"));         }     } catch (exception e) {         return null;     }     {         dbutils.closequietly(rs);         dbutils.closequietly(st);     }     return user; } 

update: tomcat jndi connection pool settings:

 <resource     name="jdbc"     auth="container"     type="javax.sql.datasource"     maxtotal ="25"     maxidle="30"     maxwaitmillis ="10000"     driverclassname="***vendor driver***"     url="***valid url"     username="***valid username***"     password="***valid password***" /> 

however, getting error occasionally:

java.sql.sqlexception: cannot connection, pool error timeout waiting idle object

but process still completes expected - may in jndi settings. bigger concern if "correct" way implement this. assumed best hold onto connection object don't have re-initialized.

there couple of issues code, importantly, not sure this:

   public void checkusersmulti(list<user> users) throws exception {     if(users!= null || users.size() != 0) {         executorservice executorservice = executors.newfixedthreadpool(20);         queue<user> queue = new concurrentlinkedqueue<>();         queue.addall(users);         (useruser: users) {             executorservice.submit(new processuser(queue));         }         executorservice.shutdown();         try {             executorservice.awaittermination(1, timeunit.hours);         } catch (interruptedexception e) {             e.printstacktrace();         }      } } 

is supposed do. if understand correctly, create queue of users in it, , submit executor many times there users. mean that:

• you limited in terms of number of users can process, because queue can not grow indefinitely (which @sabit khan worried about)

• you have run runnable @ least once each user, although unless runnables exit because of error, first 20 keep running until queue empty, , remaining invoked, see queue empty, , exit.

you should limit number of parallel processes starting. have thread pool of 20 though, there should never more 20 concurrent connections, if close connections properly. don't mention other exceptions, this:

    try {         connection conn = ds.getconnection();         user user = null;         while((user = queue.poll()) != null) {             userdao.getuser(user , conn));         }         dbutils.closequietly(conn);     } catch (exception e) {         e.printstacktrace();     } 

should be:

    connection conn = null;     try {         conn = ds.getconnection();         user user = null;         while((user = queue.poll()) != null) {             userdao.getuser(user , conn));         }     } catch (exception e) {         e.printstacktrace();     } {         dbutils.closequietly(conn);     } 

just make sure connection closed in situation.

also, since you're apparently using underlying connection pool, might want try getting , returning connections every single time.

as actual question: way you're doing means first twenty runnables try connections , hold them long time. if pool configured use provide less connections (let's 15), happen, since first 15 connection, next 5 workers continously run runnable, try, fail, exit. or of use 20 connections in pool, might leaking connections because you're not closing them described above.