How to implement a custom String sequence identifier generator with Hibernate

Question

I'm using hibernate with spring, h2 and liquibase and I'm trying to make a custom String id generator for my entities by taking example with this blog post but I'm getting an error : Caused by: org.hibernate.id.IdentifierGenerationException: Unknown integral data type for ids : java.lang.String

Here my SequenceStyleGenerator code :

public class CTCIDGenerator extends SequenceStyleGenerator {

    @Override
    public Serializable generate(SessionImplementor session, Object obj) {
        if (obj instanceof Identifiable) {
            Identifiable identifiable = (Identifiable) obj;
            Serializable id = identifiable.getId();
            if (id != null) {
                return id;
            }
        }
        return "CTC"+super.generate(session, obj);
    }
}

My entity code :

@Entity
@Table(name = "contact")
public class Contact implements Serializable, Identifiable<String> {

    private static final long serialVersionUID = 1L;

    @Id
    @GenericGenerator(
        name = "assigned-sequence",
        strategy =     "net.atos.seirich.support.domain.idgenerator.CTCIDGenerator",
        parameters = @org.hibernate.annotations.Parameter(
            name = "sequence_name", 
            value = "hibernate_sequence"
        )
    )
    @GeneratedValue(generator = "assigned-sequence", strategy = GenerationType.SEQUENCE)
    private String id;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }
}

And the liquibase XML :

<?xml version="1.0" encoding="utf-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd">

    <property name="autoIncrement" value="true" dbms="mysql,h2,postgresql,oracle"/>

    <property name="floatType" value="float4" dbms="postgresql, h2"/>
    <property name="floatType" value="float" dbms="mysql, oracle"/>

    <changeSet id="20160513091901-1" author="jhipster">
        <createTable tableName="contact">
            <column name="id" type="longvarchar" autoIncrement="${autoIncrement}">
                <constraints primaryKey="true" nullable="false"/>
            </column>
    </changeSet>
</databaseChangeLog>

Btw is it possible to avoid the parameter sequence_name so hibernate can handle this by itself ?

If anyone can help me, Thanks !

Similar discussion has happened at: http://stackoverflow.com/a/15470272/2128503 — Moon, Jun 10 '16 at 11:43

Vlad Mihalcea · Accepted Answer · 2021-01-09T15:29:22.143

The problem is that SequenceStyleGenerator expects to return a numerical value, not a String.

I already tried a solution to this problem and it works like a charm. Therefore, you need to change your generator like this:

public class StringSequenceIdentifier implements IdentifierGenerator, Configurable {

    private String sequenceCallSyntax;

    @Override
    public void configure(Type type, Properties params, ServiceRegistry serviceRegistry) throws MappingException {
        final JdbcEnvironment jdbcEnvironment = serviceRegistry.getService(JdbcEnvironment.class);
        final Dialect dialect = jdbcEnvironment.getDialect();

        final String sequencePerEntitySuffix = ConfigurationHelper.getString(CONFIG_SEQUENCE_PER_ENTITY_SUFFIX, params, DEF_SEQUENCE_SUFFIX);

        final String defaultSequenceName = ConfigurationHelper.getBoolean(CONFIG_PREFER_SEQUENCE_PER_ENTITY, params, false)
                ? params.getProperty(JPA_ENTITY_NAME) + sequencePerEntitySuffix
                : DEF_SEQUENCE_NAME;

        sequenceCallSyntax = dialect.getSequenceNextValString(ConfigurationHelper.getString(SEQUENCE_PARAM, params, defaultSequenceName));
    }

    @Override
    public Serializable generate(SessionImplementor session, Object obj) {
        if (obj instanceof Identifiable) {
            Identifiable identifiable = (Identifiable) obj;
            Serializable id = identifiable.getId();
            if (id != null) {
                return id;
            }
        }
        long seqValue = ((Number) Session.class.cast(session)
            .createSQLQuery(sequenceCallSyntax)
            .uniqueResult()).longValue();

        return "CTC" + seqValue;
    }
}

Your mapping becomes:

@Entity(name = "Post")
@Table(name = "post")
public static class Post implements Identifiable<String> {

    @Id
    @GenericGenerator(
        name = "assigned-sequence",
        strategy = "com.vladmihalcea.book.hpjp.hibernate.identifier.StringSequenceIdentifier",
        parameters = @org.hibernate.annotations.Parameter(name = "sequence_name", value = "hibernate_sequence")
    )
    @GeneratedValue(generator = "assigned-sequence", strategy = GenerationType.SEQUENCE)
    private String id;

    @Version
    private Integer version;

    public Post() {
    }

    public Post(String id) {
        this.id = id;
    }

    @Override
    public String getId() {
        return id;
    }
}

Now, when you insert the following entities:

doInJPA(entityManager -> {
    entityManager.persist(new Post());
    entityManager.persist(new Post("ABC"));
    entityManager.persist(new Post());
    entityManager.persist(new Post("DEF"));
});

Hibernate generates the right identifier:

Query:["select nextval ('hibernate_sequence')"], Params:[()]
Query:["select nextval ('hibernate_sequence')"], Params:[()]
Query:["insert into post (version, id) values (?, ?)"], Params:[(0, CTC1)]
Query:["insert into post (version, id) values (?, ?)"], Params:[(0, ABC)]
Query:["insert into post (version, id) values (?, ?)"], Params:[(0, CTC2)]
Query:["insert into post (version, id) values (?, ?)"], Params:[(0, DEF)]

Code available on GitHub.

I got error for mysql DB org.hibernate.MappingException: org.hibernate.dialect.MySQL5Dialect does not support sequences Any solution for this? — Zenith, Apr 04 '20 at 08:56
For MySQL, you need to extend the Hibernate [`IdentityGenerator`](https://docs.jboss.org/hibernate/orm/5.4/javadocs/org/hibernate/id/IdentityGenerator.html) and wrap the identity generation logic. — Vlad Mihalcea, Apr 04 '20 at 09:16
Hm.... I got `Could not fetch the SequenceInformation from the database org.postgresql.util.PSQLException: The column name start_value was not found in this ResultSet.` — dubnic, Oct 18 '21 at 07:32
Can I access the fields of my entity in your class? What I need is a String sequence generator that is composed by some of the fields of my entity. — Philippe Gioseffi, May 16 '23 at 05:00

score 1 · Answer 2 · answered Jun 10 '16 at 11:58

Yes, hibernate have prebuilt String generators. Just substitute your @GenericGenerator definition to another strategy.

@Entity
@Table(name = "contact")
public class Contact implements Serializable, Identifiable<String> {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid2")
    private String id;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }
}

For more information about different hibernate generators you can look at documentation.

Ok but I'm trying to do a custom String id generator for having ids like "CTC00001", CTC00002" ... etc without the sequence_name parameter :) — Takmashido, Jun 10 '16 at 12:24

cane · Answer 3 · 2023-05-17T15:01:29.723

it's possible to reuse sequence style generator by following solution

public class AppIdGenerator extends SequenceStyleGenerator {

    public static final String PREFIX_PARAM = "prefix";
    private String prefix;

    @Override
    public void configure(Type type, Properties properties,
                          ServiceRegistry serviceRegistry) throws MappingException {
        super.configure(new NamedBasicTypeImpl<>(new JavaTypeBasicAdaptor<>(Long.class),
                NumericJdbcType.INSTANCE, "long"), properties, serviceRegistry);
        prefix = StringUtils.capitalize(ConfigurationHelper.getString(PREFIX_PARAM, properties));
    }

    @Override
    public Serializable generate(SharedSessionContractImplementor session, Object obj) throws HibernateException {
        return String.format("%s%09d", prefix, ((Long) super.generate(session, obj)));
    }
}

and Entity

@Data
@EqualsAndHashCode(callSuper = false)
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = Subject.NAME, uniqueConstraints = {@UniqueConstraint(name = "UC_SUBJECT",
        columnNames = {"STUDY_KEY", "SUBJECT_UNIQUEID", "SUBJECT_UNIQUEID_SOURCE"})})
public class Subject extends Auditing {

  static final String NAME = "SUBJECT";

  static final String PREF = "SUBJ";

  @Id
  @GeneratedValue(generator = "subject_gen", strategy = GenerationType.SEQUENCE)
  @GenericGenerator(name = "subject_gen", strategy = "com.awesome.db.AppIdGenerator", parameters = {
          @org.hibernate.annotations.Parameter(name = "prefix", value = Subject.PREF),
          @org.hibernate.annotations.Parameter(name = "sequence_name", value = Subject.NAME + SequenceStyleGenerator.DEF_SEQUENCE_SUFFIX),
          @org.hibernate.annotations.Parameter(name = "increment_size", value = "1")
          //@org.hibernate.annotations.Parameter(name = "initial_value", value = "21"),
          //@org.hibernate.annotations.Parameter(name = "optimizer", value = "pooled-lo"),
  })
  @Column(name = "SUBJECT_KEY", unique = true, nullable = false, updatable = false, length = 13)
  private String subjectKey;

  @ManyToOne(optional = false)
  @JoinColumn(name = "STUDY_KEY", referencedColumnName = "STUDY_KEY", foreignKey = @ForeignKey(name = "FK_STUDY_KEY"),
      nullable = false, updatable = false)
  private Study study;

  @NotNull
  @Column(name = "SUBJECT_UNIQUEID", length = 120)
  private String subjectId;

  @NotNull
  @Column(name = "SUBJECT_UNIQUEID_SOURCE", length = 120)
  private String subjectIdSource;
}

In result:

uses numeric db sequence counter
supports the database structure and integrated optimizer“s options

Keep in mind, if You are using any population scripts like import.sql/data.sql then initial_value parameter must be synchronised with number of rows in the table manually (hardcoded or provided from outside app) or nextval adopted to the id string key format

--import.sql
INSERT INTO SUBJECT (subject_key, subject_uniqueid, subject_uniqueid_source, created_date, modified_date, study_key) VALUES('SUBJ'||to_char(nextval('subject_seq'), 'FM000000009'),'1234','Other.App','2023-05-17 14:05:46.521000','2023-05-17 14:05:46.521000','STUD000000001');

It works for my case that was wrong with @override public void configuration(....). — Borin --help, May 21 '23 at 06:22

How to implement a custom String sequence identifier generator with Hibernate

3 Answers3

Linked