Batch insert using native SQL in sleep mode

Question

I want to insert records into a database using Hibernate Native SQL. The code looks 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(); 

It works fine on code. I think this is not the best way. Please give me some more possible ways to do this, if any. thank you

Answer 1

Hibernate have batch functionality. But in the above case, I use Native SQL, since my batch of Hibernate observation sessions is not very effective in the case of Native SQL.Yes, of course, it avoids the memory error, but does not improve much of the presentation. So I stepped back to the implementation of JDBC Batch in Hibernate. Hibernate provides a doWork() method to get a connection to a 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(); 

Answer 2

If you do not need to worry about SQL injection. that is, you do not receive data from the user, then you can do it.

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

He will create such a request.

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

Thus, your request will be launched only once, and not for each item in the list.

Answer 3

Here is an 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); } }); } }