Pagination Using Query DSL Native Qs

Pagination  using Query DSL

Recently i got my self into a situation while using  hibernate generated query for paginating  results from database. I was using Query DSL with Spring Data JPA  and Sql Server 2012 database. I implemented pagination using offset and limit methods provided by dsl and it all seemed to be working perfectly until i had a look at the generated queries. It was fine for first page but for the subsequent pages, generated query was using top keyword bringing all the records in to memory and then giving the set of required data filtering those records. These thing were being done by hibernate behind the scene off course but again the purpose of server side pagination  was lost. So we used Native Sql queries generated by dsl and i am documenting it here for myself and any one else looking for this solution.

We were using maven as build tool for this project so lets start with adding the required maven dependencies  and pluggin in the pom.xml

<dependency>
  <groupId>com.mysema.querydsl</groupId>
  <artifactId>querydsl-sql</artifactId>
  <version>${querydsl.version}</version>
</dependency>    
      
<dependency>
  <groupId>com.mysema.querydsl</groupId>
  <artifactId>querydsl-sql-codegen</artifactId>
  <version>${querydsl.version}</version>
  <scope>provided</scope>
</dependency>

<plugin>

  <groupId>com.mysema.querydsl</groupId>

  <artifactId>querydsl-maven-plugin</artifactId>

  <version>${project.version}</version>

  <executions>

    <execution>

      <goals>

        <goal>export</goal>

      </goals>

    </execution>           

  </executions>

  <configuration>

    <jdbcDriver>your datbase driver class name</jdbcDriver>

    <jdbcUrl>your datbase url</jdbcUrl>

    <packageName>com.mycompany.mydomain</packageName>

    <targetFolder>target/generated-sources/java</targetFolder>   

    <jdbcUser>.....</jdbcUser>
    <jdbcPassword>.... </jdbcPassword>
    <tableNamePattern>comma separated table names</tableNamePattern>   

  </configuration>

  <dependencies>

   <dependency>
    <groupId>net.sourceforge.jtds</groupId>
    <artifactId>jtds</artifactId>
    <version>1.2</version>
   </dependency>

  </dependencies>

</plugin>

** use query dsl version 3.1 or greater because Q classes generation restiction 

   to the tables provided by comma separated table names are not supported before 

   Query dsl 3.1

Run goal clean install to generate Q classes for your tables.Q Classes will be

generated in target folder mentioned in above configuration.

Now use this code to create SQl query object and use availabe Dsl api to write

pagination query for ex:

                SQLTemplates dialect = new SQLServer2012Templates();
                SQLQuery query = new SQLQueryImpl(Connection, dialect); 

build your query using this SQl query object and call offset and limit on it to

generate paginated query which u will execute using list method of dsl api. 

query should be something like

               Query.distinct().offSet(offSet).limit(pageSize);

               Query.list(expr);   

Logic for calculating offset:

Count mrthod of dsl will give total no of records for your query,dividing it by 

pageSize will give the total no of pages,remember to ceil the division result to

get correct no of total pages. Use these data to calculate the off set for each

request.

**** Combination of distinct offset and limit doesnot work for SQl SErver 2008 

     and below as offset is implemented using RowNumber ranking function which needs

     to have order by clause.Calling distinct on the resultant query doesnt generate

     correct SQl for sql server.Yo will not get distinct records :(

Hope dis helps :)