java.sql.SQLSyntaxErrorException: Unknown column

Question

I am studying Spring Boot. I want to refer to the MySQL data and display the MySQL data in Thymeleaf. But, I get the following error:

java.sql.SQLSyntaxErrorException: Unknown column 'employee0_.department_department_id' in 'field list'.

Also, I want to set to refer to Department class from Employee class. (@ManyToOne) But I'm not sure if the current Entity class and MySQL settings are correct.

EmployeeController

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:/";
    }
}

Department

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;

    }
}

Employee

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;
}

DepartmentRepository

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> {

}

EmployeeRepository

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> {

}

index.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>

application.properties

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

In your employee class, I think you need to add @JoinColumn(name = "department_id") and you should add the department_id as a foreign key in the foreign key tab. — 0x44656E6E79
– 0x44656E6E79, Commented Oct 12, 2021 at 9:40

Zukaru · Accepted Answer · 2021-10-12 22:18:05Z

2

You might want to try add annotation @JoinColumn(name = "department_id") under your department data in your Employee class. Or more detail like,

@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;
}

Also, I suggest to not use the column department_name on your employee class (also in database). You can get the attribute using getter after fetching the data. e.g. employee.getDepartment().getDepartment_name().

edited Oct 12, 2021 at 22:18

answered Oct 12, 2021 at 10:47

Zukaru

3302 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

q051 Over a year ago

This worked correctly. Thank you! But, getting the Name of the Department did not work. When <td th: text = "$ {employee.department}"> or <td th: text = "$ {employee.getDepartment ()}">, the name of the department is "com.example.demo.model.Department". @ 5d9db059 "is displayed. An error occurs when the code is <td th: text = "$ {employee.getDepartment (). GetDepartment_Name ()}">. "org.thymeleaf.exceptions.TemplateInputException" Do I need to add code in the Employee class?

0x44656E6E79 Over a year ago

Can you try <td th: text = "${employee.getDepartment().getDepartment_name()}">

Collectives™ on Stack Overflow

java.sql.SQLSyntaxErrorException: Unknown column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related