[[batch]]
== Batching
:sourcedir: ../../../../../test/java/org/hibernate/userguide/batch
:bulkid-sourcedir: ../../../../../../../hibernate-core/src/test/java/org/hibernate/test/bulkid
:extrasdir: extras

[[batch-jdbcbatch]]
=== JDBC batching

JDBC offers support for batching together SQL statements that can be represented as a single PreparedStatement.
Implementation wise this generally means that drivers will send the batched operation to the server in one call,
which can save on network calls to the database. Hibernate can leverage JDBC batching.
The following settings control this behavior.

`hibernate.jdbc.batch_size`::
	Controls the maximum number of statements Hibernate will batch together before asking the driver to execute the batch.
	Zero or a negative number disables this feature.

`hibernate.jdbc.batch_versioned_data`::
	Some JDBC drivers return incorrect row counts when a batch is executed.
	If your JDBC driver falls into this category this setting should be set to `false`.
	Otherwise, it is safe to enable this which will allow Hibernate to still batch the DML for versioned entities and still use the returned row counts for optimistic lock checks.
	Since 5.0, it defaults to true. Previously (versions 3.x and 4.x), it used to be false.

`hibernate.jdbc.batch.builder`::
	Names the implementation class used to manage batching capabilities.
	It is almost never a good idea to switch from Hibernate's default implementation.
	But if you wish to, this setting would name the `org.hibernate.engine.jdbc.batch.spi.BatchBuilder` implementation to use.

`hibernate.order_updates`::
	Forces Hibernate to order SQL updates by the entity type and the primary key value of the items being updated.
	This allows for more batching to be used. It will also result in fewer transaction deadlocks in highly concurrent systems.
	Comes with a performance hit, so benchmark before and after to see if this actually helps or hurts your application.

`hibernate.order_inserts`::
	Forces Hibernate to order inserts to allow for more batching to be used.
	Comes with a performance hit, so benchmark before and after to see if this actually helps or hurts your application.

[NOTE]
====
Since version 5.2, Hibernate allows overriding the global JDBC batch size given by the `hibernate.jdbc.batch_size` configuration property on a per `Session` basis.
====

[[batch-session-jdbc-batch-size-example]]
.Hibernate specific JDBC batch size configuration on a per `Session` basis
====
[source, JAVA, indent=0]
----
include::{sourcedir}/BatchTest.java[tags=batch-session-jdbc-batch-size-example]
----
====

[[batch-session-batch]]
=== Session batching

The following example shows an anti-pattern for batch inserts.

[[batch-session-batch-example]]
.Naive way to insert 100 000 entities with Hibernate
====
[source, JAVA, indent=0]
----
include::{sourcedir}/BatchTest.java[tags=batch-session-batch-example]
----
====

There are several problems associated with this example:

. Hibernate caches all the newly inserted `Customer` instances in the session-level cache, so, when the transaction ends, 100 000 entities are managed by the persistence context.
  If the maximum memory allocated to the JVM is rather low, this example could fail with an `OutOfMemoryException`.
  The Java 1.8 JVM allocated either 1/4 of available RAM or 1Gb, which can easily accommodate 100 000 objects on the heap.
. long-running transactions can deplete a connection pool so other transactions don't get a chance to proceed.
. JDBC batching is not enabled by default, so every insert statement requires a database roundtrip.
  To enable JDBC batching, set the `hibernate.jdbc.batch_size` property to an integer between 10 and 50.

[IMPORTANT]
====
Hibernate disables insert batching at the JDBC level transparently if you use an identity identifier generator.
====

[[batch-session-batch-insert]]
==== Batch inserts

When you make new objects persistent, employ methods `flush()` and `clear()` to the session regularly, to control the size of the first-level cache.

[[batch-session-batch-insert-example]]
.Flushing and clearing the `Session`
====
[source, JAVA, indent=0]
----
include::{sourcedir}/BatchTest.java[tags=batch-session-batch-insert-example]
----
====

[[batch-session-scroll]]
==== Session scroll

When you retrieve and update data, `flush()` and `clear()` the session regularly.
In addition, use method `scroll()` to take advantage of server-side cursors for queries that return many rows of data.

[[batch-session-scroll-example]]
.Using `scroll()`
====
[source, JAVA, indent=0]
----
include::{sourcedir}/BatchTest.java[tags=batch-session-scroll-example]
----
====

[IMPORTANT]
====
If left unclosed by the application, Hibernate will automatically close the underlying resources (e.g. `ResultSet` and `PreparedStatement`) used internally by the `ScrollableResults` when the current transaction is ended (either commit or rollback).

However, it is good practice to close the `ScrollableResults` explicitly.
====

==== StatelessSession

`StatelessSession` is a command-oriented API provided by Hibernate.
Use it to stream data to and from the database in the form of detached objects.
A `StatelessSession` has no persistence context associated with it and does not provide many of the higher-level lifecycle semantics.

Some of the things not provided by a `StatelessSession` include:

* a first-level cache
* interaction with any second-level or query cache
* transactional write-behind or automatic dirty checking

Limitations of `StatelessSession`:

* Operations performed using a stateless session never cascade to associated instances.
* Collections are ignored by a stateless session.
* Lazy loading of associations is not supported.
* Operations performed via a stateless session bypass Hibernate's event model and interceptors.
* Due to the lack of a first-level cache, Stateless sessions are vulnerable to data aliasing effects.
* A stateless session is a lower-level abstraction that is much closer to the underlying JDBC.

[[batch-stateless-session-example]]
.Using a  `StatelessSession`
====
[source, JAVA, indent=0]
----
include::{sourcedir}/BatchTest.java[tags=batch-stateless-session-example]
----
====

The `Customer` instances returned by the query are immediately detached.
They are never associated with any persistence context.

The `insert()`, `update()`, and `delete()` operations defined by the `StatelessSession` interface operate directly on database rows.
They cause the corresponding SQL operations to be executed immediately.
They have different semantics from the `save()`, `saveOrUpdate()`, and `delete()` operations defined by the `Session` interface.

[[batch-bulk-hql]]
=== Hibernate Query Language for DML

DML, or Data Manipulation Language, refers to SQL statements such as `INSERT`, `UPDATE`, and `DELETE`.
Hibernate provides methods for bulk SQL-style DML statement execution, in the form of Hibernate Query Language (HQL).

[[batch-bulk-hql-update-delete]]
==== HQL/JPQL for UPDATE and DELETE

Both the Hibernate native Query Language and JPQL (Java Persistence Query Language) provide support for bulk UPDATE and DELETE.

[[batch-bulk-hql-update-delete-example]]
.Pseudo-syntax for UPDATE and DELETE statements using HQL
====
[source, JAVA, indent=0]
----
UPDATE FROM EntityName e WHERE e.name = ?

DELETE FROM EntityName e WHERE e.name = ?
----
====

[NOTE]
====
Although the `FROM` and `WHERE` clauses are optional, it is good practice to declare them explicitly.
====

The `FROM` clause can only refer to a single entity, which can be aliased.
If the entity name is aliased, any property references must be qualified using that alias.
If the entity name is not aliased, then it is illegal for any property references to be qualified.

[NOTE]
====
Joins, either implicit or explicit, are prohibited in a bulk HQL query.
You can use sub-queries in the `WHERE` clause, and the sub-queries themselves can contain joins.
====

[[batch-bulk-jpql-update-example]]
.Executing a JPQL `UPDATE`, using the `Query.executeUpdate()`
====
[source, JAVA, indent=0]
----
include::{sourcedir}/BatchTest.java[tags=batch-bulk-jpql-update-example]
----
====

[[batch-bulk-hql-update-example]]
.Executing an HQL `UPDATE`, using the `Query.executeUpdate()`
====
[source, JAVA, indent=0]
----
include::{sourcedir}/BatchTest.java[tags=batch-bulk-hql-update-example]
----
====

In keeping with the EJB3 specification, HQL `UPDATE` statements, by default, do not effect the version or the timestamp property values for the affected entities.
You can use a versioned update to force Hibernate to reset the version or timestamp property values, by adding the `VERSIONED` keyword after the `UPDATE` keyword.

[[batch-bulk-hql-update-version-example]]
.Updating the version of timestamp
====
[source, JAVA, indent=0]
----
include::{sourcedir}/BatchTest.java[tags=batch-bulk-hql-update-version-example]
----
====

[NOTE]
====
If you use the `VERSIONED` statement, you cannot use custom version types that implement the `org.hibernate.usertype.UserVersionType`.

This feature is only available in HQL since it's not standardized by JPA.
====

[[batch-bulk-jpql-delete-example]]
.A JPQL `DELETE` statement
====
[source, JAVA, indent=0]
----
include::{sourcedir}/BatchTest.java[tags=batch-bulk-jpql-delete-example]
----
====

[[batch-bulk-hql-delete-example]]
.An HQL `DELETE` statement
====
[source, JAVA, indent=0]
----
include::{sourcedir}/BatchTest.java[tags=batch-bulk-hql-delete-example]
----
====

Method `Query.executeUpdate()` returns an `int` value, which indicates the number of entities affected by the operation.
This may or may not correlate to the number of rows affected in the database.
A JPQL/HQL bulk operation might result in multiple SQL statements being executed, such as for joined-subclass.
In the example of joined-subclass, a `DELETE` against one of the subclasses may actually result in deletes in the tables underlying the join, or further down the inheritance hierarchy.

==== HQL syntax for INSERT

.Pseudo-syntax for INSERT statements
====
[source, JAVA, indent=0]
----
INSERT INTO EntityName
	properties_list
SELECT properties_list
FROM ...
----
====

Only the `INSERT INTO ... SELECT ...` form is supported.
You cannot specify explicit values to insert.

The `properties_list` is analogous to the column specification in the `SQL` `INSERT` statement.
For entities involved in mapped inheritance, you can only use properties directly defined on that given class-level in the `properties_list`.
Superclass properties are not allowed and subclass properties are irrelevant.
In other words, `INSERT` statements are inherently non-polymorphic.

The SELECT statement can be any valid HQL select query, but the return types must match the types expected by the INSERT.
Hibernate verifies the return types during query compilation, instead of expecting the database to check it.
Problems might result from Hibernate types which are equivalent, rather than equal.
One such example is a mismatch between a property defined as an `org.hibernate.type.DateType` and a property defined as an `org.hibernate.type.TimestampType`,
even though the database may not make a distinction, or may be capable of handling the conversion.

If id property is not specified in the `properties_list`,
Hibernate generates a value automatically.
Automatic generation is only available if you use ID generators which operate on the database.
Otherwise, Hibernate throws an exception during parsing.
Available in-database generators are `org.hibernate.id.SequenceGenerator` and its subclasses, and objects which implement `org.hibernate.id.PostInsertIdentifierGenerator`.

For properties mapped as either version or timestamp, the insert statement gives you two options.
You can either specify the property in the properties_list, in which case its value is taken from the corresponding select expressions or omit it from the properties_list,
in which case the seed value defined by the org.hibernate.type.VersionType is used.

[[batch-bulk-hql-insert-example]]
.HQL INSERT statement
====
[source, JAVA, indent=0]
----
include::{sourcedir}/BatchTest.java[tags=batch-bulk-hql-insert-example]
----
====

This section is only a brief overview of HQL. For more information, see <<chapters/query/hql/HQL.adoc#hql,HQL>>.

[[batch-bulk-hql-strategies]]
==== Bulk-id strategies

This article is about the https://hibernate.atlassian.net/browse/HHH-11262[HHH-11262] JIRA issue which now allows the bulk-id
strategies to work even when you cannot create temporary tables.

[[batch-bulk-hql-strategies-class-diagram]]
===== Class diagram

Considering we have the following entities:

image:images/domain/bulkid/temp_table_class_diagram.png[Entity class diagram]

The `Person` entity is the base class of this entity inheritance model, and is mapped as follows:

[[batch-bulk-hql-temp-table-base-class-example]]
.Bulk-id base class entity
====
[source, JAVA, indent=0]
----
include::{bulkid-sourcedir}/AbstractBulkCompositeIdTest.java[tags=batch-bulk-hql-temp-table-base-class-example]
----
====

Both the `Doctor` and `Engineer` entity classes extend the `Person` base class:

[[batch-bulk-hql-temp-table-sub-classes-example]]
.Bulk-id subclass entities
====
[source, JAVA, indent=0]
----
include::{bulkid-sourcedir}/AbstractBulkCompositeIdTest.java[tags=batch-bulk-hql-temp-table-sub-classes-example]
----
====

[[batch-bulk-hql-strategies-inheritance-tree]]
===== Inheritance tree bulk processing

Now, when you try to execute a bulk entity delete query:

[[batch-bulk-hql-temp-table-delete-query-example]]
.Bulk-id delete query example
====
[source, JAVA, indent=0]
----
include::{bulkid-sourcedir}/AbstractBulkCompositeIdTest.java[tags=batch-bulk-hql-temp-table-delete-query-example]
----

[source, SQL, indent=0]
----
include::{extrasdir}/batch-bulk-hql-temp-table-delete-query-example.sql[]
----
====

`HT_Person` is a temporary table that Hibernate creates to hold all the entity identifiers that are to be updated or deleted by the bulk id operation.
The temporary table can be either global or local, depending on the underlying database capabilities.

[[batch-bulk-hql-strategies-non-temporary-table]]
===== Non-temporary table bulk-id strategies

As the https://hibernate.atlassian.net/browse/HHH-11262[HHH-11262] issue describes, there are use cases when the application developer cannot use temporary tables because
the database user lacks this privilege.

In this case, we defined several options which you can choose depending on your database capabilities:

- `InlineIdsInClauseBulkIdStrategy`
- `InlineIdsSubSelectValueListBulkIdStrategy`
- `InlineIdsOrClauseBulkIdStrategy`
- `CteValuesListBulkIdStrategy`

[[batch-bulk-hql-strategies-InlineIdsInClauseBulkIdStrategy]]
====== `InlineIdsInClauseBulkIdStrategy`

To use this strategy, you need to configure the following configuration property:

[source,xml]
----
<property name="hibernate.hql.bulk_id_strategy"
          value="org.hibernate.hql.spi.id.inline.InlineIdsInClauseBulkIdStrategy"
/>
----

Now, when running the previous test case, Hibernate generates the following SQL statements:

[[batch-bulk-hql-InlineIdsInClauseBulkIdStrategy-delete-query-example]]
.`InlineIdsInClauseBulkIdStrategy` delete entity query example
====
[source, SQL, indent=0]
----
include::{extrasdir}/batch-bulk-hql-InlineIdsInClauseBulkIdStrategy-delete-query-example.sql[]
----
====

So, the entity identifiers are selected first and used for each particular update or delete statement.

[TIP]
====
The IN clause row value expression has long been supported by Oracle, PostgreSQL, and nowadays by MySQL 5.7.
However, SQL Server 2014 does not support it, so you'll have to use a different strategy.
====

[[batch-bulk-hql-strategies-InlineIdsSubSelectValueListBulkIdStrategy]]
====== `InlineIdsSubSelectValueListBulkIdStrategy`

To use this strategy, you need to configure the following configuration property:

[source,xml]
----
<property name="hibernate.hql.bulk_id_strategy"
          value="org.hibernate.hql.spi.id.inline.InlineIdsSubSelectValueListBulkIdStrategy"
/>
----

Now, when running the previous test case, Hibernate generates the following SQL statements:

[[batch-bulk-hql-InlineIdsSubSelectValueListBulkIdStrategy-delete-query-example]]
.`InlineIdsSubSelectValueListBulkIdStrategy` delete entity query example
====
[source, SQL, indent=0]
----
include::{extrasdir}/batch-bulk-hql-InlineIdsSubSelectValueListBulkIdStrategy-delete-query-example.sql[]
----
====

[TIP]
====
The underlying database must support the `VALUES` list clause, like PostgreSQL or SQL Server 2008.
However, this strategy requires the IN-clause row value expression for composite identifiers, and for this reason, you can only use the `InlineIdsSubSelectValueListBulkIdStrategy` strategy with PostgreSQL.
====

[[batch-bulk-hql-strategies-InlineIdsOrClauseBulkIdStrategy]]
====== `InlineIdsOrClauseBulkIdStrategy`

To use this strategy, you need to configure the following configuration property:

[source,xml]
----
<property name="hibernate.hql.bulk_id_strategy"
          value="org.hibernate.hql.spi.id.inline.InlineIdsOrClauseBulkIdStrategy"
/>
----

Now, when running the previous test case, Hibernate generates the following SQL statements:

[[batch-bulk-hql-InlineIdsOrClauseBulkIdStrategy-delete-query-example]]
.`InlineIdsOrClauseBulkIdStrategy` delete entity query example
====
[source, SQL, indent=0]
----
include::{extrasdir}/batch-bulk-hql-InlineIdsOrClauseBulkIdStrategy-delete-query-example.sql[]
----
====

[TIP]
====
The `InlineIdsOrClauseBulkIdStrategy` strategy has the advantage of being supported by all the major relational database systems (e.g. Oracle, SQL Server, MySQL, and PostgreSQL).
====

[[batch-bulk-hql-strategies-CteValuesListBulkIdStrategy]]
====== `CteValuesListBulkIdStrategy`

To use this strategy, you need to configure the following configuration property:

[source,xml]
----
<property name="hibernate.hql.bulk_id_strategy"
          value="org.hibernate.hql.spi.id.inline.CteValuesListBulkIdStrategy"
/>
----

Now, when running the previous test case, Hibernate generates the following SQL statements:

[[batch-bulk-hql-CteValuesListBulkIdStrategy-delete-query-example]]
.`CteValuesListBulkIdStrategy` delete entity query example
====
[source, SQL, indent=0]
----
include::{extrasdir}/batch-bulk-hql-CteValuesListBulkIdStrategy-delete-query-example.sql[]
----
====

[TIP]
====
The underlying database must support CTE (Common Table Expressions) that can be referenced from non-query statements as well. For instance, PostgreSQL supports this feature since version 9.1 and SQL Server offers support for it since version 2005.

The underlying database must also support the VALUES list clause, like PostgreSQL or SQL Server 2008.

However, this strategy requires the IN-clause row value expression for composite identifiers, so you can only use this strategy with PostgreSQL.
====

If you can use temporary tables, that's probably the best choice.
However, if you are not allowed to create temporary tables, you must pick one of these four strategies that works with your underlying database.
Before making up your mind, you should benchmark which one works best for your current workload.
For instance, http://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/[CTE are optimization fences in PostgreSQL], so make sure you measure before making a decision.

If you're using Oracle or MySQL 5.7, you can choose either `InlineIdsOrClauseBulkIdStrategy` or `InlineIdsInClauseBulkIdStrategy`.
For older version of MySQL, then you can only use `InlineIdsOrClauseBulkIdStrategy`.

If you're using SQL Server, `InlineIdsOrClauseBulkIdStrategy` is the only option for you.

If you're using PostgreSQL, then you  have the luxury of choosing any of these four strategies.

