Spring Add updateReturning, batchUpdateReturning and batchUpdateReturningStream methods to NamedParameterJdbcTemplate

Many databases now support UPDATE/INSERT/DELETE ... RETURNING * which enables a single database request to apply and return without the need for a separate SELECT, this allows the caller to see the values for generated fields such as ID's, default values and potentially data which is populated via triggers BEFORE INSERT OR UPDATE ... or in my case I have a batch insert that uses ON CONFLICT DO NOTHING RETURNING * which allows me to not fail the entire batch when some rows conflict and manage the conflicts manually.

Currently I see a lot of implementations which use an insert and then a select separately where the implementation needs to take care to use @Transactional.

The functionality of updateReturning can be achieved using the query methods however I would argue that this is counter intuitive. Both batchUpdateReturning and batchUpdateReturningStream are a little more complicated to workaround with the currently available query methods but they are possible (see: this Stack Overflow answer) but the solution has some drawbacks such as losing named parameters.

Example of the workaround I've been using

    public Set<ExternalId> markSeen(Collection<ExternalId> externalIds) {
        List<Object[]> values = externalIds.stream()
                // Object[] must be in order of the columns defined for the insert
                .map(externalId -> new Object[] {externalId.getStringId(), externalId.getDomain()})
                .toList();

        // insert batch returning everything that didn't conflict
        Set<ExternalId> unseenIds = namedParameterJdbcTemplate.query("""
            insert into seen_documents (external_id, domain)
            values :idAndDomain
            on conflict do nothing
            returning external_id, domain
            """,
                new MapSqlParameterSource("idAndDomain", values),
                resultExtractor);

        // return values that didn't get inserted
        return externalIds.stream()
                .filter(not(unseenIds::contains))
                .collect(Collectors.toUnmodifiableSet());
    }

I'm happy to add the functionality to the APIs if others think this is worth doing.

Comment From: jhoeller

We do not intend to add further methods to NamedParameterJdbcTemplate, in particular not for functionality that can be accomplished in other ways already.

That said, with the recent introduction of JdbcClient in 6.1, we have an easier arrangement to add further methods to now. However, there is no batch update support there since that is rather to be found in dedicated places such as the existing SimpleJdbcInsert in that same package. There is only so far that we can take the template style or even the fluent client style for a general database delegate.