Skip to main content

The Reactive MySQL Client is a client for MySQL with a straightforward API focusing on scalability and low overhead.

Features

  • Event driven

  • Lightweight

  • Built-in connection pooling

  • Prepared queries caching

  • Cursor support

  • Row streaming

  • RxJava 1 and RxJava 2

  • Direct memory to object without unnecessary copies

  • Java 8 Date and Time

  • Stored Procedures support

  • TLS/SSL support

  • MySQL utilities commands support

  • Working with MySQL and MariaDB

  • Rich collation and charset support

Usage

To use the Reactive MySQL Client add the following dependency to the dependencies section of your build descriptor:

  • Maven (in your pom.xml):

<dependency>
 <groupId>io.vertx</groupId>
 <artifactId>vertx-lang-kotlin</artifactId>
 <version>3.8.2</version>
</dependency>
  • Gradle (in your build.gradle file):

dependencies {
 compile 'io.vertx:vertx-lang-kotlin:3.8.2'
}

Getting started

Here is the simplest way to connect, query and disconnect

// Connect options
var connectOptions = MySQLConnectOptions(
  port = 3306,
  host = "the-host",
  database = "the-db",
  user = "user",
  password = "secret")

// Pool options
var poolOptions = PoolOptions(
  maxSize = 5)

// Create the client pool
var client = MySQLPool.pool(connectOptions, poolOptions)

// A simple query
client.query("SELECT * FROM users WHERE id='julien'", { ar ->
  if (ar.succeeded()) {
    var result = ar.result()
    println("Got ${result.size()} rows ")
  } else {
    println("Failure: ${ar.cause().getMessage()}")
  }

  // Now close the pool
  client.close()
})

Connecting to MySQL

Most of the time you will use a pool to connect to MySQL:

// Connect options
var connectOptions = MySQLConnectOptions(
  port = 3306,
  host = "the-host",
  database = "the-db",
  user = "user",
  password = "secret")

// Pool options
var poolOptions = PoolOptions(
  maxSize = 5)

// Create the pooled client
var client = MySQLPool.pool(connectOptions, poolOptions)

The pooled client uses a connection pool and any operation will borrow a connection from the pool to execute the operation and release it to the pool.

If you are running with Vert.x you can pass it your Vertx instance:

// Connect options
var connectOptions = MySQLConnectOptions(
  port = 3306,
  host = "the-host",
  database = "the-db",
  user = "user",
  password = "secret")

// Pool options
var poolOptions = PoolOptions(
  maxSize = 5)
// Create the pooled client
var client = MySQLPool.pool(vertx, connectOptions, poolOptions)

You need to release the pool when you don’t need it anymore:

// Close the pool and all the associated resources
pool.close()

When you need to execute several operations on the same connection, you need to use a client connection.

You can easily get one from the pool:

// Connect options
var connectOptions = MySQLConnectOptions(
  port = 3306,
  host = "the-host",
  database = "the-db",
  user = "user",
  password = "secret")

// Pool options
var poolOptions = PoolOptions(
  maxSize = 5)

// Create the pooled client
var client = MySQLPool.pool(vertx, connectOptions, poolOptions)

// Get a connection from the pool
client.getConnection({ ar1 ->

  if (ar1.succeeded()) {

    println("Connected")

    // Obtain our connection
    var conn = ar1.result()

    // All operations execute on the same connection
    conn.query("SELECT * FROM users WHERE id='julien'", { ar2 ->
      if (ar2.succeeded()) {
        conn.query("SELECT * FROM users WHERE id='emad'", { ar3 ->
          // Release the connection to the pool
          conn.close()
        })
      } else {
        // Release the connection to the pool
        conn.close()
      }
    })
  } else {
    println("Could not connect: ${ar1.cause().getMessage()}")
  }
})

Once you are done with the connection you must close it to release it to the pool, so it can be reused.

Configuration

There are several alternatives for you to configure the client.

Data Object

A simple way to configure the client is to specify a MySQLConnectOptions data object.

// Data object
var connectOptions = MySQLConnectOptions(
  port = 3306,
  host = "the-host",
  database = "the-db",
  user = "user",
  password = "secret")

// Pool Options
var poolOptions = PoolOptions(
  maxSize = 5)

// Create the pool from the data object
var pool = MySQLPool.pool(vertx, connectOptions, poolOptions)

pool.getConnection({ ar ->
  // Handling your connection
})

collations and character sets

The Reactive MySQL client supports configuring collations or character sets and map them to a correlative java.nio.charset.Charset. For example, you can specify charset for a connection like

var connectOptions = MySQLConnectOptions()

// set connection character set to utf8 instead of the default charset utf8mb4
connectOptions.charset = "utf8"

You can also specify collation for a connection like

var connectOptions = MySQLConnectOptions()

// set connection collation to utf8_general_ci instead of the default collation utf8mb4_general_ci
// setting a collation will override the charset option
connectOptions.charset = "gbk"
connectOptions.collation = "utf8_general_ci"

MySQL will take utf8mb4 as the default charset. Note setting a collation on the data object will override the charset option.

You can execute SQL SHOW COLLATION; or SHOW CHARACTER SET; to get the supported collations and charsets.

More information about MySQL charsets and collations can be found in the MySQL Reference Manual.

connection attributes

You can also configure the connection attributes with the setProperties or addProperty methods. Note setProperties will override the default client properties.

Code not translatable

More information about client connection attributes can be found in the MySQL Reference Manual.

connection URI

Apart from configuring with a MySQLConnectOptions data object, We also provide you an alternative way to connect when you want to configure with a connection URI:

// Connection URI
var connectionUri = "mysql://dbuser:[email protected]:3211/mydb"

// Create the pool from the connection URI
var pool = MySQLPool.pool(connectionUri)

// Create the connection from the connection URI
MySQLConnection.connect(vertx, connectionUri, { res ->
  // Handling your connection
})

More information about connection string formats can be found in the MySQL Reference Manual.

Currently the client supports the following parameter key words in connection uri

  • host

  • port

  • user

  • password

  • schema

  • socket

Unresolved directive in index.adoc - include::queries.adoc[]

MySQL LAST_INSERT_ID

You can get the auto incremented value if you insert a record into the table.

client.query("INSERT INTO test(val) VALUES ('v1')", { ar ->
  if (ar.succeeded()) {
    var rows = ar.result()
    var lastInsertId = rows.property(MySQLClient.LAST_INSERTED_ID)
    println("Last inserted id is: ${lastInsertId}")
  } else {
    println("Failure: ${ar.cause().getMessage()}")
  }
})

More information can be found in How to Get the Unique ID for the Last Inserted Row.

Unresolved directive in index.adoc - include::connections.adoc[]

Unresolved directive in index.adoc - include::cursor.adoc[]

MySQL type mapping

Currently the client supports the following MySQL types

  • BOOL,BOOLEAN (java.lang.Byte)

  • TINYINT (java.lang.Byte)

  • SMALLINT (java.lang.Short)

  • MEDIUMINT (java.lang.Integer)

  • INT,INTEGER (java.lang.Integer)

  • BIGINT (java.lang.Long)

  • FLOAT (java.lang.Float)

  • DOUBLE (java.lang.Double)

  • NUMERIC (io.vertx.sqlclient.data.Numeric)

  • DATE (java.time.LocalDate)

  • DATETIME (java.time.LocalDateTime)

  • TIME (java.time.Duration)

  • TIMESTAMP (java.time.LocalDateTime)

  • YEAR (java.lang.Short)

  • CHAR (java.lang.String)

  • VARCHAR (java.lang.String)

  • BINARY (io.vertx.core.buffer.Buffer)

  • VARBINARY (io.vertx.core.buffer.Buffer)

  • TINYBLOB (io.vertx.core.buffer.Buffer)

  • TINYTEXT (java.lang.String)

  • BLOB (io.vertx.core.buffer.Buffer)

  • TEXT (java.lang.String)

  • MEDIUMBLOB (io.vertx.core.buffer.Buffer)

  • MEDIUMTEXT (java.lang.String)

  • LONGBLOB (io.vertx.core.buffer.Buffer)

  • LONGTEXT (java.lang.String)

  • ENUM (java.lang.String)

  • SET (java.lang.String)

Tuple decoding uses the above types when storing values

Handling BOOLEAN

In MySQL BOOLEAN and BOOL data types are synonyms for TINYINT(1). A value of zero is considered false, non-zero values are considered true. A BOOLEAN data type value is stored in Row or Tuple as java.lang.Byte type, you can call Row#getValue to retrieve it as a java.lang.Byte value, or you can call Row#getBoolean to retrieve it as java.lang.Boolean value.

client.query("SELECT graduated FROM students WHERE id = 0", { ar ->
  if (ar.succeeded()) {
    var rowSet = ar.result()
    for (row in rowSet) {
      var pos = row.getColumnIndex("graduated")
      var value = row.get(Byte.`class`, pos)
      var graduated = row.getBoolean("graduated")
    }
  } else {
    println("Failure: ${ar.cause().getMessage()}")
  }
})

When you want to execute a prepared statement with a param of a BOOLEAN value, you can simply add the java.lang.Boolean value to the params list.

client.preparedQuery("UPDATE students SET graduated = ? WHERE id = 0", Tuple.of(true), { ar ->
  if (ar.succeeded()) {
    println("Updated with the boolean value")
  } else {
    println("Failure: ${ar.cause().getMessage()}")
  }
})

Handling NUMERIC

The Numeric Java type is used to represent the MySQL NUMERIC type.

var numeric = row.get(io.vertx.sqlclient.data.Numeric.`class`, 0)
if (numeric.isNaN()) {
  // Handle NaN
} else {
  var value = numeric.bigDecimalValue()
}

Collector queries

You can use Java collectors with the query API:

Code not translatable

The collector processing must not keep a reference on the Row as there is a single row used for processing the entire set.

The Java Collectors provides many interesting predefined collectors, for example you can create easily create a string directly from the row set:

Code not translatable

MySQL Stored Procedure

You can run stored procedures in queries. The result will be retrieved from the server following the MySQL protocol without any magic here.

client.query("CREATE PROCEDURE multi() BEGIN\n  SELECT 1;\n  SELECT 1;\n  INSERT INTO ins VALUES (1);\n  INSERT INTO ins VALUES (2);\nEND;", { ar1 ->
  if (ar1.succeeded()) {
    // create stored procedure success
    client.query("CALL multi();", { ar2 ->
      if (ar2.succeeded()) {
        // handle the result
        var result1 = ar2.result()
        var row1 = result1.iterator().next()
        println("First result: ${row1.getInteger(0)}")

        var result2 = result1.next()
        var row2 = result2.iterator().next()
        println("Second result: ${row2.getInteger(0)}")

        var result3 = result2.next()
        println("Affected rows: ${result3.rowCount()}")
      } else {
        println("Failure: ${ar2.cause().getMessage()}")
      }
    })
  } else {
    println("Failure: ${ar1.cause().getMessage()}")
  }
})

Note: Prepared statements binding OUT parameters is not supported for now.

MySQL LOCAL INFILE

This client supports for handling the LOCAL INFILE Request, if you want to load data from a local file into the server, you can use query LOAD DATA LOCAL INFILE '<filename>' INTO TABLE <table>;. More information can be found in the MySQL Reference Manual.

Authentication

MySQL 8.0 introduces a new authentication method named caching_sha2_password and it’s the default one to authenticate. In order to connect to the server using this new authentication method, you need either use a secure connection(i.e. enable TLS/SSL) or exchange the encrypted password using an RSA key pair to avoid leaks of password. The RSA key pair is automatically exchanged during the communication, but the server RSA public key may be hacked during the process since it’s transferred on a insecure connection. So if you’re on a insecure connection and want to avoid the risk of exposing the server RSA public key, you can set the server RSA public key like this:

var options1 = MySQLConnectOptions(
  port = 3306,
  host = "the-host",
  database = "the-db",
  user = "user",
  password = "secret",
  serverRsaPublicKeyPath = "tls/files/public_key.pem")

var options2 = MySQLConnectOptions(
  port = 3306,
  host = "the-host",
  database = "the-db",
  user = "user",
  password = "secret",
  serverRsaPublicKeyValue = Buffer.buffer("-----BEGIN PUBLIC KEY-----\nMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA3yvG5s0qrV7jxVlp0sMj\nxP0a6BuLKCMjb0o88hDsJ3xz7PpHNKazuEAfPxiRFVAV3edqfSiXoQw+lJf4haEG\nHQe12Nfhs+UhcAeTKXRlZP/JNmI+BGoBduQ1rCId9bKYbXn4pvyS/a1ft7SwFkhx\naogCur7iIB0WUWvwkQ0fEj/Mlhw93lLVyx7hcGFq4FOAKFYr3A0xrHP1IdgnD8QZ\n0fUbgGLWWLOossKrbUP5HWko1ghLPIbfmU6o890oj1ZWQewj1Rs9Er92/UDj/JXx\n7ha1P+ZOgPBlV037KDQMS6cUh9vTablEHsMLhDZanymXzzjBkL+wH/b9cdL16LkQ\n5QIDAQAB\n-----END PUBLIC KEY-----\n"))

More information about the caching_sha2_password authentication method can be found in the MySQL Reference Manual.

Using SSL/TLS

To configure the client to use SSL connection, you can configure the MySQLConnectOptions like a Vert.x NetClient. All SSL modes are supported and you are able to configure sslmode. The client is in DISABLED SSL mode by default. ssl parameter is kept as a mere shortcut for setting sslmode. setSsl(true) is equivalent to setSslMode(VERIFY_CA) and setSsl(false) is equivalent to setSslMode(DISABLED).

var options = MySQLConnectOptions(
  port = 3306,
  host = "the-host",
  database = "the-db",
  user = "user",
  password = "secret",
  sslMode = SslMode.VERIFY_CA,
  pemTrustOptions = PemTrustOptions(
    certPaths = listOf("/path/to/cert.pem")))

MySQLConnection.connect(vertx, options, { res ->
  if (res.succeeded()) {
    // Connected with SSL
  } else {
    println("Could not connect ${res.cause()}")
  }
})

More information can be found in the Vert.x documentation.

MySQL utility command

Sometimes you want to use MySQL utility commands and we provide support for this. More information can be found in the MySQL utility commands.

COM_PING

You can use COM_PING command to check if the server is alive. The handler will be notified if the server responds to the PING, otherwise the handler will never be called.

connection.ping({ ar ->
  println("The server has responded to the PING")
})

COM_RESET_CONNECTION

You can reset the session state with COM_RESET_CONNECTION command, this will reset the connection state like: - user variables - temporary tables - prepared statements

connection.resetConnection({ ar ->
  if (ar.succeeded()) {
    println("Connection has been reset now")
  } else {
    println("Failure: ${ar.cause().getMessage()}")
  }
})

COM_CHANGE_USER

You can change the user of the current connection, this will perform a re-authentication and reset the connection state like COM_RESET_CONNECTION.

var authenticationOptions = MySQLAuthOptions(
  user = "newuser",
  password = "newpassword",
  database = "newdatabase")
connection.changeUser(authenticationOptions, { ar ->
  if (ar.succeeded()) {
    println("User of current connection has been changed.")
  } else {
    println("Failure: ${ar.cause().getMessage()}")
  }
})

COM_INIT_DB

You can use COM_INIT_DB command to change the default schema of the connection.

connection.specifySchema("newschema", { ar ->
  if (ar.succeeded()) {
    println("Default schema changed to newschema")
  } else {
    println("Failure: ${ar.cause().getMessage()}")
  }
})

COM_STATISTICS

You can use COM_STATISTICS command to get a human readable string of some internal status variables in MySQL server.

connection.getInternalStatistics({ ar ->
  if (ar.succeeded()) {
    println("Statistics: ${ar.result()}")
  } else {
    println("Failure: ${ar.cause().getMessage()}")
  }
})

COM_DEBUG

You can use COM_DEBUG command to dump debug info to the MySQL server’s STDOUT.

connection.debug({ ar ->
  if (ar.succeeded()) {
    println("Debug info dumped to server's STDOUT")
  } else {
    println("Failure: ${ar.cause().getMessage()}")
  }
})

COM_SET_OPTION

You can use COM_SET_OPTION command to set options for the current connection. Currently only CLIENT_MULTI_STATEMENTS can be set.

For example, you can disable CLIENT_MULTI_STATEMENTS with this command.

connection.setOption(MySQLSetOption.MYSQL_OPTION_MULTI_STATEMENTS_OFF, { ar ->
  if (ar.succeeded()) {
    println("CLIENT_MULTI_STATEMENTS is off now")
  } else {
    println("Failure: ${ar.cause().getMessage()}")
  }
})

MySQL and MariaDB version support matrix

MySQL MariaDB

Version

Supported

Version

Supported

5.5

10.1

5.6

10.2

5.7

10.3

8.0

10.4

Known issues:

  • Reset connection utility command does not work in MySQL 5.5, 5.6 and MariaDB 10.1

  • Change user utility command is not supported with MariaDB 10.2 and 10.3