CODEWITHSUNDEEP
spring-boothibernatejpaspring-data-jpaspring-data
user2738882
user2738882

Reputation: 1190

SpringBoot build query dynamically

I'm using SpringBoot 2.3.1 and Spring Data for accessing to PostgreSQL. I have the following simple controller:

@RestController
public class OrgsApiImpl implements OrgsApi {
    @Autowired
    Orgs repository;

    @Override
    public ResponseEntity<List<OrgEntity>> listOrgs(@Valid Optional<Integer> pageLimit,
        @Valid Optional<String> pageCursor, @Valid Optional<List<String>> domainId,
        @Valid Optional<List<String>> userId) {

        List<OrgEntity> orgs;

        if (domainId.isPresent() && userId.isPresent()) {
            orgs = repository.findAllByDomainIdInAndUserIdIn(domainId.get(), userId.get());
        } else if (domainId.isPresent) {
            orgs = repository.findAllByDomainIdIn(domainId.get());
        } else if (userId.isPresent()) {
            orgs = repository.findAllByUserIdIn(userId.get());
        } else {
            orgs = findAll();
        }

        return ResponseEntity.ok(orgs);
    }
}

And a simple JPA repository:

public interface Orgs extends JpaRepository<OrgEntity, String> {
    List<OrgEntity> findAllByDomainIdIn(List<String> domainIds);

    List<OrgEntity> findAllByUserIdIn(List<String> userIds);

    List<OrgEntity> findAllByDomainIdInAndUserIdIn(List<String> domainIds, List<String> userIds);
}

The code above has several obvious issues:

  1. If number of query parameters will grow, then this if is growing very fast and too hard to maintain it. Question: Is there any way to build query with dynamic number of parameters?
  2. This code doesn't contain a mechanism to support cursor. Question: Is there any tool in Spring Data to support query based on cursor?

The second question can be easily get read if first question is answered.

Thank you in advance!

Upvotes: 0

Views: 1472

Answers (2)

joutvhu
joutvhu

Reputation: 35

You can use spring-dynamic-jpa library to write a query template

The query template will be built into different query strings before execution depending on your parameters when you invoke the method.

Upvotes: 0

Oliver Drotbohm
Oliver Drotbohm

Reputation: 83081

tl;dr

It's all in the reference documentation.

Details

Spring Data modules pretty broadly support Querydsl to build dynamic queries as documented in the reference documentation. For Spring Data JPA in particular, there's also support for Specifications on top of the JPA Criteria API. For simple permutations, query by example might be an option, too.

As for the second question, Spring Data repositories support streaming over results. That said, assuming you'd like to do this for performance reasons, JPA might not be the best fit in the first place, as it'll still keep processed items around due to its entity lifecycle model. If it's just about access subsets of the results page by page or slice by slice, that's supported, too.

For even more efficient streaming over large data sets, it's advisable to resort to plain SQL either via jOOQ (which can be used with any Spring Data module supporting relational databases), Spring Data JDBC or even Spring Data R2DBC if reactive programming is an option.

Upvotes: 4

Related Questions