Can't set the List<...> parameter in the query

Question

I can't add the List<...> parameter, or rather ArrayList ($2).

If I just put List<...> listId, I get the error: Cannot encode parameter of type java.util.ArrayList ([192, 193, 194, ....

If I set listId.toArray(), then there is no error, but the query is not executed, and nothing is even written in the logs.

The request is simple, there is no error in it: "update "schema"."table" SET "column" = $1 where "id" in ( $2 )"

The question is relevant for both postgres and mssql, my drivers and query forms in runtime are changing.

Please help me solve the problem...

    public <S, T> Mono<Boolean> update(ConnectionFactory connectionFactory, String query, S param, List<T> listId) {
        return Mono.usingWhen(connectionFactory.create(),
                        connection -> {
                            Statement statement = connection.createStatement(query);
                            statement.bind("$1", param);
                            statement.bind("$2", listId);

                            return Mono.from(statement.execute());
                        },
                        Connection::close)
                .map(el -> true)
                .doOnSuccess((a) -> log.info("Update Success!"))
                .onErrorResume(e -> {
                    log.error("ERROR update : \n{}", e.getMessage());
                    return Mono.just(false);
                });
    }

Answer 1

You are using the raw position based parameter binding, you should add param like this in your codes.

statement.bind(0, param);
statement.bind(1, listId);

Please notice, in your SQL, you should use $1, and $2 for the parameter position. But when using statement.bind, it starts with 0.

Spring R2dbc provides a wrapper client - DatabaseClient which is easy to execute SQL with named parameters.

public Mono<Long> deleteAllById(List<UUID> ids) {
    return this.databaseClient.sql("DELETE FROM posts WHERE id in (:ids)")
        .bind("ids", ids)
        .fetch()
        .rowsUpdated();
}

All above examples are based on Postgres.

Check my example using ConnectionFactory here: https://github.com/hantsy/spring-r2dbc-sample/blob/master/connection-factories/src/test/java/com/example/demo/PgTests.java

And the example using DatabaseClient: https://github.com/hantsy/spring-r2dbc-sample/tree/master/database-client