Java Spring JPA FetchMode.JOIN not using JOIN

Question

I have quite a complicated model structure in Spring using JPA. When using Spring Data to query my database I am expecting ONE query that uses JOINS to retrieve the data, instead Spring is running multiple queries. Below is my model structure:

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 get the picture - I won't keep adding models. I am running this query using spring data:

feedAttributeRepository.findByFeed(feed);

Which will query all feed attributes for a given feed. Now, what I would expect to happen is run a query against the feedAttribute table, joining entityAttribute twice and joining feed. I would then expect the subsequent nested values in feed to be joined too (system, release), and the values nested within entityAttribute (dataModel) to be joined as well - and so on, for all FetchMode.JOIN that I specify.

In fact, what is actually happening, is spring is running multiple select queries, some which are joining and some which aren't. In my case the entityAttribute objects are not being joined on the feedAttribute object. They are retrieved by running multiple SELECT statements for every feedAttribute returned. There are about 300-400 feedAttributes returned so as you can imagine, this is incredibly inefficient.

Is there an upper limit on the number of JOINs that can be performed? Or is JPA working out that this is the best way to retrieve the data? As I do understand lots of JOINs can be inefficient too. Thanks

P.S. It was working using MySQL, but I'm moving over to Oracle and the whole thing breaks down because I am running out of Cursors on the Database because of the number of SELECT statements executed. A bit of a nightmare!

Answer 1

You seem to run into a misconception here: lazy/eager definitions on an object are only considered when loading an object via it's primary key (i.e. via EntityManager.find(id, type) or FeedAttributeRepository.findOne(id). Hibernate disables the global fetch plan for query executions.

For query methods you explicitly have to specify fetch joins in the query definition (which will require a manually declared query) or define an fetch graph to be used for the query method using @EntityGraph (see the reference documentation for details).

Answer 2

As far as I know this is not a spring, but a hibernate problem called the n+1 problem which occurs in several situations. You can find a very similar question here.

I would disagree with Oliver's answer that you ran into a misconception and that there is actually no problem with spring/hibernate. If I call any find...() method, I would either expect it to fetch no relationship at all or I would expect it to consider the the fetch types AND the @Fetch annotation. Actually, the hybrid of those is the fact.The default fetch types (which is EAGER for @ManyToOne relationships) are considered (so the related object are loaded), but the @Fetch annotation is ignored by hibernate.

I can provide 3 workarounds for that:

1. Use EntityGraphs (take a look at Olivers answer for that) if you're working with JPA 2.1
2. Explicitely define Joins with @Query and JPQL
3. You could also use the Specification API

Another possibility is to switch to another JPA provider. Personally I tested it with EclipseLink and got much better results (although query optimization is also not perfect there).