CODEWITHSUNDEEP
vert.xquarkusreactive-postgresql-client
Nico B.
Nico B.

Reputation: 3

Quarkus Reactive PostgreSQL Vert.x - Collection of Long & in () queries

I am trying to use a prepared query with a 'where xxx in ()' query using a Set of Long as parameter:

public static Multi<Item> findAll(PgPool client, Set<Long> ids) {
  Tuple parameter = Tuple.of(ids.toArray(new Long[]{}));
  // Tuple parameter = Tuple.wrap(new ArrayList(ids));
  return client.preparedQuery("SELECT id, name, properties FROM items where id in ($1)").execute(parameter)
      .onItem().transformToMulti(set -> Multi.createFrom().iterable(set))
      .onItem().transform(Item::from);
    }

But while the 'in' SQL query is supposed to handle multiple values, it does work when passing an array, throwing the following:

io.vertx.core.impl.NoStackTraceThrowable: Parameter at position[0] with class = [[Ljava.lang.Long;] and value = [[Ljava.lang.Long;@1a074753] can not be coerced to the expected class = [java.lang.Number] for encoding.

Passing a single value works but it is not the purpose of the method:

  Tuple parameter = Tuple.of(1206756351360216067L);

What would be the correct way to handle the set of ids in order to return multiple rows ?

Edit

I ended up doing this:

        Tuple parameter = Tuple.of(ids.toArray(new Long[]{}));

        String query = "with values as (\n" +
                "select unnest(($1)::bigint[]) as id\n" +
                ")\n" +
                "select v.* from values vl " +
                "join items v on v.id = vl.id";

Upvotes: 0

Views: 1387

Answers (1)

tsegismont
tsegismont

Reputation: 9128

Change the query to:

SELECT id, name, properties FROM items where id = ANY($1)

And then you should be able to use a List/Set/Array parameter value.

Upvotes: 1

Related Questions