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.