I have a large table that I'd like to access via a Spring Data Repository.
Currently, I'm trying to extend the PagingAndSortingRepository
interface but it seems I can only define methods that return lists, eg.:
public interface MyRepository extends
PagingAndSortingRepository<MyEntity, Integer>
{
@Query(value="SELECT * ...")
List<MyEntity> myQuery(Pageable p);
}
On the other hand, the findAll()
method that comes with PagingAndSortingRepository
returns an Iterable
(and I suppose that the data is not loaded into memory).
Is it possible to define custom queries that also return Iterable and/or don't load all the data into memory at once?
Are there any alternatives for handling large tables?
In the repository interfaces, we can add the methods like findByCustomerNameAndPhone() (assuming customerName and phone are fields in the domain object). Then, Spring provides the implementation by implementing the above repository interface methods at runtime (during the application run).
Crud Repository doesn't provide methods for implementing pagination and sorting. JpaRepository ties your repositories to the JPA persistence technology so it should be avoided. We should use CrudRepository or PagingAndSortingRepository depending on whether you need sorting and paging or not.
PagingAndSortingRepository provides methods to do pagination and sort records. JpaRepository provides JPA related methods such as flushing the persistence context and delete records in a batch.
Every repository which is mentioned above extends the generic Repository interface and they each are responsible for different functionality. Spring Data Hierarchy defines the Repository (marker interface) as the top-level interface.
We have the classical consulting answer here: it depends. As the implementation of the method is store specific, we depend on the underlying store API. In case of JPA there's no chance to provide streaming access as ….getResultList()
returns a List
. Hence we also expose the List
to the client as especially JPA developers might be used to working with lists. So for JPA the only option is using the pagination API.
For a store like Neo4j we support the streaming access as the repositories return Iterable
on CRUD methods as well as on the execution of finder methods.
The implementation of findAll()
simply loads the entire list of all entities into memory. Its Iterable
return type doesn't imply that it implements some sort of database level cursor handling.
On the other hand your custom myQuery(Pageable)
method will only load one page worth of entities, because the generated implementation honours its Pageable
parameter. You can declare its return type either as Page
or List
. In the latter case you still receive the same (restricted) number of entities, but not the metadata that a Page
would additionally carry.
So you basically did the right thing to avoid loading all entities into memory in your custom query.
Please review the related documentation here.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With