The basics of SpringBoot + MyBatis + MySQL

Overview

I have only used JdbcTemplate and have never used MyBatis, so I tried using SpringBoot + MyBatis + MySQL.

environment

What I tried

This time, the information of the book is registered in the DB, and we will get one and display all of them.

■ Screen at the first access 初期画面.png

■ Search for 1 item (Enter id in the form and press Search) 検索結果.png

■ Display all items (press the list display) 一覧表示.png

Various classes and configuration files to prepare

For some reason, BookDao can be used from the BookService class without implementing an interface. It seems that it is implemented behind the scenes and can be used from the Service class.

Directory structure

└── src
    ├── main
    │   ├── java
    │   │   └── com
    │   │       └── example
    │   │           └── demo
    │   │               ├── ServletInitializer.java
    │   │               ├── SpringTestApplication.java
    │   │               ├── controller
    │   │               │   └── BookController.java
    │   │               ├── dao
    │   │               │   └── BookDao.java
    │   │               ├── entity
    │   │               │   └── Book.java
    │   │               ├── form
    │   │               │   └── BookForm.java
    │   │               └── service
    │   │                   └── BookService.java
    │   └── resources
    │       ├── application.properties
    │       ├── com
    │       │   └── example
    │       │       └── demo
    │       │           └── dao
    │       │               └── BookDao.xml
    │       ├── data.sql
    │       ├── schema.sql
    │       ├── static
    │       │   └── css
    │       │       └── style.css
    │       └── templates
    │           └── index.html
    └── test

Dependencies


dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
	implementation 'org.springframework.boot:spring-boot-starter-web'
	implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.3'
	compileOnly 'org.projectlombok:lombok'
	developmentOnly 'org.springframework.boot:spring-boot-devtools'
	runtimeOnly 'mysql:mysql-connector-java'
	annotationProcessor 'org.projectlombok:lombok'
	providedRuntime 'org.springframework.boot:spring-boot-starter-tomcat'
	testImplementation('org.springframework.boot:spring-boot-starter-test') {
		exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
	}

DB definition

First, create a database with MySQL. In this example, library.


CREATE DATABASE library;

If you prepare the following file, test data will be prepared every time Spring Boot is started.

schema.sql


--Delete if there is a booktable
DROP TABLE IF EXISTS booktable;

--If there is no booktable, create a new one
CREATE TABLE IF NOT EXISTS booktable(
id INT AUTO_INCREMENT,
book_name VARCHAR(50) NOT NULL,
volume_num INT NOT NULL,
author_name VARCHAR(50) NOT NULL,
published_date DATE NOT NULL,
PRIMARY KEY(id)
);

data.sql


--Book list initial data
--The id column is auto-incremented so it is unnecessary
INSERT INTO booktable
(book_name, volume_num,author_name,published_date)
VALUES
( 'HUNTER X HUNTER',36,'Yoshihiro Togashi','2018-10-04'),
( 'Berserk',40,'Kentaro Miura','2018-09-28'),
( 'Drifters',6,'Kouta Hirano','2018-11-30'),
( 'Rashomon',1,'Ryunosuke Akutagawa','1915-11-01')
;

Configuration file (application.properties)

It will not work unless you add ? ServerTimezone = JST to the connection destination. Also, with mybatis.configuration.map-underscore-to-camel-case = true, even if the DB column name is snake case, Java will recognize it as camel case.

###Database connection settings
spring.datasource.url=jdbc:mysql://localhost:3306/library?serverTimezone=JST
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
###The DB column name of Snake case is associated as camel case on Entity side of Spring.
mybatis.configuration.map-underscore-to-camel-case=true
###Specify whether to initialize.
spring.datasource.initialization-mode=always

Various classes

Form class

This time, only the id is received from the screen, so there is only one field. Match the name attribute in the input tag on the HTML side with the field name. With the lombok function, there is no need to add a setter getter with @Data. It seems that the field type should be the reference type (wrapper class) ʻInteger instead of the primitive type ʻint. Because it can be distinguished by zero and null.

BookForm.java


package com.example.demo.form;
import lombok.Data;

@Data
public class BookForm {
	private Integer id;
}

Entity class

An object that temporarily stores the data acquired from the database. As I wrote in the Form class, the type should be a reference type.

Book.java


package com.example.demo.entity;
import java.time.LocalDate;
import lombok.Data;

@Data
public class Book {
	private Integer id;
	private String bookName;
	private Integer volumeNum;
	private String authorName;
	private LocalDate publishedDate;
}

Dao interface and mapping file

In the case of MyBatis, it becomes Repository class by creating an interface and adding @Mapper annotation. In the one-case search method, it is possible to flexibly refer to each field of Entity class in the mapping file by using Entity class (Book class) rather than simply passing only the numerical value.

BookDao


package com.example.demo.dao;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import com.example.demo.entity.Book;

@Mapper
public interface BookDao {
	//1 search
	Book findById(Book book);

	//Get all
	List<Book> findAll();
}

[About XML files]

BookDao.xml


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.dao.BookDao">
    <select id="findById" resultType="com.example.demo.entity.Book">
        SELECT
        	id,
        	book_name,
        	volume_num,
        	author_name,
        	published_date
         FROM
         	booktable
         WHERE
         	id = #{id}
    </select>
    <select id="findAll" resultType="com.example.demo.entity.Book">
        SELECT
        	id,
        	book_name,
        	volume_num,
        	author_name,
        	published_date
         FROM
         	booktable
    </select>
</mapper>

Service class

As I wrote in Dao class, the one search method sets the value in the id field of Entity class. Get all records returns a list with Entity class as an element.

package com.example.demo.service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.example.demo.dao.BookDao;
import com.example.demo.entity.Book;

@Service
public class BookService {

	@Autowired
	BookDao bookDao;

	//1 search
	public Book findById(Integer id) {
		Book book = new Book();
		book.setId(id);
		return this.bookDao.findById(book);
	}

	//Get all
	public List<Book> getBookList(){
		return this.bookDao.findAll();
	}
}

Controller class

In the following, @GetMapping and @PostMapping are not used properly.

BookController.java


package com.example.demo.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import com.example.demo.entity.Book;
import com.example.demo.form.BookForm;
import com.example.demo.service.BookService;

@Controller
@RequestMapping("/book")
public class BookController {

	@Autowired
	BookService bookService;

	@RequestMapping("/search")
	public String index(BookForm bookForm, String showList, Model model) {

		//title
		model.addAttribute("title", "Bookstore");

		//Search 1 if bookform (form class) is not null
		if(bookForm.getId() != null) {
			Book book = bookService.findById(bookForm.getId());
			model.addAttribute("book", book);
		}

		//When the list display button is pressed, this list is registered in the model.
		if(showList != null) {
			List<Book> bookList = bookService.getBookList();
			model.addAttribute("bookList", bookList);
		}

		return "index";

	}

}

screen

The displayed content changes depending on whether the object registered in the Controller Model with th: if is null.

index.html


<!DOCTYPE HTML>
<html xmlns:th="http://www.thymeleaf.org">
  <head>
    <title th:text="${title}">title</title>
    <link href="/css/style.css" rel="stylesheet">
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  </head>
  <body>
  	<p>Bookstore</p>
    <form action="/book/search" method="post">
		<label>ID:<input class="input" type="text" name="id"></label><br>
		<div><input class="search" type="submit" value="Search"/></div>
    </form>
    <form action="/book/search" method="post">
		<div><input class="list" type="submit" name="showList" value="List display"/></div>
    </form>

    <div th:if="${book} !=null" th:object="${book}">
    	<table>
    		<tr>
    			<th>ID</th>
    			<th>Book title</th>
    			<th>roll</th>
    			<th>Author name</th>
    			<th>Publication date</th>
    		</tr>
    		<tr>
    			<td th:text="*{id}">id</td>
				<td th:text="*{bookName}">Book title</td>
				<td th:text="*{volumeNum}">roll</td>
				<td th:text="*{authorName}">Author name</td>
				<td th:text="*{publishedDate}">Publication date</td>
    		</tr>
    	</table>
    </div>

        <div th:if="${bookList} !=null">
    	<table>
    		<tr>
    			<th>ID</th>
    			<th>Book title</th>
    			<th>roll</th>
    			<th>Author name</th>
    			<th>Publication date</th>
    		</tr>
    		<tr th:each="book:${bookList}" th:object="${book}">
    			<td th:text="*{id}">id</td>
				<td th:text="*{bookName}">Book title</td>
				<td th:text="*{volumeNum}">roll</td>
				<td th:text="*{authorName}">Author name</td>
				<td th:text="*{publishedDate}">Publication date</td>
    		</tr>
    	</table>
    </div>
  </body>
</html>

I'm not sure about CSS. I've found that tags can be distinguished by [type = ""], and child elements can be specified by separating them from table th and spaces. .. ..

style.css


@charset "UTF-8";

input[type="text"]{
	width:70%;
	border-radius: 5px;
  	padding: 10px;
}
input[type="submit"].search{
	border-radius: 5px;
  	padding: 5px;
  	margin-top: 10px;
  	background-color:#99CCCC;
}

input[type="submit"].list{
	border-radius: 5px;
  	padding: 5px;
  	margin-top: 10px;
  	background-color: #008BBB;
  	color:#FFFFFF
}

table{
    width: 100%;
    margin-top: 10px;
    border-collapse: collapse;
}
table th, table td {
    border: 1px solid #ddd;
    padding: 6px;
}
table th {
  background-color: #6699FF;
}

Recommended Posts

The basics of SpringBoot + MyBatis + MySQL
Docker monitoring-explaining the basics of basics-
The basics of Swift's TableView
About the basics of Android development
[Challenge CircleCI from 0] Learn the basics of CircleCI
Understand the basics of Android Audio Record
[day: 5] I summarized the basics of Java
Setting example of IntellijIDEA + SpringBoot + Gradle + MyBatis
Looking back on the basics of Java
Basics of Ruby
What is JSP? ~ Let's know the basics of JSP !! ~
[Ruby] Summary of class definitions. Master the basics.
I understood the very basics of character input
The basics of the App Store "automatic renewal subscription"
Overwrite the contents of config with Spring-boot + JUnit5
The world of clara-rules (2)
[For beginners] DI ~ The basics of DI and DI in Spring ~
[For beginners] Quickly understand the basics of Java 8 Lambda
Judgment of the calendar
The world of clara-rules (4)
The world of clara-rules (1)
The world of clara-rules (3)
Basics of try-with-resources statement
The world of clara-rules (5)
I summarized the types and basics of Java exceptions
The idea of quicksort
[Ruby] Class nesting, inheritance, and the basics of self
The idea of jQuery
I tried to summarize the basics of kotlin and java
A story packed with the basics of Spring Boot (solved)
How to change the setting value of Springboot Hikari CP
[Summary of technical books] Summary of reading "Learn Docker from the basics"
When you want to change the MySQL password of docker-compose
About the handling of Null
[GCD] Basics of DispatchQueue class
Change the port with SpringBoot
The play of instantiating java.lang.Void
Explanation of the FizzBuzz problem
Summary of Java language basics
Median of the three values
The illusion of object orientation
SpringBoot + Mybatis error when booting
Switch the version of bundler
The basics of the process of making a call with an Android app
Try Hello World with the minimum configuration of Heroku Java spring-boot
[Rails / Heroku / MySQL] How to reset the DB of Rails application on Heroku
How to get the id of PRIMAY KEY auto_incremented in MyBatis