Package com.github.marschall.hibernate.batchsequencegenerator

Class BatchSequenceGenerator

  • All Implemented Interfaces:
    ExportableProducer, BulkInsertionCapableIdentifierGenerator, Configurable, StandardGenerator, IdentifierGenerator, OptimizableGenerator, PersistentIdentifierGenerator
    public class BatchSequenceGenerator
extends Object
implements BulkInsertionCapableIdentifierGenerator, PersistentIdentifierGenerator
    A sequence generator that uses a recursive query to fetch multiple values from a sequence in a single database access.

    Parameters

    The following configuration parameters are supported:
    "sequence"
    mandatory, name of the sequence to use
    "fetch_size"
    optional, how many sequence numbers should be fetched at a time, default is 10

    SQL

    Per default the generated SELECT will look something like this 
    
 WITH RECURSIVE t(n, level_num) AS (
   SELECT nextval(seq_xxx) as n, 1 as level_num
     UNION ALL
   SELECT nextval(seq_xxx) as n, level_num + 1 as level_num
   FROM t
   WHERE level_num < ?)
 SELECT n
   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.