3. Spring WebFlux: Using Relational Database (MySQL & PostgreSQL)

7 min readMay 8, 2021

On the previous article, we learned how to create a rest API using spring WebFlux. On this tutorial we will improve on that by adding database support.
Full code for this tutorial can be found on GitHub branch “3-using-relational-database” by visiting this link. https://github.com/gathecageorge/spring-webflux-rest-api-intro/tree/3-using-relational-database
Alternatively you can get code of previous tutorial and code along in this tutorial from the “2-practical-guide-intro” branch here https://github.com/gathecageorge/spring-webflux-rest-api-intro/tree/2-practical-guide-intro

The first step will be adding extra dependencies to our existing project. Open pom.xml and add the following lines between the opening “<dependencies>” and closing “</dependencies>” tags.

Now we can model our sample database.

You can create the database by running SQL statements on this file.

https://github.com/gathecageorge/spring-webflux-rest-api-intro/blob/database/db.sql

Model Our Database & Repository

We will use spring data to query for records from the database. The first step is to create models for the 3 tables as follows. We are using Lombok dependency to generate boiler plate code like setters, getters, constructors and builder pattern for our POJOs. @Table annotation comes from Spring Data and allows us to specify the table name of our model. Also each class has @Id denoting the primary key for the table and @Column specifying the column name for the attribute.

Students Table

Courses Table

Coursework Table

Query for a student

Now that we have the database, our first task is to query for a single students information from the database in a reactive way. Create “StudentsController” on controller package and a method “getStudent”. Then create a repository for students table. See below;

StudentsRepository.java

Note here the repository interface extends ReactiveCrudRepository to provide us with out of the box crud on table students in a reactive way.

StudentsController.java

As you can see, we are returning a Mono on our get student method. We can just as well return “Mono<Student>” but we want our API to be restful meaning we can modify http status codes in response. Thus we use ResponseEntity. We are also using spring and Lombok @AllArgsConstructor to auto inject an instance of StudentsRepository.java object to our controller.

The @GetMapping(“/students/{studentID}”) means we are expecting Http get method and a parameter which is the studentID on URL which will be put into studentID variable by @PathVariable. What remains is just to call repository method “findByID” passing the ID received and mapping(transforming) the response received to a ResponseEntity with correct status.

Run the application

One more step to run application, we need to update application.properties file with DB connection details as follows. Change username and password as required for location of your database.

When all this is done and you run the application, you can get users by visiting “http://localhost:8080/students/1” to get details about student 1. Sample response below.

Using tabbed postman to invoke endpoint created.

Adding a student to database.(Saving data)

To add a student create another method in StudentsController as below

We are now using @PostMapping to indicate endpoint is only reachable via http POST method. We then accept json sent with @RequestBody and create a students object from it. We then set status to active and registered date to current time. We just then call repository save method and map saved object to the ResponseEntity and Http Status Created. These means we dont have to expect a client to send status and date. See sample response.

Updating a student

To update a student, we first need to find the student and if found we update the object and save to the database. If not found we need to return a specific message with student not found.

Sample response when a valid studentID is

When an invalid studentID is sent an error is thrown and client gets this response.

That’s is not soo good response, we need to return a better message to the user indicating what occurred. Let do some modifications. Create a class to model a response as below. We are using generic programming to be able to reuse the class across the application.

Then we will modify our update method on controller as follows. NB we have introduced onErrorResume to catch the error thrown when no student was found and create a custom response. Now our response is always consistent.

Final response when studentID is found

Final response when studentID is NOT found

Deleting a student record

For whatever reason, though not recommended(always update status to deleted) and a bad practice, you might need to delete a student record. This will involve deleting all students coursework since its not possible to delete if coursework exists. This id due to database constraints set on the database with a foreign key on coursework table referencing students table. We will need to create a coursework repository to provide CRUD on coursework table. Create one as follows.

We are creating a new method that will delete coursework for a student given studentID. Good thing about spring, we dont need to provide implementation of the method since we have named the method intentionally as “deleteByStudentID”. We have a column named studentID on the model and using that we can create any combination crud operation using a special naming convenction and spring will provide the implementation. e.g “findByStudentID” or “findByStudentIDAndCourseID”.

Then we need to create the object to be autowired on the controller as follows.

Then we can create delete endpoint as follows. NOTE @Transactional this will make sure the delete operation happens inside a transaction. i.e. either pass all or fail all

Sample response on calling delete. Just as courtesy we are returning deleted model to the client. A message would be enough too. If student is not found similar message is returned as when we were checking on update.

Querying Multiple Students from Database

To query multiple students from database, remember from 1st tutorial, we need to return a flux of Students. We also annotate with @GetMapping and add produces to signify stream data is returned.

We are also accepting some parameters like page number and students per page(limit) and any filter params. If none is provided default values are used. We then call a method “findAllByStatusAndName” on student repository that we will create, then add a delay to simulate slow loading of responses.

Now lets add the method to students repository. This time we need to add custom query to allow with filter and same time pagination.

When we call the endpoint sample response can be seen here.

Summary

Okay that was a long lesson. I hope it was worth it. Well here lets summarize what we have learnt so far.

We have learnt how to use spring data on our API. We have created table models, created repositories and auto wired them to a controller using Lombok @AllArgsConstructor annotation.
We have learnt how to retrieve data from database. Either a single record or multiple records.
We have learnt how to save and update a record
We have learnt how to delete a record and chain actions on the database ie delete dependencies before deleting main data for integrity checks.
We have learnt how to paginate responses from the database.

WHATS NEXT

So what’s next after this? Well checkout for the next articles on the series.

Want to learn more, have a topic in JAVA you might need articles on or have questions? Reach out to me on my profile or via LinkedIn here.

Hello👋, Did you find this tutorial helpful, please click the clap 👏 button below to show your support for the author 👇. Also this will help the algorithm for others who are learning to see this story.