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

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

** 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