How to use Batch Process SELECT Statement in Hibernate for Native SQL

[Amit Basnak]

Friends

When the query returns more than 1500 records , I want to chunk up the requests into multiple requests of 1500 each. How can we achieve this using Hibernate ScrollableResults or similar for native SQL query used in createSQLQuery ?

I have tried using simple technique but need more input on how we can use Hibernate ScrollableResults for Native SQL batch select records

public List<EmployeeDetails> processRecordsInBatches() throws Exception {
        
        Session s = getSession();
        Transaction tx = null;
         final int BATCH_SIZE = 1500;
        List<EmployeeDetails> result = null;
        ScrollableResults allEntities = null;

        try {
            tx = beginTransaction(s);
			

		if ( ++BATCH_SIZE % 1500 == 0 ){
            Query query = s.createSQLQuery("select c.employee_id  AS EmployeeId, 99 AS assignedProjectId, c.employee_name AS employeeName, " +
                    " from employee.employee_details c" ) ;

            query.setInteger("employeeId", employeeId );
			query.setString("assignedProjectId", assignedProjectId);
			query.setString("employee_name", employee_name);
			log.info(query);

            query.setResultTransformer(new ResultTransformer() {
                @Override
                public Object transformTuple(Object[] tuple, String[] aliases) {
                    EmployeeDetails details = new EmployeeDetails();
                    details.setEmployeeId((Integer)tuple[0]);
                    details.assignedProjectId((Integer)tuple[1]);
                    details.setEmployeeName((Integer)tuple[2]);
                    return details;
                }

                @Override
                public List transformList(List list) {
                    return list;
                }
            });

            result =  query.list();
			s.flush();
			s.clear();
			}
            commitTransaction(tx);
            log.info(result);


        } catch (Exception ex) {
            log.error(ex);
        } finally {
            closeSession(s);
        }
        return result;
    }

 

[Jaikiran Pai]

Take a look at the setFirstResult and setMaxResults API on the JPA Query interface http://docs.oracle.com/javaee/6/api/javax/persistence/Query.html

P.S: If you are using Hibernate directly instead of JPA, you'll have to check the Hibernate API docs to see if it exposes something similar.