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 :
- Adding name="input_id" in the joinColumns = {@JoinColumn(... but instead of replacing the FK, it replaces the PK of input => failure during execution
- replacing the referencedColumnName by "input_id" in the joinColumns = {@JoinColumn(... but it failed at launching.
- 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