I have a Spring Data JPA project using Hibernate with entities MainTable, JoinTable1, and JoinTable2. My repository and specifications are set up to fetch joined tables with a left join. Despite this, Hibernate executes follow-up queries for JoinTable2 when a match is not initially found. The current setup allows for the join condition in JoinTable2 to occur as an inner query instead of turning into a WHERE clause at the end of the main query, which would result in rows being wrongly filtered out. I want to prevent these follow-up queries and ensure all necessary data is fetched in a single query using entity classes and JPA specifications, without resorting to using Entity Graphs or JPQL. In a nutshell I’m trying to keep the implementation lightweight so Entity Graphs or JPQL is not an option.
My classes
@Repository
public interface MainTableRepository extends JpaRepository<MainTable, Long>,
JpaSpecificationExecutor<MainTable> {}
public class MainTableSpecification {
public static Specification<MainTable> getMainTableSpecification() {
return mainTable()
.and(joinTable1())
.and(joinTable2());
}
public static Specification<MainTable> mainTable() {
return (root, query, criteriaBuilder) -> null;
}
public static Specification<MainTable> joinTable1() {
return (root, query, criteriaBuilder) -> {
if (!isCountQuery(query)) {
root.fetch("joinTable1", JoinType.LEFT);
}
return null;
};
}
public static Specification<MainTable> joinTable2() {
return (root, query, criteriaBuilder) -> {
if (!isCountQuery(query)) {
root.fetch("joinTable2", JoinType.LEFT);
}
return null;
};
}
public static boolean isCountQuery(CriteriaQuery<?> query) {
return query.getResultType() == Long.class;
}
}
@Entity(name = "main_table")
@Getter
@ToString
public class MainTable {
@Id
@Column(name = "main_table_id")
private Long id;
@OneToOne
@JoinColumn(name = "main_table_id", referencedColumnName = "join_table_1_main_table_id")
private JoinTable1 joinTable1;
@OneToOne
@JoinColumnOrFormula(column =
@JoinColumn(
name = "main_table_id",
referencedColumnName = "join_table_2_main_table_id",
insertable = false,
updatable = false
)
)
@JoinColumnOrFormula(formula =
@JoinFormula(
value = "'T'",
referencedColumnName = "join_table_2_filter_field"
)
)
private JoinTable2 joinTable2;
}
@Entity(name = "join_table_1")
@Getter
@ToString
public class JoinTable1 implements Serializable {
@Id
@Column(name = "join_table_1_main_table_id")
private Long id;
@Column(name = "join_table_1_joined_field")
private String joinTable1JoinedField;
}
@Entity(name = "join_table_2")
@Table(name = "join_table_2")
@Getter
@ToString
public class JoinTable2 implements Serializable {
@Id
@Column(name = "join_table_2_main_table_id")
private Long id;
@Column(name = "join_table_2_filter_field")
private String joinTable2FilterField;
@Column(name = "join_table_2_field")
private String joinTable2Field;
}
This is my test data:
INSERT INTO main_table (main_table_id) VALUES (1);
INSERT INTO main_table (main_table_id) VALUES (2);
INSERT INTO main_table (main_table_id) VALUES (3);
INSERT INTO join_table_1 (join_table_1_main_table_id, join_table_1_joined_field) VALUES (3, 'joinTable1JoinedField');
INSERT INTO join_table_2 (join_table_2_main_table_id, join_table_2_filter_field, join_table_2_field) VALUES (1, 'T', 'NOT FILTERED OUT');
INSERT INTO join_table_2 (join_table_2_main_table_id, join_table_2_filter_field, join_table_2_field) VALUES (2, 'F', 'FILTERED OUT');
These are my Hibernate logs showing generated queries run:
Hibernate:
select
maintable0_.main_table_id as main_tab1_2_0_,
jointable1x1_.join_table_1_main_table_id as join_tab1_0_1_,
jointable2x2_.join_table_2_main_table_id as join_tab1_1_2_,
'T' as formula2_0_,
jointable1x1_.join_table_1_joined_field as join_tab2_0_1_,
jointable2x2_.join_table_2_field as join_tab2_1_2_,
jointable2x2_.join_table_2_filter_field as join_tab3_1_2_
from
main_table maintable0_
left outer join
join_table_1 jointable1x1_
on maintable0_.main_table_id=jointable1x1_.join_table_1_main_table_id
left outer join
join_table_2 jointable2x2_
on maintable0_.main_table_id=jointable2x2_.join_table_2_main_table_id
and 'T'=jointable2x2_.join_table_2_filter_field limit ?
Hibernate:
select
jointable2x0_.join_table_2_main_table_id as join_tab1_1_0_,
jointable2x0_.join_table_2_field as join_tab2_1_0_,
jointable2x0_.join_table_2_filter_field as join_tab3_1_0_
from
join_table_2 jointable2x0_
where
jointable2x0_.join_table_2_main_table_id=?
and jointable2x0_.join_table_2_filter_field=?
Hibernate:
select
jointable2x0_.join_table_2_main_table_id as join_tab1_1_0_,
jointable2x0_.join_table_2_field as join_tab2_1_0_,
jointable2x0_.join_table_2_filter_field as join_tab3_1_0_
from
join_table_2 jointable2x0_
where
jointable2x0_.join_table_2_main_table_id=?
and jointable2x0_.join_table_2_filter_field=?
Suspected cause:
- Complex join condition: The use of @JoinColumnOrFormula with a @JoinFormula adds complexity, leading Hibernate to handle the join condition in a way that triggers additional queries?
- Follow up queries: Hibernate performs follow-up queries when an initial match is not found for JoinTable2?
What I tried:
- Played around with fetch type options in @OneToOne
- Added join filter into criteria instead. The result of that was the where clause moving out of inner join query to outer level and preventing all expected results to be returned instead of the join just having an empty match.
Why is this a problem? I’m trying to make my query as performant as possible and making follow up calls when a join couldn’t be made will add latency. Trying to achieve both performance and simple implementation.