Reputation: 221
I know for a fact that with clause on fetch join are not allowed by hibernate
I am using spring data jpa and postgres.
Here is how my entity is designed
public class Organisation {
@Id
private Long id;
@OneToMany(mappedBy = "organisation", cascade = CascadeType.ALL)
@LazyCollection(LazyCollectionOption.EXTRA)
private Set<Assignment> assignments = new HashSet<>();
@OneToMany(mappedBy = "organisation", cascade = CascadeType.ALL)
private List<Event> events;
}
public class Event {
@Id
private Long id;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "organisations_id", nullable = false)
private Organisation organisation;
@OneToMany(mappedBy = "event", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private Set<EventValue> eventValues = new HashSet<>();
}
public class EventValue {
@Id
private Long id;
@ManyToOne(fetch = FetchType.LAZY, optional = false)
@JoinColumn(name = "event_id")
private Event Event;
@ManyToOne(fetch = FetchType.LAZY, optional = false)
@JoinColumn(name = "assignment_id")
private Assignment assignment;
}
public class Assignment {
@Id
private Long id;
@OneToMany(mappedBy = "assignment", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private Set<EventValue> eventValues = new HashSet<>();
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "organisation_id", nullable = false)
private Organisation organisation;
}
Kind of a three way mapping. What the above entity design says is:
So, I tried to query something like this.
@Query("select assignment from Assignment left join fetch assignment.organisation org
left join fetch org.event event left join fetch event.eventValues eventValue
with eventValue.assignment.id=?1 where assignment.id=?1)
Assignment getByAssignmentId(Long id);
What am I trying to achive with the query ?
To get assignment with given (id) -> organisation -> list of activities with HashSet containing only ONE activity value mapped to assignment.
The query is obviously going to fail because of using with clause on fetch join. I somehow feel the entity has 3 way dependency so it might be wrong.
I do not want to generic jdbcTemplate solution or SqlResultMapping solution where we need to do some kind of projection and set values manually. Is there a ORM solution to solve this problem ?
Upvotes: 0
Views: 1650
Reputation: 16452
The reason why a WITH
or ON
clause is disallowed for join fetches is pretty simple. Hibernate works on managed entities, which means, once the entities are managed by the current persistence context, changes done to these objects will be flushed back to the database at the end of the transaction.
Now, if you were allowed to use the WITH
or ON
clause in a join fetch, the querying itself could alter the managed state of a collection, which would lead to UPDATE/DELETE statements to flush the collection changes back. Since this is completely unexpected, but a necessary side effect, it is disallowed.
Having said that, this is a perfect use case for Blaze-Persistence Entity Views.
Blaze-Persistence is a query builder on top of JPA which supports many of the advanced DBMS features on top of the JPA model. I created Entity Views on top of it to allow easy mapping between JPA models and custom interface defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure the way you like and map attributes(getters) via JPQL expressions to the entity model. Since the attribute name is used as default mapping, you mostly don't need explicit mappings as 80% of the use cases is to have DTOs that are a subset of the entity model.
A DTO mapping for your model could look as simple as the following
@EntityView(Assignment.class)
interface AssignmentDto {
Long getId();
OrganisationDto getOrganisation();
}
@EntityView(Organisation.class)
interface OrganisationDto {
Long getId();
List<EventDto> getEvents();
}
@EntityView(Event.class)
interface EventDto {
Long getId();
@Mapping("eventValues[assignment.id = VIEW_ROOT(id)]")
EventValueDto getEventValue();
}
@EntityView(EventValue.class)
interface EventValueDto {
Long getId();
// Other stuff
}
The JOIN condition is modeled in the mapping expression eventValues[assignment.id = VIEW_ROOT(id)]
which translates to what you would expect.
Querying is a matter of applying the entity view to a query, the simplest being just a query by id.
AssignmentDto dto = entityViewManager.find(entityManager, AssignmentDto.class, id);
But the Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features
It will only fetch the mappings that you tell it to fetch.
Upvotes: 1