[Java][Hibernate] How to fetch limit of child dependency collection?

Question

I'm using Spring Boot with Hibernate and JPA.

I have three entities Device, ErrorType and ErrorRecord with relation ship One-to-many (see the figure below).

Now, when I get one device, I want to get its list of errorTypes. For each errorTypes, I want to get the ONLY ONE latest errorRecord if any.

Here is my current code:

ErrorType.java

@Entity
@Table("tbl_error_type")
class ErrorType {
    ...

    @OrderBy(value = "createAt DESC")    
    @OneToMany(mappedBy = "errorType", cascade = CascadeType.ALL)
    @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)    
    private Set<ErrorRecord> errors = new LinkedHashSet<>();

    ...
}

ErrorRecord.java

@Entity
@Table("tbl_error_record")
class ErrorRecord{
    ...

    @ManyToOne
    @JoinColumn(name = "error_type_id")
    private ErrorType errorType;

    ...
}

ErrorTypeRepository.java

@Query("SELECT DISTINCT e FROM ErrorType e LEFT JOIN FETCH e.errors WHERE e.device.id =:deviceId ORDER BY e.errorType")
List<ErrorType> findErrorTypesByDeviceIdEagerErrorRecords(@Param("robotId") UUID robotId);

ErrorTypeService.java

@Transactional(readOnly = true)
public List<ErrorTypeDTO> findAll(UUID deviceId) {
    return errorTypeRepository.findErrorTypesByDeviceIdEagerErrorRecords(deviceId).stream().map(errorType -> {
            ErrorTypeDTO dto = new ErrorTypeDTO();
            // Some getter and setter here
            if (errorType.getErrors() != null && !errorType.getErrors().isEmpty()) {
                dto.setLatestError(errorType.getErrors().get(0));
            } 
     }).collect(Collectors.toCollection(LinkedList::new));
}

Currently, it works well with data correctly. However, because data in ErrorRecord increases rapidly, so there could be up to millions of ErrorRecord for each ErrorType. In that case, the performance of this API is too slow and timeout.

Expected: I want to load only one records (order by createAt, limit 1) in the fetched child errors when load all errorType.

I could load for all ErrorType first, and loop through list of errorTypes. For each errorType, make a query to get one latest errorRecords. But it seems a bad performance way, which create a lot of query to database (n+1 problem).

After researching about Hibernate query expression, I still have no idea for limit number of records in child collections.

score 1 · Answer 1 · answered Sep 07 '19 at 23:06

Getting last error record is not a simple task, as can be seen from this answer. Mapping such a thing using JPA mappings is impossible to my best knowledge. This is much more appropriate for a specific operation, like findErrorTypesByDeviceIdEagerErrorRecords().

Also such queries are not possible to write in JPQL, only in native SQL. For instance, using FluentJPA, it will be something like:

FluentQuery query = FluentJPA.SQL((ErrorType errorType) -> {

    OrderedErrorRecord orderedRec = subQuery((ErrorRecord errorRec) -> {
        Alias<Number> rn = alias(aggregateBy(ROW_NUMBER())
                .OVER(PARTITION(BY(errorRec.getErrorType().getId()))
                .ORDER(BY(errorRec.getCreatedAt()).DESC())),
                OrderedErrorRecord::getRowNumber);

        SELECT(errorRec, rn);
        FROM(errorRec);
    });

    WITH(orderedRec);
    SELECT(errorType, orderedRec.getErrorDescription());
    FROM(errorType).LEFT_JOIN(orderedRec)
            .ON(orderedRec.getErrorType() == errorType && orderedRec.getRowNumber() == 1);
    WHERE(errorType.getDevice().getId() == deviceId);
});

return query.createQuery(em, ErrorTypeWithLastError.class).getResultList();

which produces the following SQL:

WITH q0 AS 
(SELECT t1.*,  ROW_NUMBER()  OVER(PARTITION BY  t1.error_type_id 
 ORDER BY  t1.created_at  DESC   ) AS row_number 
FROM tbl_error_record t1 )

SELECT t0.*, q0.error_description 
FROM tbl_error_type t0  LEFT JOIN q0  ON ((q0.error_type_id = t0.id) AND (q0.row_number = 1)) 
WHERE (t0.device_id = ?1)

Pay attention, that I SELECT() additional fields, not declared in ErrorType, therefore a DTO projection required to map them. Also we need OrderedErrorRecord to hold the rowNumber. So 2 additional classes were declared:

@Tuple
@Getter //lombok
public static class OrderedErrorRecord extends ErrorRecord {
    private int rowNumber;
}

@Tuple
@Getter //lombok
public static class ErrorTypeWithLastError extends ErrorType {
    @Embedded
    private ErrorContent errorContent;
}

[Java][Hibernate] How to fetch limit of child dependency collection?

1 Answers1