JPA join multiple table one to many and one to one

Question

I have this 4 tables on Oracle DB

FACTORY_MASTER

FACTORY_DETAILS

LOT_MASTER

LOT_DETAILS

Relations

FACTORY_MASTER 1 - N FACTORY_DETAILS

LOT_MASTER 1 - N LOT_DETAILS

FACTORY_DETAILS 1 - 1 LOT_DETAILS

This is the current implementation of Model class

public class FactoryMaster {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "factory_master_gen")
    @SequenceGenerator(name = "factory_master_gen", sequenceName = "seq_factory_master")
    @Column(name = "FACTORY_MASTER_ID")
    private Long id;

    @OneToMany(cascade = CascadeType.ALL,
               orphanRemoval = true,
               fetch = FetchType.LAZY)
    @JoinColumn(name = "FACTORY_MASTER_ID",
                referencedColumnName = "FACTORY_MASTER_ID",
                nullable = false)
    private List<FactoryDetail> details;

}

public class FactoryDetail {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "factory_detail_gen")
    @SequenceGenerator(name = "factory_detail_gen", sequenceName = "seq_factory_detail")
    @Column(name = "FACTORY_DETAIL_ID")
    private Long id;

    // I exclude on purpose ManyToOne, because you cannot have FactoryDetail without a Master

    @OneToOne(mappedBy = "factoryDetail")
    private LotDetail lotDetail;

}

public class LotMaster {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "lot_master_gen")
    @SequenceGenerator(name = "lot_master_gen", sequenceName = "seq_lot_master")
    @Column(name = "LOT_MASTER_ID")
    private Long id;

    @OneToMany(cascade = CascadeType.ALL,
               orphanRemoval = true,
               fetch = FetchType.LAZY)
    @JoinColumn(name = "LOT_MASTER_ID",
                referencedColumnName = "LOT_MASTER_ID",
                nullable = false)
    private List<LotDetail> details;

}

public class LotDetail {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "lot_detail_gen")
    @SequenceGenerator(name = "lot_detail_gen", sequenceName = "seq_lot_detail")
    @Column(name = "LOT_DETAIL_ID")
    private Long id;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "FACTORY_DETAIL_ID",
                referencedColumnName = "FACTORY_DETAIL_ID")
    private FactoryDetail factoryDetail;
}

I need to extract the factory_master using lot_master_id

So the query would be

select distinct fm.*
  from factory_master fm
       join factory_details fd on fd.factory_master_id = fm.factory_master_id
       join lot_details ld on fd.lot_details_id = ld.lot_details_id
       join lot_master lt on ld.lot_master_id = lt.lot_master_id 
 where lt.lot_master_id = :ID

How can get the same result via JPQL valid?

Thank you

Answer 1

May be using a cross join like this would help:

select distinct fm 
from

FactoryMaster fm 
    inner join fm.details fd 
    inner join fd.lotDetail ld1

,LotMaster lm 
    inner join lm.details ld2 

where 
    ld2.id = ld1.id
    and
    lm.id = :lotMasterId