About MariaDB Connector/J

MariaDB Connector/J is used to connect applications developed in Java to MariaDB and MySQL databases using the standard JDBC API. The library is LGPL licensed.

DateReleaseStatusRelease NotesChangelog
15 Jun 2016MariaDB Connector/J 1.4.6Stable (GA)Release NotesChangelog
24 May 2016MariaDB Connector/J 1.4.5Stable (GA)Release NotesChangelog
6 May 2016MariaDB Connector/J 1.4.4Stable (GA)Release NotesChangelog

see all releases

About MariaDB Connector/J

MariaDB Connector/J is a Type 4 JDBC driver. It was developed specifically as a lightweight JDBC connector for use with MySQL and MariaDB database servers. It's originally based on the Drizzle JDBC code with numerous additions and bug fixes.

Obtaining the driver

MariaDB Connector/J source code tarballs can be downloaded from: https://downloads.mariadb.org/connector-java/

Pre-built .jar files can be downloaded from: https://mariadb.com/my_portal/download/java-client

Installing the driver

Installation of the client library is very simple. The jar file should be saved in an appropriate place for your application and the classpath of your application altered to include MariaDB Connector/J rather than your current connector.

Using maven :

<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
    <version>xxx</version>
</dependency>

Requirements

  • Java 7 or 8 (Last compatible version with java 6 is 1.1.9)
  • com.sun.JNA is used by some library functions and a jar is available at https://github.com/twall/jna
    • only needed when connecting to the server with unix sockets or windows shared memory
  • A MariaDB or MySQL Server
  • maven (only if you want to build from source)

Source code

The source code is available on GitHub: https://github.com/MariaDB/mariadb-connector-j and the most recent development version can be obtained using the following command:

git clone https://github.com/MariaDB/mariadb-connector-j.git

License

GNU Lesser General Public License as published by the Free Software Foundation; either version 2.1 of the License, or (at your option) any later version.

Building and testing the driver

This section deals with building the connector from source and testing it. If you have downloaded a ready-built connector, in a jar file, then this section may be skipped.

MariaDB Client Library for Java Applications uses maven for building. You first need to ensure you have both java and maven installed on your server before you can build the driver.

To run the unit test, you'll need a MariaDB or MySQL server running on localhost (on the default TCP port 3306) and a database called 'test', and user 'root' with an empty password

git clone https://github.com/MariaDB/mariadb-connector-j.git #  Or, unpack the source distribution tarball
cd mariadb-connector-j
# For the unit test run, start local mysqld mysqld, 
# ensure that user root with empty password can login
mvn package
# If you want to build without running unit  tests, use
# mvn -Dmaven.test.skip=true package

After that, you should have JDBC jar mariadb-java-client-x.y.z.jar in the 'target' subdirectory

Using the driver

The following subsections show the formatting of JDBC connection strings for MariaDB and MySQL database servers. Additionally, sample code is provided that demonstrates how to connect to one of these servers and create a table.

Getting a new connection

There are two standard ways to get a connection:

Using DriverManager

The prefered way to connect is to use https:docs.oracle.com/javase/7/docs/api/java/sql/DriverManager.html. Applications designed to use the driver manager to locate the entry point need no further configuration. MariaDB Connector/J will automatically be loaded and used in the way any previous MySQL driver would have been.

Example:

Connection connection = DriverManager.getConnection("jdbc:mariadb://localhost:3306/DB?user=root&password=myPassword");

The legacy way of loading a JDBC driver (using Class.forName("org.mariadb.jdbc.Driver")) still works.

Using external pool

When using an external connection pool, the mariadb Driver class org.mariadb.jdbc.Driver must be configured.

Example using hikariCP JDBC connection pool :

        final HikariDataSource ds = new HikariDataSource();
        ds.setMaximumPoolSize(20);
        ds.setDriverClassName("org.mariadb.jdbc.Driver");
        ds.setJdbcUrl("jdbc:mariadb://localhost:3306/db");
        ds.addDataSourceProperty("user", "root");
        ds.addDataSourceProperty("password", "myPassword");
        ds.setAutoCommit(false);

Please note that the driver class provided by MariaDB Connector/J is not com.mysql.jdbc.Driver but org.mariadb.jdbc.Driver!

The org.mariadb.jdbc.MariaDbDataSource class can be used when the pool datasource configuration only permits the java.sql.Datasource implementation.

Connection strings

The format of the JDBC connection string is

jdbc:(mysql|mariadb):[replication:|failover:|sequential:|aurora:]//<hostDescription>[,<hostDescription>...]/[database][?<key1>=<value1>[&<key2>=<value2>]] 

HostDescription:

<host>[:<portnumber>]  or address=(host=<host>)[(port=<portnumber>)][(type=(master|slave))]

Host must be a DNS name or IP address. In case of ipv6 and simple host description, the IP address must be written inside brackets. The default port is 3306. The default type is master. If replication failover is set, by default the first host is master, and the others are slaves.

Examples :

  • localhost:3306
  • [2001:0660:7401:0200:0000:0000:0edf:bdd7]:3306
  • somehost.com:3306
  • address=(host=localhost)(port=3306)(type=master)

Failover parameters

Failover was introduced in Connector/J 1.2.0.

sequentialFailover support for master replication cluster (for example Galera) without High availability. The hosts will be connected in the order in which they were declared.

Example when using the jdbc url string "jdbc:mysql:replication:host1,host2,host3/test" :
When connecting, the driver will always first try host1, and if not available host2 and so on. After a host fail, the driver will reconnect according to this order.
since 1.3.0
failoverHigh availability (random picking connection initialisation) with failover support for master replication cluster (for example Galera).
since 1.2.0
replicationHigh availability (random picking connection initialisation) with failover support for master/slave replication cluster (one or multiple masters)
since 1.2.0
auroraHigh availability (random picking connection initialisation) with failover support for Amazon Aurora replication cluster
since 1.2.0

See failover description for more information.

Optional URL parameters

General remark: Unknown options are accepted and silently ignored.

The following options are currently supported.

Essential options

userDatabase user name.
since 1.0.0
passwordPassword of database user.
since 1.0.0
rewriteBatchedStatementsFor insert queries, rewrite batchedStatement to execute in a single executeQuery.
example:
insert into ab (i) values (1)
insert into ab (i) values (2)
will be rewritten
insert into ab (i) values (1), (2).

when active, the useServerPrepStmts option is set to false
Default: false.
since 1.1.8
connectTimeoutThe connect timeout value, in milliseconds, or zero for no timeout.
Default: 0.
since 1.1.8


Infrequently used

useFractionalSecondsCorrectly handle subsecond precision in timestamps (feature available with MariaDB 5.3 and later).
May confuse 3rd party components (Hibernated).
Default: true.
since 1.0.0
allowMultiQueriesCombine batchedStatement queries to execute in a single executeQuery.
example:
insert into ab (i) values (1)
insert into ab (i) values (2)
will be combine in one query :
insert into ab (i) values (1); insert into ab (i) values (2).

Option inactive when rewriteBatchedStatements is active.
When active, the useServerPrepStmts option is set to false
Default: false.
since 1.0.0
dumpQueriesOnExceptionIf set to 'true', an exception is thrown during query execution containing a query string.
Default: false.
since 1.1.0
useCompressionallow compression in the MySQL Protocol.
Default: false.
since 1.0.0
useSSLForce SSL/TLS on connection.
Default: false.
since 1.1.0
trustServerCertificateWhen using SSL/TLS, do not check server's certificate.
Default: false.
since 1.1.1
serverSslCertServer's certificate in DER form, or server's CA certificate.
Can be used in one of 3 forms :
* sslServerCert=/path/to/cert.pem (full path to certificate)
* sslServerCert=classpath:relative/cert.pem (relative to current classpath)
* or as verbatim DER-encoded certificate string "------BEGING CERTIFICATE-----" .
since 1.1.3
socketFactoryto use a custom socket factory, set it to the full name of the class that implements javax.net.SocketFactory.
since 1.0.0
tcpNoDelaySets corresponding option on the connection socket.
since 1.0.0
tcpKeepAliveSets corresponding option on the connection socket.
since 1.0.0
tcpAbortiveCloseSets corresponding option on the connection socket.
since 1.1.1
tcpRcvBufset buffer size for TCP buffer (SO_RCVBUF).
since 1.0.0
tcpSndBufset buffer size for TCP buffer (SO_SNDBUF).
since 1.0.0
pipeOn Windows, specify named pipe name to connect to mysqld.exe.
since 1.1.3
tinyInt1isBitDatatype mapping flag, handle MySQL Tiny as BIT(boolean).
Default: true.
since 1.0.0
yearIsDateTypeYear is date type, rather than numerical.
Default: true.
since 1.0.0
sessionVariables<var>=<value> pairs separated by comma, mysql session variables, set upon establishing successful connection.
since 1.1.0
localSocketPermits connecting to the database via Unix domain socket, if the server allows it.
The value is the path of Unix domain socket (i.e "socket" database parameter : select @@socket) .
since 1.1.4
sharedMemoryPermits connecting to the database via shared memory, if the server allows it.
The value is the base name of the shared memory.
since 1.1.4
localSocketAddressHostname or IP address to bind the connection socket to a local (UNIX domain) socket.
since 1.1.7
socketTimeoutDefined the network socket timeout (SO_TIMEOUT) in milliseconds.
Default: 0 milliseconds(0 disable this timeout).
since 1.1.7
interactiveClientSession timeout is defined by the wait_timeout server variable. Setting interactiveClient to true will tell the server to use the interactive_timeout server variable.
Default: false.
since 1.1.7
useOldAliasMetadataBehaviorMetadata ResultSetMetaData.getTableName() returns the physical table name. "useOldAliasMetadataBehavior" permits activating the legacy code that sends the table alias if set.
Default: false.
since 1.1.9
createDatabaseIfNotExistthe specified database in the url will be created if nonexistent.
Default: false.
since 1.1.7
serverTimezoneDefines the server time zone.
to use only if the jre server has a different time implementation of the server.
(best to have the same server time zone when possible).
since 1.1.7
clientCertificateKeyStoreUrlUse the specified keystore for client certificates (can be the same as the trusted root certificate keystore).
*Default: false. Since 1.3.4*
clientCertificateKeyStorePasswordPassword for the client certificate keystore.
*Default: false. Since 1.3.4*
trustCertificateKeyStoreUrlUse the specified keystore for trusted root certificates. Overrides serverSslCert.
*Default: false. Since 1.3.4*
trustCertificateKeyStorePasswordPassword for the trusted root certificate keystore.
*Default: false. Since 1.3.4*
useServerPrepStmtsQueries are prepared on the server side before executing, permitting faster execution next time.
if allowMultiQueries or rewriteBatchedStatements is set to true, this option will be set to false.
*Default: true. Since 1.3.0*
prepStmtCacheSizeif useServerPrepStmts = true, defines the prepared statement cache size.
*Default: 250. Since 1.3.0*
prepStmtCacheSqlLimitif useServerPrepStmts = true, defined queries larger than this size will not be cached.
*Default: 2048. Since 1.3.0*
jdbcCompliantTruncationTruncation error ("Data truncated for column '%' at row %", "Out of range value for column '%' at row %") will be thrown as an error, and not as a warning.
*Default: true. Since 1.4.0*
cacheCallableStmtsenable/disable callable Statement cache
*Default: true. Since 1.4.0*
callableStmtCacheSizeThis sets the number of callable statements that the driver will cache per VM if "cacheCallableStmts" is enabled.
*Default: true. Since 1.4.0*



Failover/High availability URL parameters

autoReconnectWith basic failover: if true, will attempt to recreate connection after a failover.
With standard failover: if true, will attempt to recreate connection even if there is a temporary solution (like using a master connection temporary until reconnect to a slave connection)
Default is false.
since 1.1.7
retriesAllDownWhen searching a valid host, maximum number of connection attempts before throwing an exception.
Default: 120 seconds.
since 1.2.0
failoverLoopRetriesWhen searching silently for a valid host, maximum number of connection attempts.
This differs from the "retriesAllDown" parameter because this silent search is for example used after a disconnection of a slave connection when using the master connection
Default: 120.
since 1.2.0
validConnectionTimeoutWith multiple hosts, after this time in seconds has elapsed, verifies that the connections haven’t been lost.
When 0, no verification will be done.
Default:120 seconds
since 1.2.0
loadBalanceBlacklistTimeoutWhen a connection fails, this host will be blacklisted for the "loadBalanceBlacklistTimeout" amount of time.
When connecting to a host, the driver will try to connect to a host in the list of non-blacklisted hosts and, only if none are found, attempt blacklisted ones.
This blacklist is shared inside the classloader.
Default: 50 seconds.
since 1.2.0
assureReadOnlyIf true, in high availability, and switching to a read-only host, assure that this host is in read-only mode by setting the session to read-only.
Default to false.
Since 1.3.0



JDBC API implementation notes

"LOAD DATA INFILE"

The fastest way to load many datas is using query LOAD DATA INFILE.
Problem is using "LOAD DATA LOCAL INFILE" (ie : loading a file from client), may be a security problem :

  • if server sources has been changed, server mays asked for a different file than the file in query.
  • if someone has can execute query from client, he can have access to any file on client (according to the rights of the user running the client process).

A specific option "allowLocalInfile" (default to true) can deactivate functionality on client side. The global variable local_infile can disable LOAD DATA LOCAL INFILE on server side.

A non-JDBC method can permit to use this kind of query without those security issue: The application has to create a InputStream with the file to load. If MariaDbStatement.setLocalInfileInputStream(InputStream inputStream) is set, the inputStream will be send to server, replacing the file content (working even with option "allowLocalInfile" disabled).

Code example:

        Statement statement = ...
        InputStream in = new FileInputStream("/file.sql");        
        
        if (statement.isWrapperFor(MariaDbStatement.class)) {
            MariaDbStatement mariaDbStatement = statement.unwrap(MariaDbStatement.class);
            mariaDbStatement.setLocalInfileInputStream(in);
            String sql = "LOAD DATA LOCAL INFILE 'dummyFileName'"
                        + " INTO TABLE gigantic_load_data_infile "
                        + " FIELDS TERMINATED BY '\\t' ENCLOSED BY ''"
                        + " ESCAPED BY '\\\\' LINES TERMINATED BY '\\n'";
            statement.execute(sql);
        } else {
            in.close();
            throw new RuntimeException("Mariadb JDBC adaptor must be used");
        }

Streaming result sets

By default, Statement.executeQuery() will read the full result set from the server before returning. With large result sets, this will require large amounts of memory. Better behavior in this case would be reading row-by-row, with ResultSet.next(), so called "streaming". This is activated using Statement.setFetchSize(Integer.MIN_VALUE)

Prepared statements

The driver uses server prepared statements as a standard to communicate with the database (since 1.3.0). If the "allowMultiQueries" or "rewriteBatchedStatements" options are set to true, the driver will only use text protocol. Prepared statements (parameter substitution) is handled by the driver, on the client side.

CallableStatement

Callable statement implementation won't need to access stored procedure metadata (mysql.proc) table if both of following are true

  • CallableStatement.getMetadata() is not used
  • Parameters are accessed by index, not by name

When possible, following the two rules above provides both better speed and eliminates concerns about SELECT privileges on the mysql.proc table.

Optional JDBC classes

The following optional interfaces are implemented by the org.mariadb.jdbc.MariaDbDataSource class : javax.sql.DataSource, javax.sql.ConnectionPoolDataSource, javax.sql.XADataSource

careful : org.mariadb.jdbc.MySQLDataSource doesn't exist anymore and should be replaced with org.mariadb.jdbc.MariaDbDataSource since v1.3.0

Usage examples

The following code provides a basic example of how to connect to a MariaDB or MySQL server and create a table.

Creating a table on a MariaDB or MySQL server

Connection  connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "username", "password");
Statement stmt = connection.createStatement();
stmt.executeUpdate("CREATE TABLE a (id int not null primary key, value varchar(20))");
stmt.close();
connection.close();

Comments

Comments loading...
Loading