深入理解r2dbc在mysql中的使用

2023-07-05 13:53:04 231

简介

mysql应该是我们在日常工作中使用到的一个非常普遍的数据库，虽然mysql现在是oracle公司的，但是它是开源的，市场占有率还是非常高的。

今天我们将会介绍r2dbc在mysql中的使用。

r2dbc-mysql的maven依赖

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


dev.miku
r2dbc-mysql
0.8.2.RELEASE

当然，如果你想使用snapshot版本的话，可以这样：


dev.miku
r2dbc-mysql
${r2dbc-mysql.version}.BUILD-SNAPSHOT



sonatype-snapshots
SonaTypeSnapshots
https://oss.sonatype.org/content/repositories/snapshots

true

创建connectionFactory

创建connectionFactory的代码实际上使用的r2dbc的标准接口，所以和之前讲到的h2的创建代码基本上是一样的：

//Notice:thequerystringmustbeURLencoded
ConnectionFactoryconnectionFactory=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"
)

//CreatingaMonousingProjectReactor
MonoconnectionMono=Mono.from(connectionFactory.create());

不同的是ConnectionFactories传入的参数不同。

我们也支持unixdomainsocket的格式：

//Minimumconfigurationforunixdomainsocket
ConnectionFactoryconnectionFactory=ConnectionFactories.get("r2dbc:mysql://root@unix?unixSocket=%2Fpath%2Fto%2Fmysql.sock")

MonoconnectionMono=Mono.from(connectionFactory.create());

同样的，我们也支持从ConnectionFactoryOptions中创建ConnectionFactory：

ConnectionFactoryOptionsoptions=ConnectionFactoryOptions.builder()
.option(DRIVER,"mysql")
.option(HOST,"127.0.0.1")
.option(USER,"root")
.option(PORT,3306)//optional,default3306
.option(PASSWORD,"database-password-in-here")//optional,defaultnull,nullmeanshasnopassword
.option(DATABASE,"r2dbc")//optional,defaultnull,nullmeansnotspecifyingthedatabase
.option(CONNECT_TIMEOUT,Duration.ofSeconds(3))//optional,defaultnull,nullmeansnotimeout
.option(SSL,true)//optional,defaultsslModeis"preferred",itwillbeignoreifsslModeisset
.option(Option.valueOf("sslMode"),"verify_identity")//optional,default"preferred"
.option(Option.valueOf("sslCa"),"/path/to/mysql/ca.pem")//requiredwhensslModeisverify_caorverify_identity,defaultnull,nullmeanshasnoserverCAcert
.option(Option.valueOf("sslCert"),"/path/to/mysql/client-cert.pem")//optional,defaultnull,nullmeanshasnoclientcert
.option(Option.valueOf("sslKey"),"/path/to/mysql/client-key.pem")//optional,defaultnull,nullmeanshasnoclientkey
.option(Option.valueOf("sslKeyPassword"),"key-pem-password-in-here")//optional,defaultnull,nullmeanshasnopasswordforclientkey(i.e."sslKey")
.option(Option.valueOf("tlsVersion"),"TLSv1.3,TLSv1.2,TLSv1.1")//optional,defaultisauto-selectedbytheserver
.option(Option.valueOf("sslHostnameVerifier"),"com.example.demo.MyVerifier")//optional,defaultisnull,nullmeansusestandardverifier
.option(Option.valueOf("sslContextBuilderCustomizer"),"com.example.demo.MyCustomizer")//optional,defaultisno-opcustomizer
.option(Option.valueOf("zeroDate"),"use_null")//optional,default"use_null"
.option(Option.valueOf("useServerPrepareStatement"),true)//optional,defaultfalse
.option(Option.valueOf("tcpKeepAlive"),true)//optional,defaultfalse
.option(Option.valueOf("tcpNoDelay"),true)//optional,defaultfalse
.option(Option.valueOf("autodetectExtensions"),false)//optional,defaultfalse
.build();
ConnectionFactoryconnectionFactory=ConnectionFactories.get(options);

//CreatingaMonousingProjectReactor
MonoconnectionMono=Mono.from(connectionFactory.create());

或者下面的unixdomainsocket格式：

//Minimumconfigurationforunixdomainsocket
ConnectionFactoryOptionsoptions=ConnectionFactoryOptions.builder()
.option(DRIVER,"mysql")
.option(Option.valueOf("unixSocket"),"/path/to/mysql.sock")
.option(USER,"root")
.build();
ConnectionFactoryconnectionFactory=ConnectionFactories.get(options);

MonoconnectionMono=Mono.from(connectionFactory.create());

使用MySqlConnectionFactory创建connection

上面的例子中，我们使用的是通用的r2dbcapi来创建connection，同样的，我们也可以使用特有的MySqlConnectionFactory来创建connection：

MySqlConnectionConfigurationconfiguration=MySqlConnectionConfiguration.builder()
.host("127.0.0.1")
.user("root")
.port(3306)//optional,default3306
.password("database-password-in-here")//optional,defaultnull,nullmeanshasnopassword
.database("r2dbc")//optional,defaultnull,nullmeansnotspecifyingthedatabase
.serverZoneId(ZoneId.of("Continent/City"))//optional,defaultnull,nullmeansqueryservertimezonewhenconnectioninit
.connectTimeout(Duration.ofSeconds(3))//optional,defaultnull,nullmeansnotimeout
.sslMode(SslMode.VERIFY_IDENTITY)//optional,defaultSslMode.PREFERRED
.sslCa("/path/to/mysql/ca.pem")//requiredwhensslModeisVERIFY_CAorVERIFY_IDENTITY,defaultnull,nullmeanshasnoserverCAcert
.sslCert("/path/to/mysql/client-cert.pem")//optional,defaulthasnoclientSSLcertificate
.sslKey("/path/to/mysql/client-key.pem")//optional,defaulthasnoclientSSLkey
.sslKeyPassword("key-pem-password-in-here")//optional,defaulthasnoclientSSLkeypassword
.tlsVersion(TlsVersions.TLS1_3,TlsVersions.TLS1_2,TlsVersions.TLS1_1)//optional,defaultisauto-selectedbytheserver
.sslHostnameVerifier(MyVerifier.INSTANCE)//optional,defaultisnull,nullmeansusestandardverifier
.sslContextBuilderCustomizer(MyCustomizer.INSTANCE)//optional,defaultisno-opcustomizer
.zeroDateOption(ZeroDateOption.USE_NULL)//optional,defaultZeroDateOption.USE_NULL
.useServerPrepareStatement()//Useserver-preparingstatements,defaultuseclient-preparingstatements
.tcpKeepAlive(true)//optional,controlsTCPKeepAlive,defaultisfalse
.tcpNoDelay(true)//optional,controlsTCPNoDelay,defaultisfalse
.autodetectExtensions(false)//optional,controlsextensionauto-detect,defaultistrue
.extendWith(MyExtension.INSTANCE)//optional,manualextendanextensionintoextensions,defaultusingauto-detect
.build();
ConnectionFactoryconnectionFactory=MySqlConnectionFactory.from(configuration);

//CreatingaMonousingProjectReactor
MonoconnectionMono=Mono.from(connectionFactory.create());

或者下面的unixdomainsocket方式：

//Minimumconfigurationforunixdomainsocket
MySqlConnectionConfigurationconfiguration=MySqlConnectionConfiguration.builder()
.unixSocket("/path/to/mysql.sock")
.user("root")
.build();
ConnectionFactoryconnectionFactory=MySqlConnectionFactory.from(configuration);

MonoconnectionMono=Mono.from(connectionFactory.create());

执行statement

首先看一个简单的不带参数的statement:

connection.createStatement("INSERTINTO`person`(`first_name`,`last_name`)VALUES('who','how')")
.execute();//returnaPublisherincludeoneResult

然后看一个带参数的statement:

connection.createStatement("INSERTINTO`person`(`birth`,`nickname`,`show_name`)VALUES(?,?name,?name)")
.bind(0,LocalDateTime.of(2019,6,25,12,12,12))
.bind("name","Someone")//Notone-to-onebinding,calltwiceofnativeindex-bindings,orcallonceofname-bindings.
.add()
.bind(0,LocalDateTime.of(2009,6,25,12,12,12))
.bind(1,"MyNickname")
.bind(2,"Namingshow")
.returnGeneratedValues("generated_id")
.execute();//returnaPublisherincludetwoResults.

注意，如果参数是null的话，可以使用bindNull来进行null值的绑定。

接下来我们看一个批量执行的操作：

connection.createBatch()
.add("INSERTINTO`person`(`first_name`,`last_name`)VALUES('who','how')")
.add("UPDATE`earth`SET`count`=`count`+1WHERE`id`='human'")
.execute();//returnaPublisherincludetwoResults.

执行事务

我们看一个执行事务的例子：

connection.beginTransaction()
.then(Mono.from(connection.createStatement("INSERTINTO`person`(`first_name`,`last_name`)VALUES('who','how')").execute()))
.flatMap(Result::getRowsUpdated)
.thenMany(connection.createStatement("INSERTINTO`person`(`birth`,`nickname`,`show_name`)VALUES(?,?name,?name)")
.bind(0,LocalDateTime.of(2019,6,25,12,12,12))
.bind("name","Someone")
.add()
.bind(0,LocalDateTime.of(2009,6,25,12,12,12))
.bind(1,"MyNickname")
.bind(2,"Namingshow")
.returnGeneratedValues("generated_id")
.execute())
.flatMap(Result::getRowsUpdated)
.then(connection.commitTransaction());

使用线程池

为了提升数据库的执行效率，减少建立连接的开销，一般数据库连接都会有连接池的概念，同样的r2dbc也有一个叫做r2dbc-pool的连接池。

r2dbc-pool的依赖：


io.r2dbc
r2dbc-pool
${version}

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


io.r2dbc
r2dbc-pool
${version}.BUILD-SNAPSHOT



spring-libs-snapshot
SpringSnapshotRepository
https://repo.spring.io/libs-snapshot

看一下怎么指定数据库连接池：

ConnectionFactoryconnectionFactory=ConnectionFactories.get("r2dbc:pool:://:/[?maxIdleTime=PT60S[&…]");

PublisherconnectionPublisher=connectionFactory.create();

可以看到，我们只需要在连接URL上面添加pool这个driver即可。

同样的，我们也可以通过ConnectionFactoryOptions来创建：

ConnectionFactoryconnectionFactory=ConnectionFactories.get(ConnectionFactoryOptions.builder()
.option(DRIVER,"pool")
.option(PROTOCOL,"postgresql")//driveridentifier,PROTOCOLisdelegatedasDRIVERbythepool.
.option(HOST,"…")
.option(PORT,"…")
.option(USER,"…")
.option(PASSWORD,"…")
.option(DATABASE,"…")
.build());

PublisherconnectionPublisher=connectionFactory.create();

//Alternative:CreatingaMonousingProjectReactor
MonoconnectionMono=Mono.from(connectionFactory.create());

最后，你也可以直接通过创建ConnectionPoolConfiguration来使用线程池：

ConnectionFactoryconnectionFactory=…;

ConnectionPoolConfigurationconfiguration=ConnectionPoolConfiguration.builder(connectionFactory)
.maxIdleTime(Duration.ofMillis(1000))
.maxSize(20)
.build();

ConnectionPoolpool=newConnectionPool(configuration);


MonoconnectionMono=pool.create();

//later

Connectionconnection=…;
Monorelease=connection.close();//releasedtheconnectionbacktothepool

//applicationshutdown
pool.dispose();

到此这篇关于深入理解r2dbc在mysql中的使用的文章就介绍到这了,更多相关mysqlr2dbc内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！

声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。