Should I use ThreadLocal to store connections?

I code my Test project and it is prohibited to use Spring and Hibernate there.

I wanted to manage my transactions from Service layer. For this I have created a class that gets a Connection from the pool and puts it in the ThreadLocal.

This is an example of the fields and the method.

  private static ThreadLocal<Connection> threadLocalConnection;
  private ComboPooledDataSource comboPooledDataSource ;

  public boolean createConnectionIfAbsent() {

    boolean isConnectionCreated = false;

    try {
      Connection currentConnection = threadLocalConnection.get();
      if(currentConnection == null) {
        Connection conn = this.comboPooledDataSource.getConnection();
        conn.setAutoCommit(false);
        threadLocalConnection.set(conn);
        isConnectionCreated = true;
      }

    } catch (SQLException e) {
      e.printStackTrace();
    }
    return isConnectionCreated;
  }

JavaScript
​x
 
  private static ThreadLocal<Connection> threadLocalConnection;  private ComboPooledDataSource comboPooledDataSource ;​  public boolean createConnectionIfAbsent() {​    boolean isConnectionCreated = false;​    try {      Connection currentConnection = threadLocalConnection.get();      if(currentConnection == null) {        Connection conn = this.comboPooledDataSource.getConnection();        conn.setAutoCommit(false);        threadLocalConnection.set(conn);        isConnectionCreated = true;      }​    } catch (SQLException e) {      e.printStackTrace();    }    return isConnectionCreated;  }​

The class has also close, rollback methods.

Here is the example of how I manage Connections in a Service Layer.

public BigDecimal getTotalOrdersCount() {
    boolean connectionCreated = DBManager.getInstance().createConnectionIfAbsent();

    BigDecimal ordersCount = BigDecimal.ZERO;
    try {
        ordersCount = orderDao.getRowNumber();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    } finally {
        if (connectionCreated) DBManager.getInstance().closeConnection();
    }
    return ordersCount;
}

JavaScript
 
public BigDecimal getTotalOrdersCount() {    boolean connectionCreated = DBManager.getInstance().createConnectionIfAbsent();​    BigDecimal ordersCount = BigDecimal.ZERO;    try {        ordersCount = orderDao.getRowNumber();    } catch (SQLException throwables) {        throwables.printStackTrace();    } finally {        if (connectionCreated) DBManager.getInstance().closeConnection();    }    return ordersCount;}​

Dao just uses this to get the connection.

Connection connection = DBManager.getInstance().getConnection();

JavaScript
 
Connection connection = DBManager.getInstance().getConnection();​

I found no other way to manage connections in a Servlet project from a Service layer, could you please tell if it is ok? If not – what drawbacks does it have and what should I use instead.

UPD:

Please pay attention to this Service method. Let’s assume that Each method in DAO gets the Connection from a pool and closes it. I do know that I need connection.setAutoCommit(false); to start a transaction, but what to do it in this kind of a situation? When a single methods calls 2 DAO. Just give up on a transaction handling?

void setStatusDeclinedAndRefund() {
// sets Order status to DECLINED
// refund money to user's balance
}

JavaScript
 
void setStatusDeclinedAndRefund() {// sets Order status to DECLINED// refund money to user's balance}​

Answer

No.

Don’t second guess the connection pool. Use it in the standard way: get a connection, use it, close it.

There is no need to use the same connection for every database interaction in a given thread. Also, you’ll have serious liveliness problems if you allocate each thread a connection, because typically there are way more request processing threads than there are connections in the pool.

Advertisement

Answer