Connect with MariaDB Connector/J

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.

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

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)

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:

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:

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 PREPAREPREPARE, EXECUTEEXECUTE, and DROP PREPAREDROP 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();
        }
    }
}