Connection Pools with MariaDB Connector/J
This page is part of MariaDB's Documentation.
The parent of this page is: MariaDB Connector/J
Topics on this page:
Overview
Java developers can use MariaDB Connector/J to establish connection pools with MariaDB database products.
Connection Pools
Connection pools enable the reuse of database connections to minimize the performance overhead of connecting to the database and the churn of opening and closing connections.
Connection pools hold connections open in a pool. When a process is done with the connection, the connection is returned to the pool rather than being closed, allowing MariaDB Connector/J to acquire a connection as needed.
Several connection pool implementations are available for Java.
Apache Commons Classes
Although several connection pool implementations are available for Java, this documentation uses the commons-dbcp2 implementation from Apache Commons.
The commons-dbcp2 implementation from Apache Commons uses several classes from the org.apache.commons.dbcp2 and org.apache.commons.pool2 packages:
Class | Description |
|---|---|
| Abstract factory interface for creating |
| A java.sql.DriverManager-based implementation of org.apache.commons.dbcp2.ConnectionFactory. |
| A pooling simple interface. |
| A configurable org.apache.commons.pool2.ObjectPool implementation. |
| A simple "struct" encapsulating the configuration for a GenericObjectPool. |
| A delegating connection that, rather than closing the underlying connection, returns itself to an ObjectPool when closed. It implements the |
| A PooledObjectFactory that creates PoolableConnection instances. |
| A simple |
Connection Pool Related Connection Parameters
The following connection parameters are related to connections pools:
Parameter Name | Description | Data Type | Default Value | Version Added | Version Removed |
|---|---|---|---|---|---|
| The maximum amount of time in seconds that a connection can stay in the pool when not used. This value must always be below @wait_ |
|
| 2.2.0 | |
| The maximum number of physical connections in the pool. The minimum value is 1. |
|
| 2.2.0 | |
| The minimum number of physical connections the pool should keep available at all times. Should be less or equal to maxPoolSize. When not set, defaults to the value of |
|
| 2.2.0 | |
| Use pool. This option is useful only if not using a DataSource object, but only a connection object. |
|
| 2.2.0 | |
| Pool name that permits identifying threads. default: auto-generated as MariaDb-pool-<pool-index>. |
| 2.2.0 | ||
| When asking a connection to pool, the pool will validate the connection state. "poolValidMinDelay" permits disabling this validation if the connection has been borrowed recently, which avoids a useless verification in the face of frequent reuse of the connection. A 0 means validation is done each time the connection is asked. |
|
| 2.2.0 | |
| Register JMX monitoring pools. |
|
| 2.2.0 | |
| Indicates the values of the global variables max_ |
|
| 2.2.0 | 3.0.3 |
| When a connection is closed (given back to pool), the pool resets the connection state. Setting this option, the prepare command will be deleted, session variables changed will be reset, and user variables will be destroyed when the server permits it (>= MariaDB 10.2.4, >= MySQL 5.7.3). This can save memory on the server if the application makes extensive use of variables. Must not be used with the useServerPrepStmts option. |
|
| 2.2.0 |
Code Example: Connection Pooling
The following example program creates a connection pool to a server at the IP address 192.0.2.1 using the user account created in the example setup. A connection pool allows the application to use multiple database connections. If your application requires a single database connection instead, see Connect for a more appropriate example.
import java.sql.*;
import org.apache.commons.dbcp2.*;
import org.apache.commons.pool2.impl.*;
import javax.sql.DataSource;
public class App {
private static final String URL = "jdbc:mariadb://192.0.2.1:3306?user=db_user&password=db_user_password";
public static void main(String[] args) {
GenericObjectPool<PoolableConnection> connectionPool = App.createObjectPool();
DataSource dataSource = new PoolingDataSource(connectionPool);
try (Connection conn = dataSource.getConnection()) {
try (Statement stmt = conn.createStatement()) {
try (ResultSet rset = stmt.executeQuery("SELECT first_name from test.contacts")) {
int numcols = rset.getMetaData().getColumnCount();
while (rset.next()) {
connectionPoolStatus(connectionPool);
for (int i = 1; i <= numcols; i++) {
System.out.print("\t" + rset.getString(i));
}
System.out.println("");
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
connectionPoolStatus(connectionPool);
}
public static GenericObjectPool<PoolableConnection> createObjectPool() {
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(URL);
PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, null);
poolableConnectionFactory.setValidationQuery("SELECT 1");
GenericObjectPoolConfig<PoolableConnection> config = new GenericObjectPoolConfig<>();
config.setTestOnBorrow(true);
config.setMaxTotal(10);
return new GenericObjectPool<>(poolableConnectionFactory, config);
}
private static void connectionPoolStatus(GenericObjectPool<PoolableConnection> connectionPool) {
System.out.println(String.format("Active: %s; Idle : %s", connectionPool.getNumActive(), connectionPool.getNumIdle()));
}
}
The application output lists the number of active and idle connections in the connection pool while the query result set data is being output, and after the Connection is not in use.
Example output:
Active: 1; Idle : 0
John
Active: 1; Idle : 0
Jon
Active: 1; Idle : 0
Johnny
Active: 0; Idle : 1