java - java.sql.SQLSyntaxErrorException:未知列
问题描述
我正在学习 Spring Boot。我想参考 MySQL 数据并在 Thymeleaf 中显示 MySQL 数据。但是,我收到以下错误:
java.sql.SQLSyntaxErrorException:“字段列表”中的未知列“employee0_.department_department_id”。
另外,我想设置为从 Employee 类引用 Department 类。(@ManyToOne)但我不确定当前的实体类和 MySQL 设置是否正确。
员工控制器
package com.example.demo.controller;
@RequiredArgsConstructor
@Controller
public class EmployeeController {
private final EmployeeRepository emRepository;
private final DepartmentRepository deRepository;
@GetMapping("/")
public String showList(Model model) {
model.addAttribute("employeeList", emRepository.findAll());
return "index";
}
@GetMapping("/add")
public String addEmployee(@ModelAttribute Employee employee, Model model) {
model.addAttribute("departmentList", deRepository.findAll());
return "form";
}
@PostMapping("/save")
public String process(@Validated @ModelAttribute Employee employee, BindingResult result) {
if (result.hasErrors()) {
return "form";
}
emRepository.save(employee);
return "index";
}
@GetMapping("/edit/{id}")
public String editEmployee(@PathVariable Long id, Model model) {
model.addAttribute("employee", emRepository.findById(id));
return "form";
}
@GetMapping("/delete/{id}")
public String deleteEmployee(@PathVariable Long id) {
emRepository.deleteById(id);
return "redirect:/";
}
}
部门
package com.example.demo.model;
@NoArgsConstructor
@Getter
@Setter
@Entity
public class Department {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="department_id")
private Long department_id;
@NotBlank
@Size(max = 40)
@Column(name="department_name")
private String department_name;
public Department(String name) {
this.department_name = name;
}
}
员工
package com.example.demo.model;
@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
@Entity
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@NotBlank
@Size(max = 40)
private String name;
@ManyToOne
private Department department;
}
部门资料库
package com.example.demo.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import com.example.demo.model.Department;
public interface DepartmentRepository extends JpaRepository<Department, Long> {
}
员工资料库
package com.example.demo.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import com.example.demo.model.Employee;
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
}
索引.html
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Employee List</title>
</head>
<body>
<h3>List</h3>
<div th:if="${employeeList.size==0}">
<h3>no data</h3>
</div>
<table th:if="${employeeList.size()>0}">
<tr>
<th>id</th>
<th>name</th>
<th>Department</th>
<th></th>
</tr>
<tr th:each="employee:${employeeList}" th:object="${employee}">
<td th:text="${employee.id}"></td>
<td th:text="${employee.name}"></td>
<td th:text="${employee.department_name}"></td>
<td><form th:action="@{'/delete/'+${employee.id}}" method="post">
<button>delete</button>
</form></td>
<td><form th:action="@{'/edit/'+${employee.id}}" method="post">
<button>edit</button>
</form></td>
</tr>
</table>
<h3>
<a th:href="@{/add}">add</a>
</h3>
</body>
</html>
应用程序属性
spring.datasource.url=jdbc:mysql://localhost:3306/spring_crud
spring.datasource.username=spring_crud
spring.datasource.password=spring_crud
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#spring.jpa.hibernate.ddl-auto=update
解决方案
您可能想尝试@JoinColumn(name = "department_id")
在 Employee 类的部门数据下添加注释。或者更多细节,例如,
@Entity
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@NotBlank
@Size(max = 40)
private String name;
@ManyToOne(targetEntity = Department.class)
@JoinColumn(name = "department_id", referencedColumnName = "department_id")
private Department department;
}
另外,我建议不要department_name
在您的员工类中使用该列(也在数据库中)。您可以在获取数据后使用 getter 获取属性。例如employee.getDepartment().getDepartment_name()
。
推荐阅读
- c++ - 在带有 ReSharper 的 VS 中使用 glm::vec3 时出现“不完整类型”警告
- php - 只返回最高的设备维护id
- reactjs - 我将如何规范这个嵌套的减速器案例?
- react-native - 我在反应原生中构建 APK 时遇到了这个错误
- vuetify.js - 如何解决 NuxtJS 和 Vuetify 2.0 之间的 scss 冲突
- typescript - 如何为 Typescript 使用 socket.IO 客户端类型定义?
- r - 如何在 ggplot 2 主题中正确设置线条大小?
- swiftui - 错误不能在属性初始化程序中使用实例成员“xxx”
- reactjs - 需要帮助构建反应表的逻辑
- python - 循环遍历熊猫数据框并返回多个数据框