Hibernate Criteria to take the first record

Question

I am trying to resolve a bug in my system, related to pagination. When a user selects a record, we retrieve all the associated records back to the user using pagination.

With the page maxResults set, the DB retrieves the max records but they are duplicated and returned to hibernate. Hibernate then retrieves the duplicates and returns the remaining records back to the UI. this causes inconsistency between the number of records returned vs page max size.

Using hibernate and criteria queries, this is the SQL that gets executed which retrieves all the associated records.

select  
    this_.id ,
    this_.created_date ,
    this_.is_reanalysis_user ,
    this_.library ,
    this_.state ,
    this_.compare_category_id ,
    this_.chipkit_id ,
    this_.librarykit_id ,
    this_.sequencingkit_id ,
    this_.templatekit_id ,
    this_.templateType ,
    ucc.id ,
    usrp.user_id ,
    usrp.run_parameter_id ,
    user1.id ,
    user1.templateType ,
    utt.VALUE ,
    urp.id ,
    uki.id as id1_214_5_,
    
    uki.unique_name ,
    uki1.id ,
    uki1.unique_name ,
    ukart.KIT_ID ,
    uart.RUN_TYPE ,
    uart.RUN_TYPE ,
    uki2.id ,
    uki2.unique_name ,
    uki3.id ,
    uki3.unique_name ,
    utt.VALUE ,
from
    USER this_ 
left outer join
    USER_compare_category ucc 
        on this_.compare_category_id=ucc.id 
inner join
    USER_SAVED_RUN_PARAMETER usrp 
        on this_.id=usrp.user_id 
left outer join
    USER user1 
        on usrp.user_id=user1.id 
left outer join
    USER_TEMPLATE_TYPES utt 
        on user1.templateType=utt.VALUE 
left outer join
    USER_RUN_PARAMETER urp 
        on usrp.run_parameter_id=urp.id 
inner join
    USER_KITINFO uki 
        on this_.chipkit_id=uki.id 
inner join
    USER_KITINFO uki1 
        on this_.librarykit_id=uki1.id 
inner join
    USER_KIT_ALLOWED_RUN_TYPES ukart 
        on uki1.id=ukart.KIT_ID 
inner join
    USER_ALLOWED_RUN_TYPES uart 
        on ukart.ALLOWED_RUN_TYPE=uart.RUN_TYPE 
inner join
    USER_KITINFO uki2 
        on this_.sequencingkit_id=uki2.id 
inner join
    USER_KITINFO uki3 
        on this_.templatekit_id=uki3.id 
inner join
    USER_TEMPLATE_TYPES utt 
        on this_.templateType=utt.VALUE 
where
    utt.VALUE in (
       'custom', 'install_seq'
    ) 
    and this_.is_reanalysis_user=false
    and this_.state in (
        'Locked', 'Draft'
    ) 
    and utt.VALUE<>'install_seq' 
    and uki.unique_name='Chip-Chock'
    **and usrp.value='amplitude_1'**  
    and uart.RUN_TYPE in (
        'sample'
    ) 
    and uki1.unique_name='Blane Library Kit'
    and utt.VALUE<>'install_seq'
    and uki3.unique_name='Moon reagent Kit'
    and uki2.unique_name='Star Seq Kit'
order by
    this_.created_date desc 
    limit 5 offset 0

what i have observed is that USER WITH USER_SAVED_RUN_PARAMETER has @OneToMany relationship and thus i see duplicates, I need to somehow add a condition usrp.value='amplitude_1' to select just the first record from this table.

Is there a way to do that? first, i was hoping I can try in SQL and then hibernate criteria query.

score 0 · Answer 1 · answered Oct 04 '20 at 09:50

I don't know how you construct your query but using setFirstResult and setMaxResults when doing join fetches of collections is going to lead to problems. Usually, Hibernate does not apply the limit to the query in such a case because that would lead to the collection not being fetched properly. So Hibernate would fetch everything and do in-memory pagination which is very inefficient.

If you use join fetching, you are out of luck. JPA demands that the state of managed entities and the database must be in sync at the end of a transaction, so fetching only a partial collection of an entity would essentially delete the non-fetched elements, which is why JPA does not allow this. You can however do it with Hibernate but I would not recommend it because it could lead to deletions.

So to get what you want, you need to write a HQL or JPA Criteria query and fetch data into DTOs. In the query, you select all fields you need and can define the join condition you mentioned. If you still want to fetch collections, there are other ways to implement pagination. Blaze-Persistence provides a simple API with support for efficient keyset pagination: https://persistence.blazebit.com/documentation/core/manual/en_US/index.html#pagination

Regarding the DTOs, I think this is a perfect use case for Blaze-Persistence Entity Views.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(User.class)
public interface UserDto {
    @IdMapping
    Long getId();
    Instant getCreatedDate();
    @Mapping("savedRunParameters[value = 'aplitude_1']")
    RunParameterDto getRunParameter();

    @EntityView(RunParameter.class)
    interface RunParameterDto {
        @IdMapping
        Long getId();
    }

    // Other mappings
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

UserDto a = entityViewManager.find(entityManager, UserDto.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

Hibernate Criteria to take the first record

1 Answers1