SPRING PetClinic: BatchError when trying to delete vets with foreign key to visits-CodePudding

I got an assignment to extend the SPRING petclinic application (forked from this repo: https://github.com/spring-petclinic/spring-petclinic-rest ). One assignment is to "Show all visits by a vet".

For that I added a foreign key and a column to the table visits to the hsqldb:

...
CREATE TABLE visits (
  id          INTEGER IDENTITY PRIMARY KEY,
  pet_id      INTEGER NOT NULL,
  vet_id      INTEGER NOT NULL,
  visit_date  DATE,
  description VARCHAR(255)
);

ALTER TABLE visits ADD CONSTRAINT fk_visits_vets FOREIGN KEY (vet_id) REFERENCES vets (id);
ALTER TABLE visits ADD CONSTRAINT fk_visits_pets FOREIGN KEY (pet_id) REFERENCES pets (id);
CREATE INDEX visits_pet_id ON visits (pet_id);
...

Edited the visit model accordingly:

@Entity
@Table(name = "visits")
public class Visit extends BaseEntity {
...
    @ManyToOne
    @JoinColumn(name = "vet_id", referencedColumnName = "id", nullable = false)
    private Vet vet;
...

And added the vetId to #/components/schemas/Visit in openapi.yml too:

    Visit:
      title: Visit
      description: A booking for a vet visit.
      allOf:
        - $ref: '#/components/schemas/VisitFields'
        - type: object
          properties:
            id:
              title: ID
              description: The ID of the visit.
              type: integer
              format: int32
              minimum: 0
              example: 1
              readOnly: true
            petId:
              title: Pet ID
              description: The ID of the pet.
              type: integer
              format: int32
              minimum: 0
              example: 1
              readOnly: true
            vetId:
              title: Vet ID
              description: The ID of the veterinarian.
              type: integer
              format: int32
              minimum: 0
              example: 1
              readOnly: true
          required:
            - id
            - vetId
            - petId

I am able to build and run the project, but when I try to delete a vet I get the following error message:

{
  "className": "org.springframework.dao.DataIntegrityViolationException",
  "exMessage": "could not execute statement; SQL [n/a]; constraint [FK_VISITS_VETS]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement"
}

So I tried to Override the generated delete method from Vet to FIRST delete all corresponding Visits and THEN delete the Vet:

public class SpringDataVetRepositoryImpl implements VetRepositoryOverride {
    @PersistenceContext
    private EntityManager em;

    @Override
    public void delete(Vet vet) {
        Integer vetId = vet.getId();
        this.em.createQuery("DELETE FROM Visit visit WHERE vet_id="   vet.getId()).executeUpdate();
        this.em.createQuery("DELETE FROM Vet vet WHERE id="   vet.getId()).executeUpdate();
        if (em.contains(vet)) {
            em.remove(vet);
        }
    }
}

But when I run it now I get an error that I don't understand, nor have the slightest idea what it could mean:

{
  "className": "org.springframework.orm.ObjectOptimisticLockingFailureException",
  "exMessage": "Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; statement executed: delete from vets where id=?; nested exception is org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; statement executed: delete from vets where id=?"
}

Neither the vet, nor it's visits got deleted. Does anyone has a guess why? My current project state can be found here: https://github.com/Fabian-Anna/java-petclinic/tree/user-story-2

CodePudding user response：

The foreign key that references the vets table prevents deleting any row in that table which is referenced from the visits table.

You can define the foreign key with ON DELETE CASCASE which will cause all the referencing visits to be deleted if a row in the vets table is deleted.

See http://hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html#dbc_constraints

BTW you do not need to create an index on the visits table. The foreign key creates its own index.