[FIXED] Batch insert using Native SQL in Hibernate

Issue

I want to insert records in database using Hibernate Native SQL.The code is like below

 Session session = sessionFactory.openSession();
 Transaction tx = session.beginTransaction();

String sqlInsert = "insert into sampletbl (name) values (?) ";
for(String name : list){
   session.createSQLQuery( sqlInsert )
          .setParameter(1,name)
          .executeUpdate();
} 
tx.commit();
session.close();

Above code is working fine.I think it is not the best way. Please give me another possible ways to do this if any. Thank you

Solution

Hibernate have a Batch functionality.But in above case I am using Native SQL,as per my observation hibernate batch is not much effective in case of Native SQL.Yes,surely it avoids the out of memory error but does not improves much performance. Hence I retreated to implemented JDBC Batch in Hibernate.Hibernate provides method doWork() to get Connection from Hibernate Session.

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
//get Connction from Session
session.doWork(new Work() {
       @Override
       public void execute(Connection conn) throws SQLException {
          PreparedStatement pstmt = null;
          try{
           String sqlInsert = "insert into sampletbl (name) values (?) ";
           pstmt = conn.prepareStatement(sqlInsert );
           int i=0;
           for(String name : list){
               pstmt .setString(1, name);
               pstmt .addBatch();

               //20 : JDBC batch size
             if ( i % 20 == 0 ) { 
                pstmt .executeBatch();
              }
              i++;
           }
           pstmt .executeBatch();
         }
         finally{
           pstmt .close();
         }                                
     }
});
tx.commit();
session.close();

Answered By - Oomph Fortuity
Answer Checked By - Katrina (JavaFixing Volunteer)