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

Issue

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?

Solution

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)

Answered By - cerdoc
Answer Checked By - Terry (JavaFixing Volunteer)