Spring Data JPA OneToOne entity not found

Question

I am using Spring-boot 1.2.1, with Spring-Data-JPA and Hibernate. I have a simple OneToOne mapping that saves the entities properly, but when I try and GET the value, it fails because it can't find the child entity.

I have an entity:

@Entity
@Table(name = "OPERATORY")
public class Operatory {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long operatoryId;

    private Long officeId;

    @OneToOne(fetch=FetchType.EAGER,optional=false,cascade = CascadeType.ALL)
    @JoinColumn(name="employeeId", referencedColumnName="employeeId",nullable=false)
    private Employee employee;

..
}

That contains a unidirectional reference to this entity:

@Entity
@Table(name = "EMPLOYEE")
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long employeeId;

    @OneToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name = "officeId", nullable = false)
    private Office office;

    @OneToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name = "username", nullable = false)
    private User user;  <== this works and its the same relationship type
     ...
}

I am using a JPA Repository:

public interface OperatoryRepository  extends CrudRepository<Operatory, Long>, QueryDslPredicateExecutor<Operatory> {

    public List<Operatory> findAll(Predicate predicate);

    public List<Operatory> findByOfficeId(Long office);
}

I have this in the db:

**Operatory**

operatory_id  | office_id | employee_id | name
1             |      1    |       1     | Test One

Employee

employee_id  | office_id |.....
1            |     1     | ...

When I try and find the operatory for that office, associated with an exception:

org.springframework.orm.jpa.JpaObjectRetrievalFailureException: Unable to find Employee with id 1; nested exception is javax.persistence.EntityNotFoundException: Unable to find Employee with id 1

Here is the Hibernate logging I receive:

Hibernate: select operatory0_.operatory_id as operator1_18_, operatory0_.employee_id as employee4_18_, operatory0_.name as name2_18_, operatory0_.office_id as office_i3_18_ from operatory operatory0_ where operatory0_.office_id=?
Hibernate: select employee0_.employee_id as employee1_5_0_, employee0_.address_id as address17_5_0_, employee0_.begin_date as begin_da2_5_0_, employee0_.contact_id as contact_3_5_0_, employee0_.end_date as end_date4_5_0_, employee0_.first_name as first_na5_5_0_, employee0_.full_name as full_nam6_5_0_, employee0_.gender as gender7_5_0_, employee0_.last_name as last_nam8_5_0_, employee0_.license_number as license_9_5_0_, employee0_.masked_ssn as masked_10_5_0_, employee0_.office_id as office_18_5_0_, employee0_.portrait_image_location as portrai11_5_0_, employee0_.prefix_id as prefix_12_5_0_, employee0_.schedule_id as schedul13_5_0_, employee0_.specialty_id as special19_5_0_, employee0_.ssn as ssn14_5_0_, employee0_.status as status15_5_0_, employee0_.suffix_id as suffix_16_5_0_, employee0_.username as usernam20_5_0_, address1_.address_id as address_1_0_1_, address1_.address_line1 as address_2_0_1_, address1_.address_line2 as address_3_0_1_, address1_.address_line3 as address_4_0_1_, address1_.address_type as address_5_0_1_, address1_.begin_date_time as begin_da6_0_1_, address1_.city as city7_0_1_, address1_.end_date_time as end_date8_0_1_, address1_.state as state9_0_1_, address1_.status as status10_0_1_, address1_.zip as zip11_0_1_, office2_.office_id as office_i1_11_2_, office2_.address_id as address11_11_2_, office2_.begin_date as begin_da2_11_2_, office2_.ein as ein3_11_2_, office2_.end_date as end_date4_11_2_, office2_.language_id as language5_11_2_, office2_.name as name6_11_2_, office2_.office_type as office_t7_11_2_, office2_.owner_id as owner_i12_11_2_, office2_.service_package_type as service_8_11_2_, office2_.status as status9_11_2_, office2_.service_package_id as service13_11_2_, office2_.url as url10_11_2_, address3_.address_id as address_1_0_3_, address3_.address_line1 as address_2_0_3_, address3_.address_line2 as address_3_0_3_, address3_.address_line3 as address_4_0_3_, address3_.address_type as address_5_0_3_, address3_.begin_date_time as begin_da6_0_3_, address3_.city as city7_0_3_, address3_.end_date_time as end_date8_0_3_, address3_.state as state9_0_3_, address3_.status as status10_0_3_, address3_.zip as zip11_0_3_, officecont4_.office_id as office_i7_11_4_, officecont4_.office_contact_id as office_c1_13_4_, officecont4_.office_contact_id as office_c1_13_5_, officecont4_.begin_date as begin_da2_13_5_, officecont4_.contact_info as contact_3_13_5_, officecont4_.contact_type as contact_4_13_5_, officecont4_.end_date as end_date5_13_5_, officecont4_.office_id as office_i7_13_5_, officecont4_.status as status6_13_5_, owner5_.owner_id as owner_id1_19_6_, owner5_.begin_date as begin_da2_19_6_, owner5_.end_date as end_date3_19_6_, owner5_.first_name as first_na4_19_6_, owner5_.last_name as last_nam5_19_6_, owner5_.name as name6_19_6_, owner5_.owner_type as owner_ty7_19_6_, owner5_.prefix_id as prefix_i8_19_6_, owner5_.status as status9_19_6_, owner5_.suffix_id as suffix_10_19_6_, specialty6_.specialty_id as specialt1_28_7_, specialty6_.description as descript2_28_7_, specialty6_.title as title3_28_7_, user7_.username as username1_31_8_, user7_.account_non_expired as account_2_31_8_, user7_.account_non_locked as account_3_31_8_, user7_.credentials_non_expired as credenti4_31_8_, user7_.enabled as enabled5_31_8_, user7_.office_id as office_i8_31_8_, user7_.password as password6_31_8_, user7_.authority as authorit7_31_8_, office8_.office_id as office_i1_11_9_, office8_.address_id as address11_11_9_, office8_.begin_date as begin_da2_11_9_, office8_.ein as ein3_11_9_, office8_.end_date as end_date4_11_9_, office8_.language_id as language5_11_9_, office8_.name as name6_11_9_, office8_.office_type as office_t7_11_9_, office8_.owner_id as owner_i12_11_9_, office8_.service_package_type as service_8_11_9_, office8_.status as status9_11_9_, office8_.service_package_id as service13_11_9_, office8_.url as url10_11_9_ from employee employee0_ inner join address address1_ on employee0_.address_id=address1_.address_id inner join office office2_ on employee0_.office_id=office2_.office_id inner join address address3_ on office2_.address_id=address3_.address_id left outer join officecontact officecont4_ on office2_.office_id=officecont4_.office_id left outer join owner owner5_ on office2_.owner_id=owner5_.owner_id inner join specialty specialty6_ on employee0_.specialty_id=specialty6_.specialty_id inner join users user7_ on employee0_.username=user7_.username inner join office office8_ on user7_.office_id=office8_.office_id where employee0_.employee_id=?

changing the @OneToOne to from EAGER to LAZY just postpones the exception. I see the values in the database. But I cannot figure out why the Employee cannot be found from the Operatory.

Answer 1

Your second query fetches the employee by employee_id but there are also a number of inner join of other entities/tables (some of which are not shown in the code you posted), i.e. address joined to employee, office joined to employee, address joined to office, specialty joined to employee, users joined to employee and finally office joined to user. If any of these relationships is missing in your data, the query will not return your employee, even though you see it in the employee table.

This is the relavant part of the second hibernate query you posted slightly reformatted:

inner join address address1_ on employee0_.address_id=address1_.address_id
inner join office office2_ on employee0_.office_id=office2_.office_id
inner join address address3_ on office2_.address_id=address3_.address_id
inner join specialty specialty6_ on employee0_.specialty_id=specialty6_.specialty_id
inner join users user7_ on employee0_.username=user7_.username
inner join office office8_ on user7_.office_id=office8_.office_id