JPA fetch from multiple tables to single POJO with named query

Question

I have been trying to fetch data from 2 different tables to a single entity in JPA but with no result.

The entity that keeps data from two different tables is as below :

@Data @Entity @JsonSnakeCase

public class WareHouse {

  @Id
  @GeneratedValue
  private long id;

  @Column(unique = true)
  private String fcId;

  @Enumerated(EnumType.STRING)
  private FCStatus status;

  @OneToMany(cascade = CascadeType.PERSIST, fetch = FetchType.EAGER, mappedBy = "fcId")
  private List<WorkingHours> workingHours;

  @Enumerated(EnumType.STRING)
  private IntegrationType integrationType;
}

The other entity WorkingHours is :

@Data
@Entity
@JsonSnakeCase
public class WorkingHours {
    @Id
    @GeneratedValue
    private long id;

    private String fcId;
    private LocalDate date;
    private DateTime start;
    private DateTime end;
}

The tables WareHouse and WorkingHours have one-to-many relationship and fcId is the column that joins them.

In my use case, I have to fetch WareHouse details and its WorkingHours in a single entity WareHouse as defined above. How do I achieve this ?

The named query (below) only fetches the WareHouse data and WorkingHours is coming empty. Is the data model wrong ? Or is the query wrong ? (I thought JPA would take care of automatically fetching from the related table when given the annotations OneToMany and FetchType etc.)

<named-query name="searchByFcId">
        <query>
            <![CDATA[
            select f from WareHouse f where f.fcId = :fc_id
            ]]>
        </query>
    </named-query>

Answer 1

You can try the following mappings. The JPA 2.0 spec note (11.1.21) notes:

If the referencedColumnName element is missing, the foreign key is assumed to 
refer to the primary key of the referenced table.

However it also goes on to note that:

Support for referenced columns that are not primary key columns of the 
referenced table is optional. Applications that use such mappings 
will not be portable.

So whether or not this works will depend on your provider.

Warehouse:

@Data 
@Entity 
@JsonSnakeCase
public class WareHouse {

  @Id
  @GeneratedValue
  private long id;

  @Column(unique = true)
  private String fcId;

  @Enumerated(EnumType.STRING)
  private FCStatus status;

  @OneToMany(mappedBy = "warehouse", cascade = CascadeType.PERSIST, fetch = FetchType.EAGER)
  private List<WorkingHours> workingHours;

  @Enumerated(EnumType.STRING)
  private IntegrationType integrationType;
}

WorkingHours:

@Data
@Entity
@JsonSnakeCase
public class WorkingHours {

    @Id
    @GeneratedValue
    private long id;

    @ManyToOne
    @JoinColumn(name = "fcId", referencedColumnName="fcid")
    private Warehouse warehouse;

    private LocalDate date;
    private DateTime start;
    private DateTime end;
}