Spring Boot and JpaRepository - delete should return list of deleted objects

Question

Is it possible to return deleted objects? I use Mysql. I want to avoid too many db requests

public interface FileRepository extends JpaRepository<File, Long> {
@Modifying
    @Query(value = """
            DELETE * FROM file f 
            WHERE f.id IN :ids
            AND NOT EXISTS (SELECT * FROM time_window_file twf WHERE twf.file_id = f.id)
            """, nativeQuery = true)
    List<File> deleteFilesByIdsIfNotUsed(Set<Long> ids);

time_window_file has only two columns: time_window_id and file_id

With this "solution" I get the following error: [Statement.executeQuery() cannot issue statements that do not produce result sets.]

Answer 1

You can change your query as :

with tab1 as (
    SELECT * FROM file f 
        WHERE f.id IN :ids
        AND NOT EXISTS (SELECT * FROM time_window_file twf WHERE twf.file_id = f.id)
),
tab2 as (
    DELETE FROM file f 
        WHERE f.id in (select distinct(id) from tab1)
)
SELECT * FROM tab1;

[tested in Postgres]