Spring Boot Rest API With MySQL

Spring Boot is built for this purpose. It has a million functions to support us to create Rest API. Create a Spring Boot Rest API is quiet simple, all basic needs is already done by Spring itself so we can just focus on our business logic. Let me show you how simple it is.

First thing you must to do is, create the Spring Boot App. For this project, we need to put 2 additional dependencies:

MySQL Driver	MySQL JDBC and R2DBC driver.
Spring Web	Build web, including RESTful, applications using Spring MVC. Uses Apache Tomcat as the default embedded container.
Spring Data JPA	Persist data in SQL stores with Java Persistence API using Spring Data and Hibernate.

This project will contains a basic function of an API (Create, Read, Update, Delete), and of course we need a database to store our data. So, let’s get it done!

1. Create a Database

Let’s start with create a database structure, and execute below query to your MySQL Client. You can modify in case you want to change or even add more fields.

CREATE DATABASE todo_list;
USE todo_list;

CREATE TABLE todo(
	id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
	name varchar(255) NOT NULL,
	description varchar(255) NOT NULL,
	created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

In above query, we are trying to create a todo_list database and create a todo table. Inside the table we are trying to store a name and description of our todo data, also an information about when did the data stored and/or updated.

Why do we need to store a created and updated data? Because in a real industry, we can process this data into usable information. Such as, in what day or even hour that our user use our system.

Don’t forget to add some sample data.

insert into todo (`name`, `description`)values
	('Do Math Homework', 'Chapter 7, about Pythagoras Formula'),
	('Go to Bike Shop', 'Buy lamp and other parts'),
	('Fix Bugs on Resto App', 'So many bugs, I dont know where it come from');

Our database is ready, so let’s go to our Spring Application.

2. Create a Database Connection

In our application, first thing we must to do is create a Database Configuration. So our application can communicate with our database. After you create a Spring application, open src -> main -> resources -> application.properties file then follow below snippet code.

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/todo?serverTimezone=Asia/Jakarta
spring.datasource.username=root
spring.datasource.password=root

Spring already support for YML format for our properties, so it’s up to you to use properties or yml format. For YML format, follow this configuration.

spring:
    jpa:
        properties:
            hibernate:
                dialect: org.hibernate.dialect.MySQL5InnoDBDialect
    datasource:
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/todo?serverTimezone=Asia/Jakarta
        username: root
        password: root

That’s it. Spring Boot support auto-configuration so it helps us a lot to understand and centralized our configuration into one file with simple and understandable format. For more configuration you can find it here.

Next step, please take a look at our structures on the application.

3. Create a Database Modelling

This modelling is representation of our tables in our database into a Java classes. We don’t need to put all tables here, just the table which will we use in our application. For this project we will represent the todo table into Todo class. Create a class inside a model folder.

@Entity
@Table(name = "todo")
data class Todo(
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    var id: Long? = null,

    @Column(name = "name")
    var name: String? = null,

    @Column(name = "description")
    var description: String? = null
)

In spring, all configuration is done by the annotations. In our code above, we can tell to spring that:

@Entity	This model class is an entity of the ORM / Hibernate.
@Table	This model class is a representation of todo table.
@Id	Indicates that the field is the primary key of the table.
@GeneratedValue	Indicates that the field is auto generated or represent of auto_increment in SQL.
@Column	Indicates that the field is a column of a table. This annotation could be Optional, if the real column name is equal as the field.

4. Create a Database Repository

So, the repository is like a bridge between Application and Database. Technically, using the Repository is allow us to do a SQL query like SELECT, INSERT, DELETE, and so on.

In our code, repository is an extends of JPA. It will give more ability to repository called Query DSL, that allow us to using a keyword as SQL Query like

fun findById(id: Long) : Todo
fun countByName(name: String): Int

We will discuss it later. So for this project, let’s create a file

@Repository
interface TodoRepository : JpaRepository<Todo, Long> {

    fun findByName(name: String): List<Todo>
}

Yes, we need to add more annotation. this annotation will tell Spring that this interface is a repository of Todo entity class. You can take a look inside JpaRepository that already has a bunch of feature, so by extending JpaRepository , our repo has that features also we can add as much as we want.

5. Create a Controller

Controller is a starting point of our features. User will call our endpoint through the Controller, then our business logic will start from there. Add some validations, queries, and so on.

@RestController
@RequestMapping(value = ["/todo"], produces = [MediaType.APPLICATION_JSON_VALUE])
class TodoController @Autowired constructor(private val todoRepository: TodoRepository) {

    @GetMapping
    fun getAll(): ResponseEntity<Any> {
        val result: List<TodoEntity> = todoRepository.findAll()
        return ResponseEntity(result, HttpStatus.OK)
    }

    @GetMapping("{id}")
    fun getById(@PathVariable("id") id: Long): ResponseEntity<Any> {
        val result: TodoEntity = todoRepository.findById(id)
            .orElseThrow { throw Exception("Data not found for id: $id") }
        return ResponseEntity(result, HttpStatus.OK)
    }
}

Above code says that we create two endpoints, to get all data and get data from specific id. To run application we have 2 ways:
– run from Intelij green tringle button (usually at right-top side)
– run using terminal / command prompt ./mvn spring-boot:run for maven or ./gradlew bootRun for gradle

6. Testing

Last step, test our application using Postman or your Browser.

Done, for complete code you can find here. Hope you enjoy and see you…