I have a batch job to process the data from an oracle table and insert into Mainframe Db2 table. there is lot of XML processing involved after reading the tables. I am using hibernate to query oracle table and would prefer it to load only 100 records at a time and not all 10000+ records in the table. I read through the forums and found out multilple ways to do this.

I am currently using the below approach:

SessionFactory factory = HibernateUtil.getSessionFactory();
session = factory.openSession();

session.beginTransaction();
String sql_query = "from Activity as a where a.requestType in (:reqType) and a.requestDt between :startdate and :endDate" ;
Query query = session.createQuery(sql_query);
query.setFetchSize(100);
query.setMaxResults(500);
query.setParameterList("reqType", reqMap.get(reqType));
query.setDate("startdate", startDate);
query.setDate("endDate", endDate);

I found another example where we can define batch-size attribute to the class element of the .hbm.xml file to restrict the number of records.

Can some one please let me know what is the best approach?

Thanks
PShah.