@Where annotation causes new rows in database

Hibernate ORM
1

Hi! I am trying to debug an issue in my application but I can’t really get my head around it. I have the following two entities:

import jakarta.persistence.*;
import lombok.*;
import org.hibernate.annotations.Fetch;
import org.hibernate.annotations.FetchMode;
import org.hibernate.annotations.Where;

import java.io.Serializable;
import java.util.HashSet;
import java.util.Set;

@Entity
@Table(name = "BOOK")
@EqualsAndHashCode(of = { "id" })
@ToString(of = { "id" })
@Getter
@Setter
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Book implements Serializable {

    @Id
    @SequenceGenerator(name = "BOOK_SEQ", sequenceName = "BOOK_SEQ", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "BOOK_SEQ")
    @Column(name = "BOOK_ID", columnDefinition = "NUMBER(38,0)")
    private Long id;

    @Builder.Default
    @OneToMany(mappedBy = "book", fetch = FetchType.LAZY, orphanRemoval = true, cascade = { CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH, CascadeType.DETACH })
    @Fetch(FetchMode.SUBSELECT)
    @Where(clause = "AGE < 60")
    private Set<Author> authors = new HashSet<>();

    @Column(name = "TITLE", columnDefinition = "VARCHAR2(50 CHAR)")
    private String title;

    public Integer getAuthorAmount() {
        return authors.size();
    }
}

and

import jakarta.persistence.*;
import lombok.*;
import org.hibernate.annotations.Where;

import java.io.Serializable;

@Entity
@Table(name = "AUTHOR")
@EqualsAndHashCode(of = { "id" })
@ToString(of = { "id" })
@Getter
@Setter
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Where(clause = "AGE < 60")
public class Author implements Serializable {

    @Id
    @SequenceGenerator(name = "AUTHOR_SEQ", sequenceName = "AUTHOR_SEQ", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "AUTHOR_SEQ")
    @Column(name = "AUTHOR_ID", columnDefinition = "NUMBER(38,0)")
    private Long id;

    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    @JoinColumn(name = "BOOK_ID", columnDefinition = "NUMBER(38,0)")
    private Book book;

    @Column(name = "NAME", columnDefinition = "VARCHAR2(50 CHAR)")
    private String name;

    @Column(name = "age", columnDefinition = "NUMBER(3,0)")
    private Integer age;
}

and the following in my BookController.java:

private Book selectedBook;

@PostMapping("/increaseAge")
@Transactional
public String increaseAge() {
    selectedBook.getAuthors().forEach(author -> author.setAge(70));
    selectedBook = bookRepository.save(selectedBook);
    return "redirect:/book";
}

@PostMapping("/save")
@Transactional
public String saveEntity() {
    selectedBook = bookRepository.save(selectedBook);
    return "redirect:/book";
}

When pressing the button in my GUI that calls the increaseAge() method, the following queries are ran:

Hibernate: select b1_0.book_id,a1_0.book_id,a1_0.author_id,a1_0.age,a1_0.name,b1_0.title from book b1_0 left join author a1_0 on b1_0.book_id=a1_0.book_id and (( a1_0.AGE < 60 ) and ( a1_0.AGE < 60 ))  and (( a1_0.AGE < 60 ) and ( a1_0.AGE < 60 ))  where b1_0.book_id=?
Hibernate: update author set age=?,book_id=?,name=? where author_id=?
Hibernate: update author set age=?,book_id=?,name=? where author_id=?

Which looks good, note that selectedBooks.getAuthors() still has all authors until I do a page refresh or load the entity from a new session (which I want to avoid, but it is fine if they are still loaded in the entity, I just want to avoid the upcoming issue).

Now my issue appears, if I increase the age of all authors so that the clause of my @Where annotation goes from true to false, then I edit something with my book and hit the save button, I get the following queries:

Hibernate: select b1_0.book_id,a1_0.book_id,a1_0.author_id,a1_0.age,a1_0.name,b1_0.title from book b1_0 left join author a1_0 on b1_0.book_id=a1_0.book_id and (( a1_0.AGE < 60 ) and ( a1_0.AGE < 60 ))  and (( a1_0.AGE < 60 ) and ( a1_0.AGE < 60 ))  where b1_0.book_id=?
Hibernate: select a1_0.author_id,a1_0.age,a1_0.book_id,a1_0.name from author a1_0 where a1_0.author_id=? and (a1_0.AGE < 60)
Hibernate: select author_seq.nextval from dual
Hibernate: select a1_0.author_id,a1_0.age,a1_0.book_id,a1_0.name from author a1_0 where a1_0.author_id=? and (a1_0.AGE < 60)
Hibernate: select author_seq.nextval from dual
Hibernate: insert into author (age,book_id,name,author_id) values (?,?,?,?)
Hibernate: insert into author (age,book_id,name,author_id) values (?,?,?,?)

It looks like the Authors which @Where clause has gone from true to false gets inserted into the database again with an incremented ID:
image

Is there something wrong with my setup or the way I am saving? Currently I am testing with 6.2.7.Final but have the same issue back in 5.0.12.Final as well.

Note: This is just a mockup of an issue in my application so don’t look too much into the logistics between the Book and Author :slight_smile:

2

Not sure what to tell you. You simply can’t merge an entity with a collection that contains elements that do not match the @Where condition, because the lookup operation that happens prior to the update will include this @Where condition. If the database contains two authors with age 70 referring to a book, you can’t merge a book entity with a collection that contains these two authors.

The merge of a book will cause a load of the book and authors to understand what needs to be done for the merge operation. Since the @Where condition filters out the authors, Hibernate ORM assumes the collection is empty and hence will have to merge the authors, because of the merge cascading that you configured on the authors collection.
Since the lookup of the individual authors produces no result because of your @Where annotation on the Author class, Hibernate ORM assumes no rows exist and hence attempts to insert rows.

How to avoid this? Use optimistic locking on Book and force increment the version in increaseAge. Then the second transaction will fail because the version is not correct, which will make it necessary to reload the book entity.