Does Java provide automatic connection pooling like C#?

[Monica Shiralkar]

In C# when we open a connection by default it gives from the pool if available.

In Java we have to use external libraries for connection pooling. Is there no automatic connection pooling in Java unlike C#?

Thanks

[Stephan van Hulst]

Thankfully not. C# connection pools are among the worst code ever written. Is it a connection? Is it a connection pool? What happens when you dispose of a connection? You really don't know. We've had numerous issues in our persistence layer because we closed connections when we should have left them open, and left them open when we should have closed them.

Java makes this explicit with the DataSource interface. A DataSource is a source of connections that may be pooled or not. When you have an application that needs to access a database, make sure that you configure a DataSource somewhere, and then inject that into the classes that access the database. A database operation consists of getting a connection from the data source, running queries on it and then ALWAYS disposing the connection once you're done. You can easily do this with the try-with-resources syntax. When the connection is disposed, the data source is free to either close the underlying connection, or return it to the pool for later use.

Here is an example:

final class SqlBookRepository implements BookRepository {
  
  private static final String SELECT_BOOK_BY_ISBN_QUERY = "SELECT authorName, title FROM Books WHERE isbn = ?";

  private final DataSource dataSource;

  SqlBookRepository(DataSource dataSource) {
    this.dataSource = requireThat("dataSource", dataSource, is(notNullValue()));
  }

  @Override
  public Optional<Book> findByIsbn(Isbn isbn) throws RepositoryException {
    requireThat("isbn", isbn, is(notNullValue()));
    
    try (
      var connection = dataSource.getConnection();
      var statement  = connection.prepareStatement(SELECT_BOOK_BY_ISBN_QUERY);
    ) {
      statement.setString(1, isbn.toString());
      
      try (
        var resultSet = statement.executeQuery();
      ) {
        if (!resultSet.first())
          return Optional.empty();
        
        var authorName = resultSet.getString("authorName");
        var title      = resultSet.getString("title");
        
        return Optional.of(new Book(isbn, authorName, title));
      }
    }
    
    catch (SQLException ex) {
      throw new RepositoryException("An exception occurred while finding the book by ISBN.", ex);
    }
  }
}

[Monica Shiralkar]

Thanks. If we write the connection  statement inside the using keyword, will it not automatically dispose and close the connection?

[Stephan van Hulst]

In C#? Yes, it will dispose the connection, but it's not certain if that means the underlying database connection will return to the pool or be completely closed. DBConnection is a really weird hybrid of a database connection and a connection pool.

For instance, when you call new OleDbConnection(connectionString), it implies that you're creating a new database connection, but instead it might retrieve a database connection from some hidden magic connection pool. We've encountered issues where closing the connection with the Close() method would return the connection to the pool, while calling Dispose() would shut down the connection completely. This is probably a bug in the particular connection implementation, but it doesn't help that Microsoft's specification is ambiguous and very poorly written (much like most of their API documentation).

Java completely separates a database connection from a source of connections, whether that source is a connection pool or not. This makes it absolutely clear what is happening: DriverManager.getConnection() creates a new database connection. DataSource.getConnection() returns a pooled connection when it's configured to pool connections and a new connection when it's not. With DbConnection in C#, who knows what happens.

[Monica Shiralkar]

In C# If the underlying connections are returned to pool or closed, does it mean that in either case it gets subtracted from the number of open connections? (which is limited).   Does connection in sleeping state mean that it is part of the pool?