How to query same table with same parameter but with different where conditions and get combined res-CodePudding

I have a global search I will search with one keyword but need to get results with all the matching columns of a table.

Page<A> a = null;
a = zRepo.getResultByNameSearch(searchText)
a = zRepo.getResultByNumberSeach(searchText)
a = zRepo.getRsultByProjectSearch(searchText)

@Query("select * from a_table x where x.name like :searchText")
Page<A> getResultByNameSearch(@Param("searchText") String searchText, Pageable pageable);

@Query("select * from a_table where x.number like :searchText")
Page<A> getResultByNumberSearch(@Param("searchText") String searchText, Pageable pageable);

@Query("select * from a_table where x.project like :searchText")
Page<A> getResultByProjectSearch(@Param("searchText") String searchText, Pageable pageable);

So each repository call queries and fetches the same table but according to the searchText.

Let's assume name = "Company910", number = "XX910", project = "910".

Now I'm searching for "910" and want to get results with all the 3 values. Page<a> will be having all the columns of a_table with the list of results as per "Company910", "XX910", "910".

How to implement this or is there any other way where I can maintain a single query instead of three different for name, number and project?

CodePudding user response：

For the first part of how to get the combined result. I will do something like this.

Create repository interface as you wrote but using spring data jpa intead of raw query
user CompletableFuture with each method call
combine the result into a dto
return this combined result

@Repository
public interface SampleDocumentRepository extends JpaRepository<SampleDocument, Integer> {

    Page<SampleDocument> findByNameContains(String name, Pageable pageable);

    Page<SampleDocument> findByNumberContains(String number, Pageable pageable);

    Page<SampleDocument> findByProjectContains(String project, Pageable pageable);
}

Service class where result are combined together

@Service
@AllArgsConstructor
public class SampleDocumentService {
    private final SampleDocumentRepository repository;


    @Transactional(readOnly = true)
    public CompletableFuture<ResultDto> search(String query) {
        PageRequest page = PageRequest.of(0, 20);
        CompletableFuture<Page<SampleDocument>> nameSearch = CompletableFuture.supplyAsync(() -> repository.findByNameContains(query, page));
        CompletableFuture<Page<SampleDocument>> numberSearch = CompletableFuture.supplyAsync(() -> repository.findByNumberContains(query, page));
        CompletableFuture<Page<SampleDocument>> projectSearch = CompletableFuture.supplyAsync(() -> repository.findByProjectContains(query, page));
        return CompletableFuture.allOf(nameSearch, numberSearch, projectSearch)
                .thenApply(unused -> new ResultDto(nameSearch.join(), numberSearch.join(), projectSearch.join()));
    }
    
}

Then the call from the service

@GetMapping("/search")
    public CompletableFuture<ResultDto> search(@RequestParam("query") String query) {
        return service.search(query);
    }

call using your query argument

http://localhost:8080/sample/search?query=hello

To answer the second part, if you want to check if the query is present in any of the columns you can write JPA query combining the Or operator like this.

Page<SampleDocument> findByNameContainsOrNumberContainsOrProjectContains(String name, String number, String project, Pageable pageable);

Caller would be something like this

@Transactional(readOnly = true)
    public CompletableFuture<ResultDto> searchAll(String query) {
        PageRequest page = PageRequest.of(0, 20);
        CompletableFuture<Page<SampleDocument>> nameSearch = CompletableFuture.supplyAsync(() ->
                repository.findByNameContainsOrNumberContainsOrProjectContains(query, query, query, page));
        return CompletableFuture.supplyAsync(() -> new ResultDto(nameSearch.join(), null, null));
    }

If you want to use Async with spring data and completable please follow this link