Overview

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

environment

macOS Catalina 10.15.7
Java11
SpringBoot 2.3.4
MySQL 8.0
Gradle

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.

ID search from database and display
Get all records and display a list

■ 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

build.gradle (Add MyBatis to this dependency)
DB related: data.sql, schema.sql
BookForm class (receives value from input form)
View screen (using Thymeleaf)
Book class (Entity class that receives the value from DB)
BookDao interface (interface for making queries to DB)
BookDao.xml (SQL is described here)
BookService class (call Dao class)
BookController class (returns the view in response to the request from the browser. Calls the service class depending on the situation.)

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

MyBatis adopts the method of writing SQL in the mapping file (XML file) (There is also a method of writing SQL in the annotation). By arranging the mapping file in the same hierarchy as Dao under resorces, it will be automatically read when using Dao.

└── 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]

Make the file name the same as the corresponding Dao.
Place it in the same hierarchy as the Dao interface under resorces.
Write the fully qualified class name of the Dao interface in the namespace attribute.
Write a SELECT statement in the select element. In + ʻid attribute`, write the corresponding method name of Dao interface.
Write the class name that maps the search results in the resultType attribute. This time is Book class.
parameterType attribute is omitted this time. It seems to write the type of the argument of the method. If omitted, the actual argument type is automatically determined.
In the findById below, the id field of the Entity class (Book class) is referenced in the WHERE clause.

`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;
}

The basics of SpringBoot + MyBatis + MySQL