# Criteria api and pagination

**URL:** https://discourse.hibernate.org/t/criteria-api-and-pagination/847
**Category:** Hibernate ORM
**Created:** [May 26, 2018, 5:33pm UTC](https://discourse.hibernate.org/t/criteria-api-and-pagination/847 "2018-05-26T17:33:11Z")
**Posts on this page:** 3
**Page:** 1

<div class="post-metadata">

### Author: ![masber](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/masber/32/132_2.png) [@masber](https://discourse.hibernate.org/u/masber)
#### Post date: [May 26, 2018, 5:33pm UTC](https://discourse.hibernate.org/t/criteria-api-and-pagination/847/1 "2018-05-26T17:33:11Z")

</div>

Dear hibernate community,

I have a criteria api query which runs slow (few seconds to fetch results) although I am using pagination to make the query faster.

This is my query:

```auto
CriteriaBuilder builder = em.getCriteriaBuilder();

CriteriaQuery<Ereturn> criteria = builder.createQuery( Ereturn.class );

Root<Ereturn> er = criteria.from(Ereturn.class);
Fetch<Ereturn, User> shipperFetch = er.fetch("shipper");
Fetch<Ereturn, User> carrierFetch = er.fetch("carrier");
Fetch<Ereturn, User> consigneeFetch = er.fetch("consignee");
Fetch<Ereturn, User> destinationFetch = er.fetch("destination");
Fetch<Ereturn, ProductItem> productItemFetch = er.fetch("productItems", JoinType.LEFT);
Fetch<ProductItem, Receptacle> receptacleFetch = productItemFetch.fetch("receptacle", JoinType.LEFT);
Fetch<ProductItem, ProductDefinition> productDefinitionFetch = productItemFetch.fetch("product", JoinType.LEFT);

criteria.select( er );

if (disabled == null) {
	disabled = false;
}

List<Predicate> predicates = new ArrayList<>();

predicates.add(builder.equal(er.get( "disabled" ), disabled));

if (filter.getGlobalId() != null) {
	predicates.add(builder.equal(er.get( "globalId" ), filter.getGlobalId()));
}
		
if (filter.getShipperId() != null) {
	predicates.add(builder.equal(er.get( "shipper" ), filter.getShipperId()));
}

criteria.where(
	builder.and(predicates.toArray(new Predicate[predicates.size()]))
);

List<Ereturn> ers = em.createQuery( criteria )
	.setFirstResult(first)
	.setMaxResults(window)
	.getResultList();

```

And this is the query generated:

```auto
   select
        ereturn0_.id as id1_1_0_,
        user1_.id as id1_9_1_,
        user2_.id as id1_9_2_,
        user3_.id as id1_9_3_,
        user4_.id as id1_9_4_,
        productite5_.id as id1_7_5_,
        receptacle6_.id as id1_8_6_,
        productdef7_.id as id1_6_7_,
        ereturn0_.barcode as barcode2_1_0_,
        ereturn0_.carrier as carrier27_1_0_,
        ereturn0_.consignee as consign28_1_0_,
        ereturn0_.consigneeFirstName as consigne3_1_0_,
        ereturn0_.consigneeLastName as consigne4_1_0_,
        ereturn0_.creationtime as creation5_1_0_,
        ereturn0_.destination as destina29_1_0_,
        ereturn0_.disabled as disabled6_1_0_,
        ereturn0_.dispatchedDate as dispatch7_1_0_,
        ereturn0_.failedReturnPOBoxPrivateBag as failedRe8_1_0_,
        ereturn0_.globalCondition as globalCo9_1_0_,
        ereturn0_.globalId as globalI10_1_0_,
        ereturn0_.groupName as groupNa11_1_0_,
        ereturn0_.invoice as invoice12_1_0_,
        ereturn0_.notes as notes13_1_0_,
        ereturn0_.pickupDateTime as pickupD14_1_0_,
        ereturn0_.pickupDateTimeOffset as pickupD15_1_0_,
        ereturn0_.pieces as pieces16_1_0_,
        ereturn0_.processedByShipper as process17_1_0_,
        ereturn0_.reasonToReturn as reasonT18_1_0_,
        ereturn0_.returnAction as returnA19_1_0_,
        ereturn0_.returnMethod as returnM20_1_0_,
        ereturn0_.returned as returne21_1_0_,
        ereturn0_.rma as rma22_1_0_,
        ereturn0_.scanDateTime as scanDat23_1_0_,
        ereturn0_.shipper as shipper30_1_0_,
        ereturn0_.status as status24_1_0_,
        ereturn0_.trackingNumber as trackin25_1_0_,
        ereturn0_.weight as weight26_1_0_,
        user1_.accName as accName2_9_1_,
        user1_.accNum as accNum3_9_1_,
        user1_.address as address4_9_1_,
        user1_.address2 as address5_9_1_,
        user1_.addressId as addressI6_9_1_,
        user1_.addressId2 as addressI7_9_1_,
        user1_.bankName as bankName8_9_1_,
        user1_.branchName as branchNa9_9_1_,
        user1_.bsb as bsb10_9_1_,
        user1_.carrierCustomerInternalId as carrier11_9_1_,
        user1_.city as city12_9_1_,
        user1_.city2 as city13_9_1_,
        user1_.contactFirstName as contact14_9_1_,
        user1_.contactLastName as contact15_9_1_,
        user1_.contactNumber as contact16_9_1_,
        user1_.contactNumber2 as contact17_9_1_,
        user1_.country as country18_9_1_,
        user1_.country2 as country19_9_1_,
        user1_.disabled as disable20_9_1_,
        user1_.email as email21_9_1_,
        user1_.firstName as firstNa22_9_1_,
        user1_.importEnabled as importE23_9_1_,
        user1_.labelApiUrl as labelAp24_9_1_,
        user1_.lastName as lastNam25_9_1_,
        user1_.manager as manager39_9_1_,
        user1_.notes as notes26_9_1_,
        user1_.password as passwor27_9_1_,
        user1_.pickupApiUrl as pickupA28_9_1_,
        user1_.pobox as pobox29_9_1_,
        user1_.pobox2 as pobox30_9_1_,
        user1_.postalcode as postalc31_9_1_,
        user1_.postalcode2 as postalc32_9_1_,
        user1_.role as role33_9_1_,
        user1_.secondContactNumber as secondC34_9_1_,
        user1_.state as state35_9_1_,
        user1_.state2 as state36_9_1_,
        user1_.type as type37_9_1_,
        user1_.web as web38_9_1_,
        user2_.accName as accName2_9_2_,
        user2_.accNum as accNum3_9_2_,
        user2_.address as address4_9_2_,
        user2_.address2 as address5_9_2_,
        user2_.addressId as addressI6_9_2_,
        user2_.addressId2 as addressI7_9_2_,
        user2_.bankName as bankName8_9_2_,
        user2_.branchName as branchNa9_9_2_,
        user2_.bsb as bsb10_9_2_,
        user2_.carrierCustomerInternalId as carrier11_9_2_,
        user2_.city as city12_9_2_,
        user2_.city2 as city13_9_2_,
        user2_.contactFirstName as contact14_9_2_,
        user2_.contactLastName as contact15_9_2_,
        user2_.contactNumber as contact16_9_2_,
        user2_.contactNumber2 as contact17_9_2_,
        user2_.country as country18_9_2_,
        user2_.country2 as country19_9_2_,
        user2_.disabled as disable20_9_2_,
        user2_.email as email21_9_2_,
        user2_.firstName as firstNa22_9_2_,
        user2_.importEnabled as importE23_9_2_,
        user2_.labelApiUrl as labelAp24_9_2_,
        user2_.lastName as lastNam25_9_2_,
        user2_.manager as manager39_9_2_,
        user2_.notes as notes26_9_2_,
        user2_.password as passwor27_9_2_,
        user2_.pickupApiUrl as pickupA28_9_2_,
        user2_.pobox as pobox29_9_2_,
        user2_.pobox2 as pobox30_9_2_,
        user2_.postalcode as postalc31_9_2_,
        user2_.postalcode2 as postalc32_9_2_,
        user2_.role as role33_9_2_,
        user2_.secondContactNumber as secondC34_9_2_,
        user2_.state as state35_9_2_,
        user2_.state2 as state36_9_2_,
        user2_.type as type37_9_2_,
        user2_.web as web38_9_2_,
        user3_.accName as accName2_9_3_,
        user3_.accNum as accNum3_9_3_,
        user3_.address as address4_9_3_,
        user3_.address2 as address5_9_3_,
        user3_.addressId as addressI6_9_3_,
        user3_.addressId2 as addressI7_9_3_,
        user3_.bankName as bankName8_9_3_,
        user3_.branchName as branchNa9_9_3_,
        user3_.bsb as bsb10_9_3_,
        user3_.carrierCustomerInternalId as carrier11_9_3_,
        user3_.city as city12_9_3_,
        user3_.city2 as city13_9_3_,
        user3_.contactFirstName as contact14_9_3_,
        user3_.contactLastName as contact15_9_3_,
        user3_.contactNumber as contact16_9_3_,
        user3_.contactNumber2 as contact17_9_3_,
        user3_.country as country18_9_3_,
        user3_.country2 as country19_9_3_,
        user3_.disabled as disable20_9_3_,
        user3_.email as email21_9_3_,
        user3_.firstName as firstNa22_9_3_,
        user3_.importEnabled as importE23_9_3_,
        user3_.labelApiUrl as labelAp24_9_3_,
        user3_.lastName as lastNam25_9_3_,
        user3_.manager as manager39_9_3_,
        user3_.notes as notes26_9_3_,
        user3_.password as passwor27_9_3_,
        user3_.pickupApiUrl as pickupA28_9_3_,
        user3_.pobox as pobox29_9_3_,
        user3_.pobox2 as pobox30_9_3_,
        user3_.postalcode as postalc31_9_3_,
        user3_.postalcode2 as postalc32_9_3_,
        user3_.role as role33_9_3_,
        user3_.secondContactNumber as secondC34_9_3_,
        user3_.state as state35_9_3_,
        user3_.state2 as state36_9_3_,
        user3_.type as type37_9_3_,
        user3_.web as web38_9_3_,
        user4_.accName as accName2_9_4_,
        user4_.accNum as accNum3_9_4_,
        user4_.address as address4_9_4_,
        user4_.address2 as address5_9_4_,
        user4_.addressId as addressI6_9_4_,
        user4_.addressId2 as addressI7_9_4_,
        user4_.bankName as bankName8_9_4_,
        user4_.branchName as branchNa9_9_4_,
        user4_.bsb as bsb10_9_4_,
        user4_.carrierCustomerInternalId as carrier11_9_4_,
        user4_.city as city12_9_4_,
        user4_.city2 as city13_9_4_,
        user4_.contactFirstName as contact14_9_4_,
        user4_.contactLastName as contact15_9_4_,
        user4_.contactNumber as contact16_9_4_,
        user4_.contactNumber2 as contact17_9_4_,
        user4_.country as country18_9_4_,
        user4_.country2 as country19_9_4_,
        user4_.disabled as disable20_9_4_,
        user4_.email as email21_9_4_,
        user4_.firstName as firstNa22_9_4_,
        user4_.importEnabled as importE23_9_4_,
        user4_.labelApiUrl as labelAp24_9_4_,
        user4_.lastName as lastNam25_9_4_,
        user4_.manager as manager39_9_4_,
        user4_.notes as notes26_9_4_,
        user4_.password as passwor27_9_4_,
        user4_.pickupApiUrl as pickupA28_9_4_,
        user4_.pobox as pobox29_9_4_,
        user4_.pobox2 as pobox30_9_4_,
        user4_.postalcode as postalc31_9_4_,
        user4_.postalcode2 as postalc32_9_4_,
        user4_.role as role33_9_4_,
        user4_.secondContactNumber as secondC34_9_4_,
        user4_.state as state35_9_4_,
        user4_.state2 as state36_9_4_,
        user4_.type as type37_9_4_,
        user4_.web as web38_9_4_,
        productite5_.dangerousGood as dangerou2_7_5_,
        productite5_.disabled as disabled3_7_5_,
        productite5_.ereturn as ereturn19_7_5_,
        productite5_.expirationDate as expirati4_7_5_,
        productite5_.feedback as feedback5_7_5_,
        productite5_.forward as forward6_7_5_,
        productite5_.location as location7_7_5_,
        productite5_.lotNumber as lotNumbe8_7_5_,
        productite5_.lotReceivedDate as lotRecei9_7_5_,
        productite5_.lotStatus as lotStat10_7_5_,
        productite5_.notes as notes11_7_5_,
        productite5_.price as price12_7_5_,
        productite5_.processed as process13_7_5_,
        productite5_.product as product20_7_5_,
        productite5_.quantity as quantit14_7_5_,
        productite5_.quarantine as quarant15_7_5_,
        productite5_.receptacle as recepta21_7_5_,
        productite5_.reserve as reserve16_7_5_,
        productite5_.returnAction as returnA17_7_5_,
        productite5_.status as status18_7_5_,
        productite5_.ereturn as ereturn19_7_0__,
        productite5_.id as id1_7_0__,
        receptacle6_.closed as closed2_8_6_,
        receptacle6_.creationtime as creation3_8_6_,
        receptacle6_.disabled as disabled4_8_6_,
        receptacle6_.hawb as hawb5_8_6_,
        receptacle6_.height as height6_8_6_,
        receptacle6_.length as length7_8_6_,
        receptacle6_.masterCrossDock as masterC12_8_6_,
        receptacle6_.type as type8_8_6_,
        receptacle6_.volWeight as volWeigh9_8_6_,
        receptacle6_.weight as weight10_8_6_,
        receptacle6_.width as width11_8_6_,
        productdef7_.description as descript2_6_7_,
        productdef7_.disabled as disabled3_6_7_,
        productdef7_.height as height4_6_7_,
        productdef7_.hsCode as hsCode5_6_7_,
        productdef7_.length as length6_6_7_,
        productdef7_.productNumber as productN7_6_7_,
        productdef7_.serialNumber as serialNu8_6_7_,
        productdef7_.shipper as shipper12_6_7_,
        productdef7_.sku as sku9_6_7_,
        productdef7_.weight as weight10_6_7_,
        productdef7_.width as width11_6_7_ 
    from
        ereturn ereturn0_ 
    inner join
        user user1_ 
            on ereturn0_.shipper=user1_.id 
    inner join
        user user2_ 
            on ereturn0_.carrier=user2_.id 
    inner join
        user user3_ 
            on ereturn0_.consignee=user3_.id 
    inner join
        user user4_ 
            on ereturn0_.destination=user4_.id 
    left outer join
        product_item productite5_ 
            on ereturn0_.id=productite5_.ereturn 
    left outer join
        receptacle receptacle6_ 
            on productite5_.receptacle=receptacle6_.id 
    left outer join
        product_definition productdef7_ 
            on productite5_.product=productdef7_.id 
    where
        ereturn0_.disabled=? 
        and ereturn0_.shipper=148375 
        and ereturn0_.carrier=148380 
        and ereturn0_.carrier=148380 
        and ereturn0_.shipper=148375

```

When I run this query in my mysql console it returns thousands of rows instead of 16 (windows size)

QUESTION: Why my criteria api is ignoring the pagination?

thank you very much

---

<div class="post-metadata">

### Author: ![p3consulting](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/p3consulting/32/565_2.png) [@p3consulting](https://discourse.hibernate.org/u/p3consulting)
#### Post date: [May 26, 2018, 8:09pm UTC](https://discourse.hibernate.org/t/criteria-api-and-pagination/847/2 "2018-05-26T20:09:24Z")

</div>

The where clause contains criteria on “carrier” but I don’t see any predicate on this one in the Java code, are you sure the code corresponds to the query?

---

<div class="post-metadata">

### Author: ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)
#### Post date: [May 26, 2018, 8:27pm UTC](https://discourse.hibernate.org/t/criteria-api-and-pagination/847/3 "2018-05-26T20:27:45Z")

</div>

It’s very simple. When you join fetch, pagination will be applied in memory, not in SQL.

If you want a more efficient solution to this problem, you need to use [window functions  
 ](https://vladmihalcea.com/the-best-way-to-fix-the-hibernate-hhh000104-firstresultmaxresults-specified-with-collection-fetch-applying-in-memory-warning-message/).