Connect 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
Connection URL Syntax
Connection Info
- Example Connection URLs
High Availability (HA) Modes
Host Description
- Simple Syntax
- Complex Syntax
Optional Connection Parameters
Code Example: Connect
- Connector/J 3.0
- Connector/J 2.7

Overview

Java developers can use MariaDB Connector/J to establish client connections with MariaDB database products.

Connection URL Syntax

The connection URL syntax used to connect to MariaDB with Connector/J is as follows. Optional parameters are shown enclosed in []:

jdbc:mariadb:[<haMode>:]//[<hostDescription>[,<hostDescription>]]/[<database>][?<key1>=<value1>[&<key2>=<value2>]] }

jdbc:mariadb://HOST:PORT/DATABASE?user=USERNAME&password=PASSWD

The connection URL:

Requires jdbc:mariadb:// as the protocol component.

Accepts an optional High Availability (HA) mode in the haMode field.
Accepts an optional Host Description in the hostDescription field.

Requires the SkySQL hostname and port to be specified separated by a colon.

Accepts an optional database name after the slash.
Accepts optional connection parameters in key=value format.

Connection Info

The connection is configured via the information that is initially acquired from the SkySQL Portal pages:

What	Where to find it
connProperties `user`	Default username in the Service Credentials view, or the username you created
connProperties `password`	Default password in the Service Credentials view, the password you set on the default user, or the password for the user you created
connProperties `serverSslCert`	Path to the `skysql_chain.pem` file containing the "Certificate Authority Chain" (Download) Download For GCP services Download For AWS services
connProperties `sslMode`	With Connector/J 3.0, set `sslMode` to the string `"verify-full"` to enable TLS, which is required for SkySQL.
connProperties `useSsl`	With Connector/J 2.7 and earlier, set `useSsl` to the string `"true"` to enable TLS, which is required for SkySQL.
getConnection URL `host` component	Fully Qualified Domain Name in the Service Details view
getConnection URL `port` component	Read-Write Port or Read-Only Port in the Service Details view

Example Connection URLs

Some example connection URLs:

jdbc:mariadb://192.0.2.1/

jdbc:mariadb://192.0.2.1/?user=db_user&password=db_user_password

jdbc:mariadb://192.0.2.1/database?user=db_user&password=db_user_password

jdbc:mariadb://192.0.2.1:3307/database?user=db_user&password=db_user_password

jdbc:mariadb://mariadb.example.com/database?user=db_user&password=db_user_password

jdbc:mariadb://mariadb.example.com:3307/database?user=db_user&password=db_user_password

jdbc:mariadb://localhost/database?localSocket=/var/lib/mysql/mysql.sock&user=db_user&password=db_user_password

High Availability (HA) Modes

MariaDB Connector/J supports the use of multiple nodes in JDBC connection URL. For multi-node environments, Connector/J provides basic failover between the nodes when establishing connections.

Connector/J also features optional High Availability (HA) modes, which provide additional support for load balancing and failover.

HA modes are useful in multi-node environments, such as:

MariaDB Enterprise Cluster MariaDB Enterprise Cluster topology, powered by Galera
MariaDB Replication (Primary/Replica)MariaDB Replication (Primary/Replica) topology, which uses MariaDB Replication
Topologies with multiple redundant MariaDB MaxScale nodes

HA Mode	Description
failover	Alias of the loadbalance HA mode.
loadbalance	Balances read and write load between configured nodes.
replication	Balances read and write load between configured primary servers. Balances read load between configured replica servers.
sequential	Provides connection failover between configured nodes.

Load Balancing Mode

In the loadbalance HA mode, MariaDB Connector/J load balances JDBC connections across multiple nodes, as specified in the JDBC connection string. When sending read and write queries, Connector/J balances the load by randomly selecting from configured nodes. This HA mode does not support load balancing read queries across replica servers.

To enable the loadbalance HA mode:

jdbc:mariadb:loadbalance://192.0.2.1,192.0.2.2,192.0.2.3/test

The failover HA mode is an alias of the loadbalance mode.

Replication Mode

In the replication HA mode, MariaDB Connector/J load balances JDBC connections across multiple nodes, as specified in the JDBC connection string. When sending read and write queries, Connector/J balances the load between available primary servers. It can also balance the read query load across available replica servers.

To enable the replication HA mode:

jdbc:mariadb:replication://192.0.2.1,192.0.2.2,192.0.2.3/test

Sequential Mode

In the sequential HA mode, MariaDB Connector/J provides failover for JDBC connections across multiple nodes, as specified in the JDBC connection string. Connector/J selects the first node from the configured sequence. If the connection attempt fails, it attempts to connect to the next node in the sequence.

To use the Sequential HA mode:

jdbc:mariadb:sequential://192.0.2.1,192.0.2.2,192.0.2.3/test

Transaction Replay

Starting with MariaDB Connector/J 3.0, in-progress transactions can be replayed after failover occurs.

Connector/J implements transaction replay by caching statements for each transaction in a transaction cache. If a failover occurs before a transaction is committed or rolled back, the transaction's cached statements are re-executed on the new primary server. If the number of statements in the transaction cache exceeds transactionReplaySize, caching will be disabled until the transaction is committed or rolled back.

When transaction replay is enabled, Connector/J requires that applications only use idempotent queries.

Transaction replay is enabled by setting the transactionReplay parameter:

jdbc:mariadb:replication://192.0.2.1,192.0.2.2,192.0.2.3/test?transactionReplay=true&transactionReplaySize=128

Host Description

The hostDescription field in the connection URL has a simple syntax and a complex syntax.

For TCP/IP connections, the hostDescription field is required.

However, the hostDescription field can be omitted in some scenarios. For example:

For local Unix socket connections:

jdbc:mariadb:///database?localSocket=/var/lib/mysql/mysql.sock

For local named pipe connections:
```
jdbc:mariadb:///database?pipe=mariadb
```

Simple Syntax

The hostDescription simple syntax is as follows:

<host>:<port>

Requires an IP address or Fully Qualified Domain Name (FQDN) to the target node.
Accepts optional port number to connect to on the target node.

For example:

jdbc:mariadb://192.0.2.1:3306/database?user=db_user&password=db_user_password

Complex Syntax

The hostDescription complex syntax is as follows:

address=[(type=(master|replica))][(port=<portnumber>)](host=<host>)

type sets whether the node is a primary server or a replica
port sets the port number on which to connect
host sets the IP address or Fully Qualified Domain Name (FQDN) where the node is located

For example:

jdbc:mariadb://address=(type=master)(host=primary1),address=(port=3307)(type=replica)(host=replica1)/database?user=greg&password=pass

Optional Connection Parameters

MariaDB Connector/J supports several optional connection parameters, including the required username, password, and SSL (TLS) connection:

Parameter Name	Description	Data Type	Default Value	Version Added	Version Removed
`allowLocalInfile`	Defines if loading data from file with `LOAD DATA LOCAL INFILE` is permitted.	`boolean`	`true`	1.2.1
`allowMasterDownConnection`		`boolean`	`false`	2.2.0	3.0.3
`assureReadOnly`		`boolean`	`false`	1.3.0	3.0.3
`autoReconnect`	Defines whether the connector automatically reconnects after a connection failure.	`boolean`	`false`	1.2.0	3.0.3
`cachePrepStmts`	Defines whether the prepared statement cache is enabled.	`boolean`	`true`	1.3.0	3.0.3
`callableStmtCacheSize`		`boolean`	`true`	1.4.0	3.0.3
`connectTimeout`	Defines the connect timeout value in milliseconds. When set to `0`, there is no connect timeout.	`int`	`30000`	1.1.8
`continueBatchOnError`		`boolean`	`true`		3.0.3
`disableSslHostnameVerification`	Deprecated in 3.0.3. Use `sslMode=verify-ca` in 3.0.3 and later.	`boolean`	`false`	2.4.0
`enabledSslCipherSuites`	Forces the TLS protocol to only use the specified comma-separated list of TLS ciphers.	`string`		1.5.0
`enabledSslProtocolSuites`	Forces the TLS protocol to only use the specified comma-separated list of TLS versions.	`string`		1.5.0
`enablePacketDebug`		`boolean`	`false`	1.6.0, 2.0.1	3.0.3
`ensureSocketState`					3.0.3
`failOnReadOnly`	Defines whether an exception is thrown when only read-only servers are available.	`boolean`	`false`	1.2.0	3.0.3
`failoverLoopRetries`	Applies only when HA mode is specified. Defines the number of times the connector silently loops through available hosts and attempts to reconnect after a connection failure. Between loops, the connector will pause for 250 ms if no servers are available. When set to 0, the connector does not loop through available hosts or attempt to reconnect.	`int`	`120`	1.1.8	3.0.3
`initSql`	Enables execution of a SQL statement upon connection creation.	`string`		3.0.6
`jdbcCompliantTruncation`		`boolean`	`true`	1.4.0	3.0.3
`keyPassword`		`string`		1.5.3	3.0.3
`keyStore`	File path to the keyStore file that contains the private key store and associated certificates. This parameter is similar to the Java System property javax.net.ssl.keyStore, but ensures that only the private key entries are used.	`string`		1.3.4
`keyStorePassword`	Password for the client certificate keyStore. This parameter is similar to the Java System property javax.net.ssl.keyStorePassword.	`string`		1.3.4
`keyStoreType`	Defines the keyStore type (JKS/PKCS12).	`string`		2.4.0
`loadBalanceBlacklistTimeout`		`int`	`50`	1.2.0	3.0.3
`localSocket`	Defines the Unix socket file to use for connections to `localhost` via Unix domain socket.	`string`		1.1.4
`localSocketAddress`	Defines the hostname or IP address to use for the connection socket when connecting to `localhost` via Unix domain socket.	`string`		1.1.8
`log`		`boolean`	`false`	1.5.0	3.0.3
`maximizeMysqlCompatibility`					3.0.3
`nullCatalogMeansCurrent`					3.0.3
`password`	Defines the password of the user account to connect with.	`string`		1.0.0
`passwordCharacterEncoding`	Defines the character set used to encode the password. When not set, the connector uses the platform's default character set.	`string`		1.5.9	3.0.3
`permitMysqlScheme`	Enables the use of `jdbc:mysql:` as the protocol in connection strings.	`boolean`	`false`	3.0.3
`pipe`	For Windows. Defines the name of the named pipe to use for connections to localhost via named pipe. Default named pipe name is `MySQL`.	`string`		1.1.3
`pinGlobalTxToPhysicalConnection`					3.0.3
`prepStmtCacheSize`	Defines the number of prepared statements that are cached for each connection. This parameter only applies if `cachePrepStmts` is enabled.	`int`	`250`	1.3.0
`prepStmtCacheSqlLimit`	Defines the maximum length for a prepared statement in the cache. This parameter only applies if cachePrepStmts is enabled.	`int`	`2048`	1.3.0	3.0.3
`profileSql`		`boolean`	`false`	1.5.0	3.0.3
`restrictedAuth`	Sets a comma-separated list of client authentication plugins to allow. The following client authentication plugins are supported: `auth_gssapi_client` `caching_sha2_password` `client_ed25519` `dialog` `mysql_clear_password` `mysql_native_password`	`string`	`null`	3.0.3
`retriesAllDown`		`int`	`120`	1.2.0	3.0.3
`rewriteBatchedStatements`		`boolean`	`false`	1.1.8	3.0.3
`serverSslCert`	Defines the server certificate or the CA chain. It accepts: An absolute path to a file in PEM format: `/path/to/cert.pem` A path to a file in PEM format relative to the `CLASSPATH` in the format: `classpath:relative_path/cert.pem` A DER-encoded certificate string that begins with `------BEGIN CERTIFICATE-----`	`string`		1.1.3
`serverTimezone`				1.1.7	3.0.3
`sharedMemory`	For Windows. Defines the shared memory base name to use for connections to localhost via shared memory.	`string`		1.1.4	3.0.3
`slowQueryThresholdNanos`		`int`	`1024`	1.5.0	3.0.3
`socketFactory`	Defines the class to use for a custom socket factory. Specify the full name of the class that implements `javax.net.SocketFactory`.	`string`		1.0.0
`socketTimeout`	Defines the network socket timeout (`SO_TIMEOUT`) in milliseconds. When set to `0`, there is no socket timeout.	`int`	`0`	1.1.8
`sslMode`	Enables SSL/TLS in a specific mode. The following values are supported: `disable`: Do not use SSL/TLS (default) `trust`: Only use SSL/TLS for encryption. Do not perform certificate or hostname verification. This mode is not safe for production applications. `verify-ca`: Use SSL/TLS for encryption and perform certificates verification, but do not perform hostname verification. `verify-full`: Use SSL/TLS for encryption, certificate verification, and hostname verification. This new option replaces the deprecated options: `disableSslHostnameVerification` `trustServerCertificate` `useSsl`	`string`	`disable`	3.0.3
`staticGlobal`		`boolean`	`false`	2.2.0	3.0.3
`tcpKeepCount`	Sets the `TCP_KEEPCOUNT` socket option. Only valid when used with Java 11+.	`int`	`null`	3.0.3
`tcpKeepIdle`	Sets the `TCP_KEEPIDLE` socket option. Only valid when used with Java 11+.	`int`	`null`	3.0.3
`tcpKeepInterval`	Sets the `TCP_KEEPINTERVAL` socket option. Only valid when used with Java 11+.	`int`	`null`	3.0.3
`tcpNoDelay`				1.0.0	3.0.3
`tcpRcvBuf`				1.0.0	3.0.3
`tcpSndBuf`				1.0.0	3.0.3
`trackSchema`		`boolean`	`true`	2.5.4	3.0.3
`transactionReplay`	Enables transaction caching. If a failover occurs before a transaction is committed or rolled back, the transaction's cached statements are re-executed on the new primary server. Connector/J requires that applications only use idempotent queries. If the number of statements in the transaction cache exceeds `transactionReplaySize`, caching will be disabled until the transaction is committed or rolled back.	`boolean`	`false`	3.0.3
`transactionReplaySize`	Sets the number of statements that should be saved in the transaction cache when `transactionReplay` is enabled.	`int`	`64`	3.0.3
`trustServerCertificate`	Deprecated in 3.0.3. Use `sslMode=verify-ca` in 3.0.3 and later.	`boolean`	`false`	1.1.1
`trustStore`		`string`		1.3.4	3.0.3
`trustStorePassword`		`string`		1.3.4	3.0.3
`trustStoreType`		`string`	`null`	2.4.0	3.0.3
`useBatchMultiSend`		`boolean`	`true`	1.5.0	3.0.3
`useBatchMultiSendNumber`		`int`	`100`	1.5.0	3.0.3
`useFractionalSeconds`		`boolean`	`true`	1.0.0	3.0.3
`useLegacyDatetimeCode`					3.0.3
`useOldAliasMetadataBehavior`		`boolean`	`false`	1.1.9	3.0.3
`usePipelineAuth`	Defines whether pipelines are used to send internal queries during the initial authentication and connection process. Several queries are executed during this stage. When pipelines are used, all queries are sent simultaneously, and then all results are reads. This approach can decrease overhead and permit faster connection creation.	`boolean`	`true`	1.6.0
`user`	Defines the user name of the user account to connect with.	`string`	Current user	1.0.0
`useServerPrepStmts`	Defines whether the connector uses server-side prepared statements using the PREPARE PREPARE, EXECUTE EXECUTE, and DROP PREPARE DROP PREPARE statements. By default, the connector uses client-side prepared statements. If the `rewriteBatchedStatements` parameter is set to `true`, this parameter will be set to `false`.	`boolean`	`false`	1.3.0
`useSsl`	Defines whether TLS is used for the connection. Deprecated in 3.0.3. Use `sslMode` in 3.0.3 and later.	`boolean`	`false`	1.1.0
`validConnectionTimeout`	Defines the valid connection timeout in seconds. When the connector is using multiple hosts and a connection has not been used longer than the configured valid connection timeout, the connector will verify that the connection is still valid. When set to `0`, no connection validation is performed.	`int`	`0`	1.2.0	3.0.3

Code Example: Connect

The following example program opens a single database connection to a server at the IP address 192.0.2.1 using the user account created in the example setup. If your application requires multiple database connections, see Connection Pools for a more appropriate example.

import java.sql.*;
import java.util.Properties;

public class App {
    public static void main(String[] argv) {

        Properties connConfig = new Properties();
        connConfig.setProperty("user", "db_user");
        connConfig.setProperty("password", "db_user_password");

        try (Connection conn = DriverManager.getConnection(
               "jdbc:mariadb://192.0.2.1:3306", connConfig)) {

            // Use Connection
            // ...
        } catch (Exception e) {

            e.printStackTrace();
        }
    }
}

The following example programs open a single database connection using the user account created in the example setup.

Connector/J 3.0

In MariaDB Connector/J 3.0, TLS is enabled for connections to SkySQL using the sslMode parameter.

import java.sql.*;
import java.util.Properties;

public class App {
    public static void main(String[] argv) {
        Properties connConfig = new Properties();
        connConfig.setProperty("user", "db_user");
        connConfig.setProperty("password", "db_user_password");
        connConfig.setProperty("sslMode", "verify-full");
        connConfig.setProperty("serverSslCert", "/path/to/skysql_chain.pem");

        try (Connection conn = DriverManager.getConnection(
               "jdbc:mariadb://HOST:PORT", connConfig)) {
            // Use Connection
            // ...
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Connector/J 2.7

In MariaDB Connector/J 2.7 and before, TLS is enabled for connections to SkySQL using the useSsl parameter.

import java.sql.*;
import java.util.Properties;

public class App {
    public static void main(String[] argv) {
        Properties connConfig = new Properties();
        connConfig.setProperty("user", "db_user");
        connConfig.setProperty("password", "db_user_password");
        connConfig.setProperty("useSsl", "true");
        connConfig.setProperty("serverSslCert", "/path/to/skysql_chain.pem");

        try (Connection conn = DriverManager.getConnection(
               "jdbc:mariadb://HOST:PORT", connConfig)) {
            // Use Connection
            // ...
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}