JPA - save method looking for Join Table instead of updating record

Question

I have a RESTful endpoint that, depending on whether a record already exists or not, will either insert a new record or update the existing record in the DB using JPA's .save() method.

The problem I'm having is that when attempting to update an existing record, JPA will attempt to access a non-existent table, employee_department. I guess the reason for this is because on my Employee entity I have a @ManyToOne relation to the Department entity.

This problem only occurs when I try to map all of my Employee model data, the data being sent from the POST request, over to the Employee entity. I'll get the error that there isn't a table in the database named employee_department. If instead, I make a findByEmail(employee.getEmail()), a method created on the EmployeeRepository which extends JPA, and attempt to just save that data right back into the database then there is no error.

My question is, what could I possibly be missing from mapping the model -> entity before attempting to save in comparison to just returning a record from the database and saving that?

Here's the data flow from client -> controller -> service -> mapper and then back to the client.

Client JSON in POST request

{
  "id": 109,
"isActive": true,
"manager": null,
"firstName": "string",
"middleInitial": null,
"lastName": "string",
"department": {
  "id": 101
},
"jobTitle": {
  "id": 1001
},
"email": "g",
"skypeName": "g",
"isManager": false
}

Client -> EmployeeController

@RestController
@RequestMapping("/emps")
public class EmployeeController {

    @Autowired
    EmployeeService employeeService;

    @RequestMapping(method = {RequestMethod.POST, RequestMethod.PUT})
    public Employee createOrUpdateEmployee(@RequestBody Employee employee) {
        return employeeService.storeOrUpdate(employee);
    }

EmployeeController -> EmployeeService

@Service
public class EmployeeService {

    @Autowired
    private EmployeeRepository employeeRepository;

    @Autowired
    EmployeeMapper employeeMapper;

    public Employee storeOrUpdate(Employee employee) {
        EmployeeEntity employeeEntity = employeeMapper.modelToEntity(employee);
        EmployeeEntity savedEmployeeEntity = employeeRepository.save(employeeEntity);
        Employee employeeModel = employeeMapper.entityToModel(savedEmployeeEntity);
        return employeeModel;
    }

EmployeeService -> EmployeeMapper

@Component
public class EmployeeMapper implements EntityModelMapper<EmployeeEntity, Employee> {

    @Autowired
    private DepartmentMapper departmentMapper;

    @Autowired
    private JobTitleMapper jobTitleMapper;

    @Override
    public EmployeeEntity modelToEntity(Employee employee) {
        Assert.notNull(employee, "Employee model cannot be null.");

        EmployeeEntity employeeEntity = new EmployeeEntity();
        DepartmentEntity departmentEntity = departmentMapper.modelToEntity(employee.getDepartment());
        JobTitleEntity jobTitleEntity = jobTitleMapper.modelToEntity(employee.getJobTitle());
        Employee employeeManager = employee.getManager();

        if (employeeManager != null) {
            EmployeeEntity employeeManagerEntity = modelToEntity(employeeManager);
            employeeEntity.setManager(employeeManagerEntity);
        }

        employeeEntity.setId(employee.getId());
        employeeEntity.setEmail(employee.getEmail());
        employeeEntity.setFirstName(employee.getFirstName());
        employeeEntity.setMiddleInitial(employee.getMiddleInitial());
        employeeEntity.setLastName(employee.getLastName());
        employeeEntity.setDepartment(departmentEntity);
        employeeEntity.setJobTitle(jobTitleEntity);
        employeeEntity.setIsManager(employee.getIsManager());
        employeeEntity.setSkypeName(employee.getSkypeName());
        employeeEntity.setIsActive(employee.getIsActive());

        return employeeEntity;
    }

Again, I am able to create new records but using this exact same flow to update an existing record is when I get the no table named employee_department error but if I change my storeOrUpdate to something like

    public Employee storeOrUpdate(Employee employee) {
        EmployeeEntity preExistingEmployeeEntity = employeeRepository.findByEmail(employee.getEmail());
        EmployeeEntity savedEmployeeEntity = employeeRepository.save(preExistingEmployeeEntity);
        Employee employeeModel = employeeMapper.entityToModel(savedEmployeeEntity);
        return employeeModel;
    }

where the Employee that is being "updated" is directly from the database then everything works like a charm.

Entity Classes

EmployeeEntity

@Entity
@Table(name = "employee")
public class EmployeeEntity extends BaseEntity {

    @Column(name = "first_name")
    @NotEmpty
    @Size(min = 1)
    private String firstName;

    @Column(name = "middle_initial")
    private Character middleInitial;

    @Column(name = "last_name")
    @NotEmpty
    @Size(min = 1)
    private String lastName;

    @Column(name = "email")
    @NotEmpty
    @Size(min = 1)
    private String email;

    @Column(name = "skype_name")
    @NotEmpty
    @Size(min = 1)
    private String skypeName;

    @ManyToOne
    @JoinColumn(name = "job_title_id")
    private JobTitleEntity jobTitle;

    @ManyToOne
    @JoinColumn(name = "manager_id")
    private EmployeeEntity manager;

    @ManyToOne
    @JoinColumn(name = "department_id")
    private DepartmentEntity department;

    @OneToMany(mappedBy = "manager")
    private Set<EmployeeEntity> ManagedEmployees;

    @OneToMany
    private Set<DepartmentEntity> ManagedDepartments;

    @Column(name = "is_manager")
    @NotNull
    private boolean isManager;

    public String getFirstName() {
        return firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public Character getMiddleInitial() {
        return middleInitial;
    }

    public void setMiddleInitial(Character middleInitial) {
        this.middleInitial = middleInitial;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getSkypeName() {
        return skypeName;
    }

    public void setSkypeName(String skypeName) {
        this.skypeName = skypeName;
    }

    public JobTitleEntity getJobTitle() {
        return jobTitle;
    }

    public void setJobTitle(JobTitleEntity jobTitle) {
        this.jobTitle = jobTitle;
    }

    public EmployeeEntity getManager() {
        return manager;
    }

    public void setManager(EmployeeEntity manager) {
        this.manager = manager;
    }

    public DepartmentEntity getDepartment() {
        return department;
    }

    public void setDepartment(DepartmentEntity department) {
        this.department = department;
    }

    public Set<EmployeeEntity> getManagedEmployees() {
        return ManagedEmployees;
    }

    public void setManagedEmployees(Set<EmployeeEntity> managedEmployees) {
        ManagedEmployees = managedEmployees;
    }

    public Set<DepartmentEntity> getManagedDepartments() {
        return ManagedDepartments;
    }

    public void setManagedDepartments(Set<DepartmentEntity> managedDepartments) {
        ManagedDepartments = managedDepartments;
    }

    public boolean getIsManager() {
        return isManager;
    }

    public void setIsManager(boolean manager) {
        isManager = manager;
    }
}

DepartmentEntity

@Entity
@Table(name = "department")
public class DepartmentEntity extends BaseEntity {

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "manager_id")
    private EmployeeEntity manager;

    @Column(name = "name", nullable = false, length = 50, unique = true)
    @NotNull
    @NotEmpty
    @Size(min = 1, max = 45)
    private String name;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "parent_department_id", referencedColumnName = "ID")
    private DepartmentEntity parentDepartment;

    @OneToMany(mappedBy = "parentDepartment")
    private Set<DepartmentEntity> departments = new HashSet<>(0);

    @OneToMany(mappedBy = "department")
    private Set<EmployeeEntity> employees = new HashSet<>(0);

    public String getName() {
        return this.name;
    }

    public DepartmentEntity getParentDepartment() {
        return this.parentDepartment;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setParentDepartment(DepartmentEntity departmentEntity) {
        this.parentDepartment = departmentEntity;
    }

    public EmployeeEntity getManager() {
        return manager;
    }

    public void setManager(EmployeeEntity manager) {
        this.manager = manager;
    }

    public Set<DepartmentEntity> getChildDepartments() {
        return departments;
    }

    public void setChildDepartments(Set<DepartmentEntity> departments) {
        this.departments = departments;
    }

    public Set<EmployeeEntity> getDepartmentEmployees() {
        return employees;
    }

    public void setDepartmentEmployees(Set<EmployeeEntity> employees) {
        this.employees = employees;
    }

}

JobTitleEntity

@Entity
@Table(name = "job_title")
public class JobTitleEntity extends BaseEntity {

    @Column(name = "name", unique = true)
    @NotEmpty
    private String name;

    @OneToMany
    private Set<EmployeeEntity> titleEmployees;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Set<EmployeeEntity> getTitleEmployees() {
        return titleEmployees;
    }

    public void setTitleEmployees(Set<EmployeeEntity> titleEmployees) {
        this.titleEmployees = titleEmployees;
    }

}

Error Message

Servlet.service() for servlet [dispatcher] in context with path [/api/orgchart] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement] with root cause
java.sql.SQLSyntaxErrorException: Table 'orgchart_api.employee_department' doesn't exist
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:974)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1113)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1381)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1046)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)
        at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45)
        at org.hibernate.persister.collection.AbstractCollectionPersister.remove(AbstractCollectionPersister.java:1203)
        at org.hibernate.action.internal.CollectionRemoveAction.execute(CollectionRemoveAction.java:96)
        at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:560)
        at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:434)
        at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:337)
        at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
        at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1295)
        at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:468)
        at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3135)
        at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2352)
        at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:485)
        at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:147)
        at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$100(JdbcResourceLocalTransactionCoordinatorImpl.java:38)
        at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:231)
        at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:65)
        at org.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:61)
        at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:517)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:765)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:734)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:518)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:292)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
        at com.sun.proxy.$Proxy126.save(Unknown Source)
        at com.orgchart.service.EmployeeService.storeOrUpdate(EmployeeService.java:53)
        at com.orgchart.web.controller.EmployeeController.createOrUpdateEmployee(EmployeeController.java:35)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
        at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:218)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:506)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1087)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:748)

Your question seems to indicate that there is a flaw in how your entities are mapped to the database. The very first thing to look at, then, is the relevant entity classes, yet you have not presented them. I suppose that presenting those will also clarify which are the actual entity classes -- `Employee` and `Department` or `EmployeeEntity` and `DepartmentEntity`. Perhaps you'll even explain why you have both of those sets. — John Bollinger, Jan 24 '19 at 18:55
@JohnBollinger I've added my entity classes, EmployeeEntity, JobTitleEntity, and DepartmentEntity. Hope this helps someone in finding a solution. — Brandon Benefield, Jan 24 '19 at 20:01
Hi - I believe the problem is the same as [here](https://stackoverflow.com/questions/10268456/): "If you need a unidirectional one-to-many association, you have not to put the employee association in the tag class, you have to declare the association in the Employee class..." See also: https://vladmihalcea.com/the-best-way-to-map-a-onetomany-association-with-jpa-and-hibernate/. Also consider setting your log level to TRACE, to see the actual SQL emitted — paulsm4, Jan 24 '19 at 20:18
I think you have it backwards, @paulsm4. It looks like he wants a *bi*directional relationship, but has omitted the needed `mappedBy` attribute. I was in the middle of writing that answer, in fact, when you closed the question. — John Bollinger, Jan 24 '19 at 20:21
@JohnBollinger are you talking about adding a `mappedBy` on the `DepartmentEntity.employees` field or does something else need to have that attribute? — Brandon Benefield, Jan 24 '19 at 20:24
@JohnBollinger: Yeah, I absolutely didn't mean to "close/duplicate" as in "slam the door shut" :( My bad. Please post your response :) — paulsm4, Jan 24 '19 at 20:24
No, @BrandonBenefield, I'm talking about `EmployeeEntity.ManagedDepartments`. It is annotated as a `@OneToMany` relationship field, and it appears that the other end is supposed to be `DepartmentEntity.manager`, making the relationship bidirectional. If it is indeed bidirectional, then the "one" side needs an appropriate `mappedBy` attribute. — John Bollinger, Jan 24 '19 at 20:28
The JPA API does NOT have a `save` method! Suggest you review what API you are actually using ... — , Jan 25 '19 at 08:02

score 1 · Accepted Answer · edited Apr 29 '21 at 11:51

The @OneToMany annotation on EmployeeEntity.ManagedDepartments does not specify a mappedBy attribute, which is required on the "one" side for bidirectional relationships. It appears that this relationship is indeed intended to be bidirectional, so that ought to be

@OneToMany(mappedBy = "manager")
private Set<DepartmentEntity> ManagedDepartments;

If the relationship were actually unidirectional then it would be mapped to an auxiliary table, exactly such as your JPA provider seems to be looking for in the error case. It is furthermore plausible that whether it actually looks for that table or not in practice depends on details of specific entity objects and of how you manipulate them.