Spring Data R2DBC - Building custom postgresql query in reactive repository

Question

I have a table that contains entities with a String id, String jobId, and String status. Given a jobId and a List of ids, I would like to query that table and return a Flux of ids that are not present in the database.

I can do this successfully if I manually execute the following query in pgadmin:

SELECT a.id FROM (VALUES ('20191001_182447_1038'),('abc'),('fdjk')) AS a(id) LEFT JOIN (SELECT * FROM items WHERE job_id = '10a7a04a-aa67-499a-83eb-0cd3625fe27a') b ON a.id = b.id WHERE b.id IS null

The response comes back with only the ids that are not present, 'abc' and 'fdjk'.

In my spring data repo, I define the following method:

    @Query("SELECT a.id FROM (VALUES (:ids)) AS a(id) LEFT JOIN (SELECT * FROM items WHERE job_id = :jobId) b ON a.id = b.id WHERE b.id IS null")
    Flux<ItemId> getNotContains(@Param("jobId") String jobId, @Param("ids") Collection<String> ids);

The problem is, when I run the code, the query gets expanded to:

SELECT a.id FROM (VALUES ($1, $2, $3)) AS a(id) LEFT JOIN (SELECT * FROM items WHERE job_id = $251) b ON a.id = b.id WHERE b.id IS null]

This always returns a single value because the values are being grouped into a single set of parenthesis instead of wrapping each element of my collection in parenthesis. Just curious if there is a way to handle this properly.

EDIT Entity class is:

@Data
@Table("items")
public class Item implements Persistable {

    @Id
    private String id;
    private String jobId;
    private String customerId;
    private Date queuedDate;
    private Date lastUpdated;
    private String destination;
    private String type;
    private Status status;
}

Also, my repo is:

public interface ItemRepository extends R2dbcRepository<Item, String>

R2dbcRepository doesn't currently support the fancy magic of more mature spring data repos, so you can't do things like findByJobId and have it auto-gen the query for you.

Answer 1

You can enforce parenthesis by wrapping your arguments into a Object[] to render parameters as expression list.

interface MyRepo {
    @Query(…)
    Flux<ItemId> getNotContains(@Param("jobId") String jobId, @Param("ids") Collection<Object[]> ids);
}

MyRepo myRepo = …;
Collection<Object[]> ids = Arrays.asList(new Object[]{"1"}, new Object[]{"2"});

myRepo.getNotContains("foo", ids);

See also:

• NamedParameterUtils Javadoc