Implement CRUD with Spring Boot + Thymeleaf + MySQL

Last time, I wrote the following article. Run WEB application with Spring Boot + Thymeleaf

This time, with the same configuration, access the Web application with REST type and operate the data in the DB. It will be an application that displays a list of table records on the screen that is displayed first, and then registers data, updates existing data, and deletes existing data from that screen. As for the configuration, in addition to the one in the previous article, MySQL is used for the DB. In this article, the procedure for building MySQL is omitted. In addition, O / R mapper is not used for DB connection, only JDBC is used.

Mac OS X 10.12 Java 1.8.0_92 Spring Tool Suite 3.8.4 Spring Boot 1.5.3 thymeleaf 2.1.5

API list

Create 5 APIs as follows

NO METHOD url motion
1 GET /sample/ See all tables
2 GET /sample/:id id Refer to 1 specified
3 POST /sample/ Record registration
4 PUT /sample/:id 1 record update
5 DELETE /sample/:id Delete 1 record

common part

Build the above API in "SampleController.java" used in the previous article. Create a table called user in MySQL and manipulate data with REST on the table.

DB

CREATE DATABASE mysql CHARACTER SET UTF8;
CREATE TABLE user (
  id int AUTO_INCREMENT,
  name varchar(254)
);
INSERT INTO user (id, name) VALUES (1, "kusakarikai");

Addition / correction file 1.png

** User table definition **

java:com.example.User.java


package com.example;

public class User {
	private int id;
	private String name;

	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
}

ActionForm

java:com.example.UserForm.java


package com.example;

public class UserForm {
	private String name;

	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
}

** OR map definition ** When ResultSet.next () is looped with while, the phenomenon that the first element is not read cannot be solved and it is written directly before the loop.

java:com.example.UserMapper.java


package com.example;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.springframework.jdbc.core.RowMapper;

public class UserMapper implements RowMapper<List<User>> {
	public List<User> mapRow(ResultSet rs, int rowNum)
     throws SQLException {
		List<User> list = new ArrayList<>();
		User tmp_user = new User();
		tmp_user.setId(rs.getInt("id"));
		tmp_user.setName(rs.getString("name"));
		list.add(tmp_user);
		while (rs.next()) {
			User user = new User();
			user.setId(rs.getInt("id"));
			user.setName(rs.getString("name"));
			list.add(user);
		}
		return list;
	}
}

** DB connection settings **

src/main/resources/application.properties


spring.datasource.url=jdbc:mysql://127.0.0.1:3306/DB name
spring.datasource.username=User name
spring.datasource.password=password
spring.datasource.driver-class-name=org.gjt.mm.mysql.Driver

** Dependency settings **

pom.xml


<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

1 GET /sample/ Gets all records in the user table and displays them in a list.

Controller

java:com.example.SampleController.java


@Autowired
private JdbcTemplate jdbcTemplate;

@RequestMapping(path = "/sample", method = RequestMethod.GET)
String index(Model model) {
	List<User> list = jdbcTemplate.queryForObject("select * from user", new UserMapper());
	model.addAttribute("list", list);
	return "sample/index";
}

template

src/main/resources/templates/sample/index.html


<table class="list">
  <tr>
    <th>ID</th>
    <th>name</th>
  </tr>
  <tr th:each="list:${list}">
    <td th:text="${list.id}"></td>
    <td th:text="${list.name}"></td>
  </tr>
</table>

2 GET /sample/:id Gets one record in the user table.

Controller

java:com.example.SampleController.java


@Autowired
private JdbcTemplate jdbcTemplate;

@RequestMapping(path = "/sample/{id}", method = RequestMethod.GET)
String show(Model model, @PathVariable("id") int id) {
    List<User> list = jdbcTemplate.queryForObject("select * from user where id = ? ", new Object[] { id }, new UserMapper());
    model.addAttribute("list", list);
    return "sample/index";
}

template Omitted (same as NO1)

3 POST /sample Register the input in the user table. After registration, redirect to NO1 API to get the list and display it on the screen.

Controller

java:com.example.SampleController.java


@Autowired
private JdbcTemplate jdbcTemplate;

@ModelAttribute
UserForm userForm() {
    return new UserForm();
}

@RequestMapping(path = "/sample", method = RequestMethod.POST)
String create(Model model, @ModelAttribute UserForm userForm) {
    jdbcTemplate.update("INSERT INTO user (name) values (?)", userForm.getName());
    return "redirect:/sample";
}

template

src/main/resources/templates/sample/index.html


<form method="post" name="create" action="/sample" accept-charset="UTF8">
    <input type="text" name="name"/>
    <input type="submit" value="sign up" />
</form>

4 PUT /sample/:id Update existing registration details. After registration, redirect to NO1 API to get the list and display it on the screen.

Controller

java:com.example.SampleController.java


@Autowired
private JdbcTemplate jdbcTemplate;

@ModelAttribute
UserForm userForm() {
    return new UserForm();
}

@RequestMapping(path = "/sample/{id}", method = RequestMethod.PUT)
String update(Model model, @ModelAttribute UserForm userForm, @PathVariable("id") int id) {
    jdbcTemplate.update("UPDATE user SET name = ? where id = ? ", userForm.getName(), id);
    return "redirect:/sample";
}

template

src/main/resources/templates/sample/index.html


<form method="post" action="/sample/:id">
    <input type="hidden" name="_method" value="put">
    <input type="text" name="name">
    <input type="submit">
</form>

5 DELETE /sample/:id Delete the existing registration details. After registration, redirect to NO1 API to get the list and display it on the screen.

Controller

java:com.example.SampleController.java


@Autowired
private JdbcTemplate jdbcTemplate;

@RequestMapping(path = "/sample/{id}", method = RequestMethod.DELETE)
String destory(Model model, @PathVariable("id") int id) {
    jdbcTemplate.update("delete from user where id = ? ", id);
    return "redirect:/sample";
}

template

src/main/resources/templates/sample/index.html


<a href="javascript:void(0);" onclick="destory_func();">Delete</a>
function destory_func() {
    var form = document.createElement("form");
    var hidden = document.createElement("input");
    form.method = "post";
    form.action = "/sample/" + id;
    hidden.type = "hidden";
    hidden.name = "_method"
    hidden.value = "delete";
    form.appendChild(hidden);
    document.body.appendChild(form);
    form.submit();
}

As mentioned above, Controller and template are implemented according to 5 API. I'm proceeding while looking at some books and framework programs, but it's interesting to get an overall picture of the software little by little from the intention of implementation.

In this article, only the parts necessary for operation are extracted, but the source is below, so if you need it, please. github kaikusakari/spring_crud

Recommended Posts

Implement CRUD with Spring Boot + Thymeleaf + MySQL
Implement paging function with Spring Boot + Thymeleaf
Implement GraphQL with Spring Boot
Create CRUD apps with Spring Boot 2 + Thymeleaf + MyBatis
Implement simple CRUD with Go + MySQL + Docker
Run WEB application with Spring Boot + Thymeleaf
Implement a simple Web REST API server with Spring Boot + MySQL
Download with Spring Boot
Try to implement login function with Spring Boot
Create your own Utility with Thymeleaf with Spring Boot
Settings for connecting to MySQL with Spring Boot + Spring JDBC
Hello World with Spring Boot
Get started with Spring boot
Hello World with Spring Boot!
Run LIFF with Spring Boot
SNS login with Spring Boot
Implement a simple Rest API with Spring Security with Spring Boot 2.0
[Java] Thymeleaf Basic (Spring Boot)
File upload with Spring Boot
Spring Boot starting with copy
Spring Boot starting with Docker
Hello World with Spring Boot
Set cookies with Spring Boot
Use Spring JDBC with Spring Boot
Add module with Spring Boot
Getting Started with Spring Boot
Create microservices with Spring Boot
Send email with spring boot
Handle Java 8 date and time API with Thymeleaf with Spring Boot
Implement REST API with Spring Boot and JPA (Application Layer)
Until INSERT and SELECT to Postgres with Spring boot and thymeleaf
Connect to database with spring boot + spring jpa and CRUD operation
Use thymeleaf3 with parent without specifying spring-boot-starter-parent in Spring Boot
Implement REST API with Spring Boot and JPA (domain layer)
Implement a simple Rest API with Spring Security & JWT with Spring Boot 2.0
Use Basic Authentication with Spring Boot
Implement text link with Springboot + Thymeleaf
gRPC on Spring Boot with grpc-spring-boot-starter
Create an app with Spring Boot 2
Hot deploy with Spring Boot development
Database linkage with doma2 (Spring boot)
Until "Hello World" with Spring Boot
Inquiry application creation with Spring Boot
Implement file download with Spring MVC
(Intellij) Hello World with Spring Boot
Create an app with Spring Boot
Implement REST API in Spring Boot
Google Cloud Platform with Spring Boot 2.0.0
Check date correlation with Spring Boot
Implement Spring Boot application in Gradle
I tried GraphQL with Spring Boot
[Java] LINE integration with Spring Boot
Beginning with Spring Boot 0. Use Spring CLI
I tried Flyway with Spring Boot
Authentication / authorization with Spring Security & Thymeleaf
Thymeleaf usage notes in Spring Boot
Message cooperation started with Spring Boot
Spring Boot gradle build with Docker
Processing at application startup with Spring Boot
Hello World with Eclipse + Spring Boot + Maven
Perform transaction confirmation test with Spring Boot