Search code examples
postgresqlspring-data-jpaspring-dataqueuespring-data-jdbc

Why Spring Data doesn't support returning entity for modifying queries?

When implementing a system which creates tasks that need to be resolved by some workers, my idea would be to create a table which would have some task definition along with a status, e.g. for document review we'd have something like reviewId, documentId, reviewerId, reviewTime. When documents are uploaded to the system we'd just store the documentId along with a generated reviewId and leave the reviewerId and reviewTime empty. When next reviewer comes along and starts the review we'd just set his id and current time to mark the job as "in progress" (I deliberately skip the case where the reviewer takes a long time, or dies during the review).

When implementing such a use case in e.g. PostgreSQL we could use the UPDATE review SET reviewerId = :reviewerId, reviewTime: reviewTime WHERE reviewId = (SELECT reviewId from review WHERE reviewId is null AND reviewTime is null FOR UPDATE SKIP LOCKED LIMIT 1) RETURNING reviewId, documentId, reviewerId, reviewTime (so basically update the first non-taken row, using SKIP LOCKED to skip any already in-processing rows).

But when moving from native solution to JDBC and beyond, I'm having troubles implementing this:

  • Spring Data JPA and Spring Data JDBC don't allow the @Modifying query to return anything else than void/boolean/int and force us to perform 2 queries in a single transaction - one for the first pending row, and second one with the update
  • one alternative would be to use a stored procedure but I really hate the idea of storing such logic so away from the code
  • other alternative would be to use a persistent queue and skip the database all along but this introduced additional infrastructure components that need to be maintained and learned. Any suggestions are welcome though.

Am I missing something? Is it possible to have it all or do we have to settle for multiple queries or stored procedures?

Solution

  • Why Spring Data doesn't support returning entity for modifying queries?

    Because it seems like a rather special thing to do and Spring Data JDBC tries to focus on the essential stuff.

    Is it possible to have it all or do we have to settle for multiple queries or stored procedures?

    It is certainly possible to do this. You can implement a custom method using an injected JdbcTemplate.