How to Execute Batch Insert Update in Hibernate?

In this article, we will see how we can execute batch insert/update in hibernate.

Whenever we execute a sql statement, we do it by making a network call to the database. Now, if we have to insert 10 entries to our database table, then we have to make 10 network calls. Instead we can optimize our network calls by using batching. Batching allows us to execute a group of SQL statements in a single network call.

To understand and implement this, let us define our entities ?

<div class="code-mirror  language-java" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token annotation punctuation">@Entity</span>
<span class="token keyword">public</span> <span class="token keyword">class</span> <span class="token class-name">Parent</span> <span class="token punctuation">{</span>
   <span class="token annotation punctuation">@Id</span>
   <span class="token annotation punctuation">@GeneratedValue</span><span class="token punctuation">(</span>strategy <span class="token operator">=</span> <span class="token class-name">GenerationType</span><span class="token punctuation">.</span>AUTO<span class="token punctuation">)</span>
   <span class="token keyword">private</span> <span class="token keyword">long</span> id<span class="token punctuation">;</span>
   <span class="token keyword">private</span> <span class="token class-name">String</span> name<span class="token punctuation">;</span>
   <span class="token comment">// Getters</span>
   <span class="token comment">//Setters</span>
<span class="token punctuation">}</span></div>

To enable batching in hibernate, we need to add a property to our application properties file:

spring.jpa.properties.hibernate.jdbc.batch_size=3

Now, we need to execute persist function of the EntityManager to insert the data in the database

Example

<div class="code-mirror  language-java" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token annotation punctuation">@Autowired</span>
<span class="token keyword">private</span> <span class="token class-name">EntityManager</span> entityManager<span class="token punctuation">;</span>
<span class="token annotation punctuation">@Test</span>
<span class="token class-name">Public</span> <span class="token keyword">void</span> <span class="token class-name">InsertInBatch</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">{</span>
   <span class="token keyword">for</span> <span class="token punctuation">(</span><span class="token keyword">int</span> i <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span> i <span class="token operator"><</span> <span class="token number">6</span><span class="token punctuation">;</span> i<span class="token operator">++</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
      <span class="token class-name">Parent</span> parent <span class="token operator">=</span> <span class="token class-name">Parent</span><span class="token punctuation">[</span>i<span class="token punctuation">]</span><span class="token punctuation">;</span>
      entityManager<span class="token punctuation">.</span><span class="token function">persist</span><span class="token punctuation">(</span>parent<span class="token punctuation">)</span><span class="token punctuation">;</span>
   <span class="token punctuation">}</span>
<span class="token punctuation">}</span>
</div>

Output

"batch":true, "querySize":1, "batchSize":3, "query":["insert into parent (name, id) values (?, ?)"], "params":[["P1","1"],["P2","2"],["P3","3"]]
"batch":true, "querySize":1, "batchSize":3, "query":["insert into parent (name, id) values (?, ?)"], "params":[["P4","4"],["P5","5"],["P6","6"]]

We can see from the console that inserting into parent table is occurring in a batch of size 3.

While persisting the entities, there may occur OutOfMemoryException because Hibernate stores the entities in the persistence context. So, for optimization purpose we can use flush() and clear() of the entity manager after every batch.

Batch Update means updating large amount of data in a single network call.

For batch update, the process is the same. We need to add below two statements in our application properties file and then execute the updating process.

spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.batch_versioned_data=true

Example

Code to update the data ?

<div class="code-mirror  language-java" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token annotation punctuation">@Autowired</span>
<span class="token keyword">private</span> <span class="token class-name">EntityManager</span> entityManager<span class="token punctuation">;</span>
<span class="token annotation punctuation">@Test</span>
<span class="token keyword">public</span> <span class="token keyword">void</span> <span class="token class-name">UpdateInBatch</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
   <span class="token class-name">TypedQuery</span><span class="token generics"><span class="token punctuation"><</span><span class="token class-name">Parent</span><span class="token punctuation">></span></span> query <span class="token operator">=</span> entityManager<span class="token punctuation">.</span><span class="token function">createQuery</span><span class="token punctuation">(</span><span class="token string">"SELECT p from Parent p"</span><span class="token punctuation">,</span> <span class="token class-name">Parent</span><span class="token punctuation">.</span><span class="token keyword">class</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
   <span class="token class-name">List</span><span class="token generics"><span class="token punctuation"><</span><span class="token class-name">Parent</span><span class="token punctuation">></span></span> <span class="token class-name">Parents</span> <span class="token operator">=</span> query<span class="token punctuation">.</span><span class="token function">getResultList</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
   <span class="token keyword">int</span> i<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span>
   <span class="token keyword">for</span> <span class="token punctuation">(</span><span class="token class-name">Parent</span> parent <span class="token operator">:</span> <span class="token class-name">Parents</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token class-name">      String</span> s<span class="token operator">=</span><span class="token string">"Parent"</span><span class="token operator">+</span><span class="token class-name">Integer</span><span class="token punctuation">.</span><span class="token function">toString</span><span class="token punctuation">(</span>i<span class="token punctuation">)</span><span class="token punctuation">;</span>
      i<span class="token operator">++</span><span class="token punctuation">;</span>
      parent<span class="token punctuation">.</span><span class="token function">setName</span><span class="token punctuation">(</span>s<span class="token punctuation">)</span><span class="token punctuation">;</span>
   <span class="token punctuation">}</span>
<span class="token punctuation">}</span>
</div>

Hibernate will now bind these statements together in a single batch and executes them.

Output

<span class="str">"batch"</span><span class="pun">:</span><span class="kwd">true</span><span class="pun">,</span><span class="pln"> </span><span class="str">"querySize"</span><span class="pun">:</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">"batchSize"</span><span class="pun">:</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="str">"query"</span><span class="pun">:[</span><span class="str">"update parent set name=? where id=?"</span><span class="pun">],</span><span class="pln"> </span><span class="str">"params"</span><span class="pun">:[[</span><span class="str">"Parent1"</span><span class="pun">,</span><span class="str">"1"</span><span class="pun">],[</span><span class="str">" Parent2"</span><span class="pun">,</span><span class="str">"2"</span><span class="pun">],[</span><span class="str">" Parent3"</span><span class="pun">,</span><span class="str">"3"</span><span class="pun">]]</span><span class="pln">
</span><span class="str">"batch"</span><span class="pun">:</span><span class="kwd">true</span><span class="pun">,</span><span class="pln"> </span><span class="str">"querySize"</span><span class="pun">:</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">"batchSize"</span><span class="pun">:</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="str">"query"</span><span class="pun">:[</span><span class="str">"update parent set name=? where id=?"</span><span class="pun">],</span><span class="pln"> </span><span class="str">"params"</span><span class="pun">:[[</span><span class="str">"Parent4"</span><span class="pun">,</span><span class="str">"4"</span><span class="pun">],[</span><span class="str">"Parent5"</span><span class="pun">,</span><span class="str">"5"</span><span class="pun">],[</span><span class="str">"Parent6"</span><span class="pun">,</span><span class="str">"6"</span><span class="pun">]]</span>

We can see from the console that updating data in parent table is occurring in a batch of size 3.