JDBC and Multithreading

Question

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

SELECT * FROM TABLE WHERE ID = 123'

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 (rs.next()) {}
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

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());
        th.start();
        allThreads.add(th);
    }

    for(Thread t : allThreads){
        try {
            t.join();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }
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));
    } 
loop:
  MyThread bmthread = new MyThread(sqlConn.get(i), query);

Can you add a bit more info: what database are you using? How are you constructing the connection? How long does it take for just one query without all the thread stuff? — adamM, Aug 21 '16 at 18:26
I am using SQL server 2008. Without the thread stuff the code executes in a matter of milliseconds. Actually less than a second. — fanbondi, Aug 21 '16 at 18:31
[Is java.sql.Connection thread safe?](https://stackoverflow.com/questions/1531073/is-java-sql-connection-thread-safe) — Vadzim, Jun 12 '18 at 22:07

score 6 · Accepted Answer · edited Dec 31 '20 at 09:45

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, DBCP and similars are almost abandoned, I would use HikariCP 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) {
          pool.push(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);)

I will have a look at HirakiCP and let you know how it goes. — fanbondi, Aug 21 '16 at 19:06
You could also implement your own pool connection using a Singleton pattern, but I am not a big fan of reinventing the wheel: — Tena, Aug 21 '16 at 19:26
I edited my answer just in case you need a really simple implementation of a connection pool. — Tena, Aug 21 '16 at 19:39
Nice implementation. I almost have the same but it is using ArrayList. I have accepted your answer. — fanbondi, Aug 21 '16 at 19:47
thanks! Be careful using an ArrayList as it is not threadsafe, to make it so you need to synchronize also the collection with a `synchronized(list)` block — Tena, Aug 21 '16 at 19:56

score 3 · Answer 2 · answered Aug 21 '16 at 18:15

3

Don't use the same connection object in all threads. Give each thread a dedicated database connection.

answered Aug 21 '16 at 18:15

Asaph

159,146
25
197
199

I have used a different connection for each thread, see update in my question. It is now taken 33 seconds. However the same query takes 1 sec if I run in SQL server Management studio. – fanbondi Aug 21 '16 at 18:28
I noticed the query in your question has a trailing single quote in it. Is that a typo? Is the number in that query surrounded by single quotes? – Asaph Aug 21 '16 at 19:18
@ Asaph, yeah that is a typo! – fanbondi Aug 21 '16 at 19:27

score 1 · Answer 3 · answered Aug 21 '16 at 18:18

1

One Connection can only execute one query at a time. You need multiple connections available to execute database operations in parallel. Try using a DataSource with a connection pool, and make each thread request a connection from the pool.

answered Aug 21 '16 at 18:18

rohitvats

1,811
13
11

JDBC and Multithreading

3 Answers3

Linked