Batch insert using Native SQL in Hibernate

Question

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

Oomph Fortuity · Accepted Answer · 2014-08-25 08:33:04Z

28

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

edited Aug 25, 2014 at 8:33

answered Aug 25, 2014 at 7:02

Oomph Fortuity

6,23012 gold badges49 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Harley Over a year ago

This only works when ?rewriteBatchedStatements=true is used.

SimpleKarthik Over a year ago

I have to insert 7 Lac LoanNumbers (records) into a Temp table (LoanNumber is the only one column). What is the batch size can I mention ?

Snekse · Accepted Answer · 2017-11-09 18:50:33Z

22

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

edited Nov 9, 2017 at 18:50

Snekse

15.8k11 gold badges64 silver badges78 bronze badges

answered May 17, 2017 at 10:07

George Ninan

2,1172 gold badges15 silver badges8 bronze badges

3 Comments

valijon Over a year ago

Does named parameter work with setString(position, value)?

valijon Over a year ago

Should you close the hibernateSession?

João Pedro Schmitt Over a year ago

I'd also suggest creating a transaction if you are inside an existent one.

bitkot · Accepted Answer · 2014-08-21 08:26:46Z

3

If you don't need to worry about SQL injection. i.e you are not getting data from user side then you can do this.

StringBuilder sqlInsert = new StringBuilder("insert into sampletbl (name) values ");
for(String name : list){   
    sqlInsert.append("("+name++"),");
}
sqlInsert.setLength(sqlInsert.length() - 1);
session.createSQLQuery( sqlInsert.toString()).executeUpdate();

It will create a query like this.

insert into sampletbl (name) values ("name1"), ("name2")....

This way your query will run only once and not for each and every item in the list.

answered Aug 21, 2014 at 8:26

bitkot

4,5143 gold badges30 silver badges39 bronze badges

1 Comment

Serge Ballesta Over a year ago

I must admit that it is pure lazyness, but I do not want to ask myself if there is a risk of SQL injection in individual queries, and I do not want following coder reviewers be worry about, so I never do that. If you do strongly add in comments why it is acceptable in that particular context.

kisna · Accepted Answer · 2022-09-25 01:00:58Z

A slight variation leveraging the Named Parameter features of Hibernate Native Query without the Spring JDBC or Spring JPA:

@Transactional(propagation = Propagation.REQUIRED)
public int[] updateInBatch(List<Entity> entities) {
    int[] resultsRef = null;
    try {
        Session session = entityManager.unwrap(Session.class);
        int[] results = new int[entities.size()];
        IntStream.range(0, entities.size())
                .forEach(idx -> {
                    Entity entity = entities.get(idx);
                    Query q = session
                            .createNativeQuery("UPDATE Entity " +
                                    " WHERE " +
                                    " ID = :Id  ")
                            .unwrap(SQLQuery.class)
                            .setString("Id", entity.getId());
                    results[idx] = q.executeUpdate();
                });
        session.flush();
        session.clear();
        resultsRef = results;
    } catch (Exception ex) {
        resultsRef = null;
    }
    return resultsRef;
}

Collectives™ on Stack Overflow

Batch insert using Native SQL in Hibernate

4 Answers 4

2 Comments

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related