java sql jdbc pagination

java - Paginación JDBC



jstl core (13)

Quiero implementar la paginación usando JDBC. Lo que realmente quiero saber es "¿Cómo puedo obtener los primeros 50 y luego los siguientes 50 registros de la base de datos para la página 1 y 2 respectivamente"

Mi consulta es Select * from data [la tabla de datos contiene 20,000 filas]

Para la página n. ° 1 recibo 50 registros y para la página n. ° 2 quiero obtener los próximos 50 registros. ¿Cómo puedo implementarlo de manera eficiente en JDBC?

He buscado y he encontrado que rs.absolute(row) es la forma de omitir los registros de la primera página, pero lleva mucho tiempo en los grandes conjuntos de resultados y no quiero soportar esta cantidad de tiempo. Además, no quiero usar rownum y limit + offset en la consulta porque estos no son buenos para usar en la consulta, no sé por qué, aún así no quiero usarlo en la consulta.

¿Alguien puede ayudarme a obtener un ResultSet limitado para la paginación o hay alguna forma que JDBC nos brinde?

¿Estás utilizando algún tipo de framework ORM como hibernate o incluso Java Persistence API o simplemente SQL?

Mi respuesta: use LIMIT y OFFSET http://www.petefreitag.com/item/451.cfm

O ir a través de ROWNUM Operator Necesitas un contenedor alrededor de tu SQL, pero básicamente es

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

Comprendo implícitamente que no desea que la conexión JDBC tenga un único conjunto de resultados gigantesco que mantenga abierto durante mucho tiempo y navegue cuando sea necesario.

El enfoque habitual es agregar el SQL necesario para obtener solo un subconjunto de la solicitud completa, que desafortunadamente es diferente de la base de datos a la base de datos, y hará que sus declaraciones SQL sean específicas del proveedor. Si recuerdo correctamente, LIMIT se usa con MySQL. Pregunte por el rango apropiado para cada solicitud.

También creo que Hibernate contiene funcionalidad que le permite hacer esto para HQL, pero no estoy familiarizado con él.

Debería consultar solo los datos que realmente necesita para mostrar en la página actual. No transporte todo el conjunto de datos a la memoria de Java y luego filté allí. Solo haría las cosas innecesariamente más lentas.

Si realmente tiene dificultades para implementar esto correctamente y / o calcular la consulta SQL para la base de datos específica, entonces eche un vistazo a mi respuesta here .

Actualización: ya que está usando Oracle, aquí hay un extracto orientado a Oracle de la respuesta antes mencionada:

En Oracle necesita una subconsulta con rownum cláusula que debería verse así:

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; }

Descargo de responsabilidad: Esta publicación de blog sobre paginación SQL y paginación JDBC fue publicada por mí.

Sin tener en cuenta la paginación de Hibernate, podemos usar paginación SQL / paginación JDBC

Paginación SQL

Hay dos enfoques básicos:

  1. operando en conjunto de resultados por partes (Nueva consulta para cada página)
  2. operando en conjunto de resultados completo

La forma de hacerlo es específica de SQL

Para MySQL / muchos otros SQL se puede hacer con límite y compensación

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

En Oracle, utiliza la misma forma que para manejar la "consulta de Top-N", por ejemplo, quiénes son los 5 empleados mejor pagados, que está optimizado

select * from ( select a.*, rownum rnum from ( YOUR_QUERY_GOES_HERE -- including the order by ) a where rownum <= MAX_ROWS ) where rnum >= MIN_ROWS

Aquí hay una explicación muy detallada sobre ROW-NUM

Hilo SO similar

Paginación JDBC

La pregunta que viene a la mente es: cuando ejecuto el SQL, ¿cómo se carga el resultado? ¿Inmediatamente o bajo pedido? lo mismo que este hilo SO

Primero, debemos entender algunos conceptos básicos de JDBC, como de Oracle

Por 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.

Accedemos a los datos en el conjunto de resultados mediante un cursor. Tenga en cuenta que este cursor es diferente del DB mientras que es un puntero inicialmente ubicado antes de la primera fila de datos.

Los datos se obtienen a petición. mientras que cuando haces la ejecución () estás buscando por primera vez.

Entonces, ¿cuántos datos se cargan? Es configurable Se puede utilizar el método java API setFetchSize () en ResultSet para controlar cuántas filas se extraen de DB una vez por el controlador, qué tan grande son los bloques que recupera a la vez.

Por ejemplo, supongamos que el resultado total es 1000. Si el tamaño de búsqueda es 100, ir a la primera fila cargará 100 filas desde la base de datos y la segunda a la fila 100 se cargará desde la memoria local. Para consultar la fila 101, se cargarán otras 100 filas en la memoria.

De 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.

Tenga en cuenta la palabra "sugerencia": se puede anular mediante la implementación específica del controlador.

Esto también es en lo que se basa la función "Limitar filas a 100" en clientes como el desarrollador de SQL.

Completando toda la solución, para desplazar los resultados, uno debe considerar los Tipos de ResultSet y ScrollableCursor en API

Uno puede encontrar una implementación de ejemplo de esta publicación en el oráculo

que es del libro Oracle Toplink Developer''s Guide Ejemplo 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();

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

Después de todo, las preguntas hierven a

¿Cuál es la mejor manera de hacer paginación?

Tenga en cuenta que el SQL debe ser ORDEN por tener sentido en el enfoque SQL,

De lo contrario, es posible volver a mostrar algunas filas en la página siguiente.

A continuación se detallan algunos puntos de la documentación de Postgresql sobre JDBC Driver y otras respuestas de SO

En primer lugar, la consulta original debería tener una cláusula ORDER BY para que la solución de paginación funcione razonablemente. De lo contrario, sería perfectamente válido para Oracle devolver las mismas 500 filas para la primera página, la segunda página y la enésima página

La principal diferencia es para el modo JDBC, se requiere para mantener la conexión durante la búsqueda. Esto puede no ser adecuado en la aplicación web sin estado, por ejemplo.

Para la forma de SQL

la sintaxis es específica de SQL y puede no ser fácil de mantener. Para JDBC way

  • La conexión al servidor debe estar utilizando el protocolo V3. Este es el valor predeterminado para (y solo es compatible con) las versiones de servidor 7.4 y posteriores.
  • La conexión no debe estar en el modo de confirmación automática. El servidor cierra los cursores al final de las transacciones, por lo que en el modo de confirmación automática el servidor habrá cerrado el cursor antes de poder recuperar nada.
  • El extracto se debe crear con un tipo ResultSet de ResultSet.TYPE_FORWARD_ONLY. Este es el valor predeterminado, por lo que no será necesario reescribir ningún código para aprovechar esto, pero también significa que no puede desplazarse hacia atrás o saltar en el ResultSet.
  • La consulta dada debe ser una sola declaración, no múltiples instrucciones unidas con punto y coma.

Algunas lecturas adicionales

Esta publicación trata sobre el ajuste del rendimiento con un tamaño de alcance óptico

Entrada:

  1. Ejemplo de información de pedido (A2 o D3) (A / D ascendente / descendente) + columna
  2. Ejemplo de información de pedido (A2 o D3) (A / D ascendente / descendente) + columna
  3. Valor del filtro
  4. comenzar fila
  5. comenzar fila
  6. número máximo de filas

Resultado:

  • Valores seleccionados
  • Página seleccionada
  • Índice de la fila en este orden
  • Cuente los datos disponibles. (Guarde una segunda consulta)

Ventaja solo consulta por:

  • suma de columnas disponibles con este filtro
  • solo transfiere la página seleccionada de db
  • ordenado correctamente sin sql dinámico

Desventaja:

  • Oracle Dependend

    seleccione x. * desde (seleccione c.pk_field, c.numeric_a, c.char_b, c.char_c ROW_NUMBER () encima (ORDER BY decodificación (?, ''A1'', to_char (c.numeric_a, ''FM00000000''), ''A2 '', c.char_b,'' A3 '', c.char_c,'' A '') asc, decodificar (?,'' D1 '', to_char (c.numeric_a,'' FM00000000 ''),'' D2 '', c.char_b,'' D3 '' , c.char_c, ''A'') desc, c.pk_field asc
    ) COMO "idx", COUNT (*) OVER (ORDEN POR 1) "cnt" de myTable c donde c.haystack =? ) x donde x. "idx" entre mayor (nvl (?, 1), 1) y nvl (?, 1) -1+?

Los siguientes códigos Java funcionan bien:

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

No hay una forma eficiente de hacerlo simplemente usando JDBC. Debe formular el límite en n filas y comenzar desde las cláusulas de elemento i-ésimo directamente en el SQL para que sea eficiente. Dependiendo de la base de datos, esto podría ser bastante fácil (ver la palabra clave LIMIT de MySQL), en otras bases de datos como Oracle puede ser un poco más complicado (implica la subconsulta y el uso de pseudocolumna rownum).

Vea este tutorial de paginación JDBC: http://java.avdiel.com/Tutorials/JDBCPaging.html

Oracle admite la función de ventana estándar ROW_NUMBER () desde 8i para que pueda usar eso. Puede hacerlo como una consulta parametrizada, de modo que solo necesita establecer los números de fila de inicio y fin. P.ej

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

(Si no está utilizando parámetros con nombre, reemplace: start /: end con el marcador de posición del parámetro posicional ''?'')

Vea SELECCIONAR SQL, Funciones de Ventana en wikipedia. El artículo también enumera los otros DB que admiten la función de ventana estándar ROW_NUMBER ().

Puedes usar el método a continuación para esto.

  1. Aquí rowNo es el número de la fila inicial desde la que desea obtener n cantidad de registros.
  2. pageSize es la cantidad total de registros que desea recuperar

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; }

Sé que esta pregunta es antigua, pero así es como implementé la búsqueda, espero que ayude a alguien

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); ....

hacer que el valor 50 sea una constante llamada pageSize, por lo que se puede cambiar a cualquier número

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

Tenga en cuenta que setFetchSize(int) es solo una pista : la última vez que lo setFetchSize(int) con MySQL, por ejemplo, no era compatible. Al mirar brevemente la documentación de Oracle, parece que su JDBC sí lo admite. No me citaría sobre eso, pero vale la pena intentarlo al menos; y sí, esta respuesta es frágil, pero podría ser suficiente menos dolor de cabeza que la implementación de la solución robusta.

Esencialmente, puede emitir la solicitud de todo, y solo obtiene el tamaño de búsqueda en la memoria a la vez (siempre que no esté reteniendo los resultados anteriores). Por lo tanto, debe establecer el tamaño de su búsqueda en 50, hacer su conexión / consulta, mostrar los primeros 50 resultados (lo que hace que la otra busque el siguiente bocado de su consulta) y así sucesivamente.