CODEWITHSUNDEEP
hibernatebatch-processing
ajithparamban
ajithparamban

Reputation: 2943

How to do select the data in batches using hibernate?

I have a function which basically returns the entire data from the table.How can i implement batch fetching so that the data will be returned in batches of 60,000 rows at a time.

Will the following logic applicable in this scenario?

http://javainnovations.blogspot.com/2008/07/batch-insertion-in-hibernate.html

Upvotes: 4

Views: 16755

Answers (5)

sumit
sumit

Reputation: 3720

I had the same issue and wanted to fetch the records in batches but without firing separate queries. I came across Vlad's post here. It uses Java 8 Stream for that. Putting the slightly modified original code below.

Stream<Post> postStream = sessionFactory
        .getCurrentSession()
        .createNativeQuery(
            "SELECT p " +
                "FROM post p " +
                "ORDER BY p.created_on DESC", Post.class)
        .setHint( QueryHints.HINT_FETCH_SIZE, 50 )
        .getResultStream();

stream.forEach(x -> System.out.println(x));

Upvotes: 0

tri.akki7
tri.akki7

Reputation: 129

@nayan-wadekar

Updated form of Batch Processing. The previous comment by him will lead to the following Exception mentioned below because while you are fetching the data it won't be able to fetch the correct object type at run time even after explicit typecasting.

java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to Object

Inorder to fetch the Result List of any object type, use this:

for(int i=0; i < MAX_SIZE; i = i + BATCH_SIZE){

    List<AnyObject> resultList = entityManager.createQuery(SQL_QUERY, AnyObject.class).setFirstResult(i).setMaxResults(BATCH_SIZE).getResultList();

    //-- Batch Computation
}

Upvotes: 0

Ryan Warren
Ryan Warren

Reputation: 21

To select the data with hibernate, in hibernate.properties, set the fetch size using the parameter:

hibernate.jdbc.fetch_size= SOME_VALUE

or in the query: 

.setFetchSize(SOME_VALUE)

if you are updating data use: the batch_size

hibernate.jdbc.batch_size= SOME_VALUE

Upvotes: 2

Nayan Wadekar
Nayan Wadekar

Reputation: 11602

To select data in a batch, you can apply data pagination by setting the initial position & the number of results to be fetched for a query.

for(int i=0; i < MAX_SIZE; i = i + BATCH_SIZE){

    List<Object> resultList = entityManager.createQuery(SQL_QUERY).setFirstResult(i).setMaxResults(BATCH_SIZE).getResultList();

    //-- Batch Computation
}

I have provided sample code, can modify it accordingly.

Upvotes: 5

user950415
user950415

Reputation: 16

In hibernate.properties, set the batch size using the parameter 

hibernate.jdbc.batch_size= 'your_value'

Upvotes: 0

Related Questions