Hibernate @generatedvalue for HSQLDB

Question

I have the following definition for an id field in an entity that is mapped to a table in HSQLDB.

...
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name = "ID")
private Integer id;
...

But this does not seem to generate the an unique id; instead an attempt is made to insert null into the column which results in failure. If, I manually create a sequence and generation strategy to use that sequence then the data is persisted as expected.

Doesn't a generation strategy of auto imply that the provider (hibernate in this case) will automatically choose the correct approach and do all the heavy lifting as needed (create sequence, use a native approach or whatever works for that particular platform)? Is my understanding incorrect?

Solution 1

Doesn't a generation strategy of auto imply that the provider (hibernate in this case) will automatically choose the correct approach and do all the heavy lifting as needed (create sequence, use a native approach or whatever works for that particular platform)? Is my understanding incorrect?

It does in theory (it defaults to IDENTITY with HSQLDB) and it works for me. This begs the following questions:

• What dialect are you using (just in case)?
• How did you create the table?
• Can you show the DDL (activate the logging of org.hibernate.tool.hbm2ddl if required)?
• How do you insert (through Hibernate's API, right?)?

Here is a sample DDL for an entity Foo when using HSQLDB:

create table Foo (
    id bigint generated by default as identity (start with 1), 
    bar varchar(100),
    primary key (id)
)

---

I created the table using the HSQL DB manager. Just normal create table address... I had not set the id column as identity in my case - just set it as primary key.

Then you have your answer, use an IDENTITY column.

While Hibernate does choose the right strategy and does generate the appropriate INSERT statements (passing null into the id which is expected to be persisted into an IDENTITY column), it won't create or alter your physical model if you don't use the DDL generation and export capabilities.

Solution 2

I had the same issue when using a JpaSchemaGenerator utility class that I wrote.

When generating the schema for a org.hibernate.dialect.HSQLDialect (where I use a SEQUENCE to generate my unique IDs), I use the following Hibernate property:

hibernate.id.new_generator_mappings=true

This results in the following CREATE statement:

CREATE TABLE BATCH (
    BAT_ID NUMBER(19,0) NOT NULL,
    BAT_EXPIRY_DATE TIMESTAMP,
    BAT_NUMBER VARCHAR2(255 CHAR),
    BAT_MAT_ID NUMBER(19,0),
    PRIMARY KEY (BAT_ID)
);

But when I use this same property in my utility class to generate a schema using the org.hibernate.dialect.HSQLDialect, I get the following CREATE statement:

CREATE TABLE BATCH (
    BAT_ID BIGINT NOT NULL,
    BAT_EXPIRY_DATE TIMESTAMP,
    BAT_NUMBER VARCHAR(255),
    BAT_MAT_ID BIGINT,
    PRIMARY KEY (BAT_ID)
);

This would mean that if I created a Batch without an ID, it would not generate it for me and the NOT NULL constraint would cause an exception.

If I change the Hibernate property to the following:

hibernate.id.new_generator_mappings=false

Then it would generate the following CREATE statement:

CREATE TABLE BATCH (
    BAT_ID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
    BAT_EXPIRY_DATE TIMESTAMP,
    BAT_NUMBER VARCHAR(255),
    BAT_MAT_ID BIGINT,
    PRIMARY KEY (BAT_ID)
);

Which works perfectly when creating JPA entities with Hibernate.

Comments

[calvinkrishy]

* The dialect has been set to org.hibernate.dialect.HSQLDialect * I created the table using the HSQL DB manager. Just normal create table address... * I had created the table manually and not through Hibernate * Yes, through entitymanager's persist. I had not set the id column as identity in my case - just set it as primary key.

[Alexandr]

For some reasone the DDL command that is generated in my case is:CREATE TABLE employees (id BIGINT NOT NULL, name VARCHAR(50) NOT NULL, CONSTRAINT PK_EMPLOYEES PRIMARY KEY (id)); No identity is used at all.

[Dherik]

Your tip work for me, thanks! I had this problem when I upgrade the Hibernate and Hsqldb at the same time (to 5.0.10.Final and 2.4.0, respectively).

[JJ Roman]

In spring boot 2.2.6 and hsqldb 2.5 the property is: spring.jpa.hibernate.use-new-id-generator-mappings=false