[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
- PC: macOS Catalina 10.15.4
- IDE: Eclipse2020-03(4.15.0)
- SQL: PostgreSQL 12.2 --SQL Tools: DBeaver 7.0.3.202004191823
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
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