1

I try to implement pagination using with postgresql and primefaces' lazy datagrid model.

I call my db in this primeface's lazymodel overrided function:

@Override
public List<Query> load(int first, int pageSize, String sortField, SortOrder sortOrder, Map<String,Object> filters) {
    queryDb.getQueries(settingsBean.getUserBean().getUser().getId(), pageSize, first); 
    setRowCount(queryDb.getTotal());   //total is total=rs.getRow();     (here is a problem I'll explain)   
    setPageSize(pageSize);
    //and goes on...
}

In QueryDb.java, My search string is like:

public List<Query> getQueries(Integer user_id, Integer pageSize, Integer first){
    String searchStr = "select * from public.saved_queries "
        + "where user_id=? "
        + "order by id desc "  
        + "limit ? offset ? "; 
    //and then
    statement.setInt(1, user_id);
    statement.setInt(2, pageSize); 
    statement.setInt(3, first);

And my connection is:

statement=connection.prepareStatement(searchStr, rs.TYPE_SCROLL_INSENSITIVE,rs.CONCUR_UPDATABLE);

And I also want to get all row counts of that table to set lazy datagrid's row count. 

rs.last();
total=rs.getRow();

But the problem is that total is equal to primefaces' pagesize. Because I set the limit as primafaces page size. I need to gel all row count there. How to get it? 

setRowCount(queryDb.getTotal()); // this equals my pagesize, I need all row count of that table
Hatem Alimam
  • 9,968
  • 4
  • 44
  • 56
rLyLmZ
  • 495
  • 4
  • 21
  • Execute a `SELECT COUNT(1) FROM ... WHERE ...` first, then execute your real query. – Luiggi Mendoza Nov 07 '14 at 14:24
  • You need to do another query where you select only the id with no limits and offsets and execute it only one time if it's null. – Hatem Alimam Nov 07 '14 at 14:24
  • On Big Data `COUNT` function cloud be very slow especially with Postgresql. It's better to select the ids and get the size of the List. It's tested on a DB with millions of records :) @LuiggiMendoza – Hatem Alimam Nov 07 '14 at 14:27
  • @HatemAlimam this depends on how you declared your queries. Also, PostgreSql performs different when executing `COUNT(*)` (slow) vs `COUNT(1)` (faster than you think), also assuming that you retrieve the count with no `GROUP BY` nor `ORDER BY` statements because in this case they're useless and slow the execution of the query. And I don't think transporting million of data through network is faster than performing and retrieving a single row with a single field in it (tested on my PostgreSql environments). – Luiggi Mendoza Nov 07 '14 at 14:30
  • You are right, my comment was on your comment before your made the edit which was `COUNT(*)` ... @LuiggiMendoza – Hatem Alimam Nov 07 '14 at 14:32
  • @HatemAlimam yes, I noticed this was for PostgreSql and updated the comment. – Luiggi Mendoza Nov 07 '14 at 14:33
  • Honestly back then I couldn't find a way to do `COUNT(1)` in criteria query... – Hatem Alimam Nov 07 '14 at 14:33
  • @HatemAlimam `SELECT COUNT(id)` or another single column maybe? – Luiggi Mendoza Nov 07 '14 at 14:35
  • I meant [JPA Criteria](http://blog.hatemalimam.com/jpa-criteria-and-jpql/) – Hatem Alimam Nov 07 '14 at 14:38
  • @LuiggiMendoza There is no difference between `COUNT(*)` and `COUNT(1)` on postgres. – Ihor Romanchenko Nov 07 '14 at 14:39
  • @IgorRomanchenko let's focus on OP's question. And for personal experience, using `COUNT(*)` was a bottleneck in our processes, we changed it to `COUNT(1)` and they become 2.5x faster. Probably newer versions of PostgreSql have changed how `COUNT` is performed. – Luiggi Mendoza Nov 07 '14 at 14:41
  • @HatemAlimam [In JPA 2, using a CriteriaQuery, how to count results](http://stackoverflow.com/q/2883887/1065197) – Luiggi Mendoza Nov 07 '14 at 14:43
  • Yes already tested this back then, but unfortunately the result was count(*).. anyway maybe this case was 1 year ago :) – Hatem Alimam Nov 07 '14 at 14:44
  • Thank you all for your informative debate. One of us should answer this question as there is no way without executing a new query =) Thanks. – rLyLmZ Nov 07 '14 at 16:12

0 Answers0