Until INSERT and SELECT to Postgres with Spring boot and thymeleaf
What i did
spring boot Summary of progress while studying. In Article I wrote last time, I did Hello World with spring boot. After all, I'm working locally. Here's what I did this time.
--I made an input form with thymeleaf --Issued INSERT and SELECT SQL to postgres DB using the input data on the screen --Display the result
I made it a format to write comments for each code and file. It's not very organized, so it seems difficult to look back on it later ...
What was made
code
https://github.com/PG-practice/lyricWall/tree/simple_ui_connect_db
Screen transition diagram
Pseudo-collaboration diagram
What i learned
--How to do DI (Dependency Injection) of spring boot. Singleton.
--Flow until DB connection with spring boot and display of result
--Some annotations
--How to bind Form object and th: object
--th: object and th: text itself can be null, but if you try to access the property, an error will occur, so pack an empty instance in model and return html.
--PRG (POST-REDIRECT-GET), one of the measures to prevent double transmission
--Multiple INSERT by spring jdbc + prepared statement seems to be slow?
Preparation other than code
Create table
I manually generated the table with the following SQL.
CREATE TABLE IF NOT EXISTS lyricTable (
artist VARCHAR(32),
title VARCHAR(32),
words_writer VARCHAR(16),
music_composer VARCHAR(16),
lyric VARCHAR(1024),
url VARCHAR(256),
PRIMARY KEY (artist,title)
);
Settings for postgres connection
Add dependency
Article I wrote last time At the time, I thought it would be nice to have postgresql, but it seems that jdbc is needed, so I added the following.
build.gradle
dependencies {
//abridgement
compile('org.springframework.boot:spring-boot-starter-jdbc')
}
Connection information description
It seems that application.propaties can be yaml, so I made it yaml. The last DB name in the url is required.
application.yaml
spring:
datasource:
driver-class-name: "org.postgresql.Driver"
#The last DB name in the URL is required
url: "jdbc:postgresql://ubuntu:5432/postgres"
username: "postgres"
password: ""
Note for each code
Detailed comments so that you can look back on them later.
Lyric.java
Lyric.java
public class Lyric {
private String artist;
private String title;
private String words_writer;
private String music_composer;
private String lyric;
private String url;
//Setter, getter, no-argument constructor
//(abridgement)
}
--I feel like I got an error without a setter, getter, and no-argument constructor
--I didn't add @ Entity (maybe because I don't DI?)
LyricDaoImpl.java The interface code is omitted. Implementation only.
LyricDaoImpl.java
//Many import statements
@Repository
public class LyricDaoImpl implements LyricDao {
//Template for DI. All you have to do is call it with AutoWired without new.
private final JdbcTemplate jdbcTemplate;
//Instances are referenced in DI when needed in combination with above
@Autowired
public LyricDaoImpl(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
//Get song information of designated singer
@Override
public List<Lyric> getSongs(String artistName) {
//I want to rewrite the preparedStatement because it will be a countermeasure against SQL injection, but this time it is as it is.
String sql = "SELECT * FROM lyricTable WHERE artist='" + artistName + "';";
List<Map<String, Object>> resultList = jdbcTemplate.queryForList(sql);
List<Lyric> list = new ArrayList<Lyric>();
for(Map<String, Object> result:resultList){
Lyric lyric = new Lyric();
lyric.setArtist((String)result.get("artist"));
lyric.setTitle((String)result.get("title"));
lyric.setWords_writer((String)result.get("words_writer"));
lyric.setMusic_composer((String)result.get("music_composer"));
lyric.setLyric((String)result.get("lyric"));
lyric.setUrl((String)result.get("url"));
list.add(lyric);
}
return list;
}
//Multiple INSERTs of song information in the list.
@Override
public void insertSongs(List<Lyric> list) {
String sql = "INSERT INTO lyricTable VALUES (?,?,?,?,?,?)";
//preparedstatement Multiple INSERT. I used it as a template as it is. Now it works.
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter(){
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
Lyric lyric = list.get(i);
ps.setString(1, lyric.getArtist());
ps.setString(2, lyric.getTitle());
ps.setString(3, lyric.getWords_writer());
ps.setString(4, lyric.getMusic_composer());
ps.setString(5, lyric.getLyric());
ps.setString(6, lyric.getUrl());
}
@Override
public int getBatchSize() {
return list.size();
}
});
}
}
―― ʻinsertSongs () allows multiple INSERTs, but only one song comes from the screen. -- batchUpdate` seems to send queries for each batch size in a batch, but it's slow because it only INSERTs one row at a time just by grouping connections. It seems that batch INSERT can be done if set.
LyricServiceImpl.java The interface code is omitted. Implementation only.
LyricServiceImple.java
//Many import statements
@Service
public class LyricServiceImpl implements LyricService {
//Template for DI.
private final LyricDao dao;
@Autowired
public LyricServiceImpl(LyricDao dao) {
//Instance of implementation class is DI
this.dao = dao;
}
@Override
public List<Lyric> getSongs(String artistName) {
// TODO Auto-generated method stub
return dao.getSongs(artistName);
}
@Override
public void insertSongs(List<Lyric> list) {
// TODO Auto-generated method stub
dao.insertSongs(list);
}
}
--The argument of the constructor for DI is interface type. So it will DI the instance of the implementation class without permission. I don't know much yet. For now, keep in mind that singletons don't new good objects (check it out). --Since only the interface name is written, you do not have to rewrite it even if the class name to be implemented is changed. -Is there an error even if the class to be implemented is not created? ?? ?? (unconfirmed)
WebMvcControllerAdvice.java Define methods that are commonly processed before Controller
WebMvcControllerAdvice.java
@ControllerAdvice
public class WebMvcControllerAdvice {
//It seems that the empty string sent from html is treated as null
@InitBinder
public void initBinder(WebDataBinder dataBinder) {
dataBinder.registerCustomEditor(String.class, new StringTrimmerEditor(true));
}
//If a DB error occurs, put an error message and an empty Form object in model and return html
@ExceptionHandler(PSQLException.class)
public String sqlException(PSQLException e, Model model){
model.addAttribute("errorMessage",e.getMessage());
model.addAttribute("searchForm", new SearchForm());
model.addAttribute("insertForm", new InsertForm());
return "form.html";
}
}
--I put an empty Form object in model. I'm trying to access a property in html, so I get an error if there is no object (see below). What are the best practices?
SearchForm.java
The type that will be bound to th: object =" $ {searchForm} " in the form tag that contains the select button. The variable name should match the argument of the method in html and controller.
SearchForm.java
//A little import etc.
public class SearchForm {
@NotNull(message="Please enter the singer name")
private String artistName;
//Setter, getter, constructor
}
InsertForm.java
The type that will be bound to th: object =" $ {searchForm} " in the form tag that contains the insert button. The variable name should match the argument of the method in html and controller.
InsertForm.java
public class InsertForm {
//A little import statement etc.
@NotNull(message="Entering the singer name is required for registration")
private String artistName;
@NotNull(message = "Entering the song title is required for registration")
private String title;
private String wordsWriter;
private String musicComposer;
private String lyric;
private String url;
//Setter, getter, constructor
}
LyricController.java
LyricController.java
//import statement etc.
//Local"http://localhost:8080/lyric"The request that came to is mapped inside this class
@Controller
@RequestMapping("/lyric")
public class LyricController {
//DI without new
private final LyricService lyricService;
@Autowired
public LyricController(LyricService lyricService) {
this.lyricService = lyricService;
}
//For the model key"insertCompleteMessage"If there is, use it as it is, if not add
@GetMapping("/form")
public String form(Model model, @ModelAttribute("insertCompleteMessage") String message){
model.addAttribute("title", "artist name");
//Returns the corresponding file in the template folder
return "form.html";
}
//If you get caught in the validation anotation of the SearchForm class, you will get an error in result
@GetMapping("/select")
public String select(@Validated SearchForm searchForm, BindingResult result, Model model){
if(result.hasErrors()){
model.addAttribute("title", "Error Page");
return "form.html";
}
List<Lyric> list = lyricService.getSongs(searchForm.getArtistName());
if(list.size()!=0){
model.addAttribute("lyricList", list);
}else{
model.addAttribute("noResultMessage", "The song of the corresponding artist is not registered");
}
return "list.html";
}
//Redirects and Flash Scope for PRG(Attribute that disappears after one request)use
@PostMapping("/insert")
public String insert(@Validated InsertForm insertForm, BindingResult result, Model model, RedirectAttributes redirectAttributes){
if(result.hasErrors()){
model.addAttribute("title", "Error Page");
return "form.html";
}
//First of all, there is only one input form. DAO is a list format, so make a list with one element
List<Lyric> list = convertInsertForm(insertForm);
//Write the processing when there is a TDDO error
lyricService.insertSongs(list);
//@GetMapping("/form")Redirect to. Flash scope settings.
redirectAttributes.addFlashAttribute("insertCompleteMessage", "insert complete");
return "redirect:form";
}
//Even if SearchForm is not included in the above argument, it will be included in the model without permission. The method name can be anything.
@ModelAttribute
SearchForm setSearchForm(){
return new SearchForm();
}
@ModelAttribute
InsertForm setInsertForm(){
return new InsertForm();
}
//Convert from Inseart Form to Lyric
public static List<Lyric> convertInsertForm(InsertForm insertForm){
List<Lyric> list = new ArrayList<>();
Lyric lyric = new Lyric();
lyric.setArtist(insertForm.getArtistName());
lyric.setTitle(insertForm.getTitle());
lyric.setWords_writer(insertForm.getWordsWriter());
lyric.setMusic_composer(insertForm.getMusicComposer());
lyric.setLyric(insertForm.getLyric());
lyric.setUrl(insertForm.getUrl());
list.add(lyric);
return list;
}
}
--If the argument BindingResult result is not placed immediately after the Form object, a white label error will occur when accessing. The order of the arguments is relevant.
What is PRG
The last redirect of @PostMapping is called PRG to prevent double transmission.
--Double transmission that can be prevented --After INSERT, resend by updating browser --On the page displayed after INSERT, resend by pressing the INSERT button again. --Double transmission that seems impossible to prevent --Do your best and press the INSERT button twice at high speed
If you set the end to return form.html and return html without redirect,
- The attribute remains in
model→ The object remains bound in the browser - The last request becomes POST → POSTed when the browser is updated It turns out that. To prevent this
- Unbind
model - Forcibly make a GET request and return html It is PRG (POST-REDIRECT-GET) to do.
The property of model is not inherited at the time of redirect (it has been confirmed by the debugger that the Form object in model is initialized at the time of redirect). Therefore, except for the characters ʻinsert complete in redirectAttributes, the response is in the initialized state. In addition, the ʻinsert complete message is in flash scope (which disappears with a single request) and disappears when you refresh your browser.
list.html
The object corresponding to the key given to model can be received on the server side with$ {property name}.
list.html
<!DOCTYPE html>
<html xmlns:th="http://www.thymeLeaf.org">
<head>
<meta charset="UTF-8">
<title th:text="${title}">Insert</title>
</head>
<body>
<h1 th:text="${title}">title</h1>
<p th:text="${noResultMessage}"></p>
<table th:if="${lyricList}">
<tr>
<th>Artist Name</th><th>song title</th><th>lyric</th>
</tr>
<tr th:each="lyric:${lyricList}">
<td th:text="${lyric.artist}"></td>
<td th:text="${lyric.title}"></td>
<td th:text="${lyric.lyric}"></td>
</tr>
</table>
</body>
form.html Main screen. Access at http: // localhost: 8080 / lyrics / form.
form.html
<!DOCTYPE html>
<html xmlns:th="http://www.thymeLeaf.org">
<head>
<meta charset="UTF-8">
<title th:text="${title}">Insert</title>
</head>
<body>
<h1 th:text="${title}">title</h1>
<h1 th:text="${redirectTitle}">title</h1>
<form method="GET" action="#" th:action="@{/lyric/select}" th:object="${searchForm}">
<label for="selArtistNameId">Artist Name:</label>
<input id="selArtistNameId" name="artistName" type="text" th:value="*{artistName}">
<div th:if="${#fields.hasErrors('artistName')}" th:errors="*{artistName}"></div>
<input type="submit" value="search">
</form>
<h1>Song registration</h1>
<form method="POST" action="#" th:action="@{/lyric/insert}" th:object="${insertForm}">
<label for="insArtistNameId">Artist Name:</label>
<input id="insArtistNameId" name="artistName" type="text" th:value="*{artistName}">
<div th:if="${#fields.hasErrors('artistName')}" th:errors="*{artistName}"></div>
<label for="title">Song Title:</label>
<input id="titleId" name="title" type="text" th:value="*{title}">
<div th:if="${#fields.hasErrors('title')}" th:errors="*{title}"></div>
<label for="wordsWriter">Lyrics:</label>
<input id="wordsWriterId" name="wordsWriter" type="text" th:value="*{wordsWriter}">
<label for="musicComposer">Composition:</label>
<input id="musicComposerId" name="musicComposer" type="text" th:value="*{musicComposer}">
<label for="lyric">lyrics:</label>
<input id="lyricId" name="lyric" type="text" th:value="*{lyric}">
<label for="url">Lyrics URL:</label>
<input id="urlId" name="url" type="text" th:value="*{url}">
<input type="submit" value="insert">
</form>
<p th:text="${insertCompleteMessage}"></p>
<p th:if="${errorMessage}" th:text="'Error message:'+${errorMessage}"></p>
</body>
</html>
--For example, the $ {insertForm} object is associated with the value of name = at the time of transmission and the property name of the argument ʻInsertForm of the mapping method in Controller. --If the name and the property name of the Form object are different, they will not be picked up. --If you put SearchForm in the argument, the property name of ʻartistName is the same, so it was also linked at the same time. If you do @ModelAttribute, the value is not linked to the unrelated Form without permission.
--If you do not pack the necessary objects in model and return it, you will get a white label error due to something liketh: value = "$ * {artistName}". This is why the error handler of WebMvcControllerAdvice.java purposely creates an instance and puts it in model.
To find out
――Advantages and behavior of DI
<div th:if="${#fields.hasErrors('title')}" th:errors="*{title}"></div>--What is the jsesessionid that appears in the URL after POST?
reference:
Thanks to the company's inclusion in udemy for Business, GW has been busy, but this article is probably based on the following courses. The contents are completely different, but there are some methods that are borrowed as they are. https://www.udemy.com/course/java_spring_beginner/
Recommended Posts