MySQL JPA deleting multiple records from different tables

NOTE: I wrote this answer partially with the assistance of GitHub Copilot.

TL;DR: Use an appropriate CascadeType such as REMOVE or ALL on your Deletion entity.

If you define the User and Deletion entities as follows:

package org.behrang.stackoverflow.q75563217.entity;

import jakarta.persistence.*;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Entity
@Table(name = "Users")
public class User {

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

    private Date createdAt;

    private String email;

    @OneToMany(cascade = CascadeType.PERSIST)
    private List<Deletion> deletions = new ArrayList<>();

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public Date getCreatedAt() {
        return createdAt;
    }

    public void setCreatedAt(Date createdAt) {
        this.createdAt = createdAt;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public List<Deletion> getDeletions() {
        return deletions;
    }

    public void setDeletions(List<Deletion> deletions) {
        this.deletions = deletions;
    }

    public void addDeletion(Deletion deletion) {
        this.deletions.add(deletion);
    }
}

package org.behrang.stackoverflow.q75563217.entity;

import jakarta.persistence.*;

import java.util.Date;

@Entity
@Table(name = "Deletions")
public class Deletion {

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

    private boolean active;

    private Date date;

    @ManyToOne(cascade = CascadeType.REMOVE)
    @JoinColumn(name = "user_id")
    private User user;

    public void setId(Long id) {
        this.id = id;
    }

    public Long getId() {
        return id;
    }

    public boolean isActive() {
        return active;
    }

    public void setActive(boolean active) {
        this.active = active;
    }

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }
}

And use the following persistence.xml file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<persistence xmlns="https://jakarta.ee/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_0.xsd"
             version="3.0">
    <persistence-unit name="default">
        <class>org.behrang.stackoverflow.q75563217.entity.User</class>
        <class>org.behrang.stackoverflow.q75563217.entity.Deletion</class>
        <properties>
            <property name="jakarta.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
            <property name="jakarta.persistence.jdbc.url" value="jdbc:mysql://localhost:32773/example"/>
            <property name="jakarta.persistence.jdbc.user" value="root"/>
            <property name="jakarta.persistence.jdbc.password" value="password"/>
            <property name="jakarta.persistence.schema-generation.database.action" value="create"/>
        </properties>
    </persistence-unit>
</persistence>

And use this schema (generated by Hibernate automatically):

create table if not exists Deletions_SEQ
(
    next_val bigint null
);

create table if not exists Users
(
    id        int          not null primary key,
    createdAt datetime(6)  null,
    email     varchar(255) null
);

create table if not exists Deletions
(
    id      bigint      not null primary key,
    active  bit         not null,
    date    datetime(6) null,
    user_id int         null,
    constraint FK2bq97txm7h2eirrkbv84xpv2y foreign key (user_id) references Users (id)
);

create table if not exists Users_Deletions
(
    User_id      int    not null,
    deletions_id bigint not null,
    constraint UK_jhg9je4j0jwu07wpjw2yvm9ve unique (deletions_id),
    constraint FK98pxufvij990xbgyihq7lxn2a foreign key (User_id) references Users (id),
    constraint FKg1q9bnqdsn40gecsagv32b16q foreign key (deletions_id) references Deletions (id)
);

create table if not exists Users_SEQ
(
    next_val bigint null
);

Then this demo code shows how the cascading effect works:

package org.behrang.stackoverflow.q75563217;

import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import jakarta.persistence.EntityTransaction;
import jakarta.persistence.Persistence;
import org.behrang.stackoverflow.q75563217.entity.Deletion;
import org.behrang.stackoverflow.q75563217.entity.User;

import java.sql.Date;
import java.time.Duration;
import java.time.Instant;

public class Demo {
    public static void main(String[] args) {
        EntityManagerFactory factory = Persistence.createEntityManagerFactory("default");
        EntityManager entityManager = factory.createEntityManager();
        EntityTransaction tx = entityManager.getTransaction();
        tx.begin();

        Instant i1 = Instant.now().minus(Duration.ofHours(25));
        User u1 = new User();
        u1.setEmail("[email protected]");
        u1.setCreatedAt(Date.from(i1));

        Deletion d01 = new Deletion();
        d01.setDate(Date.from(Instant.now()));
        d01.setUser(u1);
        u1.addDeletion(d01);

        entityManager.persist(u1);

        tx.commit();
        entityManager.close();

        entityManager = factory.createEntityManager();
        tx = entityManager.getTransaction();
        tx.begin();

        Deletion d11 = entityManager.find(Deletion.class, 1L);
        entityManager.remove(d11);

        tx.commit();
        entityManager.close();

        factory.close();
    }
}

Demo video: https://www.youtube.com/watch?v=PCZcJdi9wf8.

Sample code: https://github.com/behrangsa/so-75563217

For the age comparison, you can add the date/time logic to your JPQL query.