Spring JPA join fetch with condition not working

Question

I have an Entity that as has 1 @OneToOne (as long as there is an additional condition on the join). So I must use a join column and a condition of the join to return 1 record for this property! MySQL and latest versions on everything else involved (new development project).

    @Data
    @Entity
    @Table(name="Table1")
    public class Entity1
       @Id
       private Long id;
       @Column(name="moduleName")
       private String name;
       @Column(name="moduleLabel")
       private String label;
       @OneToOne(fetch=FetchType.LAZY)
       @JoinColumn(name="moduleLabel", referencedColumnName="rootLabel", updateable=false,insertable=false)
       private Entity2 entity2

       public String getLabel(){
         return entity2?entity2.label:label;
       }

   Table 1
   
    |  id  |  moduleName  |  moduleLabel  |
    |------|--------------|---------------| 
    |  1   |  ANIMAL      |  Animals      |
    |  2   |  LOGIN       |  Login        |

    @Data
    @Entity
    @Table(name="Table2")
    public class Entity2
       @Id
       private Long id;
       @Column
       private String rootLabel;
       @Column
       private String locale;
       @Column
       private String label;

    Table 2
    |  id  |  rootLabel  |   label   |  locale |
    |------|-------------|-----------|---------|
    |  1   |  Animals    |  Животные |  ru_RU  |
    |  2   |  Animals    |  Animaux  |  fr_FR  |
    |  3   |  Animals    |  Tiere    |  de_DE  |

    public interface Entity1Repository extends JpaRepository<Entity1,Long>{
       @Query(value="select distinct e1 from Entity1 e1 "+
                    "left outer join fetch e1.entity2 e2 "+ 
                    "where e1.name = :name and (e2 is null or e2.locale = :locale)"
       Entity1 findEntity1ByName(@Param("moduleName") String name,@Param("locale") String locale);

I have tried JPQL join fetch, which is great as long as there is a record on the right side of the join. If not it fails because in the where statement if you do as example above, then e2 in the where clause gets converted to e1.moduleLabel in the query, which is never null and then fails on the or statement because there is no e2 record on the right side of the join.

I have tried an EntityGraph with an additional condition on the join statement:

    @EntityGraph(attributePaths="entity2")
    @Query(value="select distinct e1 from Entity1 e1"+
                   "left outer join e1.entity2 e2 on e2.locale = :locale "+
                   "where e1.name = :moduleName"

The problem here is that the SQL created ends up having 2 join clauses for Entity 2. the first is a multiple condition join e21_0.rootLabel=e11_0.moudleLable and e21_0.locale=? but it also creates a second join of e22_0 that has only the entity defined join condition and the select statement references the Entity annotated join instead of the JPQL defined join. if you remove the fetch from join fetch, 2 join clauses to entity2 are always created.

So, I have seen both attempts above used in other places and documentation states either should technically work, but I have found issues with both and am now at a loss :-(

My prefered method would be to use a storedProcedure, but this entity has multiple other @OneToMany relationships and I have yet to figure out a way to map those well so doing everything I can to keep this in JPA world.

Spring JPA join fetch with condition not working

0 Answers0