在Hibernate中使用Native SQL进行批量插入 [英] Batch insert using Native SQL in Hibernate

问题描述

我想用Hibernate Native SQL在数据库中插入记录。代码如下所示：

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

推荐答案

Hibernate具有批处理功能。但在上述情况下，我使用Native SQL，观察hibernate批处理在Native SQL的情况下效率不高。是的，它肯定会避免内存不足错误，但不会提高性能。
因此，我退到在Hibernate中实现JDBC批处理.Hibernate提供方法 doWork（）以从Hibernate Session获取连接。

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();

这篇关于在Hibernate中使用Native SQL进行批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！