How do you get Hibernate to send INSERT, UPDATE and DELETE statements to the database in JDBC batches, and what decides when a batch is actually executed?
answer
- hibernate.jdbc.batch_size (0/1 = off)
- batch = one PreparedStatement = one SQL string
- executes on size reached / SQL change / flush
- flush + clear every N in bulk loops
- MySQL rewriteBatchedStatements, pgjdbc reWriteBatchedInserts
basics
~20 sSet hibernate.jdbc.batch_size to a positive number, for example 30. Hibernate then buffers statements with addBatch and calls executeBatch when the batch fills, when the SQL string changes, or at flush. Statements only batch at flush time, so nothing batches before a flush occurs.
solid answer
~50 sSet **`hibernate.jdbc.batch_size`** (0 or 1 means disabled); Hibernate 5.2+ also allows `session.setJdbcBatchSize(int)` per session. With it set, Hibernate accumulates the DML it produces at flush into a JDBC `PreparedStatement` batch and calls `executeBatch()`. A batch is executed when one of three things happens: the configured size is reached, **the SQL string changes** (a batch is per prepared statement, so a different table or a different column set starts a new one), or the flush/transaction completes. Two practical consequences. First, batching only applies to statements produced at **flush**, so a loop that persists entities batches only when the flush happens — and only if the identifier strategy lets Hibernate defer the INSERT. Second, since a mixed workload constantly changes the SQL string, `hibernate.order_inserts` and `hibernate.order_updates` are usually needed as well. For bulk loops, flush and clear every batch-size entities so the persistence context does not grow without bound. Some drivers also need a flag (MySQL `rewriteBatchedStatements=true`, PostgreSQL `reWriteBatchedInserts=true`) to turn a batch into one compact round trip.
code
properties · 3 lineshibernate.jdbc.batch_size=30
hibernate.order_inserts=true
hibernate.order_updates=truego deeper
Know the property name, that it must be greater than 1, and that batching reduces round trips.
Explain flush-time production of DML, that a batch is per prepared statement, and the flush+clear loop idiom.
Add the driver rewrite flags, ordering settings, and what batching does not help with; mention how you would verify it.
Discuss sizing against latency, memory and lock duration, and when a write path should leave the ORM entirely.
## What JDBC batching is Without batching, each `INSERT` is a separate network round trip: send statement, wait, read result. At 1 ms of latency, 10 000 inserts spend 10 seconds simply waiting. JDBC's batch API lets you register many parameter sets against one prepared statement — `addBatch()` repeatedly, then `executeBatch()` once — so the driver ships them together and the server returns an array of row counts. Latency is paid a handful of times instead of thousands. ## Turning it on in Hibernate ``` hibernate.jdbc.batch_size = 30 ``` A value of 0 or 1 disables batching. It can also be set per session since Hibernate 5.2 with `session.setJdbcBatchSize(30)`, which is useful when one import job wants batching and ordinary request handling does not. That is the whole switch; no API change is required in your code, because Hibernate produces the DML itself. ## When statements are produced — and therefore when they can batch Hibernate does not write to the database when you call `persist()` or when you mutate a managed entity. It records the intent in the action queue and emits SQL at **flush**: before a query that could be affected by pending changes, on an explicit `flush()`, or at commit. Batching operates on that flush-time stream. This is why batching is invisible if you commit after each entity, and why an identifier strategy that forces an immediate INSERT defeats it entirely. ## What breaks a batch A JDBC batch belongs to one `PreparedStatement`, so it can only hold parameter sets for **the same SQL string**. Hibernate keeps the current batch open and adds to it while consecutive statements share that string. A batch is executed when: 1. it reaches `batch_size` entries; 2. the next statement has a different SQL string — different table, different operation, or even a different set of columns for the same table; 3. the flush finishes, or something forces the session to drain pending work. Point 2 is the one people trip on: persisting `Order, OrderLine, Order, OrderLine, …` in interleaved order produces alternating SQL and hence batches of size one. `hibernate.order_inserts=true` and `hibernate.order_updates=true` reorder the flush so same-shaped statements are contiguous. ## The bulk-loop idiom ```java for (int i = 0; i < rows.size(); i++) { em.persist(toEntity(rows.get(i))); if (i % batchSize == 0) { em.flush(); em.clear(); } } ``` `flush()` pushes the accumulated DML out as batches; `clear()` detaches everything so the persistence context does not grow to hold every inserted entity plus its state snapshot. Without `clear()`, a large import still batches but slowly runs out of heap and spends increasing time dirty-checking. Aligning the flush interval with `batch_size` keeps the two in step. ## Drivers matter JDBC batching is a protocol optimisation only if the driver takes advantage of it: - **MySQL Connector/J** sends batched statements individually unless the connection URL includes `rewriteBatchedStatements=true`, which rewrites inserts into one multi-`VALUES` statement. - **PostgreSQL pgjdbc** supports `reWriteBatchedInserts=true` for the same effect; without it, batching still saves round trips through pipelining but gains less. So `batch_size` is necessary but not always sufficient — the driver flag can be worth as much as the Hibernate setting. ## Things batching does not do - It does not reduce the number of SQL statements the *database executes*; it reduces round trips (unless a rewrite merges them). - It does not apply to `select`s. - It has no effect on bulk JPQL `update`/`delete` statements, which are already one statement. - It does not make the transaction shorter; if anything, batching many writes into one transaction holds locks longer, which is a separate tuning axis. ## Verifying and sizing `show_sql` prints the same lines batched or not, so it proves nothing — verify with driver-level logging, a proxying datasource, or Hibernate's batch-level TRACE logs. For sizing, values between 20 and 100 cover most workloads with diminishing returns past roughly 50; larger batches raise memory held per statement and lengthen the window in which locks are held.
- Why must you call clear() as well as flush() in a large insert loop?`flush()` only sends the pending statements; the entities stay managed in the persistence context, each with a loaded-state snapshot. Over a large import that grows until the heap is exhausted, and every subsequent flush dirty-checks all of them. `clear()` detaches everything so the memory can be reclaimed and later flushes only inspect the current chunk.
- You set batch_size to 50 but the database still shows one round trip per row on MySQL. What else would you check?The JDBC URL. MySQL Connector/J executes batched statements one by one unless `rewriteBatchedStatements=true` is set, in which case it rewrites inserts into a single multi-`VALUES` statement. PostgreSQL has the analogous `reWriteBatchedInserts=true`. Also confirm the identifier generator is not IDENTITY, which prevents insert batching entirely.
saying these in an interview costs you the question
- Believing statements are batched at persist() time rather than at flush
- Setting batch_size and never checking whether the driver actually benefits
- Omitting clear() in a bulk loop and running out of heap
- Thinking show_sql output proves whether batching happened
- Expecting batching to speed up select statements or bulk JPQL updates