What is hikari pool?

This simple question in a post on BlueSky led me to an explanation that I thought was really cool. I came here to finish it.

In the specific context, Hikari Connection Pool was being talked about. But, if Hikari is a Connection Pool, what would a "Pool" be?

First things first, pool concept

Before explaining what HikariCP is, we need to explain what a connection pool is. And to explain connection pool, we need to explain pool.

Shall we use an economic analogy for this? A historical economic analogy full of flaws and inaccuracies with the real world, but come on, suspend your disbelief quickly just for the explanation! It is self-contained.

Imagine that you are a lord/lady in the medieval era. You hold the tools to carry out the peasants' work. And you want them to work. So how do you guarantee this? If the tools are yours? You will need to deliver the tools to the peasants, simple.

So imagine the situation: your peasant needs a hoe to weed the land, so he goes there and asks you for a hoe. You will give him the hoe and live on. But what if he doesn't give it back, what about his stock of hoes? One day it will end...

An alternative to handing over the hoe is to have a hoe made. You are the lord/lady of those lands, so you have access to the blacksmith to smelt the metal into the shape of a hoe and fit it into a handle. But this is not something you can produce right away without the peasant sitting in a waiting room. To make this new feature, you require an enormous amount of time and energy.

Now, if the peasant returns the hoe at the end of the day, it becomes available for another peasant to use the next day.

Here you are controlling the pool of hoes. The pool is a design pattern that indicates something that you can do the following actions:

• ask him for an element
• return element to it

Other common things to have in pools of objects:

• ability to create more objects, on demand, by registering them in the pool
• ability to destroy objects from the pool (or disassociate it from that pool)

Connecting to the JDBC database

Well, let's get closer to HikariCP. Let's talk here about database connections in Java.

In java, we ask to establish a connection to the database. There is the direct connection option, which you need to understand directly about which classes to call and some details, or else simply enjoy the service discovery option.

A priori, to use service discovery, the service provider makes a way to register what he is providing and then the "service discovery" goes after it to see who could serve that request.

A case of service discovery: pstmt-null-safe

I had a case where I needed to make JDBC connections to talk to the database. However, my JDBC driver did not accept using nulls as values, only nulls directly in queries. So what did I do? A driver on top of a driver!

The general idea was the following. Imagine that I have this query that I want to insert values ??into:

INSERT INTO some_table (id, content, parent)
VALUES (?, ?, ?)

Now imagine that I am dealing with the first insertion of this value into the bank. To do this, I need to leave it with ID=1, CONTENT=first and PARENT=null because, after all, there is no parent record like that (it is the first, after all).

What would naturally be done:

try (final var pstmt = conn.prepareStatement(
                """
                INSERT INTO some_table (id, content, parent)
                VALUES (?, ?, ?)
                """)) {

    pstmt.setInt(1, 1);
    pstmt.setString(2, "first");
    pstmt.setNull(3, Types.INTGEGER); // java.sql.Types
    pstmt.executeUpdate(); // de fato altere o valor
}

I want to keep using it this way, after all it's the idiomatic way to use it. And according to CUPID, the I comes from "idiomatic." The idea of ??having an idiomatic code is precisely to "reduce unnecessary mental load".

To resolve this, my choice was: leave prepareStatement until the last moment before executeUpdate. So I store all the nulls to be applied and, when I realize that I actually need a null, I run a string substitution and generate a new query, and this new query will actually be executed.

In this case, I start with:

INSERT INTO some_table (id, content, parent)
VALUES (?, ?, ?)

So, I have to enter these values:

INSERT INTO some_table (id, content, parent)
VALUES (?, ?, ?)

-- 1, 'first', NULL

But I can't actually use the null, so I create a key to identify that the third place is a null:

-- (value, value, NULL)
INSERT INTO some_table (id, content, parent)
VALUES (?, ?, NULL)
-- 1, 'first'

And in this case I prepare this new string and place the arguments according to what was requested.

Okay, that said, how could I indicate to my application that I needed to use my JDBC driver? How did I register?

The project in question is Pstmt Null Safe. Basically, there is a magic in the Java classloader that, when loading a jar, it looks for a metadata folder called META-INF. And in the case of JDBC driver, META-INF/services/java.sql.Driver, and I noted it with the class that implements java.sql.Driver: br.com.softsite.pstmtnullsafe.jdbc.PstmtNullSafeDriver.

According to the java.sql.Driver documentation, every driver should create an instance of itself and register with DriverManager. I implemented it like this:

INSERT INTO some_table (id, content, parent)
VALUES (?, ?, ?)

Static block loads itself. And how do we know which connection should be managed by my driver? The call is made through DriverManager#getConnection(String url). We have the URL to ask the driver if it accepts the connection. The convention (here again, the idiomatic way to use it) is to prefix it to the URL scheme. As I want my driver to connect on top of another driver, I did it using this scheme:

try (final var pstmt = conn.prepareStatement(
                """
                INSERT INTO some_table (id, content, parent)
                VALUES (?, ?, ?)
                """)) {

    pstmt.setInt(1, 1);
    pstmt.setString(2, "first");
    pstmt.setNull(3, Types.INTGEGER); // java.sql.Types
    pstmt.executeUpdate(); // de fato altere o valor
}

So, to perform the tests, I connected with SQLite, and used the Xerial indicator to request an in-memory connection through the connection URI:

INSERT INTO some_table (id, content, parent)
VALUES (?, ?, ?)

To "envelope" the connection, my convention indicates that I do not repeat the jdbc:, so:

INSERT INTO some_table (id, content, parent)
VALUES (?, ?, ?)

-- 1, 'first', NULL

Dissecting the above URI:

-- (value, value, NULL)
INSERT INTO some_table (id, content, parent)
VALUES (?, ?, NULL)
-- 1, 'first'

Okay, and how do you indicate this? The Driver#acceptsURL must return true if I can open the connection. I could just do this:

public static final PstmtNullSafeDriver instance;

static {
    instance = new PstmtNullSafeDriver();
    try {
        DriverManager.registerDriver(instance);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

But what would this indicate if I tried to load a non-existent driver? Nothing, it would cause a problem at another time. And that's not good, the ideal would be to crash right from the beginning. So for this, I'll try to load the driver from below, and if I can't, I'll return false:

jdbc:pstmt-nullsafe:<url de conex?o sem jdbc:>
\__/ \____________/
 |    |
 |    Nome do meu driver
 Padr?o para indicar JDBC

The actual driver code has some more points that are not relevant to the discussion here about HikariCP, nor about DataSource, nor JDBC or topics covered in this post.

So, when requesting a "null safe" connection to DriverManager, first it finds my driver and my driver recursively tries to check if there is the possibility of a connection under the hood. Confirmed that there is a driver capable of dealing with this, I say yes, it is possible.

The usage pattern of JDBC connections in Java

The Connection interface implements the AutoCloseable interface. This means you take the connection, use the connection as you want, and then you close the connection. It is quite standard for you to use some indirection with this or, if you use the connection directly, use it inside a try-with-resources:
block

jdbc:sqlite::memory:

Now, the process of creating connections is an expensive process. And also the service discovery process is not exactly free. So the ideal would be to save the driver to then generate the connections. Let's develop this little by little.

First, we will need to have an object that we can launch with the driver. It can easily be a global object, an injected Spring component, or anything like that. Let's call it JdbcConnector:

jdbc:pstmt-nullsafe:sqlite::memory:

One possible implementation for getJdbcConnection() is to rely on a state encompassed by this function:

INSERT INTO some_table (id, content, parent)
VALUES (?, ?, ?)

Everything is going well so far. But... remember the initial example where the peasant asks for a hoe in the tool pool? So... Shall we take this into consideration? Instead of actually closing the connection, we can return the connection to the pool. For the sake of correctness, I will protect against multiple simultaneous accesses, but I won't worry about efficiency here.

Let's assume here that I have a class called ConnectionDelegator. It implements all Connection methods, but it does nothing on its own, it only delegates to a connection that is passed to it as a constructor. For example, for the isClosed():
method

try (final var pstmt = conn.prepareStatement(
                """
                INSERT INTO some_table (id, content, parent)
                VALUES (?, ?, ?)
                """)) {

    pstmt.setInt(1, 1);
    pstmt.setString(2, "first");
    pstmt.setNull(3, Types.INTGEGER); // java.sql.Types
    pstmt.executeUpdate(); // de fato altere o valor
}

And so on for the other methods. It is abstract for the simple fact that I want to force myself to do something other than a simple delegation when I use it.

Well, let's go. The idea is that a connection will be requested, which may or may not exist. If it exists, I wrap it in this new class so I can then return it to pool when I close the connection. Hmmm, so I'm going to do something in the close() method... Okay, let's wrap it first. Let's leave getConnection() as synchronized to avoid concurrency problems:

INSERT INTO some_table (id, content, parent)
VALUES (?, ?, ?)

Ok, if I have elements in the pool of connections I use them until it is empty. But it is never filled! So are we going to resolve this issue? When it closes, we can return it to the pool!

INSERT INTO some_table (id, content, parent)
VALUES (?, ?, ?)

-- 1, 'first', NULL

Ok, now when you finish using the connection, it is sent back to
the pool. This does not satisfy the documentation for the Connection#close() method, because in the documentation it mentions that it releases all JDBC resources related to this connection. This means I would need to keep a record of all Statements, ResultSets, PreparedStatements, etc. We can handle this by creating a protected method on ConnectionDelegator called closeAllInnerResources(). And call it in close():

-- (value, value, NULL)
INSERT INTO some_table (id, content, parent)
VALUES (?, ?, NULL)
-- 1, 'first'

And with that we have something that returns connections to me on demand and that has the ability to form a pool of resources.

Do you know what name Java gives to an object that provides connections? DataSource. And do you know what else Java has to say about DataSources? That there are some types, conceptually speaking. And of these types, the 2 most relevant are:

• basic: it doesn't do pooling, it asked for a connection, it just creates and returns it
• pooled: in which there is a pooling of connections to the bank

And here we go through the process of always creating connections (basic type) as well as evolving into a DataSource
pooled.

What is HikariCP?

HikariCP is a DataSource. Specifically, a pooled DataSource. But he has one characteristic: he is the fastest of all. To guarantee this speed, in its pool of connections for use during the application's life cycle, HikariCP makes a secret: it already creates all available connections. Thus, when a getConnection arrives, HikariCP will only need to check the pool of connections.

If you want to delve deeper into the subject, you can check out this article on Baeldung on the subject, and also check out the repository on github.

The above is the detailed content of What is hikari pool?. For more information, please follow other related articles on the PHP Chinese website!