L'AMOUR TRIOMPHE TOUJOURS

"Don't Judge A Person By His Action, But By His Intention"

Saturday, April 26, 2014

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 :)

 

4 comments:

  1. Indian Cyber Army’s most awaited internship is live now. Rush to register for Summer Internship 2018 on “ Ethical hacking” and book your seats before it runs out.Candidates have to get themselves registered to be a part of this Internship program. As career in ethical hacking is most in demand.

    ReplyDelete
  2. Thanks for the information.It is really nice .Information security is the set of processes that maintain the confidentiality, integrity and availability of business data in its various forms.In this age of Technology advancement, computer and information technology have not only brought convenience to citizens in modern life but also for policemen & various Government officials of the nation to fight cybercrime through various modus operandi. Indian Cyber Army has been dedicated in fighting cyber crime, striving to maintain law and order in cyberspace so as to ensure that everyone remains digitally safe.Read more:- Information Security

    ReplyDelete
  3. Thanks for such important information.keep up the good work.Ethical Hacking training is based on current industry standards that helps attendees to secure placements in their dream jobs at MNCs. Indian Cyber Army Provides Best Ethical Hacking Training in India.Indian Cyber Army credibility in Ethical hacking training & Cybercrime investigation training is acknowledged across nation as we offer hands on practical knowledge and full assistance with basic as well as advanced level ethical hacking & cybercrime investigation courses

    ReplyDelete