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;
  }

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;
}

Dao just uses this to get the connection.

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
}

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