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

spring画面遷移.png

Pseudo-collaboration diagram

Spring20200503構成.png

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,

  1. The attribute remains in model → The object remains bound in the browser
  2. The last request becomes POST → POSTed when the browser is updated It turns out that. To prevent this
  3. Unbind model
  4. 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

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

Until INSERT and SELECT to Postgres with Spring boot and thymeleaf
Attempt to SSR Vue.js with Spring Boot and GraalJS
Handle Java 8 date and time API with Thymeleaf with Spring Boot
Until "Hello World" with Spring Boot
Connect to database with spring boot + spring jpa and CRUD operation
Flow until output table data to view with Spring Boot
8 things to insert into DB using Spring Boot and JPA
Implement CRUD with Spring Boot + Thymeleaf + MySQL
Implement paging function with Spring Boot + Thymeleaf
Add spring boot and gradle to eclipse
Run WEB application with Spring Boot + Thymeleaf
How to apply thymeleaf changes to the browser immediately with #Spring Boot + maven
Until data acquisition with Spring Boot + MyBatis + PostgreSQL
How to use MyBatis2 (iBatis) with Spring Boot 1.4 (Spring 4)
How to use built-in h2db with spring boot
Try to implement login function with Spring Boot
Create CRUD apps with Spring Boot 2 + Thymeleaf + MyBatis
Create your own Utility with Thymeleaf with Spring Boot
Try to automate migration with Spring Boot Flyway
[Java] Article to add validation with Spring Boot 2.3.1.
I wanted to gradle spring boot with multi-project
[Introduction to Spring Boot] Authentication function with Spring Security
Spring Boot + Heroku Postgres
Download with Spring Boot
Plans to support JDK 11 for Eclipse and Spring Boot
Settings for connecting to MySQL with Spring Boot + Spring JDBC
Try using DI container with Laravel and Spring Boot
Switch environment with Spring Boot application.properties and @Profile annotation
Automatically map DTOs to entities with Spring Boot API
If you want to separate Spring Boot + Thymeleaf processing
Spring Boot with Spring Security Filter settings and addictive points
[Introduction to Spring Boot] Submit a form using thymeleaf
Create Restapi with Spring Boot ((1) Until Run of App)
Until you start development with Spring Boot in eclipse 1
How to boot by environment with Spring Boot of Maven
Until you start development with Spring Boot in eclipse 2
Connect Spring Boot and Angular type-safely with OpenAPI Generator
Until you create a Spring Boot project in Intellij and push it to Github
Try Spring Boot from 0 to 100.
Generate barcode with Spring Boot
Hello World with Spring Boot
Implement GraphQL with Spring Boot
Get started with Spring boot
Implement REST API with Spring Boot and JPA (Application Layer)
Implement REST API with Spring Boot and JPA (Infrastructure layer)
[Spring] [Thymeleaf] Insert CSRF token
Extract SQL to property file with jdbcTemplate of spring boot
Run LIFF with Spring Boot
SNS login with Spring Boot
Introduction to Spring Boot ① ~ DI ~
File upload with Spring Boot
Spring Boot starting with copy
Introduction to Spring Boot ② ~ AOP ~
How to call and use API in Java (Spring Boot)
Spring Boot starting with Docker
Hello World with Spring Boot
Set cookies with Spring Boot
Use thymeleaf3 with parent without specifying spring-boot-starter-parent in Spring Boot
Use Spring JDBC with Spring Boot
With Spring boot, password is hashed and member registration & Spring security is used to implement login function.
Add module with Spring Boot