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

 

5 comments:

  1. 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
  2. It is very good and very informative. There is a useful information in it.Thanks for posting...
    CEH Training In Hyderabad

    ReplyDelete
  3. Great explanation of the blog posting I liked it

    Pressure Vessel Design Course is one of the courses offered by Sanjary Academy in Hyderabad. We have offer professional Engineering Course like Piping Design Course,QA/QC Course,document Controller course,pressure Vessel Design Course,Welding Inspector Course, Quality Management Course, Safety officer course.
    best welding inspector course in India
    best safety officer course
    best quality management course in India
    best welding inspector course in India
    best welding inspector course near me
    best safety officer course near me
    best safety officer coursein hyderabad
    best safety officer course in India
    best quality management course

    ReplyDelete
  4. Animachi is really hard to recognize since you took over the website - what you made of it and I really appreciate your commitment to it
    Brij University BCOM TimeTable 2020

    ReplyDelete
  5. Animachi is really hard to recognize since you took over the website - what you made of it and I really appreciate your commitment to it
    DAVV University BCOM TimeTable 2020

    ReplyDelete