Harnessing the Power of QueryDSL for Data Filtering in Spring Boot

Introduction

QueryDSL is a framework that provides a type-safe way of writing queries for various data stores, including databases and search engines. Spring Boot is a popular framework for building web applications that integrates well with QueryDSL. In this article, we'll explore how to use QueryDSL with Spring Boot, including how to set up the necessary dependencies and plugins.

Setting up the Project

To get started, we'll need to create a new Spring Boot project and add the necessary dependencies and plugins. We'll use Maven for this, but you could also use Gradle if you prefer.
Next, open the pom.xml file and add the following dependencies:

        <dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-apt</artifactId>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-jpa</artifactId>
        </dependency>
        </dependency>

Next, we need to add the QueryDSL Maven plugin to our pom.xml file. This plugin generates Q classes for our JPA entities, which we can then use to write type-safe queries with QueryDSL. Add the following plugin configuration to the build section of your pom.xml:

           <plugin>
                <groupId>com.mysema.maven</groupId>
                <artifactId>apt-maven-plugin</artifactId>
                <version>1.1.3</version>
                <executions>
                    <execution>
                        <phase>generate-sources</phase>
                        <goals>
                            <goal>process</goal>
                        </goals>
                        <configuration>
                            <outputDirectory>target/generated-sources/java</outputDirectory>
                            <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
                        </configuration>
                    </execution>
                </executions>
            </plugin>

This plugin configuration tells Maven to generate Q classes for our JPA entities using the JPAAnnotationProcessor. The generated classes will be placed in the target/generated-sources/java directory.

Writing a Query with QueryDSL

Suppose we have a Spring Boot application that manages deliveries for a group of farmers. We want to write a query that filters deliveries based on various criteria such as transaction type, date range, farmer name, and cooperative ID.

Here is an example of a method that uses QueryDSL to filter deliveries:

public ListResponse filterDeliveries(TransactionType transactionType, LocalDate start, LocalDate end, String name,Long cooperativeId,int page,int perPage) {
    page = page - 1;
    Sort sort = Sort.by(Sort.Direction.ASC, "createdAt");
    Pageable pageable = PageRequest.of(page, perPage, sort);
    Page<DeliveryDto> deliveryPage=null;

    if (transactionType != null && name !=null && start != null && end != null ) {
        QAccountTransaction qAccountTransaction = QAccountTransaction.accountTransaction;
        deliveryPage=transactionRepository.findBy(qAccountTransaction.transactionType.eq(transactionType).
                and(qAccountTransaction.farmer.name.containsIgnoreCase(name)).
                and(qAccountTransaction.createdAt.between(start.atStartOfDay(),
                        LocalTime.MAX.atDate(end))), q -> q.sortBy(sort).as(DeliveryDto.class).page(pageable));

        log.info("This is a list of  deliveries found {}", deliveryPage.getContent());
        return new ListResponse(deliveryPage.getContent(), deliveryPage.getTotalPages(), deliveryPage.getNumberOfElements(),
                deliveryPage.getTotalElements());
    }

}

In this example, we first create a Pageable object to define the pagination and sorting parameters for our query. We then use the QAccountTransaction class, which is a QueryDSL-generated class based on our entity AccountTransaction, to define our query criteria. We can chain multiple conditions using the and operator. We then use the transactionRepository to execute the query and return the results as a Page object.

QueryDSL provides a type-safe, fluent API for building complex queries with ease. With QueryDSL, we can easily build dynamic queries that can be used with any database