= 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|DriverManager]].
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=myPwd");
}}}

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


==== Using a Pool
Driver provide 2 differents Datasource pool implementations :

MariaDbDataSource: The existing basic implementation. A new connection each time the getConnection() method is called.
MariaDbPoolDataSource: Connection pooling implementation. MariaDB Driver will keep a pool of connections and borrow connections when asked for it.

==== Internal pool
Driver internal pool configuration provide a very fast pool implementation and deals with the issue most of the java pool have :
* 2 different connection state cleaning after release
* deals with non-activity (connection in the pool will be released if not used after some time, avoiding issue created when server close connection after @wait_timeout is reached).

See [[use-mariadb-connector-j-driver.creole#using-pooling|pool documentation]] for more information.

==== 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|replica))]
}}}

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 replicas.

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

=== Having MariaDB and MySQL driver in the same classpath
Since MariaDB want ambition to be a droppin replacement for mysql, driver permit connection string  beginning with "jdbc:mariadb" or "jdbc:mysql".
To permit having MySQL and MariaDB driver are on the same classpath, since version 1.5.9, MariaDB driver doesn't accept connection string beginning with "jdbc:mysql" if option "disableMariaDbDriver" is set.

{{{jdbc:mysql://localhost:3306/db?user=someUser&disableMariaDbDriver}}} won't be accepted by MariaDB driver.


== Failover parameters

Failover was introduced in Connector/J 1.2.0.

|=sequential |Failover 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:mariadb: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//|
|=failover | High availability (random picking connection initialisation) with failover support for master replication cluster (for example Galera). \\//since 1.2.0//|
|=replication | High availability (random picking connection initialisation) with failover support for master/replica replication cluster (one or multiple masters) \\//since 1.2.0//|
|=aurora | High availability (random picking connection initialisation) with failover support for Amazon Aurora replication cluster \\//since 1.2.0//|

See [[failover-and-high-availability-with-mariadb-connector-j|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
|=user|Database user name. \\//since 1.0.0//|
|=password|Password of database user.\\//since 1.0.0//|
|=rewriteBatchedStatements| For insert queries, rewrite batchedStatement to execute in a single executeQuery.\\example:\\{{{insert into ab (i) values (?)}}} with first batch values = 1, second = 2 will be rewritten\\{{{insert into ab (i) values (1), (2)}}}. \\\\If query cannot be rewriten in "multi-values", rewrite will use multi-queries : {{{INSERT INTO TABLE(col1) VALUES (?) ON DUPLICATE KEY UPDATE col2=?}}} with values [1,2] and [2,3]" will be rewritten\\{{{INSERT INTO TABLE(col1) VALUES (1) ON DUPLICATE KEY UPDATE col2=2;INSERT INTO TABLE(col1) VALUES (3) ON DUPLICATE KEY UPDATE col2=4}}}\\\\when active, the useServerPrepStmts option is set to false\\//Default: false. Since 1.1.8//|
|=connectTimeout| The connect timeout value, in milliseconds.\\//Default: DriverManager.getLoginTimeout() value if set or 30s. Since 1.1.8//|
|=useServerPrepStmts|Queries are prepared on the server side before executing, permitting faster execution next time. \\if rewriteBatchedStatements is set to true, this option will be set to false.\\//Default: true. Since 1.3.0//|
|=useBatchMultiSend|*Not compatible with aurora* Driver will can send queries by batch. \\If disable, queries are send one by one, waiting for result before sending next one. \\If enable, queries will be send by batch corresponding to option useBatchMultiSendNumber value (default 100) or according to server variable @@max_allowed_packet if packet size cannot permit to send as many queries. Results will be read afterwhile, avoiding a lot of network latency when client and server aren't on same host. \\\\ There is 2 differents use case : JDBC executeBatch() and when option useServerPrepStmts is enable and MariaDB server >= 10.2.1, PREPARE commands will be delayed, to send PREPARE + EXECUTE in the same packet. This option if mainly effective when client is distant from server. [[./use-batch-multi-send-description.creole|more information]]\\//Default: true (false if using aurora failover). Since 1.5.0//|
\\

=== Pooling

See [[use-mariadb-connector-j-driver.creole#using-pooling|using pooling]] for more information.

|=pool|Use pool. This option is useful only if not using a DataSource object, but only connection. \\//Default: false. since 2.2.0//|
|=poolName|Pool name that will permit to identify thread.\\default: auto-generated as MariaDb-pool-<pool-index>//since 2.2.0//|
|=maxPoolSize| The maximum number of physical connections that the pool should contain. \\//Default: 8. since 2.2.0//|
|=minPoolSize| When connection are removed since not used since more than "maxIdleTime", connections are closed and removed from pool. "minPoolSize" indicate the number of physical connections the pool should keep available at all times. Should be less or equal to maxPoolSize.\\//Default: maxPoolSize value. Since 2.2.0//|
|=poolValidMinDelay| When asking a connection to pool, Pool will validate connection state. "poolValidMinDelay" permit to disable this validation if connection has been borrowed recently avoiding useless verification in case of frequent reuse of connection. 0 meaning validation is done each time connection is asked.\\//Default: 1000 (in milliseconds). Since 2.2.0//|
|=maxIdleTime|The maximum amount of time in seconds that a connection can stay in pool when not used. This value must always be below @wait_timeout value - 45s \\//Default: 600 in seconds (=10 minutes), minimum value is 60 seconds. Since 2.2.0//|
|=staticGlobal|Indicate the following global variable (@@max_allowed_packet,@@wait_timeout,@@autocommit,@@auto_increment_increment,@@time_zone,@@system_time_zone,@@tx_isolation) values won't changed, permitting to pool to create new connection faster.\\//Default: false. Since 2.2.0//|
|=useResetConnection|When a connection is closed() (give back to pool), pool reset connection state. Setting this option, session variables change will be reset, and user variables will be destroyed when server permit it (MariaDB >= 10.2.4, MySQL >= 5.7.3), permitting to save memory on server if application make extensive use of variables\\//Default: false. Since 2.2.0//|

\\


=== TLS (SSL)

|=useSSL|Force [[https://mariadb.com/kb/en/mariadb/secure-connections-overview/secure-connections-overview|SSL/TLS on  connection]].\\//Default: false. Since 1.1.0//|
|=trustServerCertificate|When using SSL/TLS, do not check server's certificate.\\//Default: false. Since 1.1.1//|
|=serverSslCert|Server'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//|
|=keyStore|File path of the keyStore file that contain client private key store and associate certificates (similar to java System property "javax.net.ssl.keyStore", but ensure that only the private key's entries are used).(legacy alias clientCertificateKeyStoreUrl).\\//Since 1.3.4//|
|=keyStorePassword|Password for the client certificate keyStore  (similar to java System property "javax.net.ssl.keyStorePassword").(legacy alias clientCertificateKeyStorePassword)\\//Since 1.3.4//|
|=keyPassword|Password for the private key in client certificate keyStore. (only needed if private key password differ from keyStore password).\\//Since 1.5.3//|
|=trustStore|File path of the trustStore file (similar to java System property "javax.net.ssl.trustStore"). (legacy alias trustCertificateKeyStoreUrl)\\Use the specified file for trusted root certificates.\\When set, overrides serverSslCert.\\//Since 1.3.4//|
|=trustStorePassword|Password for the trusted root certificate file (similar to java System property "javax.net.ssl.trustStorePassword").\\(legacy alias trustCertificateKeyStorePassword).\\//Since 1.3.4//|
|=enabledSslProtocolSuites|Force TLS/SSL protocol to a specific set of TLS versions (comma separated list). \\Example : "TLSv1, TLSv1.1, TLSv1.2"\\//Default: TLSv1, TLSv1.1" before 2.3.0, "TLSv1, TLSv1.1, TLSv1.2" since v2.3.0". Since 1.5.0//|
|=enabledSslCipherSuites|Force TLS/SSL cipher (comma separated list).\\ Example : "TLS_DHE_RSA_WITH_AES_256_GCM_SHA384, TLS_DHE_DSS_WITH_AES_256_GCM_SHA384"\\//Default: use JRE ciphers. Since 1.5.0//|

\\

=== Log
 |=log|Enable log information. \\//require Slf4j version > 1.4 dependency.//\\Log level correspond to Slf4j logging implementation\\//Default: false. Since 1.5.0//|
 |=maxQuerySizeToLog|Only the first characters corresponding to this options size will be displayed in logs\\//Default: 1024. Since 1.5.0//|
 |=slowQueryThresholdNanos|Will log query with execution time superior to this value (if defined )\\//Default: 1024. Since 1.5.0//|
 |=profileSql|log query execution time.\\//Default: false. Since 1.5.0//|

\\
=== Infrequently used

|=useFractionalSeconds| Correctly 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//|
|=allowMultiQueries| permit multi-queries like {{{insert into ab (i) values (1); insert into ab (i) values (2)}}}. //Default: false. Since 1.0.0//|
|=dumpQueriesOnException|If set to 'true', an exception is thrown during query execution containing a query string.\\//Default: false. Since 1.1.0//|
|=useCompression|allow compression in the MySQL Protocol.\\//Default: false. Since 1.0.0//|
|=socketFactory| to use a custom socket factory, set it to the  full name of the class that implements javax.net.SocketFactory.\\//since 1.0.0//|
|=tcpNoDelay|Sets corresponding option on the connection socket.\\//since 1.0.0//|
|=tcpKeepAlive|Sets corresponding option on the connection socket.\\//since 1.0.0//|
|=tcpAbortiveClose|Sets corresponding option on the connection socket.\\//since 1.1.1//|
|=tcpRcvBuf| set buffer size for TCP buffer (SO_RCVBUF).\\//since 1.0.0//|
|=tcpSndBuf| set buffer size for TCP buffer (SO_SNDBUF).\\//since 1.0.0//|
|=pipe| On Windows, specify named pipe name to connect to mysqld.exe. When using pipe, named pipe can throw an exception ERROR_PIPE_BUSY if all pipe are busy. Please consider setting option connectTimeout too, to permit retry connection in those particular cases. \\//since 1.1.3//|
|=tinyInt1isBit| Datatype mapping flag, handle MySQL Tiny as BIT(boolean).\\//Default: true. Since 1.0.0//|
|=yearIsDateType|Year 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//|
|=localSocket|Permits 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//|
|=sharedMemory|Permits 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//|
|=localSocketAddress|Hostname or IP address to bind the connection socket to a local (UNIX domain) socket.\\//since 1.1.7//|
|=socketTimeout|Defined the network socket timeout (SO_TIMEOUT) in milliseconds. Value of 0 disable this timeout.. \\Default: 0 (standard configuration) or 10 000ms (using "aurora" failover configuration).\\//since 1.1.7//|
|=interactiveClient|Session timeout is defined by the [[https://mariadb.com/kb/en/server-system-variables/#wait_timeout|wait_timeout]] server variable. Setting interactiveClient to true will tell the server to use the [[https://mariadb.com/kb/en/mariadb/server-system-variables/#interactive_timeout|interactive_timeout]] server variable.\\//Default: false. Since 1.1.7//|
|=useOldAliasMetadataBehavior|Metadata 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//|
|=createDatabaseIfNotExist|the specified database in the url will be created if nonexistent.\\//Default: false. Since 1.1.7//|
|=serverTimezone|Defines 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//|
|=prepStmtCacheSize| if useServerPrepStmts = true, defines the prepared statement cache size. \\//Default: 250. Since 1.3.0//|
|=prepStmtCacheSqlLimit| if useServerPrepStmts = true, defined queries larger than this size will not be cached. \\//Default: 2048. Since 1.3.0//|
|=jdbcCompliantTruncation| Truncation 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//|
|=cacheCallableStmts| enable/disable callable Statement cache\\//Default: true. Since 1.4.0//|
|=callableStmtCacheSize| This sets the number of callable statements that the driver will cache per VM if "cacheCallableStmts" is enabled.\\//Default: true. Since 1.4.0//|
|=useBatchMultiSendNumber| When option useBatchMultiSend is active, indicate the maximum query send in a row before reading results.\\//Default: 100. Since 1.5.0//|
|=connectionAttributes| When performance_schema is active, permit to send server some client information in a key;value pair format (example: connectionAttributes=key1:value1,key2,value2).\\Those informations can be retrieved on server within tables performance_schema.session_connect_attrs and performance_schema.session_account_connect_attrs.\\This can permit from server an identification of client/application\\//Since 1.4.0//|
|=continueBatchOnError| When executing batch queries, must batch continue on error and throw exception when ended, or stop immediately \\//Default: true. Since 1.4.0//
|=disableSslHostnameVerification| When using ssl, driver check hostname against the server's identity as presented in the server's Certificate (checking alternative names or certificate CN) to prevent man-in-the-middle attack. This option permit to deactivate this validation.\\//Default: false. Since 2.1.0//
|=autocommit|Set default autocommit value.\\//Default: true. Since 2.2.0//
|=galeraAllowedState|Usually, Connection.isValid just send an empty packet to server, and server send a small response to ensure connectivity. When this option is set, connector will ensure Galera server state "wsrep_local_state" correspond to allowed values (separated by comma). example "4,5", recommended is "4". see [galera state](http://galeracluster.com/documentation-webpages/nodestates.html#node-state-changes) to know more..\\//Default: empty. Since 2.2.5//
|=useAffectedRows|default correspond to the JDBC standard, reporting real affected rows. if enable, will report "affected" rows. example : if enable, an update command that doesn't change a row value will still be "affected", then report.\\//Default: false. Since 2.2.6//
|=includeInnodbStatusInDeadlockExceptions|add "SHOW ENGINE INNODB STATUS" result to exception trace when having a deadlock exception\\//Default: false. Since 2.3.0//
|=includeThreadDumpInDeadlockExceptions|add thread dump to exception trace when having a deadlock exception\\//Default: false. Since 2.3.0//
|=blankTableNameMeta|Result-set metadata getTableName always return blank. This option is mainly for ORACLE db compatibility\\//Default: false. Since 2.4.3//

\\\\
== Failover/High availability URL parameters

|=autoReconnect|With 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 replica connection) \\//Default is false. Since 1.1.7//|
|=retriesAllDown|When searching a valid host, maximum number of connection attempts before throwing an exception.\\//Default: 120 seconds. Since 1.2.0//|
|=failoverLoopRetries|When 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 replica connection when using the master connection\\//Default: 120. Since 1.2.0//|
|=validConnectionTimeout|With 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//|
|=loadBalanceBlacklistTimeout|When 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//|
|=assureReadOnly|If 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//|
|=allowMasterDownConnection|When using master/replica configuration, permit to create connection when master is down. If all masters are down, default connection is then a replica and Connection.isReadOnly() will then return true. \\//Default: false. Since 2.2.0//|
|=galeraAllowedState|Usually, Connection.isValid just send an empty packet to server, and server send a small response to ensure connectivity. When this option is set, connector will ensure server that "wsrep_local_state" correspond to allowed values (separated by comma). example "4,5".\\//Default: empty. Since 2.2.5//|
\\\\

= JDBC API implementation notes

== "LOAD DATA INFILE"
The fastest way to load lots of data is using [[https://mariadb.com/kb/en/mariadb/load-data-infile/|LOAD DATA INFILE]]. \\However, using "LOAD DATA LOCAL INFILE" (ie : loading a file from client) may be a security problem :
* A "man in the middle" proxy server can change the actual file requested from the server so the client will send a local file to this proxy.
* if someone can execute a query from the client, he can have access to any file on the client (according to the rights of the user running the client process).

A specific option "allowLocalInfile" (default to false) permit to enable functionality on the client side.
The global variable [[server-system-variables#local_infile|local_infile]] can disable LOAD DATA LOCAL INFILE on the server side.

A non-JDBC method can permit using this kind of query without this security issue: The application has to create an InputStream with the file to load. If MariaDbStatement.setLocalInfileInputStream(InputStream inputStream) is set, the inputStream will be sent to the server, replacing the file content.

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");
        }

}}}

Since 1.5.0, Interceptors can now filter LOAD DATA LOCAL INFILE queries according to filename.

These interceptors must implement the {{{org.mariadb.jdbc.LocalInfileInterceptor}}} interface.
Interceptors use the [[http://docs.oracle.com/javase/7/docs/api/java/util/ServiceLoader.html|ServiceLoader]] pattern, so interceptors must be defined in the META-INF/services/org.mariadb.jdbc.LocalInfileInterceptor file.

Example :
create the META-INF/services/org.mariadb.jdbc.LocalInfileInterceptor file with content org.project.LocalInfileInterceptorImpl.
{{{
 public class LocalInfileInterceptorImpl implements LocalInfileInterceptor {
     @Override
     public boolean validate(String fileName) {
         File file = new File(fileName);
         String absolutePath = file.getAbsolutePath();
         String filePath = absolutePath.substring(0,absolutePath.lastIndexOf(File.separator));
         return filePath.equals("/var/tmp/exchanges");
     }
 }
}}}

You can avoid defining the META-INF/services file using [[https://github.com/google/auto/tree/master/service|google auto-service]] framework
Using the previous example, just add {{{@AutoService(LocalInfileInterceptor.class)}}}, and your interceptor will be automatically defined.
{{{
 @AutoService(LocalInfileInterceptor.class)
 public class LocalInfileInterceptorImpl implements LocalInfileInterceptor {
     @Override
     public boolean validate(String fileName) {
         File file = new File(fileName);
         String absolutePath = file.getAbsolutePath();
         String filePath = absolutePath.substring(0,absolutePath.lastIndexOf(File.separator));
         return filePath.equals("/var/tmp/exchanges");
     }
 }
}}}


== Using pooling

MariaDB has 2 different Datasource implementation :
* MariaDbDataSource : Basic implementation. A new connection each time method getConnection() is called.
* MariaDbPoolDataSource : Connection pooling implementation (since 2.2.0 version). MariaDB Driver will keep a pool of connection and borrow Connections when asked for it.

When using MariaDbPoolDataSource, different options permit to indicate how pooling will handle pool. See [[use-mariadb-connector-j-driver.creole#pooling|pooling options]] for detail information of options.

Example of use :
{{{
    MariaDbPoolDataSource pool = new MariaDbPoolDataSource("jdbc:mariadb://server/db?user=myUser&maxPoolSize=10");

    try (Connection connection = pool.getConnection()) {
        Statement statement = connection.createStatement();
        statement.execute("SELECT * FROM mysql.user");
    }

    try (Connection connection = pool.getConnection()) {
        Statement statement = connection.createStatement();
        statement.execute("SELECT * FROM mysql.user");
    }

    pool.close();
}}}

Pooling can be configured at connection level using the "pool" option: (The main difference is that there is no accessible object to close pool if needed.)
{{{
    //indicating &pool(=true) indicate that pool will be initialized
    String connectionString = "jdbc:mariadb://server/db?user=myUser&maxPoolSize=10&pool";
    try (Connection connection = DriverManager.getConnection(connectionString)) {
        try (Statement statement = connection.createStatement()) {
            statement.execute("SELECT * FROM mysql.user");
        }
    }

    //since reusing the same connection string, connection will be get from pool
    try (Connection connection = DriverManager.getConnection(connectionString)) {
        try (Statement statement = connection.createStatement()) {
            statement.execute("SELECT 'another query'");
        }
    }
}}}



==== Pool connection handling :
When a new connection is asked to pool, if there is an existing connection not used, Connection validation is done then borrowed directly.
If no connection is available, the request for a connection will be put in a queue until connection timeout.
When a connection is available (new creation or released to the pool), it will be use to satisfy queued request in FIFO order.

A dedicated thread will handle new connection creation (one by one) to avoid connection burst.
This thread will create connections until "maxPoolSize" if needed with a minimum connection of "minPoolSize".

99.99% of the time, connection is created, a few queries are executed, then connection is released.
Creating connection one after another permit to handle sudden peak of connection, to avoid creating lot of connections immediately and drop them after idle timeout:

EXAMPLE

Configuration
* minPoolSize=2
* maxPoolSize=20

Pool with no activity (only 2 connection in pool).
* Average query executing : 0.1 milliseconds
* Connection creation : 5 milliseconds


If a sudden peak of 50 connections are needed to execute one query, that mean that there will be 2 connection borrowed and released 25 times each.
So all queries will be handled in 2.5 milliseconds, less than one Connection creation. 2.5 milliseconds after that pike of demand, there will be 3 connections in pool.

If peak continue, then connections in pool will quickly increase.


==== Connection close:
On connection closing, borrowed connection state will be reset, then give back to pool.
This reset goal is that next Connection get from pool has the same state as a newly "fresh" created connection.

Reset operations :
* rollback remaining active transaction
* reuse the configured database if changed
* default connection read-only state to false (master in a masters/replicas configuration) if changed
* re-initialize socket timeout if changed
* autocommit reset to default
* Transaction Isolation if changed

If server version is >= 10.2.4 (5.7.3 for MySQL server), then option "useResetConnection" can be used. This option will delete all user variables, and reset session variables to their initial state.
\\

==== Idle timeout Thread
An additional thread will periodically close idle connections not used for a time corresponding to option "maxIdleTime".
Pool will ensure to recreate connection to satisfy the option "minPoolSize" value.

This avoids keeping unused connection in pool, overloading server uselessly.
If option "staticGlobal" is set, driver will ensure that option "maxIdleTime" is less than server @@wait_timeout.

\\

==== Connection performance boost.
Driver has the advantage to know current server state, permitting fast pooling :
When creating a connection, java driver need to execute 1 or 2 additional query after socket initialization / ssl initialization.
If the following variables don't change (rarely changed) :
* @@max_allowed_packet
* @@wait_timeout
* @@autocommit
* @@auto_increment_increment
* @@time_zone
* @@system_time_zone
* @@tx_isolation
, using the option "staticGlobal", those value will be kept in memory, avoiding any additional queries when establishing a new connection (connection creation can be 30% faster, depending on network)

Statement.cancel, Connection.abort() methods using pool are super fast, because of reusing a connection from pool.

Each time a connection is asked, pool validate the connection exchanging an empty MySQL packet with the server to ensure connection state. But pool reuse connection intensively, so this validation is done only if Connection has not been use since some time (option "poolValidMinDelay" with the default value of 1000ms).
\\

==== JMX
JMX give some information. MBeans name are like "org.mariadb.jdbc.pool:type=*".

Some statistics of current pool :
* long getActiveConnections();  -> indicate current used connection
* long getTotalConnections();   -> indicate current number of connections in pool
* long getIdleConnections();    -> indicate the number of connection currently not used
* long getConnectionRequests(); -> indicate threads number that wait for a connection.

One method to reset "staticGlobal" value :
    void resetStaticGlobal(); -> method to reset staticGlobal values in memory.

Example accessing JMX through java :
{{{
try (MariaDbPoolDataSource pool = new MariaDbPoolDataSource(connUri + "jdbc:mariadb://localhost/testj?user=root&maxPoolSize=5&minPoolSize=3&poolName=PoolTestJmx")) {

    try (Connection connection = pool.getConnection()) {

        MBeanServer server = ManagementFactory.getPlatformMBeanServer();
        ObjectName filter = new ObjectName("org.mariadb.jdbc.pool:type=PoolTest*");
        Set<ObjectName> objectNames = server.queryNames(filter, null);
        ObjectName name = objectNames.iterator().next();

        System.out.println(server.getAttribute(name, "ActiveConnections"));  //1
        System.out.println(server.getAttribute(name, "TotalConnections"));   //3
        System.out.println(server.getAttribute(name, "IdleConnections"));    //2
        System.out.println(server.getAttribute(name, "ConnectionRequests")); //0
    }
}
}}}

\\


== Streaming result sets
By default, {{{Statement.executeQuery()}}} will read the full result set from the server.
With large result sets, this will require large amounts of memory. \\

To avoid using too much memory, a better behaviour is using Statement.setFetchSize(int numberOfRowInMemory) to indicate the number of row that will be store on memory\\
\\
Example :\\
Using {{{Statement.setFetchSize(1000) }}} indicate that 1000 rows will be stored in memory.\\
So, when query execute, 1000 rows will be in memory. After 1000 {{{ResultSet.next()}}}, next 1000 rows will be stored in memory, and so on.

Note that server usually expects client to read off the result set relatively fast. Server variable "net_write_timeout" controls this behavior (default to 60s).
If you doesn't expect results to be handled in this amount of time there is different possibility :
* if your server version > 10.1.2, you can use the query "SET STATEMENT net_write_timeout=10000 FOR XXX" with XXX your "normal" query. This will indicate that specifically for this query, net_write_timeout will be set to a longer time (10000 in this example).
* for older servers, a specific query will have to set net_write_timeout temporary ("SET STATEMENT net_write_timeout=..."), and set it back afterwards.
* if your application usually use a lot of long queries with fetch size, Connection can be set using option "sessionVariables=net_write_timeout=xxx"

Even using setFetchSize, Server will send all results to client. Sending another query on the same connection will throw an exception until all results aren't read

== Prepared statements
The driver uses server prepared statements as a standard to communicate with the database (since 1.3.0). If the "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 ([[https://mariadb.com/kb/en/mariadb/mysqlproc-table/|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
[[https://mariadb.com/kb/en/mariadb/mysqlproc-table/|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
{{{
try (Connection  connection = DriverManager.getConnection("jdbc:mariadb://localhost:3306/test", "username", "password")) {
    try (Statement stmt = connection.createStatement()) {
        stmt.executeUpdate("CREATE TABLE a (id int not null primary key, value varchar(20))");
    }
}
}}}