Java thread safe database connections

Question

I'm writing a servlet that handles each request by accessing and modifying some table(s) in the database. I want the connections to the database to be thread safe. I don't want to use already existing libraries/frameworks for this (spring, hibernate, etc.).

I know I can use java's ThreadLocal for this in the following way :

public class DatabaseRegistry { //assume it's a singleton


private Properties prop = new Properties();
    public static final ThreadLocal<Connection> threadConnection = new ThreadLocal<Connection>();

    private Connection connect() throws SQLException {
        try {
            // This will load the MySQL driver, each DB has its own driver
            Class.forName("com.mysql.jdbc.Driver");
            // Setup the connection with the DB
            Connection connection = DriverManager
                    .getConnection("jdbc:mysql://" + prop.getProperty("hostname") + "/" + prop.getProperty("database") + "?"
                            + "user=" + prop.getProperty("username") + "&password=" + prop.getProperty("password"));
            return connection;
        } catch (SQLException e) {          
            throw e;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } 

        return null;

    }

    public Connection getConnection() throws SQLException {

        if(threadConnection.get() == null) {
            Connection connection = connect();
            threadConnection.set(connection);
            return threadConnection.get();
        } else
            return threadConnection.get();
    } 

    private void freeConnection(Connection connection) throws SQLException {
        connection.close();
        threadConnection.remove();
    }
}

Each time you call getConnection(), the new connection is added to the ThreadLocal object and then removed when you free the connection.

Is this the proper way of doing this or should the DatabaseRegistry itself extend the ThreadLocal class? Or is there an even better way to do this to make all connections thread safe?

Thanks

Answer 1

I know you said you don't want to use libraries to do this, but you're going to be way better off if you do. Pick a standard connection pool (C3P0, DBCP, or something) and you'll be way happier than if you bake your own. Why can't you use a library to do this?

Answer 2

I don't think that making database connections thread-safe is a common practice. Usually what you want is either:

• Serialize the access to some part of your servlet, so that there is no more than one servlet executing code at a time (e.g. implementing the SingleThreadModel interface).
• Locking a particular table / table page / row so you can operate on some particular tuple (by changing the database isolation level).
• Using optimistic locking to detect modified rows in a table (using some reference attribute of the table to check if the current version is the same that the one in the table).

AFAIK, the typical use of ThreadLocal<Connection> is to store a unique database connection per thread, so that the same connection can be used in different methods in your business logic without the need of passing it as a parameter each time. Because the common servlet container implementation uses a thread to fulfill an HTTP request, then two different requests are guaranteed to use two different database connections.

Answer 3

I am not sure why you want your DB connections to be thread safe. Most of the time establishing connection to the database is the longest part of the transaction. Typically connections are reused between requests and pools of open connections are managed (via frameworks or more typically application servers).

If you are worried about concurrent modifications to the same tables you might want to look at synchronized methods: http://docs.oracle.com/javase/tutorial/essential/concurrency/syncmeth.html