Recently I was working on a small console application with a few threads that concurrently updated the database. I had to make things transactional and separated so each thread had to have it’s own connection.
ThreadLocal to the rescue:
package electro.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* Thread local database connection.
* Main thread is expected to call init with database connection parameters.
*
* @author Viktoras Agejevas
*
*/
public class ThreadConnection {
private static ThreadLocal<Connection> connection = new ThreadLocal<Connection>();
private static String user;
private static String pass;
private static String url;
private static String driver;
private static boolean initialized = false;
/**
* Initializes ThreadConnection with connection data.
*
* @param user database username
* @param pass database password
* @param url database connection jdbc url
* @param driver database driver name
*/
public static void init(String driver, String url, String user, String pass) {
ThreadConnection.user = user;
ThreadConnection.pass = pass;
ThreadConnection.url = url;
ThreadConnection.driver = driver;
ThreadConnection.initialized = true;
}
/**
* Gets connection associated with current thread.
*
* If currently associated connection is closed, recreates a new connection.
*
* @return database connection bound to this thread
* @throws SQLException if database access error occurs
* or db driver is missing
*/
public static Connection getConnection() throws SQLException {
if (!initialized) {
throw new IllegalStateException(
"ThreadConnection is not initialized");
}
if (connection.get() == null || connection.get().isClosed()) {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
throw new SQLException(
"Can't find db driver: " + e.getMessage(), e);
}
connection.set(DriverManager.getConnection(url, user, pass));
}
return connection.get();
}
}
At the application entry I just init the database connection parameters and after that every thread can access it’s connection:
package electro.jdbc;
import java.sql.Connection;
import java.sql.SQLException;
public class Runner {
public static void main(String[] args) throws SQLException {
ThreadConnection.init("org.hsqldb.jdbcDriver", "jdbc:hsqldb:test", "sa", "");
Connection connection = ThreadConnection.getConnection();
// main thread's connection
new Thread(new Runnable() {
@Override
public void run() {
try {
Connection connection = ThreadConnection.getConnection();
// this thread's connection
} catch (Exception e) {
e.printStackTrace();
}
}
}).start();
}
}
Hi!
I am not sure, but looks to me like a not the best idea: it seems to be not a thread-safe at least from what I see. While getConnection() is thread-safe at DriverManager, the rest of your code is not: i.e. threads are hammering the same channel with zero isolation. Since embedded mode of HSQLDB does not supports multiple connections, you should design your app in quite different way, I think.
Also I recommend you move to H2 instead of HSQLDB. :-)
Hi, BM,
Thanks for your comment, HSQLDB is there only for the example, in real application Oracle is being used.
I guess I’ll have to fix the example not to be misleading, I didn’t knew that embedded HSQLDB does not support multiple connections.
For Oracle? Then implement javax.sql.DataSource instead. Basically what you should do is to wrap your connection and make sure it is not in use by some thread. If there is newer request for a connection — either return cached that is free of use at the moment or allocate new one. Also you need one more thread running forever (make sure you tick 150-200 microseconds, otherwise your CPU resources “gone”). Use it to rip timed-out threads — use the same connection wrapper to see its timestamp when it was last used.
HSQLDB: yes, *does* supports multiple connections, but only in *server* mode. However, in your case you use embedded mode by a specific JDBC URL. So you have to use different connection URL and run HSQLDB in server mode. And H2 is the same HSQLDB by same author, just its second incarnation: faster, more reliable and has much more features. Also you can use PostgreSQL JDBC driver to connect to H2.
Thanks for your tips ;)