Spring数据JPA不必要的左联接 [英] spring data jpa unnecessary left join

问题描述

我有以下模型:

我想获取所有具有指定sectorId的Institutions(Intituciones).

I want to get all Institutions (Intituciones) with specified sectorId.

在tbInstitucion模型中，我与tbSector有关系:

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 = ?

我尝试过:findBySector(Sector sector)

但是与此相关的是，我需要一个附加的查询来查找扇区，并且findBySector正在生成以下查询:

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

尝试了另外一个:

findBySector_sectorId

也会生成上述查询.

形成像这样的查询会更好:

Wouldn't be better to form a query like:

select * 
from tbInstitucion 
where sectorId = ?

是否可以获取上述查询? JPA为什么生成左联接?

Is there a way to get the above query? Why is JPA generating the left join?

推荐答案

对实体模型的快速回顾

A quick review of the entity model

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

等效于:

@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;
}

注意@ManyToOne(optional = true)和@JoinColumn(nullable = true).这向ORM表示Institucion的sector属性是可选的，并且可能不会一直设置为非null值.

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

现在考虑以下存储库:

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

和

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

这是因为实体模型指示sector是可选的，因此ORM需要加载Institucion而不用担心它们的sector.

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

遵循此模式，使用以下存储库方法:

Following this pattern, the following repository method:

  List<Institucion> findAllBySector(Sector sector);

翻译为:

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

---

解决方案1 ​​

如果Institucion.sector不是可选的，则也将其在模型中强制设置:

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;

解决方案2

如果Institucion.sector确实是可选的，则只有手动查询(例如@MaciejKowalski的答案中显示的查询)将起作用.

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

简化查询

以下查询也将起作用:

  List<Institucion> findAllBySectorSectorId(Long id);

这假定模型属性名称与帖子中显示的完全相同.

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

这篇关于Spring数据JPA不必要的左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！