R2dbc连接操作mysql

R2dbc操作mysql

依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-webflux</artifactId>
        </dependency>
        <!--r2dbc mysql 库-->
        <dependency>
            <groupId>dev.miku</groupId>
            <artifactId>r2dbc-mysql</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-r2dbc</artifactId>
        </dependency>
        <!--  r2dbc 连接池 -->
        <dependency>
            <groupId>io.r2dbc</groupId>
            <artifactId>r2dbc-pool</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>io.projectreactor</groupId>
            <artifactId>reactor-test</artifactId>
            <version>3.3.13.RELEASE</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <!--r2dbc mysql 库-->
        <dependency>
            <groupId>dev.miku</groupId>
            <artifactId>r2dbc-mysql</artifactId>
        </dependency>
        <!--自动配置需要引入的一个嵌入式数据库类型对象-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <!--  r2dbc 连接池 -->
        <dependency>
            <groupId>io.r2dbc</groupId>
            <artifactId>r2dbc-pool</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>io.projectreactor</groupId>
            <artifactId>reactor-test</artifactId>
            <version>3.3.13.RELEASE</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
           

yml配置

spring:
  r2dbc:
    url: r2dbcs:mysql://{serveraddr}:{port}/r2dbc
    username: 
    password: 
           

数据库表

方式1 使用jpa操作

所对应实体类

如果需要使用一些jpa操作就需要加上@Table注解使用

但是比spring jpa 中的功能没有那么丰富

dao层

这个ReactiveCrudRepository类 里面有封装一些简单的crud操作 如果使用jpa操作就必须要在实体类中加@Table注解

这些操作都会返回结果使用flux<>包住结果给我们后续操作

@Component
public interface UserDaoRepository extends ReactiveCrudRepository<User,String> { 
	@Query("SELECT * FROM user WHERE name = :#{[0]}")
    Flux<User> findByName(String name);
}
           

测试

有一些简单的操作

比如当前操作 会返回一个Flux<> 然后我们可以对其进行操作

userDaoRepository.findAll().subscribe(s-> System.out.println(s));
userDaoRepository.findByName("lisi").subscribe(s-> System.out.println(s));
           

输出

2021-07-26 14:40:09.203 DEBUG 4100 --- [ctor-http-nio-2] o.s.r2dbc.core.DefaultDatabaseClient     : Executing SQL statement [SELECT user.* FROM user]
2021-07-26 14:40:09.206 DEBUG 4100 --- [ctor-http-nio-2] o.s.r2dbc.core.DefaultDatabaseClient     : Executing SQL statement [SELECT * FROM user WHERE name = :__synthetic_0__]
User(id=1, name=zhangsan)
User(id=2, name=lisi)
User(id=3, name=李四)
User(id=4, name=王五)
User(id=2, name=lisi)
           

方式2 R2dbcEntityTemplate

这里实体类中不需要像JPA中加一个@Table注解

如果想更灵活的话 构建一些动态条件 我们可以直接使用R2dbcEntityTemplate 进行操作

我们直接注入就好

这样更加灵活

相当于

where name=“lisi” and id in (1,2,3) order by id

输出

2021-07-26 14:40:09.205 DEBUG 4100 --- [ctor-http-nio-2] o.s.r2dbc.core.DefaultDatabaseClient     : Executing SQL statement [SELECT user.* FROM user WHERE user.name = ? AND user.id IN (?, ?, ?) ORDER BY id ASC]
User(id=2, name=lisi)
           

像mybatis plus一样动态查询

String name="";
	Criteria empty = StringUtils.isEmpty(name)? Criteria.empty():Criteria.where("name").is(name);
	template.select(User.class).from("user")
	        .matching(Query.query(empty))
	        .all().subscribe(s-> {
	    System.out.println(s);
	});
           

就像我们直接操作数据库一样简单

构建条件是使用类Criteria

我们可以点进源码进行查看可以进行哪些操作

r2dbc连接mysql出错 服务版本不支持 ssl 但是需要请求ssl

在url中添加SSL=false&sslMode=DISABLED

[HY000] Server version ‘0.0.0’ does not support SSL but mode ‘REQUIRED’ requires SSL

org.springframework.dao.DataAccessResourceFailureException: Failed to obtain R2DBC Connection; nested exception is io.r2dbc.spi.R2dbcPermissionDeniedException: [HY000] Server version '0.0.0' does not support SSL but mode 'REQUIRED' requires SSL
	at org.springframework.r2dbc.connection.ConnectionFactoryUtils.lambda$getConnection$0(ConnectionFactoryUtils.java:88) ~[spring-r2dbc-5.3.8.jar:5.3.8]
	Suppressed: reactor.core.publisher.FluxOnAssembly$OnAssemblyException: 
           

添加SSL=false&sslMode=DISABLED

spring:
  r2dbc:
    url: r2dbcs:mysql://server:3306/nlp?SSL=false&sslMode=DISABLED
    username: txz
    password: txz1234