适用于Microsoft SQL Server的Spring Data R2DBC

This post is the sibling of another blog post I wrote that focused on Spring Data R2DBC and Postgres. Hopefully, you read the title before traversing to this page. So, you should know that I will be writing about Spring Data R2DBC’s integration with Microsoft SQL Server. As Spring provides a lot of magic 🧙‍♀️, most of the code from my previous Postgres post will still work and only requires a few changes to get up and running with MSSQL.

For more background information, I do recommend reading the Postgres version of this post as I do not want to repeat myself too much. That being said, below is its introduction which should provide enough context for us to continue. I will keep this post short by providing brief notes alongside the implementation for MSSQL.

不久之前，发布了JDBC驱动程序的反应型。 称为R2DBC。 它允许将数据异步流传输到已预订的任何端点。 通过将R2DBC之类的反应性驱动程序与Spring WebFlux结合使用，可以编写一个完整的应用程序，以异步方式处理数据的接收和发送。 在本文中，我们将重点介绍数据库。 从连接到数据库，然后最终保存和检索数据。 为此，我们将使用Spring Data。 与所有Spring Data模块一样，它为我们提供了现成的配置。 减少为实现应用程序设置而需要编写的样板代码数量。 最重要的是，它在数据库驱动程序上提供了一层，使执行简单任务变得更加容易，而较困难的任务则减轻了一些痛苦。

Dependencies

<dependencies>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
  </dependency>
  <dependency>
    <groupId>org.springframework.data</groupId>
    <artifactId>spring-data-r2dbc</artifactId>
    <version>1.0.0.M1</version>
  </dependency>
  <dependency>
    <groupId>io.r2dbc</groupId>
    <artifactId>r2dbc-mssql</artifactId>
    <version>1.0.0.M7</version>
  </dependency>
  <dependency>
    <groupId>io.projectreactor</groupId>
    <artifactId>reactor-core</artifactId>
  </dependency>
</dependencies>

<repositories>
  <repository>
    <id>repository.spring.milestone</id>
    <name>Spring Milestone Repository</name>
    <url>http://repo.spring.io/milestone</url>
  </repository>
</repositories>

这些依赖是您发布此帖子所需的最低要求。 用弹簧数据r2dbc和r2dbc-mssql做所有繁重的工作。 根据您要使用的数据库r2dbc-mssql可以切换到另一个具有R2DBC驱动程序的数据库（例如r2dbc-postgresql）。

Connecting to the database

春天在这里回来了。 它仅扩展您即可获得所有基本设置抽象R2dbc配置并实施连接工厂豆：

@Configuration
@EnableR2dbcRepositories
class DatabaseConfiguration(
  @Value("\${spring.data.mssql.host}") private val host: String,
  @Value("\${spring.data.mssql.port}") private val port: Int,
  @Value("\${spring.data.mssql.database}") private val database: String,
  @Value("\${spring.data.mssql.username}") private val username: String,
  @Value("\${spring.data.mssql.password}") private val password: String
) : AbstractR2dbcConfiguration() {

  override fun connectionFactory(): ConnectionFactory {
    return MssqlConnectionFactory(
      MssqlConnectionConfiguration.builder()
        .host(host)
        .port(port)
        .database(database)
        .username(username)
        .password(password).build()
    )
  }
}

您唯一需要实现的bean是连接工厂包含（顾名思义）有关数据库连接的详细信息。 这个bean是数据库客户端。 这是一个重要的类，因为它对于在Spring Data R2DBC模块中执行SQL至关重要。 剩下的豆子数据库客户端建立于内部抽象R2dbc配置并送入客户 让Spring带动轮子🙌🚗，您会没事的。 严肃地说抽象R2dbc配置和实施connectionFactory将使您的应用程序快速启动并运行。

最后，@ EnableR2dbcRepositories注释指示Spring查找扩展Spring的Repository接口的任何存储库接口。 这用作检测Spring Data存储库的基本接口。 在下一节中，我们将对此进行更仔细的研究。

Creating a Spring Data Repository

R2DBC当前不支持查询推断，这是我习惯于Spring Data给我的便捷功能之一。 尽管它丢失了，但是生命可以继续，但是这次它需要更多的代码才能完成：

@Repository
interface PersonRepository : R2dbcRepository<Person, Int> {

  @Query("SELECT * FROM people WHERE name = @name")
  fun findAllByName(name: String): Flux<Person>

  @Query("SELECT * FROM people WHERE age = @age")
  fun findAllByAge(age: Int): Flux<Person>
}

通过扩展R2DBC存储库，Spring将获取这些查询并进行分析，并为您提供一些典型的查询，例如findByAllId。 这是@ EnableR2dbcRepositories先前添加的注释起作用。 没有此注释，则该存储库将无法为您做任何事情，而是需要完全手动执行。

@查询用于定义函数将提供的查询，Spring提供实现本身。 传递给查询的输入是使用以下格式定义的：@<parameter_name>。

的助焊剂从这些函数返回的对象基本上是清单。 换句话说，它们返回多个值。 只要您订阅了这些值，这些值就会在到达时返回并进行处理。助焊剂一旦创建完成。

The entity

尽管Spring Data R2DBC并非旨在成为完整的ORM，但它仍然提供实体映射。 下面是一个实体类，实际上并不需要太多解释：

@Table("people")
data class Person(
  @Id val id: Int? = null,
  val name: String,
  val age: Int
)

我说不需要太多解释，所以让我提出这一点。ID已设为可为空，并且默认值为空值 to allow Postgres to generate the next suitable value itself. If this is not 空值able and an ID value is provIDed, Spring will actually try to run an update instead of an insert upon saving. There are other ways around this, but I think this is good enough. To be honest, this is a Kotlin specific problem. If you are using Java, then relax, you won’t need to worry about this. That being saID, come to the light sIDe and program in Kotlin, all your dreams will be fulfilled if you do (I cannot guarantee that will actually happen 🤦‍♂️).

该实体将映射到人下表定义：

CREATE TABLE people (
  id INT NOT NULL IDENTITY PRIMARY KEY,
  name VARCHAR(75) NOT NULL,
  age INTEGER NOT NULL
)

Seeing it all in action

所有设置均已完成，应用下面的类利用了之前编写的查询以及Spring Data开箱即用的查询。

@SpringBootApplication
class Application : CommandLineRunner {

  @Autowired
  private lateinit var personRepository: PersonRepository

  override fun run(vararg args: String?) {
    personRepository.saveAll(
      listOf(
        Person(name = "Dan Newton", age = 25),
        Person(name = "Laura So", age = 23)
      )
    ).log().subscribe()
    personRepository.findAll().subscribe { log.info("findAll - $it") }
    personRepository.findAllById(Mono.just(1)).subscribe { log.info("findAllById - $it") }
    personRepository.findAllByName("Laura So").subscribe { log.info("findAllByName - $it") }
    personRepository.findAllByAge(25).subscribe { log.info("findAllByAge - $it") }
  }
}

在此代码的实际实现中，睡觉已添加，以确保响应代码有机会工作。 反应性应用程序旨在异步执行操作，因此该应用程序将处理不同线程中的函数调用。 如果不阻塞主线程，这些异步进程可能永远不会完全执行。 为了使所有内容保持整洁，我删除了睡觉来自示例。

运行上面的代码的输出如下所示：

2019-05-06 18:05:04.766 INFO 23225 --- [main] reactor.Flux.ConcatMap.1 : onSubscribe(FluxConcatMap.ConcatMapImmediate)
2019-05-06 18:05:04.767 INFO 23225 --- [main] reactor.Flux.ConcatMap.1 : request(unbounded)
2019-05-06 18:05:15.451 INFO 23225 --- [actor-tcp-nio-1] reactor.Flux.ConcatMap.1 : onNext(Person(id=1, name=Dan Newton, age=25))
2019-05-06 18:05:20.533 INFO 23225 --- [actor-tcp-nio-1] reactor.Flux.ConcatMap.1 : onNext(Person(id=2, name=Laura So, age=23))
2019-05-06 18:05:20.533 INFO 23225 --- [actor-tcp-nio-1] reactor.Flux.ConcatMap.1 : onComplete()
2019-05-06 18:05:25.550 INFO 23225 --- [actor-tcp-nio-2] com.lankydanblog.tutorial.Application : findAll - Person(id=1, name=Dan Newton, age=25)
2019-05-06 18:05:25.550 INFO 23225 --- [actor-tcp-nio-2] com.lankydanblog.tutorial.Application : findAll - Person(id=2, name=Laura So, age=23)
2019-05-06 18:05:30.554 INFO 23225 --- [actor-tcp-nio-3] com.lankydanblog.tutorial.Application : findAllById - Person(id=1, name=Dan Newton, age=25)
2019-05-06 18:05:35.582 INFO 23225 --- [actor-tcp-nio-4] com.lankydanblog.tutorial.Application : findAllByName - Person(id=2, name=Laura So, age=23)
2019-05-06 18:05:40.587 INFO 23225 --- [actor-tcp-nio-5] com.lankydanblog.tutorial.Application : findAllByAge - Person(id=1, name=Dan Newton, age=25)

这里有一些要注意的地方：

• onSubscribe和请求发生在主线程上助焊剂被叫。 只要保存全部输出此内容，因为它已包含日志 function. Adding this to the other calls would have lead to the same result of 日志ging to the main thread.包含在订阅 function和the internal steps of the 助焊剂在不同的线程上运行。

这与您在生产应用程序中如何使用反应式流的真实表示并不接近，但希望可以演示如何使用它们，并对它们的执行方式提供一些见解。

doOnComplete()

是的，这是一个聪明的双关语，使用的名称是助焊剂功能🤣😒🤦‍♀️。

We have come to the close of this shortish post. I haven’t explained too much because my Postgres version did all that and I am a too lazy to rewrite it. But, this is also because Spring Data provides a generic interface that will lay most of the paving for you. Leaving you with only a few steps that you need to do yourself. Firstly, deciding which database to use, whether this is Postgres, H2, Microsoft SQL Server (as shown in this post) or other databases that have R2DBC drivers in the future. Finally, on the code side, setting up the ConnectionFactory and repository queries which will slightly differ between databases. I should probably mention the fact that R2DBC enables you to move towards more reactive applications. I mean this, this post is about R2DBC after all 🤷‍♀️. Utilising databases with R2DBC drivers and Spring modules will allow you to build a fully reactive application. Starting from the core of your application (the database) to the edges and endpoints exposed to external clients. Spring’s push for reactive applications as well as the improvements in the R2DBC drivers that are yet to come, will make this sort of transition (if appropriate to your business) less painful and faster to implement.

The code used in this post can be found on my GitHub.

If you found this post helpful, you can follow me on Twitter at @LankyDanDev to keep up with my new posts.

from: https://dev.to//lankydandev/spring-data-r2dbc-for-microsoft-sql-server-1084