java - paginación - spring data pageable null

¿Existe una forma más eficiente de hacer paginación en Hibernate que ejecutar consultas de selección y recuento?

Question

Por lo general, las consultas de paginación se ven así. ¿Hay una mejor manera en lugar de hacer dos métodos casi iguales, uno de los cuales ejecuta "select * ..." y el otro "count * ..."?

public List<Cat> findCats(String name, int offset, int limit) {
Query q = session.createQuery("from Cat where name=:name");
q.setString("name", name);
if (offset > 0) {
q.setFirstResult(offset);
}
if (limit > 0) {
q.setMaxResults(limit);
}
return q.list();
}
public Long countCats(String name) {
Query q = session.createQuery("select count(*) from Cat where name=:name");
q.setString("name", name);
return (Long) q.uniqueResult();
}

Answer 1

Baron Schwartz en MySQLPerformanceBlog.com escribió una post sobre esto. Desearía que hubiera una solución mágica para este problema, pero no es así. Resumen de las opciones que presentó:

1. En la primera consulta, busque y guarde en caché todos los resultados.
2. No mostrar todos los resultados
3. No muestre el recuento total o los enlaces intermedios a otras páginas. Mostrar solo el "siguiente" enlace.
4. Estime cuántos resultados hay.

Answer 2

Conozco este problema y lo he enfrentado antes. Para empezar, el mecanismo de consulta doble donde se realizan las mismas condiciones SELECT no es óptimo. Pero funciona, y antes de que te vayas y hagas un cambio gigante, simplemente date cuenta de que no valdrá la pena.

Pero de cualquier manera:

1) Si está tratando con datos pequeños en el lado del cliente, use una implementación del conjunto de resultados que le permite establecer el cursor hasta el final del conjunto, obtener su desplazamiento de fila, luego restablecer el cursor a antes.

2) Rediseñe la consulta para que pueda contar COUNT (*) como una columna adicional en las filas normales. Sí, contiene el mismo valor para cada fila, pero solo incluye 1 columna adicional que es un número entero. Este es un SQL incorrecto para representar un valor agregado con valores no agregados, pero puede funcionar.

3) Rediseñe la consulta para usar un límite estimado, similar a lo que se mencionaba. Use filas por página y un límite superior. Por ejemplo, solo di algo como "Mostrando 1 a 10 de 500 o más". Cuando buscan "Mostrar 25o a 260 de X", es una consulta posterior, por lo que puede actualizar la estimación X haciendo el límite superior relativo a la página * filas / página.

Answer 3

Creo que la solución depende de la base de datos que está utilizando. Por ejemplo, estamos usando MS SQL y usando la siguiente consulta

select
COUNT(Table.Column) OVER() as TotalRowsCount,
Table.Column,
Table.Column2
from Table ...

Esa parte de la consulta se puede cambiar con la base de datos especificada SQL.

También establecemos el resultado máximo de la consulta que esperamos ver, por ejemplo

query.setMaxResults(pageNumber * itemsPerPage)

Y obtiene la instancia ScrollableResults como resultado de la ejecución de la consulta:

ScrollableResults result = null;
try {
result = query.scroll();
int totalRowsNumber = result.getInteger(0);
int from = // calculate the index of row to get for the expected page if any
/*
* Reading data form page and using Transformers.ALIAS_TO_ENTITY_MAP
* to make life easier.
*/
}
finally {
if (result != null)
result.close()
}

Answer 4

En esta página wiki de Hibernate:

https://www.hibernate.org/314.html

Presento una solución completa de paginación; en particular, el número total de elementos se calcula desplazándose hasta el final del conjunto de resultados, que ahora es compatible con varios controladores JDBC. Esto evita la segunda consulta de "conteo".

Answer 5

Encontré una manera de hacer paginación en hibernación sin hacer un recuento selectivo (*) en un gran tamaño de conjunto de datos. Mire la solución que publiqué para mi respuesta aquí.

procesar una gran cantidad de entradas de la base de datos con paginación se ralentiza con el tiempo

puede realizar paginación de uno en uno sin saber cuántas páginas necesitará originalmente

Answer 6

Hay una manera

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
-> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

El segundo SELECT devuelve un número que indica cuántas filas habría devuelto el primer SELECT si se hubiera escrito sin la cláusula LIMIT.

Referencia: FOUND_ROWS()

Answer 7

Mi solución funcionará para el caso de uso muy común de Hibernate + Spring + MySQL

De forma similar a la respuesta anterior, basé mi solución en la del Dr. Richard Kennar . Sin embargo, dado que Hibernate se usa a menudo con Spring, quería que mi solución funcionara muy bien con Spring y con el método estándar para usar Hibernate. Por lo tanto, mi solución usa una combinación de locals de hilo y beans singleton para lograr el resultado. Técnicamente, el interceptor se invoca en cada instrucción SQL preparada para SessionFactory, pero omite toda la lógica y no inicializa ningún ThreadLocal (s) a menos que sea una consulta específicamente configurada para contar el total de filas.

Usando la clase de abajo, tu configuración de Spring se ve así:

<bean id="foundRowCalculator" class="my.hibernate.classes.MySQLCalcFoundRowsInterceptor" />
<!-- p:sessionFactoryBeanName="mySessionFactory"/ -->
<bean id="mySessionFactory"
class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"
p:dataSource-ref="dataSource"
p:packagesToScan="my.hibernate.classes"
p:entityInterceptor-ref="foundRowCalculator"/>

Básicamente debe declarar el bean interceptor y luego hacer referencia a él en la propiedad "entityInterceptor" de SessionFactoryBean. Solo debe establecer "sessionFactoryBeanName" si hay más de una SessionFactory en su contexto Spring y la fábrica de sesiones a la que desea hacer referencia no se llama "sessionFactory". La razón por la que no puede establecer una referencia es que esto causaría una interdependencia entre los beans que no se puede resolver.

Usando un bean envoltorio para el resultado:

package my.hibernate.classes;
public class PagedResponse<T> {
public final List<T> items;
public final int total;
public PagedResponse(List<T> items, int total) {
this.items = items;
this.total = total;
}
}

Luego, utilizando una clase DAO base abstracta, debe llamar a "setCalcFoundRows (true)" antes de realizar la consulta y "reset ()" después de [en un bloque finally para asegurarse de que se llama]:

package my.hibernate.classes;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.springframework.beans.factory.annotation.Autowired;
public abstract class BaseDAO {
@Autowired
private MySQLCalcFoundRowsInterceptor rowCounter;
public <T> PagedResponse<T> getPagedResponse(Criteria crit, int firstResult, int maxResults) {
rowCounter.setCalcFoundRows(true);
try {
@SuppressWarnings("unchecked")
return new PagedResponse<T>(
crit.
setFirstResult(firstResult).
setMaxResults(maxResults).
list(),
rowCounter.getFoundRows());
} finally {
rowCounter.reset();
}
}
public <T> PagedResponse<T> getPagedResponse(Query query, int firstResult, int maxResults) {
rowCounter.setCalcFoundRows(true);
try {
@SuppressWarnings("unchecked")
return new PagedResponse<T>(
query.
setFirstResult(firstResult).
setMaxResults(maxResults).
list(),
rowCounter.getFoundRows());
} finally {
rowCounter.reset();
}
}
}

Luego, un ejemplo concreto de la clase DAO para un @Entity llamado MyEntity con una propiedad de cadena "prop":

package my.hibernate.classes;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions
import org.springframework.beans.factory.annotation.Autowired;
public class MyEntityDAO extends BaseDAO {
@Autowired
private SessionFactory sessionFactory;
public PagedResponse<MyEntity> getPagedEntitiesWithPropertyValue(String propVal, int firstResult, int maxResults) {
return getPagedResponse(
sessionFactory.
getCurrentSession().
createCriteria(MyEntity.class).
add(Restrictions.eq("prop", propVal)),
firstResult,
maxResults);
}
}

Finalmente, la clase de interceptor que hace todo el trabajo:

package my.hibernate.classes;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.hibernate.EmptyInterceptor;
import org.hibernate.HibernateException;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.jdbc.Work;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.BeanFactoryAware;
public class MySQLCalcFoundRowsInterceptor extends EmptyInterceptor implements BeanFactoryAware {
/**
*
*/
private static final long serialVersionUID = 2745492452467374139L;
//
// Private statics
//
private final static String SELECT_PREFIX = "select ";
private final static String CALC_FOUND_ROWS_HINT = "SQL_CALC_FOUND_ROWS ";
private final static String SELECT_FOUND_ROWS = "select FOUND_ROWS()";
//
// Private members
//
private SessionFactory sessionFactory;
private BeanFactory beanFactory;
private String sessionFactoryBeanName;
private ThreadLocal<Boolean> mCalcFoundRows = new ThreadLocal<Boolean>();
private ThreadLocal<Integer> mSQLStatementsPrepared = new ThreadLocal<Integer>() {
@Override
protected Integer initialValue() {
return Integer.valueOf(0);
}
};
private ThreadLocal<Integer> mFoundRows = new ThreadLocal<Integer>();
private void init() {
if (sessionFactory == null) {
if (sessionFactoryBeanName != null) {
sessionFactory = beanFactory.getBean(sessionFactoryBeanName, SessionFactory.class);
} else {
try {
sessionFactory = beanFactory.getBean("sessionFactory", SessionFactory.class);
} catch (RuntimeException exp) {
}
if (sessionFactory == null) {
sessionFactory = beanFactory.getBean(SessionFactory.class);
}
}
}
}
@Override
public String onPrepareStatement(String sql) {
if (mCalcFoundRows.get() == null || !mCalcFoundRows.get().booleanValue()) {
return sql;
}
switch (mSQLStatementsPrepared.get()) {
case 0: {
mSQLStatementsPrepared.set(mSQLStatementsPrepared.get() + 1);
// First time, prefix CALC_FOUND_ROWS_HINT
StringBuilder builder = new StringBuilder(sql);
int indexOf = builder.indexOf(SELECT_PREFIX);
if (indexOf == -1) {
throw new HibernateException("First SQL statement did not contain ''" + SELECT_PREFIX + "''");
}
builder.insert(indexOf + SELECT_PREFIX.length(), CALC_FOUND_ROWS_HINT);
return builder.toString();
}
case 1: {
mSQLStatementsPrepared.set(mSQLStatementsPrepared.get() + 1);
// Before any secondary selects, capture FOUND_ROWS. If no secondary
// selects are
// ever executed, getFoundRows() will capture FOUND_ROWS
// just-in-time when called
// directly
captureFoundRows();
return sql;
}
default:
// Pass-through untouched
return sql;
}
}
public void reset() {
if (mCalcFoundRows.get() != null && mCalcFoundRows.get().booleanValue()) {
mSQLStatementsPrepared.remove();
mFoundRows.remove();
mCalcFoundRows.remove();
}
}
@Override
public void afterTransactionCompletion(Transaction tx) {
reset();
}
public void setCalcFoundRows(boolean calc) {
if (calc) {
mCalcFoundRows.set(Boolean.TRUE);
} else {
reset();
}
}
public int getFoundRows() {
if (mCalcFoundRows.get() == null || !mCalcFoundRows.get().booleanValue()) {
throw new IllegalStateException("Attempted to getFoundRows without first calling ''setCalcFoundRows''");
}
if (mFoundRows.get() == null) {
captureFoundRows();
}
return mFoundRows.get();
}
//
// Private methods
//
private void captureFoundRows() {
init();
// Sanity checks
if (mFoundRows.get() != null) {
throw new HibernateException("''" + SELECT_FOUND_ROWS + "'' called more than once");
}
if (mSQLStatementsPrepared.get() < 1) {
throw new HibernateException("''" + SELECT_FOUND_ROWS + "'' called before ''" + SELECT_PREFIX + CALC_FOUND_ROWS_HINT + "''");
}
// Fetch the total number of rows
sessionFactory.getCurrentSession().doWork(new Work() {
@Override
public void execute(Connection connection) throws SQLException {
final Statement stmt = connection.createStatement();
ResultSet rs = null;
try {
rs = stmt.executeQuery(SELECT_FOUND_ROWS);
if (rs.next()) {
mFoundRows.set(rs.getInt(1));
} else {
mFoundRows.set(0);
}
} finally {
if (rs != null) {
rs.close();
}
try {
stmt.close();
} catch (RuntimeException exp) {
}
}
}
});
}
public void setSessionFactoryBeanName(String sessionFactoryBeanName) {
this.sessionFactoryBeanName = sessionFactoryBeanName;
}
@Override
public void setBeanFactory(BeanFactory arg0) throws BeansException {
this.beanFactory = arg0;
}
}

Answer 8

Puede usar MultiQuery para ejecutar ambas consultas en una sola llamada a la base de datos, que es mucho más eficiente. También puede generar la consulta de recuento, por lo que no tiene que escribirla cada vez. Aquí está la idea general ...

var hql = "from Item where i.Age > :age"
var countHql = "select count(*) " + hql;
IMultiQuery multiQuery = _session.CreateMultiQuery()
.Add(s.CreateQuery(hql)
.SetInt32("age", 50).SetFirstResult(10))
.Add(s.CreateQuery(countHql)
.SetInt32("age", 50));
var results = multiQuery.List();
var items = (IList<Item>) results[0];
var count = (long)((IList<Item>) results[1])[0];

Me imagino que sería bastante fácil resumir esto en algún método fácil de usar para que pueda tener consultas contables y paginado en una sola línea de código.

Como alternativa , si está dispuesto a probar el Linq de trabajo en curso para NHibernate en nhcontrib , puede encontrar que puede hacer algo como esto:

var itemSpec = (from i in Item where i.Age > age);
var count = itemSpec.Count();
var list = itemSpec.Skip(10).Take(10).AsList();

Obviamente, no hay lotes en marcha, por lo que no es tan eficiente, pero aún puede satisfacer sus necesidades.

¡Espero que esto ayude!

Answer 9

Si no necesita mostrar el número total de páginas, no estoy seguro de que necesite la consulta de recuento. Muchos sitios, incluido google, no muestran el total en los resultados paginados. En su lugar, solo dicen "siguiente>".

Answer 10

así es como se hace la paginación en hibernación

Query q = sess.createQuery("from DomesticCat cat");
q.setFirstResult(20);
q.setMaxResults(10);
List cats = q.list();

Puede obtener más información de los documentos de hibernación en: http://www.hibernate.org/hib_docs/v3/reference/en-US/html_single/#objectstate-querying-executing-pagination La sección 10.4.1.5 y 10.4.1.6 le ofrece más opciones de flexbile

BR,
~ A

Answer 11

Aquí hay una solución del Dr. Richard Kennard (¡cuidado con la corrección de errores en el comentario del blog!), Utilizando interceptores Hibernate

Para resumen, enlaza su sessionFactory a su clase de interceptor, de modo que su interceptor pueda darle el número de filas encontradas más tarde.

Puede encontrar el código en el enlace de la solución. Y abajo hay un uso de ejemplo.

SessionFactory sessionFactory = ((org.hibernate.Session) mEntityManager.getDelegate()).getSessionFactory();
MySQLCalcFoundRowsInterceptor foundRowsInterceptor = new MySQLCalcFoundRowsInterceptor( sessionFactory );
Session session = sessionFactory.openSession( foundRowsInterceptor );
try {
org.hibernate.Query query = session.createQuery( ... ) // Note: JPA-QL, not createNativeQuery!
query.setFirstResult( ... );
query.setMaxResults( ... );
List entities = query.list();
long foundRows = foundRowsInterceptor.getFoundRows();
...
} finally {
// Disconnect() is good practice, but close() causes problems. Note, however, that
// disconnect could lead to lazy-loading problems if the returned list of entities has
// lazy relations
session.disconnect();
}