HQL supports statements of the form 'insert into CustomerArchive (id, name) select ... from Customer c where ...'. What does such a statement do, what restrictions apply around identifiers and version columns, and when is it worth using instead of reading rows into memory?
answer
- HQL-only INSERT ... SELECT
- executeUpdate returns row count
- id: select it or use a sequence
- identity generator not supported
- no @PrePersist, no cascade
basics
~20 sIt performs a server-side INSERT ... SELECT: rows are copied entity-to-entity without leaving the database. No entities are instantiated, so no callbacks or cascades run. Identifiers must be selected or come from a generator usable inside the statement, not an identity column.
solid answer
~60 sHQL's insert-select translates to a single SQL INSERT INTO target (...) SELECT ..., executed with executeUpdate(), returning the number of inserted rows. It is written against entity and attribute names, so Hibernate resolves the tables and columns for you, and the data never travels to the JVM. The main restrictions: the property list and the select list must line up in order and type; the identifier must either be selected explicitly or be produced by a generator that can be applied inside the statement - a database identity column cannot, because the value only exists after each row is inserted; and if the target entity has a version attribute you either select it or let Hibernate seed it. It is worth it for copy-shaped work: archiving, snapshotting, materialising a summary table, seeding a partition. A read-then-persist loop for the same job costs a round trip per row plus a persistence context full of objects. When you need per-row logic, callbacks or transformation that SQL cannot express, go back to reading entities.
code
java · 7 linesint copied = session.createMutationQuery(
"insert into CustomerArchive (id, name, archivedAt) "
+ "select c.id, c.name, :now from Customer c "
+ "where c.lastLogin < :cutoff")
.setParameter("now", Instant.now())
.setParameter("cutoff", cutoff)
.executeUpdate();go deeper
Know it exists, that it is HQL rather than standard JPQL, and that no entities are created in memory.
Explain the property-list and select-list alignment and the identifier rules, especially why identity columns do not fit.
Weigh it against read-then-persist by asking whether the transformation is expressible in SQL, and raise transaction size and chunking.
Decide whether such data movement belongs in the ORM at all versus a migration tool or database job, and who owns the resulting schema and audit story.
## What the statement is Standard JPQL defines UPDATE and DELETE bulk statements but no INSERT. Hibernate's HQL adds one, for example 'insert into CustomerArchive (id, name, archivedAt) select c.id, c.name, :now from Customer c where c.lastLogin < :cutoff'. Hibernate maps CustomerArchive and Customer to their tables and emits a single insert into customer_archive (id, name, archived_at) select ... from customer where .... Execution is via executeUpdate(), and the return value is the row count. Everything happens inside the database; no result set crosses the network and no entity instances exist. Because it is a bulk statement, it inherits the whole family's semantics: no lifecycle callbacks (@PrePersist never fires), no cascading, the persistence context is not populated with the new rows, and the second-level cache regions for the target entity are invalidated rather than filled. ## The identifier question Every inserted row needs a primary key, and the statement has no per-row Java step in which a generator could run. Two workable shapes: - Select the id. Common for archive and copy scenarios where the archive keeps the source identifier, as above. The property list simply includes the id. - Let a database-side generator supply it. A sequence can be referenced inside the statement, so a target whose identifier uses a sequence generator can have its id omitted and filled per row. A generator whose value is only known after insertion - an identity or auto-increment column - cannot work this way, and Hibernate cannot support the omission. That asymmetry is the single most-asked detail about this feature: identity columns and HQL insert-select do not combine. ## Versions and types If the target entity declares a version attribute, Hibernate either takes the value you selected or seeds it with the initial version, so the inserted rows are consistent with normal optimistic-locking use afterwards. Types must match position by position between the property list and the select list; Hibernate checks this when the query is compiled, so mistakes surface early rather than as a raw database error. Attribute converters mapped on the target are applied where Hibernate can express them, but anything that needs Java code per row is a signal to use the read-then-persist path instead. Since Hibernate 6.1 HQL also accepts a values form for inserting literal rows, which is occasionally handy for seeding but is not what the select form is for. ## When to reach for it The decisive factor is whether the transformation is expressible in SQL. Good fits: archiving rows older than a cutoff; snapshotting a table before a migration; materialising a denormalised or summary table from a join; splitting one table into partitions; seeding a new table during a schema change. In all of these, the JVM adds nothing but latency, and the set-based version runs in a fraction of the time. Poor fits: anything needing per-row branching, external calls, encryption performed in application code, generated identifiers that live in Java, or lifecycle callbacks that maintain audit fields. Also poor when the target must publish domain events for each row - the statement is invisible to the object layer. ## Operational care One statement inserting tens of millions of rows is a single transaction: a long-held write, a large amount of undo/redo or WAL, and potentially significant lock or bloat pressure. In a live system, chunk it by a key range and commit between chunks, exactly as you would with a hand-written INSERT ... SELECT. Also be aware that the statement's cost is invisible to the ORM's usual instrumentation - it looks like one query in the log and may run for minutes - so measure it with database-side tooling rather than the query count. Finally, since the persistence context knows nothing about the inserted rows, any code later in the same transaction that queries the target entity will read them from the database - which is correct, but means the rows you just created are not in memory and any assumption to the contrary is a bug.
- Why can a target entity whose identifier uses a database identity column not omit the id in an HQL insert-select?An identity value is assigned by the database as each row is inserted and is only readable afterwards, so there is no way to weave it into a single set-based statement that Hibernate constructs and validates up front. A sequence, by contrast, can be called inline for every row, so sequence-based identifiers work.
- What do you lose compared with reading entities and persisting copies?Lifecycle callbacks such as @PrePersist, cascading to associated entities, any per-row Java logic including converters that need code, and visibility of the new rows in the persistence context. You gain one statement instead of two round trips per row, and no heap pressure from materialising the data.
saying these in an interview costs you the question
- Believing standard JPQL defines an INSERT statement - it is a Hibernate HQL extension
- Expecting @PrePersist or cascade behaviour for the inserted rows
- Assuming an identity or auto-increment identifier can be omitted from the property list
- Expecting the inserted rows to appear in the persistence context afterwards
- Running a single statement over tens of millions of rows in production without chunking