Why Spring JPA Hibernate is fetching all the records individually by id?

Question

I am running following query using Spring JPA in my project. Its internally using Hibernate for connecting to MySql DB. This query is written inside JpaRepository.

@Query(value = "SELECT ipd.* FROM identifier_pool_definition ipd, identifier_definition id WHERE\n" +
            "ipd.definition_id = id.definition_id AND id.acquirer_id = :acquirerId AND" +
            " id.domain = :domain AND id.definition_type = 'pool' AND id.status IN :statuses  AND id.type = :poolType  AND id.is_deleted = false",
            nativeQuery = true)
List<IdentifierPoolDefinitionEntity> findAllWithPoolTypeAndStatuses(@Param("acquirerId") String processorId,
                                                                        @Param("domain") String domain,
                                                                        @Param("poolType") String poolType,
                                                                        @Param("statuses") Collection<String> statuses);

In the application logs I am observing that after running above query, Hibernate is making individual select DB calls by id for each record fetched in the above query.

Sample logs:

Hibernate: 
    SELECT
        ipd.* 
    FROM
        identifier_pool_definition ipd,
        identifier_definition id 
    WHERE
        ipd.definition_id = id.definition_id 
        AND id.acquirer_id = ? 
        AND id.domain = ? 
        AND id.definition_type = 'pool' 
        AND id.status IN (
            ?, ?, ?
        )  
        AND id.type = ?  
        AND id.is_deleted = false
Hibernate: 
    select
        identifier0_.definition_id as definiti1_2_0_,
        identifier0_.acquirer_id as acquirer2_2_0_,
        identifier0_.created as created3_2_0_,
        identifier0_.created_by as created_4_2_0_,
        identifier0_.definition_type as definiti5_2_0_,
        identifier0_.domain as domain6_2_0_,
        identifier0_.is_deleted as is_delet7_2_0_,
        identifier0_.merchant_id as merchant8_2_0_,
        identifier0_.processor_id as processo9_2_0_,
        identifier0_.status as status10_2_0_,
        identifier0_.type as type11_2_0_,
        identifier0_.updated as updated12_2_0_,
        identifier0_.updated_by as updated13_2_0_ 
    from
        identifier_definition identifier0_ 
    where
        identifier0_.definition_id=?
...
...
after n records
...
...
Hibernate: 
    select
        identifier0_.definition_id as definiti1_2_0_,
        identifier0_.acquirer_id as acquirer2_2_0_,
        identifier0_.created as created3_2_0_,
        identifier0_.created_by as created_4_2_0_,
        identifier0_.definition_type as definiti5_2_0_,
        identifier0_.domain as domain6_2_0_,
        identifier0_.is_deleted as is_delet7_2_0_,
        identifier0_.merchant_id as merchant8_2_0_,
        identifier0_.processor_id as processo9_2_0_,
        identifier0_.status as status10_2_0_,
        identifier0_.type as type11_2_0_,
        identifier0_.updated as updated12_2_0_,
        identifier0_.updated_by as updated13_2_0_ 
    from
        identifier_definition identifier0_ 
    where
        identifier0_.definition_id=?

Following are my entity classes:

IdentifierPoolDefinitionEntity.java

@Entity
@Table(name = "identifier_pool_definition")
@Getter
@Setter
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class IdentifierPoolDefinitionEntity implements Serializable {

    private static final long serialVersionUID = 518449602683891708L;

    @Id
    @Column(name = "definition_id", columnDefinition = "BINARY(16)")
    private UUID definitionId;

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

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

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

    @Column(name = "lower_bound")
    private Long lowerBound;

    @Column(name = "upper_bound")
    private Long upperBound;

    @Column(name = "`separator`")
    private String separator;

    @Column(name = "created")
    @CreationTimestamp
    @JsonSerialize(using = CustomLocalDateTimeSerializer.class)
    private LocalDateTime created;

    @Column(name = "created_by")
    private String createdBy;

    @Column(name = "updated")
    @JsonSerialize(using = CustomLocalDateTimeSerializer.class)
    private LocalDateTime updated;

    @Column(name = "updated_by")
    private String updatedBy;

    @JsonIgnore
    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @PrimaryKeyJoinColumn
    private IdentifierDefinitionEntity identifierDefinitionEntity;

}

IdentifierDefinitionEntity.java

@Entity
@Table(name = "identifier_definition")
@Getter
@Setter
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class IdentifierDefinitionEntity implements Serializable {

    private static final long serialVersionUID = 7809377866509417398L;

    @Id
    @Column(name = "definition_id", columnDefinition = "BINARY(16)")
    private UUID definitionId;

    @Column(name = "definition_type")
    private String definitionType;

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

    @Column(name = "acquirer_id")
    private String acquirerId;

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

    @Column(name = "processor_id")
    private String processorId;

    @Column(name = "merchant_id")
    private String merchantId;

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

    @Column(name = "is_deleted")
    private boolean isDeleted;

    @Column(name = "created")
    @CreationTimestamp
    @JsonSerialize(using = CustomLocalDateTimeSerializer.class)
    private LocalDateTime created;

    @Column(name = "created_by")
    private String createdBy;

    @Column(name = "updated")
    @JsonSerialize(using = CustomLocalDateTimeSerializer.class)
    private LocalDateTime updated;

    @Column(name = "updated_by")
    private String updatedBy;

    @JsonIgnore
    @OneToOne(mappedBy = "identifierDefinitionEntity", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    private IdentifierPoolDefinitionEntity identifierPoolDefinitionEntity;

    @JsonIgnore
    @OneToMany(mappedBy = "identifierDefinitionEntity", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    private Set<IdentifierListValuesEntity> listValues;

    @JsonIgnore
    @OneToMany(mappedBy = "identifierDefinitionEntity", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    private List<IdentifierAssignmentEntity> identifierAssignmentEntity;
}

Driver code:

val allPoolsForProcessor = identifierPoolDefinitionRepository.findAllWithPoolTypeAndStatuses(acquirerId, domain,
                listType.getValue(), Arrays.stream(PoolStatus.values())
                        .map(PoolStatus::getValue)
                        .collect(Collectors.toList()));

I want to understand why Hibernate is showing this behaviour? Is there a way to restrict the implicit DB calls done by Hibernate?

I am going to assume it has to do with the few toMany associations you have which are lazily fetched. I'm assuming you're requesting one of those and is causing subsequent queries to fire when requested. — Anthony Cathers, Jul 06 '22 at 20:23
@AnthonyCathers - That's the fun part. I am not making use of any of the associations after fetching DB results for the query that I am running. — Nilesh Barai, Jul 06 '22 at 20:46

score 1 · Accepted Answer · answered Jul 06 '22 at 22:15

I think it's something related to this topic : How can I make a JPA OneToOne relation lazy Try to make the relation explicitly not-nullable and lazy so that hibernate can knows if it can makes a proxy or have to get the real entity for the field identifierDefinitionEntity on IdentifierPoolDefinitionEntity :

@OneToOne(optional = false, fetch = FetchType.LAZY)

Why Spring JPA Hibernate is fetching all the records individually by id?

1 Answers1