mysql應該是我們在日常工作中使用到的一個非常普遍的數(shù)據(jù)庫，雖然mysql現(xiàn)在是oracle公司的，但是它是開源的，市場占有率還是非常高的。

今天我們將會介紹r2dbc在mysql中的使用。

要想使用r2dbc-mysql，我們需要添加如下的maven依賴：

<dependency> <groupId>dev.miku</groupId> <artifactId>r2dbc-mysql</artifactId> <version>0.8.2.RELEASE</version></dependency>

當然，如果你想使用snapshot版本的話，可以這樣：

<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>創(chuàng)建connectionFactory

創(chuàng)建connectionFactory的代碼實際上使用的r2dbc的標準接口，所以和之前講到的h2的創(chuàng)建代碼基本上是一樣的：

// Notice: the query string must be URL encodedConnectionFactory connectionFactory = ConnectionFactories.get( 'r2dbcs:mysql://root:[email protected]:3306/r2dbc?' + 'zeroDate=use_round&' + 'sslMode=verify_identity&' + 'useServerPrepareStatement=true&' + 'tlsVersion=TLSv1.3%2CTLSv1.2%2CTLSv1.1&' + '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 ReactorMono<Connection> connectionMono = Mono.from(connectionFactory.create());

不同的是ConnectionFactories傳入的參數(shù)不同。

我們也支持unix domain socket的格式：

// Minimum configuration for unix domain socketConnectionFactory connectionFactory = ConnectionFactories.get('r2dbc:mysql://root@unix?unixSocket=%2Fpath%2Fto%2Fmysql.sock')Mono<Connection> connectionMono = Mono.from(connectionFactory.create());

同樣的，我們也支持從ConnectionFactoryOptions中創(chuàng)建ConnectionFactory：

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 sslMode is 'preferred', 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('sslCert'), '/path/to/mysql/client-cert.pem') // optional, default null, null means has no client cert .option(Option.valueOf('sslKey'), '/path/to/mysql/client-key.pem') // optional, default null, null means has no client key .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.3,TLSv1.2,TLSv1.1') // optional, default is auto-selected by the server .option(Option.valueOf('sslHostnameVerifier'), 'com.example.demo.MyVerifier') // optional, default is null, null means use standard verifier .option(Option.valueOf('sslContextBuilderCustomizer'), 'com.example.demo.MyCustomizer') // optional, default is no-op customizer .option(Option.valueOf('zeroDate'), 'use_null') // optional, default 'use_null' .option(Option.valueOf('useServerPrepareStatement'), true) // optional, default false .option(Option.valueOf('tcpKeepAlive'), true) // optional, default false .option(Option.valueOf('tcpNoDelay'), true) // optional, default false .option(Option.valueOf('autodetectExtensions'), false) // optional, default false .build();ConnectionFactory connectionFactory = ConnectionFactories.get(options);// Creating a Mono using Project ReactorMono<Connection> connectionMono = Mono.from(connectionFactory.create());

或者下面的unix domain socket格式：

// Minimum configuration for unix domain socketConnectionFactoryOptions 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());使用MySqlConnectionFactory創(chuàng)建connection

上面的例子中，我們使用的是通用的r2dbc api來創(chuàng)建connection，同樣的，我們也可以使用特有的MySqlConnectionFactory來創(chuàng)建connection：

MySqlConnectionConfiguration configuration = MySqlConnectionConfiguration.builder() .host('127.0.0.1') .user('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 .serverZoneId(ZoneId.of('Continent/City')) // optional, default null, null means query server time zone when connection init .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 .sslCert('/path/to/mysql/client-cert.pem') // optional, default has no client SSL certificate .sslKey('/path/to/mysql/client-key.pem') // optional, default has no client SSL key .sslKeyPassword('key-pem-password-in-here') // optional, default has no client SSL key password .tlsVersion(TlsVersions.TLS1_3, TlsVersions.TLS1_2, TlsVersions.TLS1_1) // optional, default is auto-selected by the server .sslHostnameVerifier(MyVerifier.INSTANCE) // optional, default is null, null means use standard verifier .sslContextBuilderCustomizer(MyCustomizer.INSTANCE) // optional, default is no-op customizer .zeroDateOption(ZeroDateOption.USE_NULL) // optional, default ZeroDateOption.USE_NULL .useServerPrepareStatement() // Use server-preparing statements, default use client-preparing statements .tcpKeepAlive(true) // optional, controls TCP Keep Alive, default is false .tcpNoDelay(true) // optional, controls TCP No Delay, default is false .autodetectExtensions(false) // optional, controls extension auto-detect, default is true .extendWith(MyExtension.INSTANCE) // optional, manual extend an extension into extensions, default using auto-detect .build();ConnectionFactory connectionFactory = MySqlConnectionFactory.from(configuration);// Creating a Mono using Project ReactorMono<Connection> connectionMono = Mono.from(connectionFactory.create());

或者下面的unix domain socket方式：

// Minimum configuration for unix domain socketMySqlConnectionConfiguration configuration = MySqlConnectionConfiguration.builder() .unixSocket('/path/to/mysql.sock') .user('root') .build();ConnectionFactory connectionFactory = MySqlConnectionFactory.from(configuration);Mono<Connection> connectionMono = Mono.from(connectionFactory.create());執(zhí)行statement

首先看一個簡單的不帶參數(shù)的statement:

connection.createStatement('INSERT INTO `person` (`first_name`, `last_name`) VALUES (’who’, ’how’)') .execute(); // return a Publisher include one Result

然后看一個帶參數(shù)的statement:

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.

注意，如果參數(shù)是null的話，可以使用bindNull來進行null值的綁定。

接下來我們看一個批量執(zhí)行的操作：

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.執(zhí)行事務

我們看一個執(zhí)行事務的例子：

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());使用線程池

為了提升數(shù)據(jù)庫的執(zhí)行效率，減少建立連接的開銷，一般數(shù)據(jù)庫連接都會有連接池的概念，同樣的r2dbc也有一個叫做r2dbc-pool的連接池。

r2dbc-pool的依賴：

<dependency> <groupId>io.r2dbc</groupId> <artifactId>r2dbc-pool</artifactId> <version>${version}</version></dependency>

如果你想使用snapshot版本，也可以這樣指定：

<dependency> <groupId>io.r2dbc</groupId> <artifactId>r2dbc-pool</artifactId> <version>${version}.BUILD-SNAPSHOT</version></dependency><repository> <id>spring-libs-snapshot</id> <name>Spring Snapshot Repository</name> <url>https://repo.spring.io/libs-snapshot</url></repository>

看一下怎么指定數(shù)據(jù)庫連接池：

ConnectionFactory connectionFactory = ConnectionFactories.get('r2dbc:pool:<my-driver>://<host>:<port>/<database>[?maxIdleTime=PT60S[&…]');Publisher<? extends Connection> connectionPublisher = connectionFactory.create();

可以看到，我們只需要在連接URL上面添加pool這個driver即可。

同樣的，我們也可以通過ConnectionFactoryOptions來創(chuàng)建：

ConnectionFactory connectionFactory = ConnectionFactories.get(ConnectionFactoryOptions.builder() .option(DRIVER, 'pool') .option(PROTOCOL, 'postgresql') // driver identifier, PROTOCOL is delegated as DRIVER by the pool. .option(HOST, '…') .option(PORT, '…') .option(USER, '…') .option(PASSWORD, '…') .option(DATABASE, '…') .build());Publisher<? extends Connection> connectionPublisher = connectionFactory.create();// Alternative: Creating a Mono using Project ReactorMono<Connection> connectionMono = Mono.from(connectionFactory.create());

最后， 你也可以直接通過創(chuàng)建ConnectionPoolConfiguration來使用線程池：

ConnectionFactory connectionFactory = …;ConnectionPoolConfiguration configuration = ConnectionPoolConfiguration.builder(connectionFactory) .maxIdleTime(Duration.ofMillis(1000)) .maxSize(20) .build();ConnectionPool pool = new ConnectionPool(configuration); Mono<Connection> connectionMono = pool.create();// laterConnection connection = …;Mono<Void> release = connection.close(); // released the connection back to the pool// application shutdownpool.dispose();

到此這篇關于深入理解r2dbc在mysql中的使用的文章就介紹到這了,更多相關mysql r2dbc 內容請搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持好吧啦網(wǎng)！