java - Many to one relation beetween 3 entities

Question

In my springboot application I have these 3 entities :

@Entity
public class Process {
    @Id
    private Long Id;

    @ManyToOne(fetch = FetchType.EAGER, cascade = {CascadeType.ALL})
    @JoinColumn(name = "input_id")
    private Input input;
    ...
}

@Entity
public class Input{
    @Id
    private Long Id;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "template_id")
    private Template template;
    ...
}

@Entity
public class Template{
    @Id
    private Long Id;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "template_id")
    private Template template;
    
    private String name;    
    ...
}

In summary, Process has an FK to Input and Input has an FK to Template.

I would like to filter the processes whose template have a certain name. Here is the SQL I would to perform something like that:

    select
        *
    from
        process p 
    left outer join
        input i 
            on p.input_id=i.id 
    left outer join
        template t 
            on i.template_id=t.id 
    where
        t.name='templateName'

Here is what I currently have in my Process entity to access the template :

    @ManyToOne
    @JoinTable(name = "Input",
            joinColumns = {@JoinColumn(table = "Input", referencedColumnName = "id")},
            inverseJoinColumns = {
                    @JoinColumn(name = "template_id", referencedColumnName = "id", table = "Template")})
    private Template template;

Here is my ProcessRepository class, where I now have access to the desired find method :

@Repository
public interface ProcessRepository extends PagingAndSortingRepository<Process, Long> {
    ...
    List<Process> findByTemplateNameEquals(String templateName);
    ...
}

When I execute the findByTemplateNameEquals method, I retrieve the process and one template. But the result I got was not the one expected. I enabled the sql logging and here is the query really performed (I hide the columns, it is not important here):

    select
...
    from
        process process0_ 
    left outer join
        input process0_1_ 
            on process0_.id=process0_1_.id 
    left outer join
        template template1_ 
            on process0_1_.template_id=template1_.id 
    where
        template1_.name=?

There is one problem with the join between Process and Input. It executes

from
  process process0_ 
left outer join
  input process0_1_ 
  on process0_.id=process0_1_.id

instead of

from
  process process0_ 
left outer join
  input process0_1_ 
  on process0_.input_id=process0_1_.id

I don't understand why it use the PK of Process instead of the FK to Input. I tried several things to solve this :

1. Adding name="input_id" in the joinColumns = {@JoinColumn(... but instead of replacing the FK, it replaces the PK of input => failure during execution
2. replacing the referencedColumnName by "input_id" in the joinColumns = {@JoinColumn(... but it failed at launching.
3. Configuring a @ForeignKey(name = "input_id") at several places (directly in the @JoinTable, in the @JoinColumn and even in the @JoinColumn of the Input input attribute ) but there was no change.

I also remarked that the joinColumns = {@JoinColumn(table = "Input", referencedColumnName = "id")} was not necessary, because I have the same behaviour if I remove it.

Could someone help me on this ?

Many thanks in advance

Answer 1

I think the declaration of the template field at the Process level is probably unnecessary because you already have the relationship with Input, and certainly error prone.

In any case, if necessary, I would define the field with something like that instead:

@ManyToOne
@JoinTable(
  name = "Input",
  joinColumns = { @JoinColumn(name = "input_id")},
  inverseJoinColumns = {@JoinColumn(name = "template_id")}
)
private Template template;

Pease, verify the code.

Having said that, if you only need to obtain the repositories associated with a certain template by name you can try to navigate through the object hierarchy in your find method. Please, try:

@Repository
public interface ProcessRepository extends PagingAndSortingRepository<Process, Long> {
    ...
    List<Process> findByInput_Template_Name(final String templateName);
    ...
}