How to build a CRUD app with MySQL and Spring-boot

All right where do we start? If you are in here just for the code start from the next paragraph! Peace!. Let’s start with a bit of a back story — Kids! back in 2005, before I was dad, I was this whole other person… Oh, wait, I’m not Ted. Let me switch back to my story — As I’m volunteering as a SE in an open-source organization called Sustainable Education Foundation, I was assigned into a project where I had to build a CRUD application to be functioned as the admin panel for the SEF — Sustainable Education Foundation — fellowship programme where we had to generate a digital certificate for the SEF fellows. I started coding from the moment I was assigned to the project and after two days I deployed the fully functioning web app. All right… alright… It was a fat ugly lie; Just like any other programmer would do, I started googling (Come on guys! This is the industrial standard! Google, Copy, Paste, Repeat! Seriously though, don’t copy-paste try to grasp what you google in the best way you can). And here I am sharing what I learned and my experience with spring framework.

Here’s the outline of the application we are going to build, We will use a MySQL database to store our data and to do the CRUD (Create, Read, Update, Delete) operations we are gonna create a spring boot application. We are going to build a web app to be used as your digital bookshelf.

PART ONE: MySQL

I assume that you already have MySQL installed in your machine, If not follow this tutorial and install it. Hit Crtl + Alt + T , Open your terminal and type mysql -u root -p and hit enter. You will be asked for your root password, enter that and you will get a screen like this.

Now we are going to create a new database by executing below commands.

//create database
CREATE DATABASE book_shelf;

Hello Mr? Is that all for part one? Err… yeah. Pretty easy right? Yes, it is. Type exit and be done with the terminal for now.

PART TWO: Spring-boot

Alright, now we are in for the most intriguing part of the development process. First, we need to set up our spring-boot project. We can easily create our project with all the dependencies we need by using Spring Initializer. On this page, click on add dependencies and add these dependencies.

• Spring Web
• Spring Data JPA
• MySQL Driver
• Spring Boot DevTools

Spring Initializer

Change Artifact to anything you like or leave it as it is. Then hit Ctrl + Enter to download your project file. Extract the downloaded file and open it with your favourite IDE, I prefer using IntelliJ but it's your choice, I'll be using IntelliJ for the rest of this tutorial.

File Structure

This is the project file structure of our spring application. As you can see they have built the basic application up, all we have to do is shape this up a bit to fit into our scenario. If you open the pom.xml file you will find all the dependencies we added are listed there in an HTML-like format, well that's XML. If you are like, really new to maven project build, pom.xml is the maven equivalent to package.json of Node package manager.

You can run this app by hitting the run button on the top right corner if you are using IntelliJ otherwise you can run mvn spring-boot:runcommand in the terminal to run the application. Now the application will fail to start (obviously) but the build will be a success.

Okay! Great, Let’s start coding.

Open application.properties file on the resources directory and copy/paste these codes and add in your information. This is where you connect your database to your project.

spring.datasource.url = jdbc:mysql://localhost:3306/book_shelf
spring.datasource.username = root
spring.datasource.password = Your mysql root password

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

spring.jpa.hibernate.ddl-auto = update

These are the files we have to create and their functionalities.

• Book.java (com.example.bookshelf.model.Book) — To work as the blueprint of the books we are saving in the database.
• BookController.java (com.example.bookshelf.controller.BookController) — To work as the API/Rest controller of our application. This is where we define all the endpoints.
• BookNotFoundException.java (com.example.bookshelf.exception.BookNotFoundException) — Well, the file name speaks for it. This is the error handler of our application.
• BookRepository.java (com.example.bookshelf.repository.) — This is an interface we use to do all the query and inserts we need to do. Unless we need to customize our queries we can use built-in methods in JPA repository since we are going extend this one from that.

Now create a package on the com.example.bookshelf and name it model. We are going to build the model of our objects that we are going to store in our database. Since we are storing book objects name your file as Book.java (Don’t forget to set getters and setters)

This is how its gonna look like after adding the java persistence annotations. Notice how the annotations have been placed to align this object with the MySQL table.

Book.java

Let’s move on and create our repository and exception files.

Create a package under the same package and name it repository. Create an interface under that package and name it BookRepostiory.java. We need this to be a child interface of the JPA repository. This is how it’s gonna look like.

BookRepository.java

Alright, again the same ritual, package name exception, filename BookNotFoundException, Go! In this file, we should define the error message to prompt if the requested book is not in the database. This is how you do that.

BookNotFoundException.java

Now, all we have to do is create our Rest controller or the API or whatever the thing you may wanna call it. We are going to create it under the package controller, go ahead and create that package. Before we start coding we have to plan out the endpoints and what each endpoint gonna do.

• We should be able to list all the books on the bookshelf. Thus we need to create an endpoint to GET the books out of the table. GET — book/
• We should be able to add books to the shelf. Thus we need to create an endpoint to POST a new book to the table. POST — book/
• We need another endpoint to remove books from the shelf. DELETE — book/{id}

Those are the basic operations we need to with the application. Let’s go ahead and create these endpoints on a file named BookController.java.

BookController.java

Notice how we used our BookRepository and BookNotFoundException in the API. Alright, Since you guys read this far thank you for sticking up, You guys are the real winners here. Now we can test our API using postman.

First, let's run the application by executing the command mvn spring-boot:run and open postman. Now the application should be up and running on a tomcat server on port:8080 of your localhost.

Okay, let’s add some books to our table.

Now if we call the get endpoint to list all the books you can get the list of all the books you have added.

If you need to delete a book simply call the delete endpoint books/{id} replacing the id of the book you wanna delete.

Now if you list your books again you will see that book with the id 3 had been deleted.

Oh! yeah, Go ahead play around with those requests. Congratulations! You have kept up all the way and now you have successfully built up your program.

Thank You for reading and I hope you found the knowledge you were looking for when you stumbled upon my article.

Here's my Linkedin account, Hit me up with a message if you… I don’t know… Talk lord of the rings, heavy-metal or programming.