Let's create a TODO application in Java 3 Save temporary data in MySQL-> Get all-> Display on top

Hello. This is a continuation of Let's make a TODO application with Java 2 I want to make a template with Spring Initializr and make a Hello world.

This time, I will save the temporary data in the database (MySQL), get all of them, and display them on the top screen.

TODO application creation link collection

1: Brief description of MVC 2: I want to make a template with Spring Initializr and make a Hello world 3: Let's create a TODO application in Java 3 Save temporary data in MySQL-> Get all-> Display on top (here and now)

Install MySQL and Sequel Pro

First, we will introduce a DB and a convenient GUI.

I will not specify the detailed introduction here, but I think the following link will be helpful.

About the introduction of MySQL

MySQL environment construction procedure Introduction to MySQL

About the introduction of Sequel PRO

How to install Mac MySQL Sequel Pro

Create DB / table-> Create temporary data

Follow the link above to create a DB!

In this article, I will use the DB name qiita_todo.

Once the DB is created, start Sequel Pro and connect to the DB.

Then enter the following in the Query tab.

CREATE TABLE `todo` (
  `id` bigint(11) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `title` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'title',
  `deadline` date NOT NULL COMMENT 'Deadline',
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'status',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation date and time',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update date and time',
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

↓ The image looks like this.

If you press ** Ctrl + r ** to execute a Query statement ...

↓ The table should be created like this. (You can check the column specifications on the Structure tab.)

Introduction of JPA

After DB is over, let's introduce JPA next.

JPA is like a translator who connects Spring and DB. It passes the request from Spring to the DB and processes it in various ways.

For example, if you use JPA's findAll method, all the DB data will be acquired. This method will be described later.

Added JPA and MySQL to build.gradle

Add the following to the dependencies of app / build.gradle.

dependencies {
~~~abridgement~~~
　　　implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
	 runtimeOnly 'mysql:mysql-connector-java'
~~~abridgement~~~
}

Update build.gradle

You can use JPA by updating the changed part of build.gradle according to the image below!

Edit application.properties (or application.yaml)

Next, edit application.properties (or yaml) to connect the created DB and Spring.

The location of the file is app / src / main / application.properties.

Add the following.

spring.datasource.url=jdbc:mysql://localhost:3306/qiita_todo # ①
spring.datasource.username=root # ②
spring.datasource.password=     # ②
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.initialization-mode=never # ③
spring.datasource.sql-script-encoding=utf-8
spring.jpa.hibernate.ddl-auto=none # ③

① 3306 should be the default port number, but if you have changed it, change it. The part of qiita_todo is the DB name.

② If you have set your own username and password, use that as well. In this article, the username is root and there is no password. (Please note that if you copy and paste, the password may be treated as a space.)

③ There is no problem changing the settings around here, so if you are interested, please try google (throwing)

Try to put data

Now that the settings are complete, let's put in some temporary data.

Open Sequel Pro, press the Content tab and double-click an empty record to enter it, so enter an appropriate value.

Get all DB records from Controller

Next is Intel iJ's turn.

First, prepare a container (Entity) to be used when fetching data from the DB. Introducing Lombok is useful in many ways, so start from there.

Introduction of Lombok

Using Lombok makes it easier to input Getters and Setters.

Add the following to the dependencies of build.gradle.

dependencies {
~~~abridgement~~~
	compileOnly 'org.projectlombok:lombok:1.18.12'
	annotationProcessor 'org.projectlombok:lombok:1.18.12'
~~~abridgement~~~
}

After adding, let's reload gradle in the same way as when JPA was inserted.

Creating Entity class

Next is the creation of Entity.

First, create a package (folder) in the hierarchy where TodoContoroller is located.

main / java / com.example.todo <-You can create it by right-clicking on this hierarchy and clicking New-> Package.

This time I will name the package dao.

Right-click the created dao and press-> New-> Java Class to create a class called TodoEntity.

Edit Entity class

@Entity
@Getter
@Setter
@Table(name="todo")
public class TodoEntity {
    @Id
    @Column
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name="title")
    private String title;
    
    @Column(name="deadline")
    private LocalDate deadline;
    
    @Column(name="status")
    private boolean status;
    
    @CreationTimestamp
    @Column(name="create_time")
    private LocalDateTime createTime;
    
    @UpdateTimestamp
    @Column(name="update_time")
    private LocalDateTime updateTime;
}

Edit the Entity class like this.

You can create a variable corresponding to the table by setting @Column.

@CreationTimestamp and @UpdateTimestamp are annotations that automatically enter the time when creating / updating.

Now that you have an Entity class, you're ready to get and send data from the database.

Creating Repository

Next, create the Repository class. This class is the class that actually interacts with the application and the database. (It is the role of this class to get, edit, and delete everything)

Create TodoRepository class in the dao package created earlier.

@Repository
public interface TodoRepository extends JpaRepository<TodoEntity, Long> {
}

This is all you need to edit. (Import is omitted.)

Extends inherits the JpaRepository class.

The detailed Repository is explained on the here site, so if you are interested, check it out.

Now you are ready to populate the TodoEntity with the data retrieved via the TodoRepository!

Creating a Service class

Now, let's create the Service class.

The role of the Service class in this Todo application is to ask the Repository class to perform ** DB related processing **.

In other words, write the business logic in this class.

Create TodoService class under main / java / com.example.todo.

@Service
@RequiredArgsConstructor
public class TodoService {
    private final TodoRepository todoRepository;
    
    public List<TodoEntity> findAllTodo() {
        return todoRepository.findAll();
    }
}

I feel like this. (Import is omitted.)

@RequiredArgsConstructor is Lombok's annotation for the default construct.

It is an annotation that automatically generates a default constructor with arguments for the member declared in final (todoRepository in this case).

Here and here You may want to refer to the site.

Editing Controller

Now that the Service, Entity, and Repository have been created, let's edit the controller.

@Controller
@RequiredArgsConstructor
public class TodoController {

    private final TodoService todoService;
    
    @GetMapping("/top")
    public String top(Model model){
        List<TodoEntity> todoEntityList =  todoService.findAllTodo(); //①
        model.addAttribute("todoList", todoEntityList); //②
        return "top";
    }
}

It is like this.

① Get all DB records from the Service class created earlier and return it to the variable todoEntityList as the return value List .

② Make todoEntityList a variable called todoList and pass it to the front.

Edit top.html

<!DOCTYPE html>
<!--↓①-->
<html lang="ja" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>hello</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
</head>
<body>
<!--↓②-->
<div th:each="todo: ${todoList}" class=" w-75 h-25 my-1 mx-auto pt-5">
    <div class="container">
        <div  class="row">
            <div class="col-5 pl-5">
                <p th:text="${todo.title}" class="mb-1"></p>
                <p class="mb-1">Deadline:<span  th:text="${todo.deadline}"></span></p>
                <p class="mb-1">Creation date:<span  th:text="${todo.createTime}"></span></p>
            </div>
            <div class="col-2 d-flex justify-content-start align-items-center px-0">
                <a class="h-100 w-75 btn btn-info pt-4">
Edit
                </a>
            </div>
        </div>
    </div>
</div>

<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js" integrity="sha384-wfSDF2E50Y2D1uUdj0O3uMBJnjuUD4Ih7YwaYd1iqfktj0Uod8GCExl3Og8ifwB6" crossorigin="anonymous"></script>
</body>
</html>

I'm using BootStrap for easy and clean display. Please note that the writing style is not very beautiful!

① First, declare the use of Thymeleaf here. Now you can use methods like th: ~ ~! As you can see below, there are various things like th: each and th: text, so why not refer to here? ..

② In this each, the list described in the controller is decomposed and used. What is passed as todoList is processed as todo in HTML. After that, you can display it by calling todo.title, deadline, or variables in the list!

↓ Run the app and hopefully it should look like this ...!

A brief summary

What I did this time ...

① Call findAllTodo of TodoService with TodoContoroller

② findAllTodo calls findAll of TodoRepository

③ findAll fetches all records from DB

④ All data goes into TodoEntity

⑤ The data returns to TodoController and is passed to the front desk for display!

That's the flow!

Instead of having one class have a lot of processing, we created various classes to distribute the processing and make it easier to respond when an error occurs!

The continuation is coming again!