spring data jpa unnecessary left join

java, jpa, postgresql, spring, spring-data-jpa

Solution

A quick review of the entity model

@Entity
class Institucion {
  @ManyToOne(fetch=FetchType.LAZY)
  @JoinColumn(name="`sectorId`")
  private Sector sector;
}

is equivalent to:

@Entity
class Institucion {
  @ManyToOne(cascade = {}
             , fetch=FetchType.LAZY
             , optional = true
             , targetEntity = void.class)
  @JoinColumn(columnDefinition = ""
             , foreignKey = @ForeignKey
             , insertable = true
             , name="`sectorId`"
             , nullable = true
             , referencedColumnName = ""
             , table = ""
             , unique = false
             , updatable = false)
  private Sector sector;
}

Note `@ManyToOne(optional = true)` and `@JoinColumn(nullable = true)`. This signifies to the ORM that the `sector` attribute of `Institucion` is optional and may not be set (to a non-null value) all the time.

How the entity model impacts repository queries

Now consider the following repository:

public interface InstitucionRepository extends CrudRepository<Institucion, Long> {
  List<Institucion> findAllByInstitucionNombre(String nombre);

  List<Institucion> findAllByInstitucionEmail(String email);
}

Given the entity declaration above, the repository methods should produce queries such as:

select
    generatedAlias0 
from
    Institucion as generatedAlias0 
left join
    generatedAlias0.sector as generatedAlias1 
where
    generatedAlias0.institucionNombre=:param0

and

select
    generatedAlias0 
from
    Institucion as generatedAlias0 
left join
    generatedAlias0.sector as generatedAlias1 
where
    generatedAlias0.institucionEmail=:param0

This is because the entity model indicates `sector` to be optional so the ORM needs to load `Institucion`s without worrying about their `sector`s.

Following this pattern, the following repository method:

  List<Institucion> findAllBySector(Sector sector);

translates to:

select
    generatedAlias0 
from
    Institucion as generatedAlias0 
left join
    generatedAlias0.sector as generatedAlias1 
where
    generatedAlias1=:param0

Solution 1

If `Institucion.sector` is not optional, make it mandatory in the model too:

  @ManyToOne(fetch=FetchType.LAZY, optional = false)
  @JoinColumn(name="`sectorId`", nullable = false)
  private Sector sector;

Solution 2

If `Institucion.sector` is indeed optional, only a manual query such as the one shown in @MaciejKowalski's answer will work.

Simplified query

The following query will also work:

  List<Institucion> findAllBySectorSectorId(Long id);

This assumes that the model attribute names are exactly as shown in the post.

Problem

I have the following model: I want to get all `Institutions` (Intituciones) with specified `sectorId`. In the `tbInstitucion` model I have a relationship with `tbSector`: ``` @ManyToOne(fetch=FetchType.LAZY) @JoinColumn(name="`sectorId`") private Sector sector; ``` is there a way to obtain a query like: ``` select * from tbInstitucion where sectorId = ? ``` I tried with: `findBySector(Sector sector)` but with this I need an additional query to find the sector and `findBySector` is generating the following query: ``` select generatedAlias0.institucionId, generatedAlias0.institucionNombre from Institucion as generatedAlias0 left join generatedAlias0.sector as generatedAlias1 where generatedAlias1=:param0 ``` tried with this other one: ``` findBySector_sectorId ``` which generates the above query as well. Wouldn't be better to form a query like: ``` select * from tbInstitucion where sectorId = ? ``` Is there a way to get the above query? Why is JPA generating the left join?

Original source