Uploaded image for project: 'Spring Data JPA'
  1. Spring Data JPA
  2. DATAJPA-1076

Use foreign key instead of joining the full table when possible

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.11.1 (Ingalls SR1)
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      If I have the following mapping:

      @Entity
      public class Person {
          @Id
          @GeneratedValue
          private Long id;
       
          @Column(name = "language")
          private String language;
       
          @ManyToOne(fetch = FetchType.LAZY)
          @JoinColumn(name = "city_fk", referencedColumnName = "id")
          private City city;
      }
      

      and I'm only interested in the languages present for a certain City, I can define the following repository interface:

      public interface PersonRepository extends JpaRepository<Person, Long> {
          Collection<LanguageProjection> findDistinctByCityId(Long cityId);
      }
      

      When calling the repository, it will execute the following query:

      select
          distinct person0_.language as col_0_0_ 
      from
          person person0_ 
      left outer join
          city city1_ on person0_.city_fk=city1_.id 
      where
          city1_.id=?
      

      However, there is no need for joining the full city table. This might cause performance problems. Spring Data could create the following query instead as they are equivalent:

      select
          distinct person0_.language as col_0_0_ 
      from
          person person0_ 
      where
         person0_.city_fk=?
      

        Activity

        Hide
        shubham3110 Shubham Gupta added a comment -

        Hello Spring Team,

        Even I am facing this issue. I have put up a http://stackoverflow.com/q/43054887/2701126 SO for this too.

        Show
        shubham3110 Shubham Gupta added a comment - Hello Spring Team, Even I am facing this issue. I have put up a http://stackoverflow.com/q/43054887/2701126 SO for this too.

          People

          • Assignee:
            olivergierke Oliver Gierke
            Reporter:
            galovics Arnold Galovics
            Last updater:
            Shubham Gupta
          • Votes:
            2 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated: