JDBC Pagination

Question

I want to implement pagination using JDBC. The actual thing I want to know is "How can i get first 50 and then next 50 records from database for page 1 and 2 respectively"

My Query is Select * from data [data table contains 20,000 rows]

For page #1 I get 50 records and for page #2 I want to get next 50 records. How can I implement it efficiently in JDBC?

I have searched and found that rs.absolute(row) is the way to skip first page records but it takes some amount of time on large result sets and I don't want to bear this amount of time. Also, I don't want to use rownum and limit + offset in query because these are not good to use in query, I dont know why, still I don't want to use it in query.

Can anyone help me how to get limited ResultSet for pagination or is there any way JDBC is giving us?

Answer 1

There is no efficient way of doing this by simply using JDBC. You have to formulate the limit to n rows and start from i-th item clauses directly to the SQL for it to be efficient. Depending on the database this might actually be quite easy (see MySQL's LIMIT -keyword), on other databases such as Oracle it can be a little trickier (involves subquery and using rownum pseudo column).

See this JDBC Pagination Tutorial: http://java.avdiel.com/Tutorials/JDBCPaging.html

Answer 2

You should query only the data you actually need to display on the current page. Do not haul the entire dataset into Java's memory and then filter it there. It would only make things unnecessarily slower.

If you actually have a hard time in implementing this properly and/or figuring the SQL query for the specific database, then have a look at my answer here.

Update: since you're using Oracle, here's an Oracle-targeted extract from the aforementioned answer:

In Oracle you need a subquery with rownum clause which should look like:

private static final String SQL_SUBLIST = "SELECT id, username, job, place FROM"
    + " (SELECT id, username, job, place FROM contact ORDER BY id)"
    + " WHERE ROWNUM BETWEEN %d AND %d";

public List<Contact> list(int firstrow, int rowcount) {
    String sql = String.format(SQL_SUBLIST, firstrow, firstrow + rowcount);

    // Implement JDBC.
    return contacts;
}

Answer 3

Disclaimer: This blog post on SQL pagination & JDBC pagination is posted by me.

Disregarding Hibernate pagination, we can use SQL pagination / JDBC pagination

SQL pagination

There are two basic approaches:

1. operating on piecemeal result set (New Query for Each Page)
2. operating on full result set

The way to do it is SQL specific

For MySQL / many other SQLs it can be done with limit and offset

Postgresql: http://microjet.ath.cx/WebWiki/ResultPaginationWithPostgresql.html

In Oracle, it use the same form as to handle "Top-N query" e.g. who are the 5 highest paid employee, which is optimized

select *   from ( select a.*, rownum rnum

from ( YOUR_QUERY_GOES_HERE -- including the order by ) a

where rownum <= MAX_ROWS )

where rnum >= MIN_ROWS

Here is a very detailed explanation on ROW-NUM

Similar SO Thread

JDBC Pagination

The question comes into mind is: when I execute the SQL, how is the result being loaded? Immediately or on request? same as this SO thread

First we need to understand some basics of JDBC, as from Oracle

Per javadoc: statement.execute()

execute: Returns true if the first object that the query returns is a ResultSet object. Use this method if the query could return one or more ResultSet objects. Retrieve the ResultSet objects returned from the query by repeatedly calling Statement.getResutSet.

We access data in Resultset via a cursor. Note this cursor is different from that of DB while it is a pointer initially positioned before the first row of data.

The data is fetch on request. while when you do the execute() you are fetching for the first time.

Then, how many data is loaded? It is configurable. One can use the java API setFetchSize() method on ResultSet to control how many rows are fetched from DB a time by the driver, how big the blocks it retrieves at once.

For example assume the total result is 1000. If fetch size is 100, fetching the 1st row will load 100 rows from DB and 2nd to 100th row will be loaded from local memory.to query 101st row another 100 rows will be load into memory.

From JavaDoc

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects genrated by this Statement. If the value specified is zero, then the hint is ignored. The default value is zero.

Note the word "hint" - it can be override by driver specific implementation.

This is also what the "Limit Rows to 100" feature in client like SQL developer based on.

Completing the whole solution, to scroll results, one need to consider the ResultSet Types and ScrollableCursor in API

One can find an example implementation from this post in oracle

which is from the book Oracle Toplink Developer's Guide Example 112 JDBC Driver Fetch Size

ReadAllQuery query = new ReadAllQuery();

query.setReferenceClass(Employee.class);

query.setSelectionCriteria(new ExpressionBuilder.get("id").greaterThan(100));

// Set the JDBC fetch size

query.setFetchSize(50);

// Configure the query to return results as a ScrollableCursor

query.useScrollableCursor();

// Execute the query

ScrollableCursor cursor = (ScrollableCursor) session.executeQuery(query);

// Iterate over the results

while (cursor.hasNext()) {

System.out.println(cursor.next().toString());

}

cursor.close();

.....................

After all, the questions boil to

Which is the better way to do pagination?

Note the SQL should be ORDER by to make sense in the SQL approach,

Otherwise it is possible to show some rows again in next page.

Below is some points from Postgresql's documentation on JDBC Driver and other SO answers

First off, the original query would need to have an ORDER BY clause in order to make the paging solution work reasonably. Otherwise, it would be perfectly valid for Oracle to return the same 500 rows for the first page, the second page, and the Nth page

The major difference is for the JDBC way, it is required to hold the connection during the fetching. This may not be suitable in stateless web application, for example.

For SQL way

the syntax is SQL specific and may not be easy to maintain. For JDBC way

• The connection to the server must be using the V3 protocol. This is the default for (and is only supported by) server versions 7.4 and later.
• The Connection must not be in autocommit mode. The backend closes cursors at the end of transactions, so in autocommit mode the backend will have closed the cursor before anything can be fetched from it.
• The Statement must be created with a ResultSet type of ResultSet.TYPE_FORWARD_ONLY. This is the default, so no code will need to be rewritten to take advantage of this, but it also means that you cannot scroll backwards or otherwise jump around in the ResultSet.
• The query given must be a single statement, not multiple statements strung together with semicolons.

Some Further reading

This post is about performance tuning with optical fetch size

Answer 4

I know this question is old, but this is how I implemented paging, I hope it helps someone

int pageNo = ....;    
String query = "SELECT * FROM data LIMIT ";

switch (pageNo) {
case 0: // no pagination, get all rows
    query += Integer.MAX_VALUE; // a big value to get all rows
    break;
case 1: // get 1st page i.e 50 rows
    query += 50;
    break;
default:
    query += String.valueOf((pageNo-1)*50 + 1) ", " + String.valueOf(50);
    break;
}

PreparedStatement ps = connection.prepareStatement(query);
....

make the value 50 a constant named pageSize, so it can be changed to any number

Answer 5

If you are using MySQL or PostgreSQL limit and offset are your keywords. MSSqlServer and Oracle have similar features, but I seems to be a bit more painful.

For MySQL and PostgreSQL have a look here:

http://www.petefreitag.com/item/451.cfm

For Oracle have a look here:

http://www.oracle-base.com/forums/viewtopic.php?f=2&t=8635

Answer 6

Are you using some kind of ORM Framework like hibernate or even Java Persistence API or just plain SQL?

My Answer then: use LIMIT and OFFSET http://www.petefreitag.com/item/451.cfm

Or go via ROWNUM Operator You need a wrapper arround your SQL then, but basicaly it's

  select * from (select bla.*, ROWNUM rn from (
  <your sql here>
  ) bla where rownum < 200) where rn >= 150'

Answer 7

This is link to a hibernate solution for paginating results: HQL - row identifier for pagination

Answer 8

Input:

1. Order Information example (A2 or D3) (A/D ascending/descending) + column
2. Order Information example (A2 or D3) (A/D ascending/descending) + column
3. Filter value
4. start row
5. start row
6. maximum nuber of rows

Result:

• Selected values
• Selected page
• Index of the row in this ordering
• Count off available data. (Save an second query)

Advantage only Query for:

• sum of available columns with this filter
• only transfer the selected page from db
• correctly ordered without dynamic sql

Disadvantage:

• Oracle Dependend

  select x.* from ( select c.pk_field,c.numeric_a, c.char_b, c.char_c ROW_NUMBER( ) over(ORDER BY decode(?,'A1',to_char(c.numeric_a,'FM00000000'),'A2',c.char_b,'A3',c.char_c,'A') asc , decode(?,'D1',to_char(c.numeric_a,'FM00000000'),'D2',c.char_b,'D3',c.char_c,'A') desc, c.pk_field asc
  ) AS "idx", COUNT (*) OVER (ORDER BY 1) "cnt" from myTable c where c.haystack=? ) x where x."idx" between greatest(nvl(?,1),1) and nvl(?,1)-1+?

Answer 9

I understand implicitly that you do not want the JDBC connection to have a single gigantic resultset which you keep open for a very long time and navigate when required.

The usual approach is to add the SQL needed to only get a subset of the full request, which unfortunately is different from database to database, and will make your SQL statements vendor specific. If I recall correctly, LIMIT is used with MySQL. Ask for the appropriate range for every request.

I also believe that Hibernate contains functionality which allows you to do this for HQL, but I am unfamiliar with it.

Answer 10

Oracle supports the standard ROW_NUMBER() window function since 8i so you can use that. You can do it as a parameterized query so you just need to set the start and end row numbers. E.g.

SELECT * 
   FROM ( SELECT *, ROW_NUMBER() ORDER BY (sort key) AS rowNumber FROM <your table name> ) AS data
WHERE 
   rowNumber>=:start AND
   rowNumber<:end

(If you're not using named parameters, replace :start/:end with the positional parameter placeholder '?')

See SELECT SQL, Window Functions on wikipedia. The article also lists the other DBs that support the ROW_NUMBER() standard windowing function.

Answer 11

PreparedStatement pStmt = // ... however you make it
pStmt.setFetchSize( /* desired number of records to get into memory */ ); 

Note, setFetchSize(int) is only a hint - last time I was using it with MySQL for example, it wasn't supported. Looking around briefly at the Oracle documentation, it looks like their JDBC does support it. I wouldn't quote me on that, but it's worth trying at least; and yes, this answer is fragile, but it might be enough less headache than implementing the robust solution.

Essentially, you can issue the request for everything, and you only get the fetch size into memory at a time (providing you're not holding onto the previous results). So you'd set your fetch size to 50, make your connection/query, display the first 50 results (causing the another fetch for the next bite of your query) and so on.

Answer 12

You can use below method for this.

1. Here rowNo is the number of the starting row from which you want to fetch n number of records.
2. pageSize is the total number of records you want to fetch

     public CachedRowSet getScrollableList(PreparedStatement prestmt, int rowNo)
                {   
                    getPageSize();
                    ResultSet rs = null;
                    CachedRowSet crs = null;
                    try
                    {           
                        rs = prestmt.executeQuery();
                        crs = new CachedRowSetImpl();
                        crs.setPageSize(pageSize);
                        crs.populate(rs, rowNo);
                    }
                    catch (SQLException ex)
                    {
                        logger.error("Exception in DatabaseInterface in getScrollableList() method: "+ ex.getMessage(),ex);
                    }   
                    finally
                    {
                        //close rs
                        //close pstmt
                    }
                    return crs;
                }



 

Answer 13

The following java codes works well:

package paginationSample;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @ Nwaeze Emmanuel (FUNAI, 2016)
 */
public class PaginationSample extends javax.swing.JFrame {  
public void getRows() {
Connection con2;
Statement stmt2;
ResultSet rs2;
int j=0;
String sz="";

  try {
// Load MS accces driver class
 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 
String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, 
  *.accdb)};DBQ=" + "C:\\Database2.mdb"; 
try{
con2 = DriverManager.getConnection(url, "user", "");
System.out.println("Connection Succesfull");

 try{
   stmt2=con2.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
   ResultSet.CONCUR_UPDATABLE );
   String sql="";

   if (txtpage.getText().trim().equals("") || 
      txtpagesize.getText().trim().equals("")){

    }else{
     int pagesize=Integer.parseInt(txtpagesize.getText());   
     int page=Integer.parseInt(txtpage.getText());
     sql="SELECT * FROM FSRegistration100L WHERE SN >= " +  
    (pagesize*page-pagesize+1) + " AND " + "SN <= " + (pagesize*page);
     rs2=stmt2.executeQuery(sql);
     if (rs2.wasNull()){

     }else{
       while ( rs2.next()){
         sz=sz + rs2.getString("RegNo") + "\n";

          j++;  

        }
       txta.setText(sz);
       txta.append(" Total rows =" + Integer.toString(j));
    } 
    }
      stmt2.close();
      con2.close();
  }
  catch (NullPointerException s){
  System.err.println("Got an exception166! ");
  System.out.println(s.getMessage());
  } 
 } catch (SQLException e1) {
System.err.println("Got an exception1! ");
System.err.println(e1.getMessage());
}
} catch (ClassNotFoundException e2) {
System.err.println("Got an exception2! ");
System.err.println(e2.getMessage());

} 
}



    private void jButton1ActionPerformed(java.awt.event.ActionEvent 
       evt)     {                                         
       // TODO add your handling code here:
        getRows();
    }                                        

    // Variables declaration - do not modify                     
    private javax.swing.JButton jButton1;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JTextArea txta;
    private javax.swing.JTextField txtpage;
    private javax.swing.JTextField txtpagesize;
    // End of variables declaration