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>

looks alright, but maybe it is getting confused as you have fcId in both the `Warehouse` entity and the `WorkingHours` entity, so I guess that the real mapping column is not fcId. Check you real DB to see what the FK is. — Scary Wombat, Dec 18 '14 at 08:19
but the PK of Warehouse is not the field that is referencing it is it? — Scary Wombat, Dec 18 '14 at 08:48
Yes. the referenced column is not PK but a unique constrained column. — gaganbm, Dec 18 '14 at 08:51
Sorry I am not communicating well. If you look at your Warehouse table, what is the column name that references the WorkingHours ? — Scary Wombat, Dec 18 '14 at 08:53
In the WareHouse table no relation is defined in the DB. But in the WorkingHours table fcId references fcId of WareHouse. — gaganbm, Dec 18 '14 at 09:21

Alan Hay · Accepted Answer · 2014-12-18T09:51:45.383

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

Yes. This solved it. Later I had to annotate jadira time types for date types to avoid serialization errors, and then I had to do JsonIgnoreProperties for the referenced objects to avoid infinite loops. But yes, this solved the original problem I had asked. Thank you. — gaganbm, Dec 18 '14 at 11:34
Ok, good. There is actually no need to make the relationship bidirectional (causing infinite loops). Removing the mapping of WorkingHour > Warehouse, moving the '@JoinColumn' annotation to the '@OneToMany' in Warehouse and removing the mappedBy would fix this issue and everything should still work as expected. — Alan Hay, Dec 18 '14 at 11:38

JPA fetch from multiple tables to single POJO with named query

1 Answers1