I have a problem with testing entities in my simple Spring Boot library api.
In this test I even manually remove every book from titles to ensure it doesn't violate anything, but the problem still occurs in line entityManager.remove(title);
@Test
@Transactional
void titleAssociationTest() {
title.getBooks().forEach(book -> {
if (book.getLoan() != null) {
entityManager.remove(book.getLoan());
}
entityManager.remove(book);
});
title.getBooks().clear();
entityManager.flush();
entityManager.remove(title);
entityManager.flush();
Title foundTitle = entityManager.find(Title.class, title.getId());
assertThat(foundTitle).isNull();
}
I receive this error message:
Referential integrity constraint violation: "FK6I4NJ1U4KX1J2YOVIIM8HT8S9: PUBLIC.BOOKS FOREIGN KEY(TITLE_ID) REFERENCES PUBLIC.TITLES(ID) (CAST(1 AS BIGINT))"; SQL statement:
delete from titles where id=? [23503-214]] [delete from titles where id=?]
Here is my book entity
package com.projects.library.model;
import com.projects.library.enums.BookStatus;
import jakarta.persistence.*;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
@NoArgsConstructor
@AllArgsConstructor
@Setter
@Getter
@Entity
@Table(name = "books")
public class Book {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@ManyToOne
@JoinColumn(name = "title_id", nullable = false)
private Title title;
@Enumerated(EnumType.STRING)
@Column(name = "status", nullable = false)
private BookStatus status;
@OneToOne(mappedBy = "book", cascade = CascadeType.ALL, orphanRemoval = true)
private Loan loan;
public Book(Title title, BookStatus status) {
this.title = title;
this.status = status;
}
}
And here is title entity, where I use Cascade.ALL, so when a title is removed all books should be removed as well.
package com.projects.library.model;
import jakarta.persistence.*;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import java.util.HashSet;
import java.util.Set;
@NoArgsConstructor
@AllArgsConstructor
@Setter
@Getter
@Entity
@Table(name = "titles")
public class Title {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@Column(name = "`title`", nullable = false, unique = true)
private String title;
@Column(name = "author", nullable = false)
private String author;
@Column(name = "`year`", nullable = false)
private int year;
@OneToMany(mappedBy = "title", cascade = CascadeType.ALL, orphanRemoval = true)
private Set<Book> books = new HashSet<>();
public Title(String title, String author, int year) {
this.title = title;
this.author = author;
this.year = year;
}
}
For testing i use hibernate's H2 database with this testing profile in application-test.properties
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
Here are the full SQL logs from hibernate:
create table books (
id bigint generated by default as identity,
status varchar(255) not null check (status in ('AVAILABLE','RENTED')),
title_id bigint not null,
primary key (id)
)
Hibernate:
create table books (
id bigint generated by default as identity,
status varchar(255) not null check (status in ('AVAILABLE','RENTED')),
title_id bigint not null,
primary key (id)
)
2024-08-20T14:07:28.365+02:00 DEBUG 12728 --- [ Test worker] org.hibernate.SQL :
create table loans (
id bigint generated by default as identity,
borrow_date timestamp(6) not null,
return_date timestamp(6),
book_id bigint not null,
user_id bigint not null,
primary key (id)
)
Hibernate:
create table loans (
id bigint generated by default as identity,
borrow_date timestamp(6) not null,
return_date timestamp(6),
book_id bigint not null,
user_id bigint not null,
primary key (id)
)
2024-08-20T14:07:28.371+02:00 DEBUG 12728 --- [ Test worker] org.hibernate.SQL :
create table titles (
id bigint generated by default as identity,
author varchar(255) not null,
"title" varchar(255) not null,
"year" integer not null,
primary key (id)
)
Hibernate:
create table titles (
id bigint generated by default as identity,
author varchar(255) not null,
"title" varchar(255) not null,
"year" integer not null,
primary key (id)
)
2024-08-20T14:07:28.371+02:00 DEBUG 12728 --- [ Test worker] org.hibernate.SQL :
create table users (
id bigint generated by default as identity,
account_creation_date timestamp(6) not null,
first_name varchar(255) not null,
last_name varchar(255) not null,
primary key (id)
)
Hibernate:
create table users (
id bigint generated by default as identity,
account_creation_date timestamp(6) not null,
first_name varchar(255) not null,
last_name varchar(255) not null,
primary key (id)
)
2024-08-20T14:07:28.379+02:00 DEBUG 12728 --- [ Test worker] org.hibernate.SQL :
alter table if exists loans
drop constraint if exists UK_7t9p42ubdqp606tp4whc233lt
Hibernate:
alter table if exists loans
drop constraint if exists UK_7t9p42ubdqp606tp4whc233lt
2024-08-20T14:07:28.379+02:00 DEBUG 12728 --- [ Test worker] org.hibernate.SQL :
alter table if exists loans
add constraint UK_7t9p42ubdqp606tp4whc233lt unique (book_id)
Hibernate:
alter table if exists loans
add constraint UK_7t9p42ubdqp606tp4whc233lt unique (book_id)
2024-08-20T14:07:28.379+02:00 DEBUG 12728 --- [ Test worker] org.hibernate.SQL :
alter table if exists titles
drop constraint if exists UK_teg25db4y95gscy7bdb9jqmes
Hibernate:
alter table if exists titles
drop constraint if exists UK_teg25db4y95gscy7bdb9jqmes
2024-08-20T14:07:28.379+02:00 DEBUG 12728 --- [ Test worker] org.hibernate.SQL :
alter table if exists titles
add constraint UK_teg25db4y95gscy7bdb9jqmes unique ("title")
Hibernate:
alter table if exists titles
add constraint UK_teg25db4y95gscy7bdb9jqmes unique ("title")
2024-08-20T14:07:28.379+02:00 DEBUG 12728 --- [ Test worker] org.hibernate.SQL :
alter table if exists books
add constraint FK6i4nj1u4kx1j2yoviim8ht8s9
foreign key (title_id)
references titles
Hibernate:
alter table if exists books
add constraint FK6i4nj1u4kx1j2yoviim8ht8s9
foreign key (title_id)
references titles
2024-08-20T14:07:28.389+02:00 DEBUG 12728 --- [ Test worker] org.hibernate.SQL :
alter table if exists loans
add constraint FKokwvlrv6o4i4h3le3bwhe6kie
foreign key (book_id)
references books
Hibernate:
alter table if exists loans
add constraint FKokwvlrv6o4i4h3le3bwhe6kie
foreign key (book_id)
references books
2024-08-20T14:07:28.389+02:00 DEBUG 12728 --- [ Test worker] org.hibernate.SQL :
alter table if exists loans
add constraint FK6xxlcjc0rqtn5nq28vjnx5t9d
foreign key (user_id)
references users
Hibernate:
alter table if exists loans
add constraint FK6xxlcjc0rqtn5nq28vjnx5t9d
foreign key (user_id)
references users
2024-08-20T14:07:28.389+02:00 INFO 12728 --- [ Test worker] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2024-08-20T14:07:28.780+02:00 INFO 12728 --- [ Test worker] o.s.d.j.r.query.QueryEnhancerFactory : Hibernate is in classpath; If applicable, HQL parser will be used.
2024-08-20T14:07:29.384+02:00 INFO 12728 --- [ Test worker] c.projects.library.model.BookTestSuite : Started BookTestSuite in 3.519 seconds (process running for 4.796)
WARNING: A Java agent has been loaded dynamically (C:\Users\Pawe?\.gradle\caches\modules-2\files-2.1\net.bytebuddy\byte-buddy-agent\1.14.13\979ce25f7d3096a2e82214ba7dc972a05ce7a171\byte-buddy-agent-1.14.13.ja)
WARNING: If a serviceability tool is in use, please run with -XX:+EnableDynamicAgentLoading to hide this warning
WARNING: If a serviceability tool is not in use, please run with -Djdk.instrument.traceUsage for more information
WARNING: Dynamic loading of agents will be disallowed by default in a future release
2024-08-20T14:07:29.884+02:00 DEBUG 12728 --- [ Test worker] org.hibernate.SQL :
insert
into
titles
(author,"title","year",id)
values
(?,?,?,default)
Hibernate:
insert
into
titles
(author,"title","year",id)
values
(?,?,?,default)
2024-08-20T14:07:29.899+02:00 DEBUG 12728 --- [ Test worker] org.hibernate.SQL :
insert
into
users
(account_creation_date,first_name,last_name,id)
values
(?,?,?,default)
Hibernate:
insert
into
users
(account_creation_date,first_name,last_name,id)
values
(?,?,?,default)
2024-08-20T14:07:29.903+02:00 DEBUG 12728 --- [ Test worker] org.hibernate.SQL :
insert
into
books
(status,title_id,id)
values
(?,?,default)
Hibernate:
insert
into
books
(status,title_id,id)
values
(?,?,default)
2024-08-20T14:07:29.906+02:00 DEBUG 12728 --- [ Test worker] org.hibernate.SQL :
insert
into
loans
(book_id,borrow_date,return_date,user_id,id)
values
(?,?,?,?,default)
Hibernate:
insert
into
loans
(book_id,borrow_date,return_date,user_id,id)
values
(?,?,?,?,default)
2024-08-20T14:07:29.924+02:00 DEBUG 12728 --- [ Test worker] org.hibernate.SQL :
delete
from
titles
where
id=?
Hibernate:
delete
from
titles
where
id=?
2024-08-20T14:07:29.931+02:00 WARN 12728 --- [ Test worker] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 23503, SQLState: 23503
2024-08-20T14:07:29.931+02:00 ERROR 12728 --- [ Test worker] o.h.engine.jdbc.spi.SqlExceptionHelper : Naruszenie więzów integralności: "FK6I4NJ1U4KX1J2YOVIIM8HT8S9: PUBLIC.BOOKS FOREIGN KEY(TITLE_ID) REFERENCES PUBLIC.TITLES(ID) (CAST(1 AS BIGINT))"
Referential integrity constraint violation: "FK6I4NJ1U4KX1J2YOVIIM8HT8S9: PUBLIC.BOOKS FOREIGN KEY(TITLE_ID) REFERENCES PUBLIC.TITLES(ID) (CAST(1 AS BIGINT))"; SQL statement:
delete from titles where id=? [23503-214]
Ok the solution to the problem was simply to add on delete cascade to the constraint by adding @OnDelete(action = OnDeleteAction.CASCADE) to the @OneToMany relation in entity.
@OneToMany(mappedBy = "title", cascade = CascadeType.REMOVE, orphanRemoval = true)
@OnDelete(action = OnDeleteAction.CASCADE)
private Set<Book> books = new HashSet<>();
So the constraint is being added like this:
alter table if exists books
add constraint FK6i4nj1u4kx1j2yoviim8ht8s9
foreign key (title_id)
references titles
on delete cascade