Part 2: Connect Restful api with MySQL

Part 1: Write Restful api with spring boot

Spring Boot gives you defaults on all things. For example, the default database is H2. When you want to use any other database, you must define the connection attributes in the application.properties file.

We have a repository which can persist data while the application is running. By connecting it with MySql we can make sure we have our tasks available always. In case you are wondering what is difference between Hibernate and Spring Data JPA?

Spring Data is a part of the Spring Framework. The goal of Spring Data repository abstraction is to significantly reduce the amount of boilerplate code required to implement data access layers for various persistence stores.

Spring Data JPA is not a JPA provider. It is a library/framework that adds an extra layer of abstraction on the top of our JPA provider (like Hibernate).

Setup MySql:

For development purposes, it is good idea to have local MySQL server running. You can this tutorials to setup MySQL (Mac or Windows).

Once you have mysql running, you can use terminal to create a database and a user to manage the database.

Set the path to mysql using: export PATH=/usr/local/mysql/bin:$PATH

mysql -u root -p
mysql> create database db_todo; -- Creates the new database
mysql> create user 'todouser'@'%' identified by 'YourStrongPassword'; -- Creates the user
mysql> grant all on db_todo.* to 'todouser'@'%'; -- Gives all privileges to the new user on the newly created database

Now we have a database available and we need to hook it up in our project. Go to the application.properties file and add following:

spring.jpa.hibernate.ddl-auto=update

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect
spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/db_todo
spring.datasource.username=todouser
spring.datasource.password=YourStrongPassword

spring.jpa.hibernate.ddl-auto can be none, update, create, or create-drop. See the Hibernate documentation for details.

Now go to build.gradle.ktx and add following:

dependencies {
   runtimeOnly("mysql:mysql-connector-java")

That’s it. Run the project and now when you will add tasks, they will be saved in mysql table.

Lets update our Todo entity to have due date of a task. This is so that we can play with the repository to do some more operations. Now our Task entity will look like following:

@Entity(name = "tasks")
data class Task(
        @Id @GeneratedValue(strategy = GenerationType.IDENTITY) 
        val id: Long = 0,
        
        @get: NotBlank 
        val title: String,
        
        val description: String,
        
        @DateTimeFormat(pattern = "yyyy-MM-dd") 
        val dueDate: Date
)

We only created an interface of repository by extending JpaRepository but now we will add more methods to do different type of operations on the repository. The cool part is the IDE helping us construct those methods with autocomplete. When you type findAllBy, IDE will propose you various options to choose. This is columns of your table and an operation. You can combine multiple operations by using And or Or.

We add 2 more functions in our repository that will help us searching tasks by title and get tasks due after a given date. Now our repository will look like following:

@Repository
interface TasksRepository : JpaRepository<Task, Long> {
    fun findAllByTitleContains(search: String): List<Task>

    fun findAllByDueDateGreaterThanEqual(date: Date): List<Task>
}

In our controller we will add 2 more mappings to use these new methods in the repository.

@GetMapping("/tasks/title/{search}")
fun getTasksByTitle(@PathVariable search: String): ResponseEntity<List<Task>> {
    return ResponseEntity.ok(
       repository.findAllByTitleContains(search)
     )
}

@GetMapping("/tasks/date/{dueDate}")
fun getTasksDueAfterDate(@PathVariable dueDate: String): ResponseEntity<List<Task>> {
    val date = SimpleDateFormat("yyyy-MM-dd").parse(dueDate)
    return ResponseEntity.ok(
        repository.findAllByDueDateGreaterThanEqual(date)
    )
}

Obviously everything can not be done by just using predicted operators. We can also write mysql query to get data from the database. Lets add one last method in the repository which will get us tasks due after given date, exactly what we already have, but using a mysql query.

@Query(
        value = "SELECT * FROM db_todo.tasks WHERE date>=:date_limit",
        nativeQuery = true
)
fun getDueTasksAfterDate(
        @Param("date_limit") dateLimit: Date
): List<Task>

Next:

• Wouldn’t it be super cool to have auto generated swagger documentation of your api?
• How to deploy your application to make it actually useful?