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.

instead of `(:ids)` just use `:ids` in the @Query. Can you share your entity class. May be there is a way you can do it without `@Query` — pvpkiran, Oct 02 '19 at 18:01
Did you try this `findByJobIdAndItemIdNotIn(@Param("jobId") String jobId, @Param("ids") List ids)`? — pvpkiran, Oct 02 '19 at 21:04
Thanks @pvpkiran - removing the parentheses from :ids was part of the solution! — Bal, Oct 03 '19 at 13:48

score 2 · Accepted Answer · answered Oct 02 '19 at 22:55

2

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

answered Oct 02 '19 at 22:55

mp911de

17,546
2
55
95

Thanks! I know see it expanding with with parenthesis around each value so that gets me much closer, however now it returns 0 results. I also tried it with String[] instead of Object[] -- no luck. Is there a way to see the _actual_ statement being executed, with the real values populated in the statement? Setting org.springframework.data and io.r2dbc to DEBUG still doesn't seem to show it. – Bal Oct 03 '19 at 11:32
Use logging categories of Spring Data (org.springfrsmework.data) – mp911de Oct 03 '19 at 12:32
This ended up working after I also implemented the suggestion from @pvpkiran to remove the parentheses from :ids – Bal Oct 03 '19 at 13:49

Spring Data R2DBC - Building custom postgresql query in reactive repository

1 Answers1