I have a one-to-many relationship between routes and stops. In order to maintain an audit trail, my Stop entities have a "historic" boolean.
When fetching a route, I want to ignore historic stops, and so I constructed this query:
@Query("select r from Route r " +
"left join fetch r.schedules schedule " +
"left join fetch r.stops stop " +
"where r.routeId = :routeId and stop.historic = false ")
Optional<Route> findByIdLoadStops(@Param("routeId") int routeId);
This works fine when the route has non-historic stops and no stops, but when the route only has a historic stop (which shouldn't happen but I want to be able to at least handle it), it returns an empty optional as though an inner join has been performed.
When logging the JPA query created by hibernate, I can see that the query uses a left outer join.
What have I done incorrectly?
Route and Stop entities:
@Table(name = "route")
@Entity
public class Route {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "routeId", columnDefinition = "SMALLINT(5) UNSIGNED")
private int routeId;
@Column(name = "locked")
private boolean locked = false;
@OneToMany(mappedBy = "route",
cascade = CascadeType.ALL,
fetch = FetchType.LAZY)
@OrderBy("stopTime asc")
private SortedSet<Stop> stops = new TreeSet<>();
public Route() {
}
}
@Table(name = "stop", uniqueConstraints = {
@UniqueConstraint(columnNames = {"stopTime", "routeId"}),
@UniqueConstraint(columnNames = {"stopName", "routeId"})})
@Entity
public class Stop implements Comparable<Stop> {
@Id
@Column(name = "stopId")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int stopId;
@Column(name = "routeId",
columnDefinition = "SMALLINT(5)",
updatable = false, insertable = false)
private int routeId;
@ManyToOne(cascade = CascadeType.MERGE,
fetch = FetchType.LAZY)
@JoinColumn(name = "routeId")
private Route route;
@Column(name = "stopTime")
private LocalTime stopTime;
@Column(name = "stopName")
private String stopName;
@JoinColumn(name = "originalId", referencedColumnName = "stopId")
@ManyToOne(fetch = FetchType.LAZY)
private Stop originalStop = this;
@Column(name = "historic")
private boolean historic = false;
public Stop() {
}
}