Package com.github.marschall.hibernate.batchsequencegenerator

Class BatchSequenceGenerator

java.lang.Object
com.github.marschall.hibernate.batchsequencegenerator.BatchSequenceGenerator
All Implemented Interfaces:
Serializable, ExportableProducer, BeforeExecutionGenerator, Generator, BulkInsertionCapableIdentifierGenerator, Configurable, IdentifierGenerator
public final class BatchSequenceGenerator extends Object implements BulkInsertionCapableIdentifierGenerator, IdentifierGenerator, ExportableProducer
A sequence generator that uses a recursive query to fetch multiple values from a sequence in a single database access.

Configuration


 @Id
 @BatchSequence(name = "SOME_SEQUENCE_NAME", fetch_size = SOME_FETCH_SIZE_VALUE)
 private Long someColumnName;

SQL

Per default the generated SELECT will look something like this 

 WITH RECURSIVE t(n) AS (
   SELECT 1
     UNION ALL
   SELECT n + 1
   FROM t
   WHERE n < ?)
 SELECT nextval(seq_xxx)
   FROM t;

DB2

For DB2 the generated SELECT will look something like this 

 WITH t(n) AS (
   SELECT 1 as n
     FROM (VALUES 1)
       UNION ALL
     SELECT n + 1 as n
       FROM t
      WHERE n < ?)
 SELECT next value for SEQ_CHILD_ID as n
   FROM t;

HSQLDB

For HSQLDB the generated SELECT will look something like this 

 SELECT next value for seq_parent_id
   FROM UNNEST(SEQUENCE_ARRAY(1, ?, 1));

Oracle

For Oracle the generated SELECT will look something like this because Oracle does not support using recursive common table expressions to fetch multiple values from a sequence. 

 SELECT seq_xxx.nextval
 FROM dual
 CONNECT BY rownum <= ?

SQL Server

For SQL Server the generated SELECT will look something like this 

 WITH t(n) AS (
   SELECT 1 as n
     UNION ALL
   SELECT n + 1 as n
     FROM t
    WHERE n < ?)
 SELECT NEXT VALUE FOR seq_xxx as n
   FROM t

Firebird

For Firebird the generated SELECT will look something like this 

 WITH RECURSIVE t(n, level_num) AS (
   SELECT NEXT VALUE FOR seq_xxx as n, 1 as level_num
   FROM rdb$database
     UNION ALL
   SELECT NEXT VALUE FOR seq_xxx as n, level_num + 1 as level_num
     FROM t
    WHERE level_num < ?)
 SELECT n
   FROM t

Database Support

The following RDBMS have been verified to work
  • DB2
  • Firebird
  • Oracle
  • H2
  • HSQLDB
  • MariaDB
  • Postgres
  • SQL Sever
In theory any RDBMS that supports WITH RECURSIVE and sequences is supported.
See Also: