Java Spring JPA FetchMode.JOIN does not use JOIN
I have a fairly complex model structure in Spring using JPA. When using Spring data to query my database, I'm expecting a single request that uses JOINS to fetch the data, Spring is doing multiple requests instead. Below is my model:
Feed attribute:
public class FeedAttribute {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="feedAttributeId", nullable=false)
private Integer feedAttributeId;
@ManyToOne(cascade = CascadeType.DETACH)
@Fetch(value=FetchMode.JOIN)
@JoinColumn(name="feedId", nullable=false)
private Feed feed;
@ManyToOne(cascade = CascadeType.DETACH)
@Fetch(value=FetchMode.JOIN)
@JoinColumn(name="sourceEntityAttributeId", nullable=false)
private EntityAttribute sourceEntityAttribute;
@ManyToOne(cascade = CascadeType.DETACH)
@Fetch(value=FetchMode.JOIN)
@JoinColumn(name="targetEntityAttributeId", nullable=false)
private EntityAttribute targetEntityAttribute;
}
Feed:
public class Feed {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="feedId", nullable=false, length=100)
private Integer feedId;
@Column(name="feedName", nullable=false, length=100)
private String feedName;
@ManyToOne(cascade = CascadeType.DETACH)
@Fetch(value=FetchMode.JOIN)
@JoinColumn(name="releaseId", nullable=false)
private Release release;
@ManyToOne(cascade = CascadeType.DETACH)
@Fetch(value=FetchMode.JOIN)
@JoinColumn(name="sourceSystemId", nullable=false)
private System sourceSystem;
@ManyToOne(cascade = CascadeType.DETACH)
@Fetch(value=FetchMode.JOIN)
@JoinColumn(name="targetSystemId", nullable=false)
private System targetSystem;
@ManyToOne(cascade = CascadeType.DETACH)
@Fetch(value=FetchMode.JOIN)
@JoinColumn(name="companyId", nullable=false)
private Company company;
}
Entity Attribute:
public class EntityAttribute {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="entityAttributeId", nullable=false)
private Integer entityAttributeId;
@Column(name="entityCode", nullable=false, length=100)
private String entityCode;
@Column(name="attributeCode", nullable=false, length=100)
private String attributeCode;
@ManyToOne(cascade = CascadeType.DETACH)
@Fetch(value=FetchMode.JOIN)
@JoinColumn(name="dataModelId", nullable=false)
private DataModel dataModel;
}
Data model:
public class DataModel {
@Id
@Column(name="dataModelId", nullable=false)
private String dataModelId;
@Column(name="dataModelDescription", nullable=false)
private String dataModelDescription;
@ManyToOne(cascade = CascadeType.DETACH)
@Fetch(value=FetchMode.JOIN)
@JoinColumn(name="companyId", nullable=false)
private Company company;
}
System:
public class System {
@Id
@Column(name="systemId", nullable=false, length=100)
private String systemId;
@Column(name="systemName", nullable=false)
private String systemName;
@ManyToOne(cascade = CascadeType.DETACH)
@Fetch(value=FetchMode.JOIN)
@JoinColumn(name="systemSubType", nullable=true)
private SystemSubType systemSubType;
@ManyToOne(cascade = CascadeType.DETACH)
@Fetch(value=FetchMode.JOIN)
@JoinColumn(name="systemType", nullable=false)
private SystemType systemType;
@ManyToOne(cascade = CascadeType.DETACH)
@Fetch(value=FetchMode.JOIN)
@JoinColumn(name="companyId", nullable=false)
private Company company;
}
Release:
public class Release {
@Id
@Column(name="releaseId", nullable=false, length=100)
private String releaseId;
@Column(name="releaseDescription", nullable=false, length=1000)
private String releaseDescription;
@Column(name="releaseDate")
private Date releaseDate = new Date();
@Column(name="releaseLocation", nullable=false)
private String releaseLocation;
@ManyToOne(cascade = CascadeType.DETACH)
@Fetch(value=FetchMode.JOIN)
@JoinColumn(name="companyId", nullable=false)
private Company company;
}
You will get an image - I will not continue to add models. I am using this request using Spring data:
feedAttributeRepository.findByFeed(feed);
Which will request all the feed attributes for a given channel. Now, what I expect is a query on the feedAttribute table, merging the entityAttribute twice and joining the feed. Then I expected subsequent nested values in the feed to be merged (system, release), as well as the values nested in entityAttribute (dataModel) that need to be merged, etc. For all FetchMode.JOINs I specify.
In fact, what actually happens is Spring is running multiple select queries, some of which are pooled and some are not. In my case, entityAttribute entities are not being merged into feedAttribute entity. They are retrieved by running multiple SELECT statements for each feedAttribute returned. There are approximately 300-400 feedAttributes returned, as you can imagine, this is incredibly inefficient.
Is there an upper limit on the number of JOINs that can be executed? Or is JPA figuring out what is the best way to get the data? As I understand it, many JOINs can be ineffective. Thanks to
PS It worked using MySQL but I go to Oracle and it all breaks because I run out of cursors in the database due to the number of SELECT statements executed. A bit of a nightmare!
source to share
You seem to be running into a misunderstanding here: lazy / impatient object definitions are only considered when the object is loaded via its primary key (i.e. via EntityManager.find(id, type)
or FeedAttributeRepository.findOne(id)
). Hibernate disables the global fetch plan for executing queries.
For query methods, you must explicitly specify selection joins in the query definition (which requires a manually declared query) or define a selection graph to be used for the query method using @EntityGraph
(see reference documentation ).
source to share
As far as I know, this is not spring, but a hibernation problem called n + 1 problem that occurs in several situations. You can find a very similar question here .
I would disagree with Oliver's answer that you are facing a misconception and that there is actually no problem with spring / hibernate. If I call any find ... () method, I would either expect it to get no relationship at all, or I would expect it to consider fetch types and @Fetch annotation. In fact, a hybrid is that fact. The default fetch types (which are EAGERs for @ManyToOne relationships) are respected (hence the associated object is loaded), but the @Fetch annotation is ignored by hibernation.
I can provide 3 workarounds for this:
- Use EntityGraphs (look at Olivers answer for this) if you are working with JPA 2.1
- Explicitly defining joins with @Query and JPQL
- You can also use the specification API
Another possibility is to switch to a different JPA provider. Personally I've tested it with EclipseLink and got much better results (although query optimization isn't perfect either).
source to share