Hibernate attempting to insert null id in CrudRepository

Question

I'm doing some testing on a spring application using an in-memory H2 database. The database has a 'message' table with the schema:

CREATE TABLE `message` (
  `id` bigint(20) NOT NULL,
  `body` CLOB,
  `channels` varchar(500) NOT NULL,
  `end_date` datetime DEFAULT NULL,
  `start` datetime DEFAULT NULL,
  `status` varchar(120) DEFAULT NULL,
  `title` varchar(120) DEFAULT NULL,
  `approved` bit(1) DEFAULT NULL,
  `approver_name` varchar(150) DEFAULT NULL,
  `approver_sub` varchar(120) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `creator_name` varchar(150) DEFAULT NULL,
  `creator_sub` varchar(120) DEFAULT NULL,
  `modify_date` datetime DEFAULT NULL,
  `affiliations` varchar(500) NOT NULL,
  `html` bit(1) NOT NULL,
  `reject_reason` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

and an entity class:

@Entity
public class Message {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id", length = 100, nullable = false, unique = true)
    private long id;

    @Column(name = "title", length = 120, nullable = true)
    private String title;

    @Lob
    @Column(name = "body", nullable = true)
    private String body;
...
}

In my code, I'm trying to insert a new entry into the message table. I have a service method like:

public Message saveMessage(MessageInput messageInput, LocalDateTime start, LocalDateTime end, String status, String requestor) {
        Message message = new Message();
        message.setTitle(messageInput.getMessage_title());
        message.setBody(messageInput.getMessage_body());
        messageRepository.save(message);
} 

where messageRepository is a org.springframework.data.repository.CrudRepository

I turned on logging and when I run my code I get the following log messages:

Hibernate: insert into message (id, affiliations, approved, approver_name, approver_sub, body, channels, create_date, creator_name, creator_sub, end_date, html, modify_date, reject_reason, start, status, title) values (default, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2023-08-31 12:30:03 [main] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - [[Employee, Staff]]
2023-08-31 12:30:03 [main] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [2] as [BOOLEAN] - [false]
2023-08-31 12:30:03 [main] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [3] as [VARCHAR] - [null]
2023-08-31 12:30:03 [main] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [4] as [VARCHAR] - [null]
2023-08-31 12:30:03 [main] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [5] as [CLOB] - [the test body]
2023-08-31 12:30:03 [main] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [6] as [VARCHAR] - [[57823ff6-e262-4201-8766-84cd17dfe116]]
2023-08-31 12:30:03 [main] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [7] as [TIMESTAMP] - [2023-08-31T12:30:03.304774]
2023-08-31 12:30:03 [main] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [8] as [VARCHAR] - [ptb014]
2023-08-31 12:30:03 [main] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [9] as [VARCHAR] - [ptb014]
2023-08-31 12:30:03 [main] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [10] as [TIMESTAMP] - [2023-09-07T12:30:03.268680]
2023-08-31 12:30:03 [main] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [11] as [BOOLEAN] - [false]
2023-08-31 12:30:03 [main] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [12] as [TIMESTAMP] - [2023-08-31T12:30:03.304793]
2023-08-31 12:30:03 [main] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [13] as [VARCHAR] - [null]
2023-08-31 12:30:03 [main] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [14] as [TIMESTAMP] - [2023-08-31T12:30:03.268680]
2023-08-31 12:30:03 [main] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [15] as [VARCHAR] - [MODERATOR_APPROVED]
2023-08-31 12:30:03 [main] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [16] as [VARCHAR] - [A Title]
2023-08-31 12:30:03 [main] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - NULL not allowed for column "ID"; SQL statement:
insert into message (id, affiliations, approved, approver_name, approver_sub, body, channels, create_date, creator_name, creator_sub, end_date, html, modify_date, reject_reason, start, status, title) values (default, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [23502-220]

Looking at the logs, it seems that the insert is including an id parameter, but since the database is expecting to generate that value, it is getting passed NULL. I did some research, and most of the suggestions I have seen suggest either setting the GenerationType to IDENTITY and or changing the spring.jpa.hibernate.ddl-auto configuration to 'update'. I tried both and I'm still getting the same results. What do I need to do to get identity generation to work?

Answer 1

You need to declare the id column as an identity column:

id BIGINT GENERATED BY DEFAULT AS IDENTITY

In MySQL or MariaDB compatibility modes of H2 you can use non-stantard

`id` BIGINT AUTO_INCREMENT

instead.