JDBC and Multithreading
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);
)
Don't use the same connection object in all threads. Give each thread a dedicated database connection.
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.