[Spring sample code included] How to create a form and how to get multiple records

How to make a form and how to get multiple records

It is explained in order according to the basic class.

The code is published on GitHub, so if you are interested, please clone it. Click here for GitHub repository

This environment

All tools are free to use. If you can work with SQL in the terminal, you don't need SQL tools.

Database

We have prepared sql files for database creation and search data on GitHub. Please execute according to the environment.

HTML Although it is described as th: value in the HTML tag, it will be explained later.

Create a search form

form.html


<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
    <head>
        <meta charset="UTF-8"></meta>
        <title>Actually search the database</title>
    </head>
    <body>
        <h1>Search form</h1>
        <form method="post" action="/form/db">
Enter Employee ID:<input type="text" name="Id" th:value="${id_value}" /><br>
Enter your name:<input type="text" name="Name" th:value="${name_value}" /><br>
Age input:<input type="text" name="Age" th:value="${age_value}" /><br>
            <input type="submit" value="Search" />
        </form>
    </body>
</html>

Create a page to display the results

responseDB.html


<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
    <head>
        <meta charset="UTF-8"></meta>
        <title>Try searching the database</title>
    </head>
    <body>
        <h1>Display search results</h1>
        <table>
            <tr th:each="employee : ${employees}">
                <td th:text="${employee.employeeId}"></td>
    	        <td th:text="${employee.employeeName}"></td>
    	        <td th:text="${employee.empoyeeAge}"></td>
            </tr>
        </table>
    </body>
</html>

Java The following four classes are required to create a web service.

--Repository class --Domain class --Service class --Controller class

spring基本.jpg

Annotation article coming soon

Repository class

FormRepository.java


package com.example.demo;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class FormRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public List<Map<String, Object>> findData(Integer id, String name, Integer age) {

        String query = "SELECT"
            + " employee_id,"
            + " employee_name,"
            + " age"
            + " FROM employee"
            + " WHERE employee_id=?"
            + " or employee_name LIKE ?"
            + " or age=?";

        //Search execution
        // queryForList =>Since there are multiple search results, get them in List
        List<Map<String, Object>> employees = jdbcTemplate.queryForList(query, new Object[] {id, name, age});
        return employees;
    }
}

This is the answer to the main question. Use new Object.

jdbcTemplate.queryForList(query, new Object[] {id, name, age});

Domain class

Employee.java


package com.example.demo;

import lombok.Data;

@Data
public class Employee {

    private int employeeId;
    private String employeeName;
    private int employeeAge;
}

If you use the @Data annotation, getter and setter will be created automatically, so it will be easier to modify. Article about @Data coming soon

Service class

FormService.java


package com.example.demo;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class FormService {
    
    @Autowired
    private FormRepository formRepository;

    //Since I got the inspection result as List type in the repository class to get multiple search results
    //Domain class where the data is stored[Employee]As a List type
    public List<Employee> findData(Integer id, String name, Integer age) {

        //Of the repository class[findData]Puts Map in List, so get it with the same type
        List<Map<String, Object>> list = formRepository.findData(id, name, age);
    
        //Generate a List of return values of findData
        List<Employee> employees = new ArrayList<Employee>();

        //Of the repository class[findData]Search result list of
        //One by one in the domain class[Employee]Store in
        for(Map<String, Object> map: list) {

            int employeeId = (Integer)map.get("employee_id");
            String employeeName = (String)map.get("employee_name");
            int employeeAge = (Integer)map.get("age");

            //Create an Employee instance
            Employee employee = new Employee();
        
            //Store the value in the automatically generated setter of the domain class
            employee.setEmployeeId(employeeId);
            employee.setEmployeeName(employeeName);
            employee.setEmployeeAge(employeeAge);

            employees.add(employee);
        }
 
        return employees;
    }
}

By default, the names of getter and setter are capitalized variable names. It will be the one with get and set.

employee.setEmployeeId(employeeId);

Controller class

FormController.java


package com.example.demo;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;

@Controller
public class FormController {

    @Autowired
    private FormService formService;

    @GetMapping("/form")
    public String getHello() {
        // hello.Screen transition to html
        return "form";
    }
    
    //Search form action destination
    @PostMapping("/form/db")
    public String postDbReqest(
        @RequestParam("Id")String Id,
        @RequestParam("Name")String Name,
        @RequestParam("Age")String Age, Model model) {

        Integer id = (Id == "") ? null:Integer.parseInt(Id);
        String name = (Name == "") ? "":Name;
        Integer age = (Age == "") ? null:Integer.parseInt(Age);

        //Service class[findData]Is a domain class in the List[Employee]Because it is stored
        //List when receiving<Employee>
        List<Employee> employees = formService.findData(id, name, age);

        //Inside employees
        // {"employeeId":1, "employeeName":"Yamada Taro", "employeeAge":30}
        model.addAttribute("employees", employees);
            
        return "responseDB";
    }
}

Description of html

Since the ʻemployees registered in the model contains multiple ʻEmployee classes

<tr th:each="employee : ${employees}">  

Extracted one by one from ʻemployees and assigned to ʻemployee

ʻEmployee class` is

private int employeeId;  
private String employeeName;  
private int employeeAge;  

Since the value was stored using setter in the repository class, it can be retrieved as follows.

<td th:text="${employee.employeeId}"></td>  
<td th:text="${employee.employeeName}"></td>  
<td th:text="${employee.empoyeeAge}"></td>

Search value setting failure example

There is only one search result in the book, in order to acquire multiple data I know I use queryForList, but even if I write a sql statement with a placeholder There was a problem that the search value could not be bound.

Repository class

Success story

//Search execution
// queryForList =>Since there are multiple search results, get them in List
//Search value is passed as Object
List<Map<String, Object>> employees = jdbcTemplate.queryForList(query, new Object[] {id, name, age});

Failure example

//sql statement
String query = "SELECT"  
  + " employee_id,"  
  + " employee_name,"  
  + " age"  
  + " FROM employee"  
  + " WHERE employee_id=:id"  
  + " or employee_name LIKE :name"  
  + " or age=:age";

//Use Map
Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put("id", id);
parameters.put("name", name);
parameters.put("age", age);

List<Map<String, Object>> employees = jdbcTemplate.queryForList(query, parameters);

//Use SqlParameterSource
SqlParameterSource parameters = new MapSqlParameterSource("id", id)
                                    .addValue("name", name)
                                    .addValue("name", age);

List<Map<String, Object>> employees = jdbcTemplate.queryForList(query, parameters);

Task

I really wanted to make an ambiguous search for the name in the sql statement of the repository class. I couldn't because I couldn't use wildcards. There may be a way to describe it, but I don't know yet, so I'll make it possible in future learning.

String query = "SELECT"  
  + " employee_id,"  
  + " employee_name,"  
  + " age"  
  + " FROM employee"  
  + " WHERE employee_id=?"  
  + " or employee_name LIKE %?%" //Can't do this
  + " or age=?";

Recommended Posts

[Spring sample code included] How to create a form and how to get multiple records
How to create a server executable JAR and WAR with Spring gradle
How to create an Excel form using a template file with Spring MVC
How to create a Spring Boot project in IntelliJ
[Spring Boot] How to create a project (for beginners)
How to create a method
How to create a form to select a date from the calendar
How to create and launch a Dockerfile for Payara Micro
[Java] How to create a folder
[Rails 6] How to create a dynamic form input screen using cocoon
How to load a Spring upload file and view its contents
[Swift5] How to create a .gitignore file and the code that should be written by default
[Docker] How to create a virtual environment for Rails and Nuxt.js apps
[Swift5] How to create a splash screen
[rails] How to create a partial template
[Rails] How to create a table, add a column, and change the column type
How to create a convenient method that utilizes generics and functional interfaces
How to create your own annotation in Java and get the value
A memo to simply create a form using only HTML and CSS in Rails 6
[Spring Boot] How to get properties dynamically from a string contained in a URL
How to create a database for H2 Database anywhere
[Rails] How to create a graph using lazy_high_charts
How to get a heapdump from a Docker container
How to create pagination for a "kaminari" array
How to create a class that inherits class information
How to create multiple pull-down menus with ActiveHash
How to add a classpath in Spring Boot
How to create a theme in Liferay 7 / DXP
How to create search conditions involving multiple models
[1st] How to create a Spring-MVC framework project
How to easily create a pull-down in Rails
[Rails] How to create a Twitter share button
[Rails] How to get success and error messages
How to define multiple orm.xml in Spring4, JPA2.1
[Ruby On Rails] How to retrieve and display column information of multiple records linked to a specific id at once
I tried to create a shopping site administrator function / screen with Java and Spring
Until you create a Spring Boot project in Intellij and push it to Github
How to create a header or footer once and use it on another page
Create a private key / public key in CentOS8.2 and connect to SSH with VS Code
Create a portfolio app using Java and Spring Boot
How to create a Java environment in just 3 seconds
[Rails] How to create a signed URL for CloudFront
How to write a unit test for Spring Boot 2
Reasons to include ActiveModel :: Model to create a Form object
How to create a JDBC URL (Oracle Database, Thin)
How to create a data URI (base64) in Java
[Java] How to get a request by HTTP communication
How to display a browser preview in VS Code
[Introduction to Spring Boot] Submit a form using thymeleaf
How to convert A to a and a to A using AND and OR in Java
How to check CircleCI code and automatically deploy to Heroku
How to create and execute method, Proc, Method class objects
[Ruby] How to get the tens place and the ones place
(Ruby on Rails6) How to create models and tables
How to get started with creating a Rails app
[Kotlin] How to get IP address and user agent
[Apple Subscription Offer] How to create a promotional offer signature
How to create docker-compose
How to specify character code and line feed code in JAXB
Java conditional branching: How to create and study switch statements
How to create a lightweight container image for Java apps