java.sql.SQLSyntaxErrorException: Unknown column

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

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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().

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating