java如何使用Hibernate避免不必要的查询？

3 周 Questions & Answers 446

我想对一个名为“利用者”的实体进行查询：

@Override
@Transactional
public List<Utilisateur> list() {

    String hql = "from Utilisateur where deleted is null or deleted <> 1";

    Query query = sessionFactory.getCurrentSession().createQuery(hql);

    @SuppressWarnings("unchecked")
    List<Utilisateur> listUser = (List<Utilisateur>) query.list();

    return listUser;

}

@Entity
@Table(name = "utilisateur")
public class Utilisateur {

    @Id
    @SequenceGenerator(name="s_utilisateur", sequenceName="s_utilisateur", allocationSize=1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="s_utilisateur")
    @Column(name = "user_code")
    private Long code;

    @Column(name = "user_nom")
    private String nom;

    @Column(name = "user_prenom")
    private String prenom;

    @Column(name = "user_login")
    private String login;

    @ManyToOne
    @JoinColumn(name = "struct_code")
    private Structure structure;

    ...

}

正如您所看到的，有structure类型的属性结构。这里是实体结构：

@Entity
@Table(name = "structure")
public class Structure {

    @Id()
    @Column(name="struct_code") 
    private String code;

    @ManyToOne
    @JoinColumn(name = "str_struct_code")
    private Structure parent;

    @ManyToOne
    @JoinColumn(name="niv_struct_code") 
    private NiveauStructure niveauStructure;

    @ManyToMany(fetch = FetchType.EAGER, mappedBy = "structures")
    @JsonBackReference
    private Set<Cdmt> programmes = new HashSet<Cdmt>();

    @Column(name="struct_lib")
    private String lib;

    ...

}

还有属性程序，下面是它的代码：

@Entity
@Table(name = "cdmt")
public class Cdmt {

    @Id
    @Column(name = "cdmt_code")
    private String code;

    @ManyToOne
    @JoinColumn(name = "class_cdmt_code")
    private ClasseCdmt classeCdmt;

    @Column(name="cdmt_design")
    @Lob
    private String lib;

    @ManyToOne
    @JoinColumn(name = "prog_code")
    private Pmo pmo;

    @ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.MERGE)
    @JoinTable(name = "programme_structure" , joinColumns = {@JoinColumn(name = "cdmt_code")} , inverseJoinColumns = {@JoinColumn(name = "struct_code")} )
    @JsonManagedReference
    private Set<Structure> structures = new HashSet<Structure>();

    @ManyToOne
    @JoinColumn(name = "cdm_cdmt_code")
    private Cdmt cdmtParent;

    ...
}

在运行时，控制台中会显示许多查询，其中有针对Pmo实体的查询，尽管查询中没有提到该实体！那么，如何避免这种查询的传播呢

更新：

以下是控制台中的查询跟踪：

Hibernate: select programmes0_.struct_code as struct_code2_50_0_, programmes0_.cdmt_code as cdmt_code1_33_0_, cdmt1_.cdmt_code as cdmt_code1_4_1_, cdmt1_.cdm_cdmt_code as cdm_cdmt_code7_4_1_, cdmt1_.cdmt_comment as cdmt_comment2_4_1_, cdmt1_.class_cdmt_code as class_cdmt_code8_4_1_, cdmt1_.cdmt_debut as cdmt_debut3_4_1_, cdmt1_.deleted as deleted4_4_1_, cdmt1_.cdmt_fin as cdmt_fin5_4_1_, cdmt1_.cdmt_design as cdmt_design6_4_1_, cdmt1_.prog_code as prog_code9_4_1_, cdmt2_.cdmt_code as cdmt_code1_4_2_, cdmt2_.cdm_cdmt_code as cdm_cdmt_code7_4_2_, cdmt2_.cdmt_comment as cdmt_comment2_4_2_, cdmt2_.class_cdmt_code as class_cdmt_code8_4_2_, cdmt2_.cdmt_debut as cdmt_debut3_4_2_, cdmt2_.deleted as deleted4_4_2_, cdmt2_.cdmt_fin as cdmt_fin5_4_2_, cdmt2_.cdmt_design as cdmt_design6_4_2_, cdmt2_.prog_code as prog_code9_4_2_, classecdmt3_.class_cdmt_code as class_cdmt_code1_5_3_, classecdmt3_.class_cdmt_comment as class_cdmt_comment2_5_3_, classecdmt3_.class_cdmt_lib as class_cdmt_lib3_5_3_, classecdmt3_.class_cdmt_niveau as class_cdmt_niveau4_5_3_, pmo4_.prog_code as prog_code1_31_4_, pmo4_.class_pmo_code as class_pmo_code6_31_4_, pmo4_.prog_debut as prog_debut2_31_4_, pmo4_.prog_fin as prog_fin3_31_4_, pmo4_.prog_design as prog_design4_31_4_, pmo4_.pmo_prog_code as pmo_prog_code7_31_4_, pmo4_.pnd_code as pnd_code8_31_4_, pmo4_.prog_comment as prog_comment5_31_4_, classepmo5_.class_pmo_code as class_pmo_code1_6_5_, classepmo5_.class_pmo_comment as class_pmo_comment2_6_5_, classepmo5_.class_pmo_lib as class_pmo_lib3_6_5_, classepmo5_.class_pmo_niveau as class_pmo_niveau4_6_5_, pmo6_.prog_code as prog_code1_31_6_, pmo6_.class_pmo_code as class_pmo_code6_31_6_, pmo6_.prog_debut as prog_debut2_31_6_, pmo6_.prog_fin as prog_fin3_31_6_, pmo6_.prog_design as prog_design4_31_6_, pmo6_.pmo_prog_code as pmo_prog_code7_31_6_, pmo6_.pnd_code as pnd_code8_31_6_, pmo6_.prog_comment as prog_comment5_31_6_, pnd7_.pnd_code as pnd_code1_32_7_, pnd7_.creation as creation2_32_7_, pnd7_.pnd_debut as pnd_debut3_32_7_, pnd7_.deleted as deleted4_32_7_, pnd7_.pnd_fin as pnd_fin5_32_7_, pnd7_.pnd_intitule as pnd_intitule6_32_7_, pnd7_.modification as modification7_32_7_, pnd7_.obj_code as obj_code10_32_7_, pnd7_.owner as owner8_32_7_, pnd7_.pnd_comment as pnd_comment9_32_7_, objectif8_.obj_code as obj_code1_25_8_, objectif8_.cdmt_code as cdmt_code8_25_8_, objectif8_.obj_comment as obj_comment2_25_8_, objectif8_.creation as creation3_25_8_, objectif8_.deleted as deleted4_25_8_, objectif8_.obj_intitule as obj_intitule5_25_8_, objectif8_.modification as modification6_25_8_, objectif8_.nat_obj_code as nat_obj_code9_25_8_, objectif8_.obj_obj_code as obj_obj_code10_25_8_, objectif8_.owner as owner7_25_8_, objectif8_.prog_code as prog_code11_25_8_, objectif8_.pta_code as pta_code12_25_8_, cdmt9_.cdmt_code as cdmt_code1_4_9_, cdmt9_.cdm_cdmt_code as cdm_cdmt_code7_4_9_, cdmt9_.cdmt_comment as cdmt_comment2_4_9_, cdmt9_.class_cdmt_code as class_cdmt_code8_4_9_, cdmt9_.cdmt_debut as cdmt_debut3_4_9_, cdmt9_.deleted as deleted4_4_9_, cdmt9_.cdmt_fin as cdmt_fin5_4_9_, cdmt9_.cdmt_design as cdmt_design6_4_9_, cdmt9_.prog_code as prog_code9_4_9_, natureobje10_.nat_obj_code as nat_obj_code1_23_10_, natureobje10_.nat_obj_comment as nat_obj_comment2_23_10_, natureobje10_.nat_obj_lib as nat_obj_lib3_23_10_, objectif11_.obj_code as obj_code1_25_11_, objectif11_.cdmt_code as cdmt_code8_25_11_, objectif11_.obj_comment as obj_comment2_25_11_, objectif11_.creation as creation3_25_11_, objectif11_.deleted as deleted4_25_11_, objectif11_.obj_intitule as obj_intitule5_25_11_, objectif11_.modification as modification6_25_11_, objectif11_.nat_obj_code as nat_obj_code9_25_11_, objectif11_.obj_obj_code as obj_obj_code10_25_11_, objectif11_.owner as owner7_25_11_, objectif11_.prog_code as prog_code11_25_11_, objectif11_.pta_code as pta_code12_25_11_, pmo12_.prog_code as prog_code1_31_12_, pmo12_.class_pmo_code as class_pmo_code6_31_12_, pmo12_.prog_debut as prog_debut2_31_12_, pmo12_.prog_fin as prog_fin3_31_12_, pmo12_.prog_design as prog_design4_31_12_, pmo12_.pmo_prog_code as pmo_prog_code7_31_12_, pmo12_.pnd_code as pnd_code8_31_12_, pmo12_.prog_comment as prog_comment5_31_12_, pta13_.pta_code as pta_code1_34_13_, pta13_.cdmt_code as cdmt_code18_34_13_, pta13_.class_pta_code as class_pta_code19_34_13_, pta13_.creation as creation2_34_13_, pta13_.pta_definitif as pta_definitif3_34_13_, pta13_.deleted as deleted4_34_13_, pta13_.pta_desc as pta_desc5_34_13_, pta13_.exer_code as exer_code20_34_13_, pta13_.pta_intitule as pta_intitule6_34_13_, pta13_.modification as modification7_34_13_, pta13_.owner as owner8_34_13_, pta13_.pta_pta_code as pta_pta_code21_34_13_, pta13_.pta_activite as pta_activite9_34_13_, pta13_.pta_cloture as pta_cloture10_34_13_, pta13_.pta_limite_decaisse as pta_limite_decais11_34_13_, pta13_.pta_num_credit as pta_num_credit12_34_13_, pta13_.pta_signature as pta_signature13_34_13_, pta13_.pta_unite_execution as pta_unite_executi14_34_13_, pta13_.pta_vigueur as pta_vigueur15_34_13_, pta13_.pta_ref as pta_ref16_34_13_, pta13_.pta_resultat_annee as pta_resultat_anne17_34_13_, pta13_.sect_code as sect_code22_34_13_, pta13_.struct_code as struct_code23_34_13_, pta13_.typ_proj_code as typ_proj_code24_34_13_, cdmt14_.cdmt_code as cdmt_code1_4_14_, cdmt14_.cdm_cdmt_code as cdm_cdmt_code7_4_14_, cdmt14_.cdmt_comment as cdmt_comment2_4_14_, cdmt14_.class_cdmt_code as class_cdmt_code8_4_14_, cdmt14_.cdmt_debut as cdmt_debut3_4_14_, cdmt14_.deleted as deleted4_4_14_, cdmt14_.cdmt_fin as cdmt_fin5_4_14_, cdmt14_.cdmt_design as cdmt_design6_4_14_, cdmt14_.prog_code as prog_code9_4_14_, classepta15_.class_pta_code as class_pta_code1_7_15_, classepta15_.class_pta_comment as class_pta_comment2_7_15_, classepta15_.class_pta_lib as class_pta_lib3_7_15_, classepta15_.class_pta_niveau as class_pta_niveau4_7_15_, exer16_.exer_code as exer_code1_15_16_, exer16_.exer_en_cours as exer_en_cours2_15_16_, exer16_.exer_lib as exer_lib3_15_16_, pta17_.pta_code as pta_code1_34_17_, pta17_.cdmt_code as cdmt_code18_34_17_, pta17_.class_pta_code as class_pta_code19_34_17_, pta17_.creation as creation2_34_17_, pta17_.pta_definitif as pta_definitif3_34_17_, pta17_.deleted as deleted4_34_17_, pta17_.pta_desc as pta_desc5_34_17_, pta17_.exer_code as exer_code20_34_17_, pta17_.pta_intitule as pta_intitule6_34_17_, pta17_.modification as modification7_34_17_, pta17_.owner as owner8_34_17_, pta17_.pta_pta_code as pta_pta_code21_34_17_, pta17_.pta_activite as pta_activite9_34_17_, pta17_.pta_cloture as pta_cloture10_34_17_, pta17_.pta_limite_decaisse as pta_limite_decais11_34_17_, pta17_.pta_num_credit as pta_num_credit12_34_17_, pta17_.pta_signature as pta_signature13_34_17_, pta17_.pta_unite_execution as pta_unite_executi14_34_17_, pta17_.pta_vigueur as pta_vigueur15_34_17_, pta17_.pta_ref as pta_ref16_34_17_, pta17_.pta_resultat_annee as pta_resultat_anne17_34_17_, pta17_.sect_code as sect_code22_34_17_, pta17_.struct_code as struct_code23_34_17_, pta17_.typ_proj_code as typ_proj_code24_34_17_, secteur18_.sect_code as sect_code1_48_18_, secteur18_.sect_comm as sect_comm2_48_18_, secteur18_.sect_lib as sect_lib3_48_18_, structure19_.struct_code as struct_code1_50_19_, structure19_.struct_lib as struct_lib2_50_19_, structure19_.niv_struct_code as niv_struct_code15_50_19_, structure19_.str_struct_code as str_struct_code16_50_19_, structure19_.struct_sigle as struct_sigle3_50_19_, structure19_.struct_comment as struct_comment4_50_19_, structure19_.struct_contact as struct_contact5_50_19_, structure19_.struct_interne as struct_interne6_50_19_, structure19_.struct_mission_fonc as struct_mission_fon7_50_19_, structure19_.struct_mission_oper as struct_mission_ope8_50_19_, structure19_.struct_resp_fonc as struct_resp_fonc9_50_19_, structure19_.struct_resp_hiera as struct_resp_hiera10_50_19_, structure19_.struct_resp_oper as struct_resp_oper11_50_19_, structure19_.struct_site as struct_site12_50_19_, structure19_.struct_tache_fonc as struct_tache_fonc13_50_19_, structure19_.struct_tache_oper as struct_tache_oper14_50_19_, niveaustru20_.niv_struct_code as niv_struct_code1_24_20_, niveaustru20_.niv_struct_comment as niv_struct_comment2_24_20_, niveaustru20_.niv_struct_lib as niv_struct_lib3_24_20_, niveaustru20_.niv_struct_ordre as niv_struct_ordre4_24_20_, structure21_.struct_code as struct_code1_50_21_, structure21_.struct_lib as struct_lib2_50_21_, structure21_.niv_struct_code as niv_struct_code15_50_21_, structure21_.str_struct_code as str_struct_code16_50_21_, structure21_.struct_sigle as struct_sigle3_50_21_, structure21_.struct_comment as struct_comment4_50_21_, structure21_.struct_contact as struct_contact5_50_21_, structure21_.struct_interne as struct_interne6_50_21_, structure21_.struct_mission_fonc as struct_mission_fon7_50_21_, structure21_.struct_mission_oper as struct_mission_ope8_50_21_, structure21_.struct_resp_fonc as struct_resp_fonc9_50_21_, structure21_.struct_resp_hiera as struct_resp_hiera10_50_21_, structure21_.struct_resp_oper as struct_resp_oper11_50_21_, structure21_.struct_site as struct_site12_50_21_, structure21_.struct_tache_fonc as struct_tache_fonc13_50_21_, structure21_.struct_tache_oper as struct_tache_oper14_50_21_, typeprojet22_.typ_proj_code as typ_proj_code1_52_22_, typeprojet22_.typ_proj_comment as typ_proj_comment2_52_22_, typeprojet22_.typ_proj_lib as typ_proj_lib3_52_22_ from programme_structure programmes0_ inner join cdmt cdmt1_ on programmes0_.cdmt_code=cdmt1_.cdmt_code left outer join cdmt cdmt2_ on cdmt1_.cdm_cdmt_code=cdmt2_.cdmt_code left outer join classe_cdmt classecdmt3_ on cdmt2_.class_cdmt_code=classecdmt3_.class_cdmt_code left outer join pmo pmo4_ on cdmt2_.prog_code=pmo4_.prog_code left outer join classe_pmo classepmo5_ on pmo4_.class_pmo_code=classepmo5_.class_pmo_code left outer join pmo pmo6_ on pmo4_.pmo_prog_code=pmo6_.prog_code left outer join pnd pnd7_ on pmo6_.pnd_code=pnd7_.pnd_code left outer join objectif objectif8_ on pnd7_.obj_code=objectif8_.obj_code left outer join cdmt cdmt9_ on objectif8_.cdmt_code=cdmt9_.cdmt_code left outer join nature_objectif natureobje10_ on objectif8_.nat_obj_code=natureobje10_.nat_obj_code left outer join objectif objectif11_ on objectif8_.obj_obj_code=objectif11_.obj_code left outer join pmo pmo12_ on objectif11_.prog_code=pmo12_.prog_code left outer join pta pta13_ on objectif11_.pta_code=pta13_.pta_code left outer join cdmt cdmt14_ on pta13_.cdmt_code=cdmt14_.cdmt_code left outer join classe_pta classepta15_ on pta13_.class_pta_code=classepta15_.class_pta_code left outer join exercice exer16_ on pta13_.exer_code=exer16_.exer_code left outer join pta pta17_ on pta13_.pta_pta_code=pta17_.pta_code left outer join secteur secteur18_ on pta17_.sect_code=secteur18_.sect_code left outer join structure structure19_ on pta17_.struct_code=structure19_.struct_code left outer join niveau_structure niveaustru20_ on structure19_.niv_struct_code=niveaustru20_.niv_struct_code left outer join structure structure21_ on structure19_.str_struct_code=structure21_.struct_code left outer join type_projet typeprojet22_ on pta17_.typ_proj_code=typeprojet22_.typ_proj_code where programmes0_.struct_code=?

Tags:

共 (2) 个答案

# 1 楼答案

尝试向Utilisateur实体类的Structure对象上的@ManyToOne添加属性fetch=FetchType.LAZY

因为ManyToOne关系被急切地加载。我认为它试图在新的SELECT语句中加载Structure实体。这将导致进一步加载具有inStructure的实体

因此，在Structure上添加fetch=FetchType.LAZY应该可以防止Structure的初始SELECT查询，从而防止Structure实体中映射的后续实体

更新

在上面的语句中，假设在获取Utilisateur对象时不想填充Structure对象。如果不是这样，那么可以将FetchType.LAZY进一步向下移动关联链。假设您想要获取Structure，但不是它的相关实体，如parent等，那么您可以将FetchType.LAZY移动到这些注释（ManyToOne等）

请注意，对于HQL，您使用的是不带连接的from Utilisateur where deleted is null or deleted <> 1，默认情况下，它将触发一个新的SELECT查询，以获取默认情况下的那些惰性关联。如果您想使用单个（或更少数量的）查询获取连接，则需要更新HQL以使用JOIN FETCH查询

我们经常遇到的一个常见问题是，我们不想获取关联，但在toString方法的某个地方，我们导航关联，开始打印它们，并导致LazyInitializationException

# 2 楼答案

我也有同样的问题。我在用SqlResultSetMapping

SqlResultSetMapping的要点是为NamedNativeQuery定义自定义映射

使用SqlResultSetMapping，您可以使用：

^{}将查询结果映射到实体对象
^{}使用构造函数将查询结果映射到“非实体”对象
^{}

ConstructorResult示例

假设我们有一个非常复杂的实体ComplexObject，它与其他对象有很多关系

@Entity
@Table(name = "complex_object")
public class ComplexObject {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id_complex_object")
    private Integer id;

    @Column(name = "label")
    private String label;

    // More relations...

}

我想要一个只检索这个实体的id和标签的查询

在ComplexObject中，我定义了一个新的NamedNativeQuery，如下所示

@NamedNativeQueries({
        @NamedNativeQuery(name = "ComplexObject.getIdAndLabel", query = "SELECT co.id_complex_object, co.label FROM complex_object", resultSetMapping = "SimpleObject")
})

这个NamedNativeQuery的重要部分是resultSetMapping = "SimpleObject"

然后我可以定义一个非实体的SimpleObject，并按如下方式匹配我的查询：

public class SimpleObject {

    private Integer id;
    private String label;

    /**
     * This constructor is very important !
     * Its signature has to match the SqlResultSetMapping defined in the entity class.
     * Otherwise, an exception will occur.
     */
    public SimpleObject(Integer id, String label) {
        this.id = id;
        this.label = label;
    }

    // Getters and setters...
}

然后我可以在ComplexObject中定义SqlResultSetMapping，如下所示：

@SqlResultSetMappings({
    @SqlResultSetMapping(name = "SimpleObject", classes = {
        @ConstructorResult(targetClass = SimpleObject.class, columns = {
            @ColumnResult(name = "id_complex_object", type = Integer.class),
            @ColumnResult(name = "label", type = String.class)
        })
    })
})

完成了

NamedNativeQuery将使用SimpleObject{}构造一个SimpleObject（通过构造函数），因此您的查询将返回一个SimpleObject，而不是ComplexObject

Python中文网

有 Java 编程相关的问题?

java如何使用Hibernate避免不必要的查询？

共 (2) 个答案

# 1 楼答案

# 2 楼答案

ConstructorResult示例