Page History

...

Unfortunately database connections, like sockets, are not inherently interruptible. So this design does not permit a client to cancel a task by closing it if the corresponding thread is blocked on a long running activity such as a join query. Furthermore, it is important to provide a mechanism to close connections to prevent thread starvation caused because of the limited number of database connections available in the pool. Similar task cancellation mechanisms are required when using objects local to a method, such as sockets.

Compliant Solution (ThreadLocal)

This compliant solution uses a ThreadLocal wrapper around the connection so that a thread that calls initialValue() obtains a unique connection instance. The advantage of this approach is that a shutdownConnection() method can be provided so that clients external to the class can also close the connection when the corresponding thread is blocked, or is performing some time consuming activity.

class DBConnector implements Runnable {
  final String query;
  
  DBConnector(String query) {
    this.query = query;
  }
	
  private static ThreadLocal<Connection> connectionHolder = new ThreadLocal<Connection>() {
    Connection connection = null;
    @Override public Connection initialValue() {		
      try {
        // Username and password are hard coded for brevity
	connection = DriverManager.getConnection
          ("jdbc:driver:name", "username","password");  	    	      
      } catch (SQLException e) {
        // Forward to handler
      }
      return connection;
    }
		
    @Override public void set(Connection con) {
      if(connection == null) { // Shuts down connection when null value is passed 		       
        try {
          connection.close();
        } catch (SQLException e) {
          // Forward to handler 
        }	       		       
      } else {
        connection = con; 
      }
    }
  };

  public static Connection getConnection() {
    return connectionHolder.get();
  }

  public static void shutdownConnection() { // Allows client to close connection anytime
    connectionHolder.set(null);
  }

  public void run() {
    Connection dbConnection = getConnection();
    Statement stmt;
    try {
      stmt = dbConnection.createStatement();		
      ResultSet rs = stmt.executeQuery(query);
    } catch (SQLException e) {
      // Forward to handler	 
    }    
    // ...
  }

  public static void main(String[] args) throws InterruptedException {
    DBConnector connector = new DBConnector(/* suitable query */);
    Thread thread = new Thread(connector);
    thread.start();
    Thread.sleep(5000);
    connector.shutdown();
  }
}

Noncompliant Code Example (database connection)

...

Unfortunately database connections, like sockets, are not inherently interruptible. So this design does not permit a client to cancel a task by closing it if the corresponding thread is blocked on a long running activity such as a join query. Furthermore, it is important to provide a mechanism to close connections to prevent thread starvation caused because of the limited number of database connections available in the pool. Similar task cancellation mechanisms are required when using objects local to a method, such as sockets.

Compliant Solution (ThreadLocal)

This compliant solution uses a ThreadLocal wrapper around the connection so that a thread that calls initialValue() obtains a unique connection instance. The advantage of this approach is that a shutdownConnection() method can be provided so that clients external to the class can also close the connection when the corresponding thread is blocked, or is performing some time consuming activity.

...