2

I want to persist TODOs in a H2 DB facilitating a Spring Boot application.

The following SQL script initializes the DB and it works properly:

DROP TABLE IF EXISTS todos;

CREATE TABLE todos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(50) NOT NULL UNIQUE,
  description VARCHAR(250) NOT NULL,
  completion_date DATE,
  priority VARCHAR(6) CHECK(priority IN ('LOW', 'MEDIUM', 'HIGH'))
);

INSERT INTO todos (title, description, priority) VALUES
  ('Create xxx Todo', 'An xxx-TODO must be created.', 'HIGH'),
  ('Delete xxx Todo', 'An xxx-TODO must be deleted.', 'HIGH'),
  ('Update xxx Todo', 'An xxx-TODO must be updated.', 'MEDIUM'),
  ('Complete xxx Todo', 'An xxx-TODO must be completed.', 'LOW');

Console output when starting Spring Boot:

Hibernate: drop table if exists todos CASCADE 
Hibernate: drop sequence if exists hibernate_sequence
Hibernate: create sequence hibernate_sequence start with 1 increment by 1
Hibernate: create table todos (id bigint not null, completion_date date, description varchar(250) not null, priority varchar(250) not null, title varchar(50) not null, primary key (id))
Hibernate: alter table todos add constraint UK_c14g1nqfdaaixe1nyw25h3t0n unique (title)

I implemented controller, service and repositiory in Java within the Spring Boot application. I used Postman to test the implemented functionality and getting all Todos works well but creating a Todo fails for the first 4 times because of an

org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Unique index or primarky key violated: "PRIMARY KEY ON PUBLIC.TODOS(ID) [1, 'Create xxx Todo', 'An xxx TODO must be created.', NULL, 'HIGH']"

This is the request body:

{
    "title": "Creating xxxx Todo via API",
    "description": "An xxxx TODO was created via API.",
    "id": null,
    "completionDate": null,
    "priority": "LOW"
}

This exception occurs 4 times with the following response:

{
    "timestamp": "2021-05-25T17:32:57.129+00:00",
    "status": 500,
    "error": "Internal Server Error",
    "message": "",
    "path": "/api/todo/create"
}

With the fifth attempt the Todo gets created:

{
    "title": "Create xxxx Todo via API",
    "description": "An xxxx TODO was created via API.",
    "id": 5,
    "completionDate": null,
    "priority": "LOW"
}

and the ID 5 was assigned to this record. Hence, the problem seems to be the number of inserted records during the H2 start-up when Spring Boot starts and initializes the H2 database.

In the Todo entity I annotated the id as follows:

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Long id;

How can I solve this problem that when I try to access the creation endpoint of the Spring Boot application via postman?

du-it
  • 2,561
  • 8
  • 42
  • 80
  • We(i) cannot see from your post, *why* there is a unique constraint on `title`, but *there is* one! (hibernate ...add constraint). But the (straight-forward) solution: Use unique `title`s!! (with every postman request) – xerx593 May 31 '21 at 09:59
  • You're very intent. After posting my question I already added the UNIQUE constraint (I edited this in my question yet) but the exception still occurs. The request I submitted using Postman contains a Todo with a title currently not existend in the database. – du-it Jun 01 '21 at 11:43
  • ..ok! (sry, the title thing confuses the case addionally) ..but now, we(i;) see: (if post is consistent): With `AUTO` (on h2), hibernate generates the `hibernate_sequence` (with start=1 and increment=1), uses that obviously ...and (of course) explains the *first 4* failing ids (which are not considered from your init script)... solution(s)... – xerx593 Jun 01 '21 at 11:57
  • solution(s): 1. to tell hibernate to use the "correct auto incrementing strategy/sequence" 2. simple but hacky: to `ALTER hibernate_seqence` ..to 5. 3. (when you have an init script anyways): Omit `@GeneratedValue` (deactivate hiberante sequencing for that) – xerx593 Jun 01 '21 at 12:04
  • I'm facing the same but I don't understand your solution – Cristian May 27 '22 at 11:36
  • 1
    Please have a look at https://stackoverflow.com/questions/72402946/h2-auto-increment-not-working-after-update-from-1-4-200-to-2-1-212 – Cristian May 27 '22 at 11:36

0 Answers0