= Reactive MySQL Client

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
* MySQL utilities commands support
* Compatible with MySQL 5.6 and 5.7
* 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`):

[source,xml]
----
<dependency>
 <groupId>io.vertx</groupId>
 <artifactId>vertx-lang-groovy</artifactId>
 <version>3.8.1</version>
</dependency>
----
* Gradle (in your `build.gradle` file):

[source,groovy]
----
dependencies {
 compile 'io.vertx:vertx-lang-groovy:3.8.1'
}
----

== Getting started

Here is the simplest way to connect, query and disconnect

[source,groovy]
----

// Connect options
def connectOptions = [
  port:3306,
  host:"the-host",
  database:"the-db",
  user:"user",
  password:"secret"
]

// Pool options
def poolOptions = [
  maxSize:5
]

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

// A simple query
client.query("SELECT * FROM users WHERE id='julien'", { ar ->
  if (ar.succeeded()) {
    def 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:

[source,groovy]
----

// Connect options
def connectOptions = [
  port:3306,
  host:"the-host",
  database:"the-db",
  user:"user",
  password:"secret"
]

// Pool options
def poolOptions = [
  maxSize:5
]

// Create the pooled client
def 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:

[source,groovy]
----

// Connect options
def connectOptions = [
  port:3306,
  host:"the-host",
  database:"the-db",
  user:"user",
  password:"secret"
]

// Pool options
def poolOptions = [
  maxSize:5
]
// Create the pooled client
def client = MySQLPool.pool(vertx, connectOptions, poolOptions)

----

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

[source,groovy]
----

// 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
`link:../../apidocs/io/vertx/mysqlclient/MySQLConnection.html[connection]`.

You can easily get one from the pool:

[source,groovy]
----

// Connect options
def connectOptions = [
  port:3306,
  host:"the-host",
  database:"the-db",
  user:"user",
  password:"secret"
]

// Pool options
def poolOptions = [
  maxSize:5
]

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

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

  if (ar1.succeeded()) {

    println("Connected")

    // Obtain our connection
    def 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.

[source,groovy]
----

// Data object
def connectOptions = [
  port:3306,
  host:"the-host",
  database:"the-db",
  user:"user",
  password:"secret"
]

// Pool Options
def poolOptions = [
  maxSize:5
]

// Create the pool from the data object
def 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

[source,groovy]
----
def connectOptions = [:]

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

----

You can also specify collation for a connection like

[source,groovy]
----
def connectOptions = [:]

// 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 https://dev.mysql.com/doc/refman/8.0/en/charset.html[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.

[source,groovy]
----
Code not translatable
----

More information about client connection attributes can be found in the https://dev.mysql.com/doc/refman/8.0/en/performance-schema-connection-attribute-tables.html[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:

[source,groovy]
----

// Connection URI
def connectionUri = "mysql://dbuser:secretpassword@database.server.com:3211/mydb"

// Create the pool from the connection URI
def 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 https://dev.mysql.com/doc/refman/8.0/en/connecting-using-uri-or-key-value-pairs.html#connecting-using-uri[MySQL Reference Manual].

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

* host
* port
* user
* password
* schema
* socket

include::queries.adoc[]

== MySQL LAST_INSERT_ID

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

[source,groovy]
----
client.query("INSERT INTO test(val) VALUES ('v1')", { ar ->
  if (ar.succeeded()) {
    def rows = ar.result()
    def 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 https://dev.mysql.com/doc/refman/8.0/en/getting-unique-id.html[How to Get the Unique ID for the Last Inserted Row].

include::connections.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`)

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.

[source,groovy]
----
client.query("SELECT graduated FROM students WHERE id = 0", { ar ->
  if (ar.succeeded()) {
    def rowSet = ar.result()
    rowSet.each { row ->
      def pos = row.getColumnIndex("graduated")
      def value = row.get(java.lang.Byte.class, pos)
      def 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.

[source,groovy]
----
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 `link:../../apidocs/io/vertx/sqlclient/data/Numeric.html[Numeric]` Java type is used to represent the MySQL `NUMERIC` type.

[source,groovy]
----
def numeric = row.get(io.vertx.sqlclient.data.Numeric.class, 0)
if (numeric.isNaN()) {
  // Handle NaN
} else {
  def value = numeric.bigDecimalValue()
}

----

== Collector queries

You can use Java collectors with the query API:

[source,groovy]
----
Code not translatable
----

The collector processing must not keep a reference on the `link:../../apidocs/io/vertx/sqlclient/Row.html[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:

[source,groovy]
----
Code not translatable
----

== 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 https://dev.mysql.com/doc/refman/8.0/en/load-data.html[MySQL Reference Manual].

== MySQL utility command

Sometimes you want to use MySQL utility commands and we provide support for this.
More information can be found in the https://dev.mysql.com/doc/dev/mysql-server/8.0.12/page_protocol_command_phase_utility.html[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.

[source,groovy]
----
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

[source,groovy]
----
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`.

[source,groovy]
----
def authenticationOptions = [
  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.

[source,groovy]
----
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.

[source,groovy]
----
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.

[source,groovy]
----
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.

[source,groovy]
----
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()}")
  }
})

----