盗墓笔记全集,《完美世界》txt全集

主頁 > 知識庫 > 深入理解r2dbc在mysql中的使用

深入理解r2dbc在mysql中的使用

簡介

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

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

r2dbc-mysql的maven依賴

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

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

當(dāng)然，如果你想使用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的標(biāo)準(zhǔn)接口，所以和之前講到的h2的創(chuàng)建代碼基本上是一樣的：

// Notice: the query string must be URL encoded
ConnectionFactory connectionFactory = ConnectionFactories.get(
  "r2dbcs:mysql://root:database-password-in-here@127.0.0.1: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 Reactor
MonoConnection> connectionMono = Mono.from(connectionFactory.create());

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

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

// Minimum configuration for unix domain socket
ConnectionFactory connectionFactory = ConnectionFactories.get("r2dbc:mysql://root@unix?unixSocket=%2Fpath%2Fto%2Fmysql.sock")

MonoConnection> 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 Reactor
MonoConnection> connectionMono = Mono.from(connectionFactory.create());

或者下面的unix domain socket格式：

// 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);

MonoConnection> 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 Reactor
MonoConnection> connectionMono = Mono.from(connectionFactory.create());

或者下面的unix domain socket方式：

// Minimum configuration for unix domain socket
MySqlConnectionConfiguration configuration = MySqlConnectionConfiguration.builder()
  .unixSocket("/path/to/mysql.sock")
  .user("root")
  .build();
ConnectionFactory connectionFactory = MySqlConnectionFactory.from(configuration);

MonoConnection> 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來進(jìn)行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í)行事務(wù)

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

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 Reactor
MonoConnection> 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);
 

MonoConnection> connectionMono = pool.create();

// later

Connection connection = …;
MonoVoid> release = connection.close(); // released the connection back to the pool

// application shutdown
pool.dispose();

到此這篇關(guān)于深入理解r2dbc在mysql中的使用的文章就介紹到這了,更多相關(guān)mysql r2dbc 內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

mysql安裝圖解 mysql圖文安裝教程(詳細(xì)說明)
Mysql字符串截取函數(shù)SUBSTRING的用法說明
MySQL創(chuàng)建用戶與授權(quán)方法
mysql update語句的用法詳解
MySQL 的CASE WHEN 語句使用說明
mysql中int、bigint、smallint 和 tinyint的區(qū)別詳細(xì)介紹
mysql 添加索引 mysql 如何創(chuàng)建索引
mySQL中replace的用法

標(biāo)簽：荊州資陽甘南吉林錦州滄州隨州黑河

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《深入理解r2dbc在mysql中的使用》，本文關(guān)鍵詞深入,理解,r2dbc,在,mysql,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。