JDBC and Multithreading
Solution 1
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);
)
Solution 2
Don't use the same connection object in all threads. Give each thread a dedicated database connection.
Solution 3
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.
fanbondi
Researcher in Distributed Systems, Big Data and Cloud Computing Apache Gora PMC and Committer. Love playing and watching soccer. :)
Updated on June 12, 2022Comments
-
fanbondi almost 2 years
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);
-
fanbondi over 7 yearsI 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 over 7 yearsI will have a look at HirakiCP and let you know how it goes.
-
Asaph over 7 yearsI 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?
-
Tena over 7 yearsYou could also implement your own pool connection using a Singleton pattern, but I am not a big fan of reinventing the wheel:
-
fanbondi over 7 years@ Asaph, yeah that is a typo!
-
Tena over 7 yearsI edited my answer just in case you need a really simple implementation of a connection pool.
-
fanbondi over 7 yearsNice implementation. I almost have the same but it is using ArrayList. I have accepted your answer.
-
Tena over 7 yearsthanks! 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