0

I have 2 tables in postgres DB:

system: id, system_id, system_title

file: id, file_name, uploaded_date_time, system_id references id column of system table. ...etc.

All the system and file details from DB are shown to the user on home page of the application. User can use a search option to search for the system title or file name. I need to return all system and file details where the given search_parameter is present in system_title or file_name.

We are using Spring webflux and Spring Data R2DBC and code looks something like below:

RequestHandler method:

public Mono<ServerResponse> handleFilteredFileList(final ServerRequest request){
  return ServerResponse.ok()
.contentType(APPLICATIOn_JSON)
.body(Bodyinserters.fromProducer(service.filterFileList(request.queryParam("searchParam").get(), PageRequest.of(Integer.parseInt(request.queryParam("page").get())
, Integer.parseInt(request.queryParam("size").get()))),Page.class));

}

Service method(here uploadDateTime is the property of the Java entity object):

{
  repository.getFilteredFileList(searchParam, pageRequest.withSort(Sort.by("uploadDateTime").descending()))
.collectList()
.zipWith(repository.count())
.flatMap(e-> Mono.just(new PageImpl<>(e.getT1(), pageRequest. e.getT2())));
}

Repository method:

@Query("select f from file f inner join system p on f.project_id=p.id where f.is_deleted=false AND (f.file_name LIKE CONCAT('%', :searchParam, '%') OR p.syetem_title LIKE CONCAT('%', :searchParam, '%'))")
Flux<File> getFilteredFileList(@Param("searchParam" String searchParam, Pageable pageable))

I'm facing 2 issues:

  1. I'm not sure if searchParam value is correctly substituted in the query at runtime/execution.
  2. This code isn't working as expected. I'm getting list of null elements.

I tried to implement it by adding LIMIT and OFFSET to the query itself but it didn't work as well.

Any idea what I might be missing here?

Thanks in advance!

Rahul
  • 637
  • 5
  • 16

1 Answers1

0
  1. searchParam should be corretly substituted in the query. :searchParam is a correct way to refere to a parameter in the context of spring data query.
  2. In the context of spring data r2dbc, the query select f from file f ... won't work'. This is a JPQL syntax related to spring data JPA. With spring data r2dbc, the annnotation @Query is expecting a native sql query (select * from file). There is no ORM mapping. So a mapper should be implemented manually for a join query.
Andrianekena Moise
  • 1,018
  • 7
  • 16
  • Regarding pt 2, how is the query's syntax JPQL? I have mentioned table and column names there not entity class names and properties. – Rahul Jul 12 '23 at 10:43
  • If this is intended to be a `standard SQL`, then it is not valid. The following query `select f from file f` means `get the data from column named f`. So it will not return any result if the table file don't have a column with name `f`. The correct syntax to select all datas is `select f.* from file f` in a standard SQL. However it is a valid syntax in JPQL since here, `f` can be considered as an alias to the entity object by JPA. – Andrianekena Moise Jul 17 '23 at 14:04