Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Batch insert using Native SQL in Hibernate

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

like image 520
Oomph Fortuity Avatar asked Aug 21 '14 06:08

Oomph Fortuity


People also ask

Can we execute native SQL query in Hibernate?

Hibernate provide option to execute native SQL queries through the use of SQLQuery object. Hibernate SQL Query is very handy when we have to execute database vendor specific queries that are not supported by Hibernate API.

What is native SQL query in Hibernate?

You can use native SQL to express database queries if you want to utilize database-specific features such as query hints or the CONNECT keyword in Oracle. Hibernate 3. x allows you to specify handwritten SQL, including stored procedures, for all create, update, delete, and load operations.

What is batch update in Hibernate?

1. Overview. In this tutorial, we'll learn how we can batch insert and update entities using Hibernate/JPA. Batching allows us to send a group of SQL statements to the database in a single network call. This way, we can optimize the network and memory usage of our application.


2 Answers

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();
like image 97
Oomph Fortuity Avatar answered Oct 17 '22 09:10

Oomph Fortuity


Here is the same example for Java 8, Hibernate-JPA 2.1:

@Repository
public class SampleNativeQueryRepository {
    private final Logger log = LoggerFactory.getLogger(SampleNativeQueryRepository.class);
    @PersistenceContext
    private EntityManager em;

    public void bulkInsertName(List<String> list){
        Session hibernateSession = em.unwrap(Session.class);
        String sql = "insert into sampletbl (name) values (:name) ";
        hibernateSession.doWork(connection -> {
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                int i = 1;
                for(String name : list) {
                    preparedStatement.setString(1, name);
                    preparedStatement.addBatch();
                    //Batch size: 20
                    if (i % 20 == 0) {
                        preparedStatement.executeBatch();
                    }
                    i++;
                }
                preparedStatement.executeBatch();
            } catch (SQLException e) {
                log.error("An exception occurred in SampleNativeQueryRepository.bulkInsertName: {}", e);
            }
        });
    }
}
like image 41
George Ninan Avatar answered Oct 17 '22 08:10

George Ninan