Connection Pools with MariaDB Connector/J
This page is part of MariaDB's Enterprise 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 Enterprise.
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 |
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