References

Introduction

JDBC, short for Java Database Connectivity, is primarily an API standard used for connecting Java programming language to databases. You can also think of it as a library. But what exactly does this API standard encompass?

Connecting to a database.
Creating and executing SQL statements.

JDBC Architecture

The JDBC API provides the following interfaces and classes:

Driver Manager: Manages a list of database drivers. It matches connection requests from Java applications with the appropriate database driver to establish a database connection.
Driver: Handles communication with the database. It is rarely communicated with directly, and instead, interactions are managed through the Driver Manager.
Connection: Provides methods for connecting to the database.
Statement: Used for executing SQL statements and returning results.
ResultSet: Used for holding the results obtained from a database query.
SQLException: This class handles any errors that occur in the database application.

You can understand the purposes of JDBC API interfaces through the following example:

// Reference: https://medium.com/learning-from-jhipster/14-%E6%B7%B1%E5%85%A5-jdbc-connection-pool-%E4%B8%A6%E5%B0%8E%E5%85%A5-h2-db-939adee9c50
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQLDatabaseConnection {

  // Connect to your database.
  // Replace server name, username, and password with your credentials
  public static void main(String[] args) {
    // 1. Load the JDBC Driver for SQL Server
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    
    // 2. Edit the connection URL
    String connectionUrl =
        "jdbc:sqlserver://yourserver.database.windows.net:1433;"
        + "database=AdventureWorks;"
        + "user=yourusername@yourserver;"
        + "password=yourpassword;";

    // Used to hold the result set
    ResultSet resultSet = null;

    // https://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html
    try (
      // 3. Establish a connection
      Connection connection = DriverManager.getConnection(connectionUrl);
      // 4. Create a Statement object for executing SQL commands
      Statement statement = connection.createStatement();
    ) {

      // Create and execute a SELECT SQL statement.
      String selectSql = "SELECT TOP 10 Title, FirstName, LastName from SalesLT.Customer";
      // 5. Execute the SQL command and retrieve the result
      resultSet = statement.executeQuery(selectSql);

      // Print results from the select statement
      while (resultSet.next()) {
        System.out.println(resultSet.getString(2) + " " + resultSet.getString(3));
      }
      
      // Cleanup
      // 6. Release resources, close the Connection, and Statement
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
  }
}

JDBC Connection

Usually, to establish a database connection, the following steps are required:

Set up the Driver: Load the JDBC Driver for SQL Server.
Create a Connection: (Using DriverManager.getConnection()) to establish a TCP socket connection.
Submit SQL: Create a Statement and execute SQL commands.
Close the Connection: Close the TCP socket.

However, every time we need to communicate with the database, performing these steps repeatedly can create unnecessary overhead for the system.

If we can extract those repetitive actions every time we connect, we can reduce the system’s burden and improve program execution performance. This is what we are going to discuss next - Connection Pooling.

Connection Pool

Connection Pooling is implemented using the “Object Pool Pattern.” Let’s start with a brief explanation from Wikipedia:

The object pool pattern is a creational design pattern. An object pool contains a set of initialized and ready-to-use objects that can be created and destroyed as needed. Clients can request objects from the pool, use them for processing, and return them to the pool instead of creating and destroying them directly. It’s a special kind of factory object.

wikien.wikipedia.org/wiki/Object_pool_pattern

In simple terms, it means pre-creating and storing some commonly used objects in a pool. When needed, you can directly take objects from the pool, avoiding the need to recreate them every time. Using an object pool can significantly improve performance. The time to get an object from the pool is predictable, whereas creating a new instance takes an uncertain amount of time.

The following diagram illustrates how clients use connections from the pool to connect to a data source. The main tasks are:

Managing available connections.
Allocating connections.
Releasing connections.

Now, let’s take a look at the UML diagram:

Object Pool Class: It is a Singleton, meaning it has a private constructor and a static getInstance() method to obtain an instance of the Object Pool.
acquireReusable(): Used to get objects from the Object Pool. If there are no available objects, it will create a new one.
releaseReusable(): Used to release an object from the client’s hand and return it to the available objects.
setMaxPoolSize(): Used to set the maximum capacity of the Object Pool.

  classDiagram
    class client
    class ResuablePool{
        - reusable
        +BigDecimal balance
        +static getInstance() ReusablePool
        +acquireReusable() Reusable
        +releaseReusable(Reusable reusable)
        +setMaxPoolSize(int maxPoolSize)
    }
    client --> ResuablePool : ResusablePool.getInstance().acquareReusable()

JDBC Connection Pool Example

You can refer to Azure’s approach for a simple JDBC Connection Pool implementation. Inside, you can see how a Connection Pool is implemented.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;
import java.util.Stack;

public class MySQLConnectionPool {
    // Connection information
    private String databaseUrl;
    private String userName;
    private String password;
    // Pool size
    private int maxPoolSize = 10;
    // Current number of connections
    private int connNum = 0;

    private static final String SQL_VERIFYCONN = "select 1";

    // Currently available connections
    Stack<Connection> freePool = new Stack<>();
    // Currently in-use connections
    Set<Connection> occupiedPool = new HashSet<>();

    /**
     * Constructor: Create a Connection Pool
     * 
     * @param databaseUrl
     *            The connection URL
     * @param userName
     *            User name
     * @param password
     *            Password
     * @param maxSize
     *            Max size of the connection pool
     */
    public MySQLConnectionPool(String databaseUrl, String userName,
            String password, int maxSize) {
        this.databaseUrl = databaseUrl;
        this.userName = userName;
        this.password = password;
        this.maxPoolSize = maxSize;
    }

    /**
     * Get an available connection
     * 
     * @return An available connection
     * @throws SQLException
     *             Fail to get an available connection
     */
    public synchronized Connection getConnection() throws SQLException {
        Connection conn = null;

        // Cannot create a new connection (reached the maximum limit)
        if (isFull()) {
            throw new SQLException("The connection pool is full.");
        }

        // Return a connection from the freePool and mark it as occupied
        conn = getConnectionFromPool();

        // If conn is null, it means there are no objects in freePool, so we need to create a new connection
        if (conn == null) {
            conn = createNewConnectionForPool();
        }

        // Some databases automatically disconnect idle connections after some time, so we check if the connection is still active
        // For Azure Database for MySQL, if there is no action on one connection for some time, the connection is lost. By this, make sure the connection is
        // active. Otherwise, reconnect it.
        conn = makeAvailable(conn);
        return conn;
    }

    /**
     * Return a connection to the pool
     * 
     * @param conn
     *            The connection
     * @throws SQLException
     *             When the connection is returned already or it isn't gotten
     *             from the pool.
     */
    public synchronized void returnConnection(Connection conn)
            throws SQLException {
        // If conn is null, it means the client hasn't used the connection, so there's no need to return it
        if (conn == null) {
            throw new NullPointerException();
        }
        // Check if conn is in the occupiedPool and can be removed from it, if not, throw SQLException
        if (!occupiedPool.remove(conn)) {
            throw new SQLException(
                    "The connection is returned already or it isn't for this pool");
        }
        // If no exception is thrown, it's removed from occupiedPool and can be returned to freePool
        freePool.push(conn);
    }

    /**
     * Verify if the connection pool is full
     * 
     * @return True if the connection pool is full
     */
    private synchronized boolean isFull() {
        return ((freePool.size() == 0) && (connNum >= maxPoolSize));
    }

    /**
     * Create a connection for the pool
     * 
     * @return The new created connection
     * @throws SQLException
     *             When fail to create a new connection.
     */
    private Connection createNewConnectionForPool() throws SQLException {
        Connection conn = createNewConnection();
        connNum++;
        occupiedPool.add(conn);
        return conn;
    }

    /**
     * Create a new connection
     * 
     * @return The new created connection
     * @throws SQLException
     *             When fail to create a new connection.
     */
    private Connection createNewConnection() throws SQLException {
        Connection conn = null;
        conn = DriverManager.getConnection(databaseUrl, userName, password);
        return conn;
    }

    /**
     * Get a connection from the pool. If there is no free connection, return null
     * 
     * @return The connection.
     */
    private Connection getConnectionFromPool() {
        Connection conn = null;
        if (freePool.size() > 0) {
            conn = freePool.pop();
            occupiedPool.add(conn);
        }
        return conn;
    }

    /**
     * Make sure the connection is available now. Otherwise, reconnect it.
     * 
     * @param conn
     *            The connection for verification.
     * @return The available connection.
     * @throws SQLException
     *             Fail to get an available connection
     */
    private Connection makeAvailable(Connection conn) throws SQLException {
        // Verify if the connection is still active by running a query
        if (isConnectionAvailable(conn)) {
            return conn;
        }

        // If the connection is found to be inactive, remove it from occupiedPool and close it
        occupiedPool.remove(conn);
        connNum--;
        conn.close();

        // Then create a new connection and add it to occupiedPool
        conn = createNewConnection();
        occupiedPool.add(conn);
        connNum++;
        return conn;
    }

    /**
     * Check if the connection is available by running a query
     * 
     * @param conn
     *            The connection for verification
     * @return True if the connection is available
     */
    private boolean isConnectionAvailable(Connection conn) {
        try (Statement st = conn.createStatement()) {
            st.executeQuery(SQL_VERIFYCONN);
            return true;
        } catch (SQLException e) {
            return false;
        }
    }
    
    // Just an Example
    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        // Create a connection pool
        MySQLConnectionPool pool = new MySQLConnectionPool(
                "jdbc:mysql://mysqlaasdevintic-sha.cloudapp.net:3306/<Your DB name>",
                "<Your user>", "<Your Password>", 2);
        try {
            // Get a connection
            conn = pool.getConnection();

            // Prepare a query to execute
            try (Statement statement = conn.createStatement())
            {
                ResultSet res = statement.executeQuery("show tables");
                System.out.println("There are below tables:");
                while (res.next()) {
                    String tblName = res.getString(1);
                    System.out.println(tblName);
                }
            }
        }
        finally {
            if (conn != null) {
                // Release the connection
                pool.returnConnection(conn);
            }
        }
    }
}

About Third-Party Connection Pool Libraries

Now that we understand how Connection Pools work, it’s important to note that there are many mature third-party libraries available for us to use. These third-party Connection Pool libraries have been optimized for performance and reducing redundant work.

The development of different third-party Connection Pools is primarily driven by performance considerations. Since JDBC has defined interfaces for JDBC Drivers, these third-party Connection Pool libraries adhere to the JDBC Driver Interface API. They all have the same API, but the difference lies in the internal implementation, which affects performance.

Albertmedium.com/learning-from-jhipster/14-%E6%B7%B1%E5%85%A5-jdbc-connection-pool-%E4%B8%A6%E5%B0%8E%E5%85%A5-h2-db-939adee9c50

Currently, the most powerful and high-performance third-party Connection Pool library is HikariCP, with performance that surpasses other Connection Pool libraries by more than two times.
In the graph below:

Connection Cycle Comparison: It shows the connection speed from DataSource.getConnection() to Connection.close(). HikariCP can complete around 40,000 to 50,000 connections in less than 1 millisecond.
Statement Cycle Comparison: It shows the SQL execution speed from Connection.prepareStatement() to Statement.execute() to Statement.close(). In terms of SQL execution speed, it can complete approximately 15,000 cycles within 1 millisecond. The only other library that can keep up is Tomcat’s JDBC Connection Pool; other libraries have much lower performance.

To understand why it is so fast, you can read this article: Reasons for HikariCP’s Speed

About Third-Party Libraries Used by Spring Boot

Previously, we explained how to connect to databases using JDBC and the importance of Connection Pools. In Spring Boot, a series of commonly used tools are integrated, simplifying configuration tasks related to JDBC, Connection Pools, JPA, and more.

Now, you only need to import dependencies and configure properties to quickly and conveniently establish a connection with the database. If you want to communicate with a database using a Java application, you typically import the spring-boot-starter-jdbc library:

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

But now it mainly uses spring-boot-starter-data-jdbc, with an extra word for data!

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>

Spring Boot Starters for Data and JDBC

When using spring-boot-starter-jdbc or spring-boot-starter-data-jpa starters, we automatically get a dependency on HikariCP. (See the snapshot below)

But what’s the difference between these data-related starters? If you are developing with JPA, the most commonly used one is spring-boot-starter-data-jdbc.

spring-boot-starter-jdbc: This starter is the most basic package. If you only need JDBC, you can import this starter.
spring-boot-starter-data-jdbc: This starter builds upon spring-boot-starter-jdbc and provides a quick development interface for curdRepository. If you need to use JPA, then you should import this starter.

spring-jdbc (from: https://blog.csdn.net/assember/article/details/121976290)

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

public class RunAQuery {

    private JdbcTemplate jdbcTemplate;

    // only provide basic JDBC connection
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public int getCount() {
        return this.jdbcTemplate.queryForObject("select count(*) from mytable", Integer.class);
    }

    public String getName() {
        return this.jdbcTemplate.queryForObject("select name from mytable", String.class);
    }
}

spring-data-jdbc Except for the basic database connection, it also provides CrudReporitoy

import org.springframework.data.repository.CrudRepository;
@Repository
public interface UserRepository extends CrudRepository<User, Long> {
}

Properties Configuration

If you take a closer look at this starter’s content, you will find that it mainly includes two dependencies:

spring-JDBC: This is Spring’s library that wraps JDBC. If you use this starter, you must configure the database connection information in the properties (application.yml).
HikariCP: Spring Boot uses HikariCP as the default Connection Pool and includes it in spring-boot-starter-jdbc. (It’s highly efficient!)

<dependency>
  <groupId>org.springframework</groupId>
  <artifactId>spring-jdbc</artifactId>
</dependency>
<dependency>
  <groupId>com.zaxxer</groupId>
  <artifactId>HikariCP</artifactId>
</dependency>

Can refer to Spring Boot - Data Properties for configuration methods:

spring:
  datasource:
    # To use HikariCP, you can configure it directly using spring.datasource.*
    type: Configure the type of DataSource for the Connection Pool (or automatically detect from classpath)
    url: Configure the JDBC URL for connecting to the database
    username: Configure the username for connecting to the database
    password: Configure the password for connecting to the database
    driver-class-name: Configure the JDBC driver class (or automatically detect based on the URL)

spring.datasource.type

Since we’ve imported spring-boot-starter-data-jdbc, we can directly use spring.datasource.type to configure the Connection Pool we want to use. By default, Spring Boot will automatically detect it from the classpath, and if it’s not found, it will default to HikariCP.

If you’re curious about how it auto-detects or which class names it can auto-detect, you can refer to the DataSourceBuilder.java — Source Code, or check out the code snippet below:

  private static <T extends DataSource> MappedDataSourceProperties<T> lookupPooled(ClassLoader classLoader,
		Class<T> type) {
	MappedDataSourceProperties<T> result = null;
	result = lookup(classLoader, type, result, "com.zaxxer.hikari.HikariDataSource",
			HikariDataSourceProperties::new);
	result = lookup(classLoader, type, result, "org.apache.tomcat.jdbc.pool.DataSource",
			TomcatPoolDataSourceProperties::new);
	result = lookup(classLoader, type, result, "org.apache.commons.dbcp2.BasicDataSource",
			MappedDbcp2DataSource::new);
	result = lookup(classLoader, type, result, "oracle.ucp.jdbc.PoolDataSourceImpl",
			OraclePoolDataSourceProperties::new, "oracle.jdbc.OracleConnection");
	result = lookup(classLoader, type, result, "com.mchange.v2.c3p0.ComboPooledDataSource",
			ComboPooledDataSourceProperties::new);
	return result;
}

In this code snippet, we can see that Spring Boot automatically detects several Connection Pool implementations, with HikariCP being the default choice.

org.springframework.jdbc.datasource.SimpleDriverDataSource
com.zaxxer.hikari.HikariDataSource
org.apache.tomcat.jdbc.pool.DataSource
oracle.ucp.jdbc.PoolDataSourceImpl
oracle.jdbc.OracleConnection
and more.

If you use the spring-boot-starter-jdbc or spring-boot-starter-data-jpa “starters”, you automatically get a dependency to HikariCP.

spring-docdocs.spring.io/spring-boot/docs/current/reference/htmlsingle/#data.sql.datasource.connection-pool

Regarding org.springframework.jdbc.datasource.SimpleDriverDataSource, check its API documentation, and you’ll find the note This class is not an actual connection pool; it does not actually pool Connections. This means that this class is not a genuine Connection Pool, and it doesn’t perform Connection Pooling operations.