title: Java Spring JPA FetchMode.JOIN not using JOIN
body: <p>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:</p>
  
  <p><strong>Feed Attribute:</strong></p>
  
  <pre><code>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;
  
  }
  </code></pre>
  
  <p><strong>Feed:</strong></p>
  
  <pre><code>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;
  
  }
  </code></pre>
  
  <p><strong>Entity Attribute:</strong></p>
  
  <pre><code>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;
  
  }
  </code></pre>
  
  <p><strong>Data Model:</strong></p>
  
  <pre><code>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;
  
  }
  </code></pre>
  
  <p><strong>System:</strong></p>
  
  <pre><code>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;
  
  }
  </code></pre>
  
  <p><strong>Release:</strong></p>
  
  <pre><code>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;
  
  }
  </code></pre>
  
  <p>You get the picture - I won't keep adding models. I am running this query using spring data:</p>
  
  <pre><code>feedAttributeRepository.findByFeed(feed);
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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>
  
  <p>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!</p>