fanbondi fanbondi - 1 year ago 185
Java Question

JDBC and Multithreading

I am trying to run few queries using a multithreaded approach, however I think I am doing something wrong because my program takes about five minute to run a simple select statement like


My implementation is below and I am using one connection object.

In my run method

public void run() {
runQuery(conn, query);

runQuery method

public void runQuery(Connection conn, String queryString){
Statement statement;
try {
statement = conn.createStatement();
ResultSet rs = statement.executeQuery(queryString);
while ( {}
} catch (SQLException e) {

Finally in the main method, I start the threads using the snippet below.

MyThread bmthread = new MyThread(conn, query);
ArrayList<Thread> allThreads = new ArrayList<>();
double start = System.currentTimeMillis();
int numberOfThreads = 1;
for(int i=0; i<=numberOfThreads; i++){
Thread th = new Thread(bmthread);
th.setName("Thread "+i);
System.out.println("Starting Worker "+th.getName());

for(Thread t : allThreads){
try {
} catch (InterruptedException e) {
double end = System.currentTimeMillis();
double total = end - start;
System.out.println("Time taken to run threads "+ total);

Update : I am now using separate connection for each thread.

ArrayList<Connection> sqlConn = new ArrayList<>();
for(int i =0; i<10; i++){
sqlConn.add(_ut.initiateConnection(windowsAuthURL, driver));
MyThread bmthread = new MyThread(sqlConn.get(i), query);

Answer Source

As rohivats and Asaph said, one connection must be used by one and only one thread, that said, consider using a database connection pool. Taking into account that c3p0 and similars are almost abandoned, I would use HirakiCP which is really fast and reliable.

If you want something very simple you could implement a really simple connection pool using a thread safe collection (such as LinkedList), for example:

 public class CutrePool{
      String connString;    
      String user;
      String pwd;

      static final int INITIAL_CAPACITY = 50;
      LinkedList<Connection> pool = new LinkedList<Connection>();
      public String getConnString() {
          return connString;
      public String getPwd() {
          return pwd;

      public String getUser() {
          return user;

      public CutrePool(String connString, String user, String pwd) throws SQLException {
          this.connString = connString;

          for (int i = 0; i < INITIAL_CAPACITY; i++) {
               pool.add(DriverManager.getConnection(connString, user, pwd));
          this.user = user;
          this.pwd = pwd;

      public synchronized Connection getConnection() throws SQLException {
          if (pool.isEmpty()) {
              pool.add(DriverManager.getConnection(connString, user, pwd));
          return pool.pop();

      public synchronized void returnConnection(Connection connection) {

As you can see getConnection and returnConnection methods are synchronized to be thread safe. Get a connection (conn = pool.getConnection();) and don't forget to return/free a connection after being used (pool.returnConnection(conn);)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download