Jdbc pagination

Question

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

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

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

I searched and found that rs.absolute(row) is a way to skip the first page entries, but large result sets require a certain amount of time, and I do not want to transfer this amount of time. In addition, I do not want to use rownum and limit + offset in the request, because they are not suitable for use in the request, I do not know why, but I do not want to use it in the request.

Can someone help me how to get a limited ResultSet for pagination or is there any way that JDBC gives us?

Answer 1

There is no effective way to do this simply using JDBC. You must formulate a limit for n rows and start with the sentences of the ith element directly in SQL so that it is efficient. Depending on the database, this can be quite simple (see MySQL LIMIT-keyword), in other databases, such as Oracle, it can be a little more complicated (includes a subquery and using the rownum pseudo-field).

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

Answer 2

You should request only the data that you really need to display on the current page. Do not drag the entire data set into Java memory and then filter it there. It would only make things unnecessarily slow.

If it is really difficult for you to do this correctly and / or calculate the SQL query for a specific database, see my answer .

Update: since you are using Oracle, here's an extract from Oracle derived from the above answer:

In Oracle, you need a subquery with a rownum that should look like this:

 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 about pagination and pagination JDBC posted by me.

Without considering Hibernate pagination, we can use pagination / pagination / JDBC

pagination <

There are two main approaches:

• working on a private result set (a new query for each page)
• works with a full set of results

The way to do this is SQL specific

For MySQL / many other SQL, this can be done using constraint and offset

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

In Oracle, it uses the same form as for processing a “Top-N request,” for example. who are the 5 highest paid employees who are 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 detailed description of ROW-NUM

Similar SO stream

JDBC Breakdown

The question is: when I execute SQL, how is the result loaded? Immediately or upon request? same as this SO stream

First we need to understand some of the basics of JDBC, like 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 the data in the Resultset using the cursor. Note that this cursor is different from what the DB has, while it is a pointer originally positioned before the first row of data.

Data is displayed on request. and when you execute execute (), you select for the first time.

Then how much data is downloaded? It is customizable. You can use the java API setFetchSize () method for ResultSet to control how many rows are retrieved from the DB in time by the driver, how large blocks it retrieves immediately.

For example, suppose the final result is 1000. If the sample size is 100, the first row will load 100 rows from the database, and 2 — the 100th row will be loaded from local memory. To request the 101st line, another 100 lines will be loaded 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. 

Pay attention to the word "hint" - it can be redefined by the specific driver implementation.

It is also that "Limit Rows to 100" functions as a client, such as an SQL developer.

Completing the entire solution, to scroll through the results, you need to consider the ResultSet and ScrollableCursor types in the API

You can find an example implementation from this post in oracle

which from the book Oracle Toplink Developer's Guide Example 112 JDBC Driver Sample 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, questions boil up

What is the best way to do pagination?

Note that SQL must be ORDER in order to make sense in the SQL approach,

Otherwise, you can show several lines on the next page.

Below are some points from the Postgresql documentation for the JDBC driver and other SO answers

Initially, the original query must have an ORDER BY clause to make the swap decision reasonable. Otherwise, it would be perfectly fair if Oracle returns the same 500 rows for the first page, second page and Nth page

The main difference is the JDBC method, connection is required during extraction. For example, this may not be suitable for an unnamed web application.

For SQL path

The SQL syntax is specific and can be difficult to maintain. For the JDBC path

• The connection to the server must be connected to the V3 protocol. This is the default for (and only supported) server versions 7.4 and later.
• The connection should not be in automatic mode. The backend closes the cursors at the end of transactions, so in autocommit mode, the backend will close the cursor before anything can be extracted from it.
• A statement must be created using the ResultSet type ResultSet.TYPE_FORWARD_ONLY. This is the default value, so the code will not need to be rewritten to take advantage of this, but it also means that you cannot scroll back or jump into the ResultSet otherwise.
• The given query should be one operator, and not several statements strung together with semicolons.

Some extra reading

This post is about tuning performance with optical sample size.

Answer 4

If you are using MySQL or PostgreSQL limit and offset are your keywords. MSSqlServer and Oracle have similar features, but it seems to me that this is a bit more painful.

For MySQL and PostgreSQL, see here:

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

For Oracle, see here:

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

Answer 5

This is a link to the hibernation solution for pagination: HQL - row identifier for pagination

Answer 6

I understand that you do not want the JDBC connection to have one gigantic set of results that you keep open for a very long time and move as needed.

The usual approach is to add the SQL required only to get a subset of the full query, which unfortunately differs from database to database, and will make your SQL spec specifiers specific. If I remember correctly, LIMIT is used with MySQL. Set the appropriate range for each query.

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

Answer 7

Are you using some kind of ORM platform 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 through the ROWNUM Operator. Then you need a wrapper around your SQL, but basically it

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

Answer 8

Oracle supports the standard window function ROW_NUMBER () with 8i, so you can use this. You can do this as a parameterized query, so you just need to set the start and end line numbers. For example.

 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 are not using named parameters, replace: start /: end with placeal parameter place ??)

See SELECT SQL, Window functions on Wikipedia. The article also lists other databases that support the standard window function ROW_NUMBER ().

Answer 9

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

Note. setFetchSize(int) is just a hint - the last time I used it with MySQL, it was not supported. Looking briefly at the Oracle documentation, it seems that their JDBC really supports it. I would not quote me about this, but at least it's worth a try. and yes, this answer is fragile, but there can be less headache than implementing a reliable solution.

Essentially, you can issue a request for everything, and you only get the sample size in memory at a time (provided that you are not holding onto previous results). Thus, you set the sample size to 50, make your connection / request, display the first 50 results (causing another sample for the next bite of your request), etc.

Answer 10

Entrance:

• Example order information (A2 or D3) (A / D ascending / descending) + column
• Example order information (A2 or D3) (A / D ascending / descending) + column
• Filter value
• start line
• start line
• The maximum number of lines

Result:

• Selected Values
• Selected Page
• Row index in that order
• Countdown of available data. (Save second query)

Advantage Only Request for:

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

Inconvenience:

• Oracle Dependend

  select x. * from (select c.pk_field, c.numeric_a, c.char_b, c.char_c ROW_NUMBER () (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 the largest (nvl (?, 1), 1) and nvl (?, 1) -1 +?

Answer 11

I know this question is old, but this is how I implemented the paging, hope this 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 ie 50 rows query += 50; break; default: query += String.valueOf((pageNo-1)*50 + 1) ", " + String.valueOf(50); break; } PreparedStatement ps = connection.prepareStatement(query); .... 

enter a value of 50 a constant called pageSize, so it can be changed to any number

Answer 12

The following java codes work 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