Connect with MSSQL with Spring Reactive (R2DBC)

Question

I am currently trying to establish a database connection with a Microsoft SQL Server. Unfortunately I can not understand why it does not work. And the error message can unfortunately not give me precise information. It looks like my code isn't even trying to connect to the database.

My Starterclaas:

@SpringBootApplication
public class R2Dbc3Application {

public static void main(String[] args) {
    SpringApplication.run(R2Dbc3Application.class, args);
}

}

DatabaseConfiguration:

    package com.example.config;

import io.r2dbc.mssql.MssqlConnectionConfiguration;
import io.r2dbc.mssql.MssqlConnectionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.r2dbc.config.AbstractR2dbcConfiguration;
import org.springframework.data.r2dbc.repository.config.EnableR2dbcRepositories;

@Configuration
@EnableR2dbcRepositories("com.example.repository")
public class DatabaseConfiguration extends AbstractR2dbcConfiguration {

    private final Logger log = LoggerFactory.getLogger(DatabaseConfiguration.class);

    @Value("${spring.data.mssql.host}")
    private String host;

    @Value("${spring.data.mssql.database}")
    private String database;

    @Value("${spring.data.mssql.username}")
    private String username;

    @Value("${spring.data.mssql.password}")
    private String password;



    @Bean
    @Override
    public MssqlConnectionFactory connectionFactory() {

        System.out.println("Connecting to database" +  host);
        return new MssqlConnectionFactory(MssqlConnectionConfiguration.builder()
                .host(host)
                .port(1453)
                .database(database)
                .username(username)
                .password(password)
                .build());
    }
}

my DatabaseInitializer:

package com.example.config;

import com.example.domain.Person;
import com.example.repository.PersonRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;

@Component
public class DatabaseInitializer {

    private final Logger log = LoggerFactory.getLogger(DatabaseInitializer.class);

    @Autowired
    PersonRepository personRepository;

    public DatabaseInitializer(PersonRepository personRepository) {
        this.personRepository = personRepository;
    }

    @PostConstruct
    public void init() {
        log.info("Initializing database if necessary");
        personRepository.findAll().count().subscribe(count -> {
            if (count == 0) {
                log.info("Database is empty, inserting sample data");
                createPerson("Josh", "Long", "Pivotal");
                createPerson("Julien", "Dubois", "Microsoft");
            } else {
                log.info("Database is already initialized");
            }
        });
    }

    private void createPerson(String firstName, String lastName, String company) {
        Person person = new Person();
        person.setFirstName(firstName);
        person.setLastName(lastName);
        person.setCompany(company);
        personRepository.save(person).log().subscribe();
    }
}

Person.Java:

package com.example.domain;

import org.springframework.data.annotation.Id; import org.springframework.data.relational.core.mapping.Table;

@Table("person") public class Person {

@Id
private Long id;

private String firstName;

private String lastName;

private String company;

and getter/setter

My PersonRepository

package com.example.repository;

import com.example.domain.Person;
import org.springframework.data.repository.reactive.ReactiveCrudRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface PersonRepository extends ReactiveCrudRepository<Person, Long> {
}

And my Controller:

package com.example.web;

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.example.domain.Person;
import com.example.repository.PersonRepository;

import reactor.core.publisher.Flux;

@RestController
@RequestMapping("/")
public class PersonController {

    private final PersonRepository personRepository;

    public PersonController(PersonRepository personRepository) {
        this.personRepository = personRepository;
    }

    @GetMapping("/persons")
    public Flux<Person> list() {
        return personRepository.findAll();
    }


}

My Pom:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.6.RELEASE</version>
        <relativePath /> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>R2DBC2</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>R2DBC2</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-webflux</artifactId>
        </dependency>
        <!-- see https://github.com/r2dbc/r2dbc-mssql/issues/77 -->
        <dependency>
            <groupId>io.projectreactor</groupId>
            <artifactId>reactor-core</artifactId>
        </dependency>

        <dependency>
            <groupId>io.r2dbc</groupId>
            <artifactId>r2dbc-mssql</artifactId>
            <version>1.0.0.M7</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-r2dbc</artifactId>
            <version>1.0.0.gh-151-SNAPSHOT</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>io.projectreactor</groupId>
            <artifactId>reactor-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

    <repositories>
        <repository>
            <id>spring-libs-snapshot</id>
            <url>https://repo.spring.io/libs-snapshot</url>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </repository>
        <repository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
        </repository>
    </repositories>
    <pluginRepositories>
        <pluginRepository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
        </pluginRepository>
    </pluginRepositories>


</project>

Error Message:

This application has no configured error view, so you are seeing this as a fallback.

Tue May 19 12:23:33 CEST 2020
[58026f55-7] There was an unexpected error (type=Not Found, status=404).
org.springframework.web.server.ResponseStatusException: 404 NOT_FOUND
    at org.springframework.web.reactive.resource.ResourceWebHandler.lambda$handle$0(ResourceWebHandler.java:325)
    Suppressed: reactor.core.publisher.FluxOnAssembly$OnAssemblyException: 
Error has been observed at the following site(s):
    |_ checkpoint ⇢ HTTP GET "/persons" [ExceptionHandlingWebHandler]
Stack trace:
        at org.springframework.web.reactive.resource.ResourceWebHandler.lambda$handle$0(ResourceWebHandler.java:325)
        at reactor.core.publisher.MonoDefer.subscribe(MonoDefer.java:44)
        at reactor.core.publisher.Mono.subscribe(Mono.java:4210)
        at reactor.core.publisher.FluxSwitchIfEmpty$SwitchIfEmptySubscriber.onComplete(FluxSwitchIfEmpty.java:75)
        at reactor.core.publisher.MonoFlatMap$FlatMapMain.onComplete(MonoFlatMap.java:174)
        at reactor.core.publisher.MonoNext$NextSubscriber.onComplete(MonoNext.java:96)
        at reactor.core.publisher.FluxConcatMap$ConcatMapImmediate.drain(FluxConcatMap.java:359)
        at reactor.core.publisher.FluxConcatMap$ConcatMapImmediate.onSubscribe(FluxConcatMap.java:21

You have a class-level `@RequestMapping("/")` and a method-level `@GetMapping("/persons")`. Count the amount of slashes when combining the two. — Kayaman, May 19 '20 at 10:33
Thank you for your answer: I have now deleted the @RequestMapping ("/") from the class, but it still does not work. But thanks for the quick answer — kemal, May 19 '20 at 10:47
can you please post your directory structure for the project and post the server logs that shows that `PersonController` get registered at startup — Toerktumlare, May 19 '20 at 10:54
As I thought, my code didn't even try to connect because it didn't even reach the controller. The controller and other classes were in different packages. A @ComponentScan ({"com.example.web", "com.example.repository", "com.example.config", "com.example.domain"}) in the starter class solved the problem. — kemal, May 19 '20 at 12:07

score 1 · Answer 1 · answered May 19 '20 at 10:44

1

org.springframework.web.server.ResponseStatusException: 404 NOT_FOUND

It means the endpoint mapping through @GetMapping was not hit. Remember the paths are joined with defined mappings at the class level and then at the method level, the current endpoint would look like:

localhost:8080//persons

This is incorrect.

As long as the @RestController is a root with no further mapping, don't include the "/" character as long as it adds another "layer" or "subpath" in the path. The correct usage would be @RequestMapping("/endpoint-name"). In your case, you don't want an extra "subpath" so omit the annotation:

@RestController
public class PersonController { ... }

Unfortunatelly, I couldn't find any reference to support my statement.

answered May 19 '20 at 10:44

Nikolas Charalambidis

40,893
16
117
183

Thank you for your answer. I accepted your suggestion. Nevertheless, the same error message still appears My Changes: @RestController public class PersonController { private final PersonRepository personRepository; public PersonController(PersonRepository personRepository) { this.personRepository = personRepository; } @GetMapping("/persons") public Flux list() { return personRepository.findAll(); } } – kemal May 19 '20 at 10:54
Try to add `produces = MediaType.TEXT_EVENT_STREAM_VALUE` to the endpoing mapping. – Nikolas Charalambidis May 19 '20 at 11:27

Connect with MSSQL with Spring Reactive (R2DBC)

1 Answers1