Spring Data for Postgresql specific query - DELETE RETURNING with Spring repositories

Question

My question is very concrete and I could not find any answers on Stackoverflow or wider internet.

I would like to use Spring repository to execute "DELETE FROM TABLE ... RETURNING *" type of query, which is specific to Postgresql.

Assume that I have following classes:

@Entity
@Getter // lombok annotations
@Setter
public class Item {

    @Id
    @GeneratedValue
    private Integer id;

    private String name;

}

then I would like to have something like following:

interface ItemRepository extends CrudRepository<Item, Integer> {

    // This is a Postgres specific query
    @Query("SELECT FROM Item where Item.id=?1 RETURNING *")
    Optional<Item> deleteByIdReturning(Integer id)

}

How can I do something like this?

Additional info:

In order to motivate my needs a bit better I would like to have a CRUD-y REST resource, which will return 200 HTTP status code together with deleted resource if something has been deleted, but will return HTTP status code 204 without body in case that nothing has actually been deleted on the server side.

I thank you in advance for your time

Answer 1

If you are using Spring Data JPA you can try using native query this way:

 @Query("SELECT FROM Item where Item.id=?1 RETURNING *", nativeQuery = true)
 Optional<Item> deleteByIdReturning(Integer id)