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

Use a @NativeQuery?

Bragolgirith
– Bragolgirith

2022-01-31 20:23:20 +00:00
Commented Jan 31, 2022 at 20:23 — Bragolgirith
– Bragolgirith, Commented Jan 31, 2022 at 20:23
May I ask for an example?

TheCoolDrop
– TheCoolDrop

2022-01-31 20:25:48 +00:00
Commented Jan 31, 2022 at 20:25 — TheCoolDrop
– TheCoolDrop, Commented Jan 31, 2022 at 20:25

ChrisBall · Accepted Answer · 2022-01-31 20:45:51Z

2

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)

answered Jan 31, 2022 at 20:45

ChrisBall

212 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related