Spring Webflux - R2dbc : How to run a child query and update value while iterating a result set

Question

I am new to Reactive repositories and webflux. I am fetching a list of data from DB, iterating it using map() to build a DTO class object, in this process I need to run another query to get the count value and update the same DTO object. When I try as follows, the count is set to null

@Repository
public class CandidateGroupCustomRepo {
  public Flux<CandidateGroupListDTO> getList(BigInteger userId){
        final String sql = "SELECT gp.CANDIDATE_GROUP_ID,gp.NAME  ,gp.GROUP_TYPE   \n" +
                "                             ,gp.CREATED_DATE  ,cd.DESCRIPTION STATUS ,COUNT(con.CANDIDATE_GROUP_ID)\n" +
                "                             FROM  ........" +
                "                             WHERE gp.CREATED_BY_USER_ID = :userId  GROUP BY gp.CANDIDATE_GROUP_ID,gp.NAME  ,gp.GROUP_TYPE   \n" +
                "                             ,gp.CREATED_DATE  ,cd.DESCRIPTION";
        return dbClient.execute(sql)
                .bind("userId", userId)
                .map(row ->{
                            CandidateGroupListDTO info = new CandidateGroupListDTO();
                            info.setGroupId(row.get(0, BigInteger.class));
                            info.setGroupName(row.get(1, String.class)) ;
                            info.setGroupType(row.get(2, String.class));
                            info.setCreatedDate( row.get(3, LocalDateTime.class));
                            info.setStatus(row.get(4, String.class));

                            if(info.getGroupType().equalsIgnoreCase("static")){
                                info.setContactsCount(row.get(5, BigInteger.class));
                            }else{
                getGroupContactCount(info.getGroupId()).subscribe(count ->{
                    System.out.println(">>>>>"+count);
                    info.setContactsCount(count);
                
                        });
                            }
                            return info;
                            }
                        )
                .all() ;
    }
    
    Mono<BigInteger> getGroupContactCount(BigInteger groupId){
            final String sql = "SELECT 3 WHERE :groupId IS NOT NULL;";
            return dbClient.execute(sql)
                    .bind("groupId", groupId)
                    .map(row -> {
                        System.out.println(row.get(0, BigInteger.class));
                        return row.get(0, BigInteger.class);
                    }  ).one();
    }
    
}

When I call getGroupContactCount, I am trying to extract count from Mono<BigInteger> and set it in my DTO.... sys out prints the count value correctly but still I get null for count in response.

Toerktumlare · Answer 1 · 2021-04-16T09:30:32.747

You are calling subscribe in the middle which in turn is essentially blocking. The one subscribing is usually the final consumer, which im guessing your spring application is not, most likely the final consumer is the webpage that initiated the call. Your server is the producer.

call the database, flatMap and return.

return dbClient.execute(sql)
    .bind("userId", userId)
    .flatMap(row ->{
        CandidateGroupListDTO info = new CandidateGroupListDTO();
        info.setGroupId(row.get(0, BigInteger.class));
        info.setGroupName(row.get(1, String.class)) ;
        info.setGroupType(row.get(2, String.class));
        info.setCreatedDate( row.get(3, LocalDateTime.class));
        info.setStatus(row.get(4, String.class));

        if(info.getGroupType().equalsIgnoreCase("static")){
            return Mono.just(info.setContactsCount(row.get(5, BigInteger.class)));
        } else {
            return getGroupContactCount(info.getGroupId()).flatMap(count -> {
                info.setContactsCount(count);
                return Mono.just(info)
            });
        }
    }).all();

Use map if order matters, otherwise try to use flatMap to do async work.

Spring Webflux - R2dbc : How to run a child query and update value while iterating a result set

1 Answers1