This project contains the MySQL implementation of the R2DBC SPI. This implementation is not intended to be used directly, but rather to be used as the backing implementation for a humane client library to delegate to. See R2DBC Homepage.
This driver provides the following features:
- Support MySQL database server versions between 5.5 to 8.x.
- Unix domain socket.
- Login with username/password (or no password)
- Execution of simple or batch statements without bindings (MySQL use text result for simple or batch statements).
- Execution of prepared statements with bindings (MySQL use binary result for prepared statements).
- Reactive LOB types (e.g. BLOB, CLOB)
- All charsets from MySQL, like
utf8mb4_0900_ai_ci
,latin1_general_ci
,utf32_unicode_520_ci
, etc. - All authentication types for MySQL, like
caching_sha2_password
,mysql_native_password
, etc. - General exceptions of error code and standard SQL state mappings.
- Secure connection with verification (SSL/TLS), auto-select TLS version for community and enterprise editions.
- Transactions with savepoint.
- Native ping command that can be verifying when argument is
ValidationDepth.REMOTE
- Prepared statements cache.
- Statement parser cache.
<dependency>
<groupId>dev.miku</groupId>
<artifactId>r2dbc-mysql</artifactId>
<version>0.8.0.RELEASE</version>
</dependency>
If you'd rather like the latest snapshots of the upcoming major version, use SonaType Maven snapshot repository and declare the appropriate dependency version.
<dependency>
<groupId>dev.miku</groupId>
<artifactId>r2dbc-mysql</artifactId>
<version>${r2dbc-mysql.version}.BUILD-SNAPSHOT</version>
</dependency>
<repository>
<id>sonatype-snapshots</id>
<name>SonaType Snapshots</name>
<url>https://oss.sonatype.org/content/repositories/snapshots</url>
<snapshots>
<enabled>true</enabled>
</snapshots>
</repository>
dependencies {
implementation 'dev.miku:r2dbc-mysql:0.8.0.RELEASE'
}
dependencies {
// Maybe should to use `compile` instead of `implementation` on the lower version of Gradle.
implementation("dev.miku:r2dbc-mysql:0.8.0.RELEASE")
}
Here is a quick teaser of how to use R2DBC MySQL in Java:
// Notice: the query string must be URL encoded
ConnectionFactory connectionFactory = ConnectionFactories.get(
"r2dbcs:mysql://root:[email protected]:3306/r2dbc?" +
"zeroDate=use_round&" +
"sslMode=verify_identity&" +
"tlsVersion=TLSv1.1%2CTLSv1.2%2CTLSv1.3&" +
"sslCa=%2Fpath%2Fto%2Fmysql%2Fca.pem&" +
"sslKey=%2Fpath%2Fto%2Fmysql%2Fclient-key.pem&" +
"sslCert=%2Fpath%2Fto%2Fmysql%2Fclient-cert.pem&" +
"sslKeyPassword=key-pem-password-in-here"
)
// Creating a Mono using Project Reactor
Mono<Connection> connectionMono = Mono.from(connectionFactory.create());
Or use unix domain socket like following:
// Minimum configuration for unix domain socket
ConnectionFactory connectionFactory = ConnectionFactories.get("r2dbc:mysql://root@unix?unixSocket=%2Fpath%2Fto%2Fmysql.sock")
Mono<Connection> connectionMono = Mono.from(connectionFactory.create());
ConnectionFactoryOptions options = ConnectionFactoryOptions.builder()
.option(DRIVER, "mysql")
.option(HOST, "127.0.0.1")
.option(USER, "root")
.option(PORT, 3306) // optional, default 3306
.option(PASSWORD, "database-password-in-here") // optional, default null, null means has no password
.option(DATABASE, "r2dbc") // optional, default null, null means not specifying the database
.option(CONNECT_TIMEOUT, Duration.ofSeconds(3)) // optional, default null, null means no timeout
.option(SSL, true) // optional, default is enabled, it will be ignore if "sslMode" is set
.option(Option.valueOf("sslMode"), "verify_identity") // optional, default "preferred"
.option(Option.valueOf("sslCa"), "/path/to/mysql/ca.pem") // required when sslMode is verify_ca or verify_identity, default null, null means has no server CA cert
.option(Option.valueOf("sslKey"), "/path/to/mysql/client-key.pem") // optional, default null, null means has no client key
.option(Option.valueOf("sslCert"), "/path/to/mysql/client-cert.pem") // optional, default null, null means has no client cert
.option(Option.valueOf("sslKeyPassword"), "key-pem-password-in-here") // optional, default null, null means has no password for client key (i.e. "sslKey")
.option(Option.valueOf("tlsVersion"), "TLSv1.1,TLSv1.2,TLSv1.3") // optional, default is auto-selected by the server
.option(Option.valueOf("zeroDate"), "use_null") // optional, default "use_null"
.build();
ConnectionFactory connectionFactory = ConnectionFactories.get(options);
// Creating a Mono using Project Reactor
Mono<Connection> connectionMono = Mono.from(connectionFactory.create());
Or use unix domain socket like following:
// Minimum configuration for unix domain socket
ConnectionFactoryOptions options = ConnectionFactoryOptions.builder()
.option(DRIVER, "mysql")
.option(Option.valueOf("unixSocket"), "/path/to/mysql.sock")
.option(USER, "root")
.build();
ConnectionFactory connectionFactory = ConnectionFactories.get(options);
Mono<Connection> connectionMono = Mono.from(connectionFactory.create());
MySqlConnectionConfiguration configuration = MySqlConnectionConfiguration.builder()
.host("127.0.0.1")
.username("root")
.port(3306) // optional, default 3306
.password("database-password-in-here") // optional, default null, null means has no password
.database("r2dbc") // optional, default null, null means not specifying the database
.connectTimeout(Duration.ofSeconds(3)) // optional, default null, null means no timeout
.sslMode(SslMode.VERIFY_IDENTITY) // optional, default SslMode.PREFERRED
.sslCa("/path/to/mysql/ca.pem") // required when sslMode is VERIFY_CA or VERIFY_IDENTITY, default null, null means has no server CA cert
.sslKeyAndCert("/path/to/mysql/client-cert.pem", "/path/to/mysql/client-key.pem", "key-pem-password-in-here") // optional, default has no client key and cert
.tlsVersion(TlsVersions.TLS1_1, TlsVersions.TLS1_2, TlsVersions.TLS1_3) // optional, default is auto-selected by the server
.zeroDateOption(ZeroDateOption.USE_NULL) // optional, default ZeroDateOption.USE_NULL
.build();
ConnectionFactory connectionFactory = MySqlConnectionFactory.from(configuration);
// Creating a Mono using Project Reactor
Mono<Connection> connectionMono = Mono.from(connectionFactory.create());
Or use unix domain socket like following:
// Minimum configuration for unix domain socket
MySqlConnectionConfiguration configuration = MySqlConnectionConfiguration.builder()
.unixSocket("/path/to/mysql.sock")
.username("root")
.build();
ConnectionFactory connectionFactory = MySqlConnectionFactory.from(configuration);
Mono<Connection> connectionMono = Mono.from(connectionFactory.create());
name | valid values | required | description |
---|---|---|---|
driver | A constant "mysql" | Required in R2DBC discovery | This driver needs to be discovered by name in R2DBC |
host | A hostname or IP | Required when unixSocket does not exists |
The host of MySQL database server |
unixSocket | An absolute or relative path | Required when host does not exists |
The .sock file of Unix Domain Socket |
port | A positive integer less than 65536 | Optional, default 3306 | The port of MySQL database server |
username | A valid MySQL username and not be empty | Required | Who wants to connect to the MySQL database |
password | Any printable string | Optional, default no password | The password of the MySQL database user |
database | A valid MySQL database name | Optional, default does not initialize database | Database used by the MySQL connection |
connectTimeout | A Duration which must be positive duration |
Optional, default has no timeout | TCP connect timeout |
sslMode | Any value of SslMode |
Optional, default PREFERRED when using hosting connection, DISABLED when using Unix Domain Socket |
SSL mode, see following notice |
sslCa | A path of local file which type is PEM |
Required when sslMode is VERIFY_CA or VERIFY_IDENTITY |
The CA cert of MySQL database server |
sslCert | A path of local file which type is PEM |
Required when sslKey exists |
The SSL cert of client |
sslKey | A path of local file which type is PEM |
Required when sslCert exists |
The SSL key of client |
sslKeyPassword | Any valid password for PEM file |
Optional, default sslKey has no password |
The password for client SSL key (i.e. sslKey ) |
tlsVersion | Any value list of TlsVersions |
Optional, default is auto-selected by the server | The TLS version for SSL, see following notice |
zeroDateOption | Any value of ZeroDateOption |
Optional, default USE_NULL |
The option indicates "zero date" handling, see following notice |
SslMode
: Considers security level and verification for SSL, make sure the database server supports SSL before you want change SSL mode toREQUIRED
or higher. The Unix Domain Socket only offers "DISABLED" availableDISABLED
: I don't care about security and don't want to pay the overhead for encryptionPREFERRED
: I don't care about encryption but will pay the overhead of encryption if the server supports it. Unavailable on Unix Domain SocketREQUIRED
: I want my data to be encrypted, and I accept the overhead. I trust that the network will make sure I always connect to the server I want. Unavailable on Unix Domain SocketVERIFY_CA
: I want my data encrypted, and I accept the overhead. I want to be sure that I connect to a server that I trust. Unavailable on Unix Domain SocketVERIFY_IDENTITY
(highest level, most like web browser): I want my data encrypted, and I accept the overhead. I want to be sure that I connect to a server I trust, and that it's the one I specify. Unavailable on Unix Domain Socket
TlsVersions
: Considers TLS version names for SSL, can be multi-values in configuration, make sure the database server supports selected TLS versions. Unavailable on Unix Domain SocketTLS1
(i.e. "TLSv1"): Under generic circumstances, MySQL database supports it if database supports SSLTLS1_1
(i.e. "TLSv1.1"): Under generic circumstances, MySQL database supports it if database supports SSLTLS1_2
(i.e. "TLSv1.2"): Supported only in Community Edition8.0.4
or higher, otherwise in Enterprise Edition5.6.0
or higherTLS1_3
(i.e. "TLSv1.3"): Supported only available as of MySQL8.0.16
or higher, and requires compiling MySQL using OpenSSL1.1.1
or higher
ZeroDateOption
: Considers special handling when MySQL database server returning "zero date" (i.e.0000-00-00 00:00:00
)EXCEPTION
: Just throw a exception when MySQL database server return "zero date".USE_NULL
: Usenull
when MySQL database server return "zero date".USE_ROUND
: NOT RECOMMENDED, only for compatibility. Use "round" date (i.e.0001-01-01 00:00:00
) when MySQL database server return "zero date".
Should use enum
in Programmatic configuration that not like discovery configurations, except TlsVersions
(All elements of TlsVersions
will be always String
which is case sensitive).
See r2dbc-pool.
connection.createStatement("INSERT INTO `person` (`first_name`, `last_name`) VALUES ('who', 'how')")
.execute(); // return a Publisher include one Result
connection.createStatement("INSERT INTO `person` (`birth`, `nickname`, `show_name`) VALUES (?, ?name, ?name)")
.bind(0, LocalDateTime.of(2019, 6, 25, 12, 12, 12))
.bind("name", "Some one") // Not one-to-one binding, call twice of native index-bindings, or call once of name-bindings.
.add()
.bind(0, LocalDateTime.of(2009, 6, 25, 12, 12, 12))
.bind(1, "My Nickname")
.bind(2, "Naming show")
.returnGeneratedValues("generated_id")
.execute(); // return a Publisher include two Results.
- All parameters must be bound before execute, even parameter is
null
(usebindNull
to bindnull
). - In one-to-one binding, because native MySQL prepared statements use index-based parameters, index-bindings will have better performance than name-bindings.
connection.createBatch()
.add("INSERT INTO `person` (`first_name`, `last_name`) VALUES ('who', 'how')")
.add("UPDATE `earth` SET `count` = `count` + 1 WHERE `id` = 'human'")
.execute(); // return a Publisher include two Results.
The last
;
will be removed if has only whitespace follow the last;
.
connection.beginTransaction()
.then(Mono.from(connection.createStatement("INSERT INTO `person` (`first_name`, `last_name`) VALUES ('who', 'how')").execute()))
.flatMap(Result::getRowsUpdated)
.thenMany(connection.createStatement("INSERT INTO `person` (`birth`, `nickname`, `show_name`) VALUES (?, ?name, ?name)")
.bind(0, LocalDateTime.of(2019, 6, 25, 12, 12, 12))
.bind("name", "Some one")
.add()
.bind(0, LocalDateTime.of(2009, 6, 25, 12, 12, 12))
.bind(1, "My Nickname")
.bind(2, "Naming show")
.returnGeneratedValues("generated_id")
.execute())
.flatMap(Result::getRowsUpdated)
.then(connection.commitTransaction());
This reference table shows the type mapping between MySQL and Java data types:
MySQL Type | Unsigned | Support Data Type |
---|---|---|
INT | UNSIGNED | Long , BigInteger |
INT | SIGNED | Integer , Long , BigInteger |
TINYINT | UNSIGNED | Short , Integer , Long , BigInteger |
TINYINT | SIGNED | Byte , Short , Integer , Long , BigInteger |
SMALLINT | UNSIGNED | Integer , Long , BigInteger |
SMALLINT | SIGNED | Short , Integer , Long , BigInteger |
MEDIUMINT | SIGNED/UNSIGNED | Integer , Long , BigInteger |
BIGINT | UNSIGNED | BigInteger , Long (Not check overflow) |
BIGINT | SIGNED | Long , BigInteger |
FLOAT | SIGNED/UNSIGNED | Float , BigDecimal |
DOUBLE | SIGNED/UNSIGNED | Double , BigDecimal |
DECIMAL | SIGNED/UNSIGNED | BigDecimal , Float (Size less than 7), Double (Size less than 16) |
BIT | - | ByteBuffer , BitSet , Boolean (Size is 1), byte[] |
DATETIME/TIMESTAMP | - | LocalDateTime |
DATE | - | LocalDate |
TIME | - | LocalTime , Duration |
YEAR | - | Short , Integer , Long , BigInteger , Year |
VARCHAR/NVARCHAR | - | String |
VARBINARY | - | ByteBuffer , Blob , byte[] |
CHAR/NCHAR | - | String |
ENUM | - | String , Enum<?> |
SET | - | String[] , String , Set<String> and Set<Enum<?>> (Set<T> need use ParameterizedType ) |
BLOB (LONGBLOB, etc.) | - | ByteBuffer , Blob , byte[] |
TEXT (LONGTEXT, etc.) | - | String , Clob |
JSON | - | String , Clob |
GEOMETRY | - | byte[] , Blob |
The R2DBC MySQL Implementation uses GitHub as issue tracking system to record bugs and feature requests. If you want to raise an issue, please follow the recommendations below:
- Before you log a bug, please search the issue tracker to see if someone has already reported the problem.
- If the issue doesn't already exist, create a new issue.
- Please provide as much information as possible with the issue report, we like to know the version of R2DBC MySQL that you are using and JVM version.
- If you need to paste code, or include a stack trace use Markdown ``` escapes before and after your text.
- If possible try to create a test-case or project that replicates the issue. Attach a link to your code or a compressed file containing your code.
- MySQL database does NOT support table definition in a prepared statement, please use simple statement if want to execute table definitions.
- The MySQL data fields encoded by index-based natively, get fields by index will have better performance than get by column name.
- Each
Result
should be used (callgetRowsUpdated
ormap
, even table definition), can NOT just ignore anyResult
, otherwise inbound stream is unable to align. (likeResultSet.close
in jdbc,Result
auto-close after used by once) - The MySQL returns microseconds & milliseconds when only in prepared statement result (and maybe contains not microsecond & milliseconds even in prepared statement results). Therefore this driver does not guarantee decoded
LocalDateTime
s precisions contain milliseconds. - The MySQL server does not actively return time zone when query
DATETIME
orTIMESTAMP
, this driver does not attempt time zone conversion. That means should always useLocalDateTime
for SQL typeDATETIME
orTIMESTAMP
. ExecuteSHOW VARIABLES LIKE '%time_zone%'
to get more information. - Should not turn-on the
trace
log level unless debugging. Otherwise, the security information may be exposed throughByteBuf
dump. - If
Statement
boundreturnGeneratedValues
, theResult
of theStatement
can be called both:getRowsUpdated
to get affected rows, andmap
to get last inserted ID. - The MySQL may be not support search rows by binary field, like
BIT
orBLOB
, because those data fields maybe just an address of reference in MySQL server, or maybe need strict bit-aligned. (butVARBINARY
is OK)
This project is released under version 2.0 of the Apache License.