QueryDSL in a Spring Boot Application

In Spring Boot, QueryDSL can be defined as the Query Domain Specific Language, and it is a framework that can provide a type-safe way to construct queries in Java. It can allow the developers can write queries fluently and concisely of string-based queries such as SQL injection vulnerabilities and lack of the compile time safe QueryDSL generates the query classes and entity classes based on the database schema.

Key Terminologies

• QueryDSL: This stands for Query Domain Specific Language, and it is a framework that can provide a type-safe way to construct queries in Java.
• Query Class: It is a pre-defined class that can represent the query that can be executed against the database, and it generates the classes based on the domain model and database schema.
• Query Entity: It can represent the database table or view and it can generate the entity classes based on the JPA entity annotations.
• Expression: Expressions in QueryDSL can represent the value or the function that can be used in a query.
• Path: It can represent the property of the field of an entity, and it can be used to reference the columns in the query. It can be used to build the predicates and expressions.

Implementation of QueryDSL in Spring Boot Application

We can develop the spring application that can take an employee CSV file as input and that CSV file contains the names and salaries then QueryDSL can process the file then generate the output CSV that can calculate the bonus of the employees.

Step 1: We can create the spring project using Spring STS IDE including the below-mentioned dependencies into the project.

Dependencies:

• Spring Data for JPA
• Spring Web
• Lombok
• Spring Dev Tools

External dependencies:

        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-mongodb</artifactId>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-apt</artifactId>
            <scope>provided</scope>
            <classifier>jakarta</classifier>
            <version>${querydsl.version}</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.mysema.maven/apt-maven-plugin -->
        <dependency>
            <groupId>com.mysema.maven</groupId>
            <artifactId>apt-maven-plugin</artifactId>
            <version>1.1.3</version>
        </dependency>

External Plugin:

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

Once complete the creation of the spring project then the spring project file structure look like the below image.

Step 2: Create the sql file and it named as schema-h2.sql and it can be created the table for the users of the sql database.

Go to src > resources > schema-h2.sql and put the below code.

CREATE TABLE user_seq  (
                      id BIGINT AUTO_INCREMENT PRIMARY KEY,
                      name VARCHAR(255) NOT NULL, -- Adding NOT NULL constraint
                      age INT
);

Step 3: Create the SQL file and it named as data-h2.sql and it can be inserting the data into the table for the users of the SQL database.

Go to src > resources > data-h2.sql and put the below code.

INSERT INTO user_seq (name, age) VALUES ('Mahesh Kadambala', 21);
INSERT INTO user_seq (name, age) VALUES ('Eswar Betha', 22);
INSERT INTO user_seq (name, age) VALUES ('Jagan Mall', 25);
INSERT INTO user_seq (name, age) VALUES ('Ruchitha', 22);
INSERT INTO user_seq (name, age) VALUES ('Sirisha', 26);
INSERT INTO user_seq (name, age) VALUES ('Hema Nookeswari', 21);
INSERT INTO user_seq (name, age) VALUES ('tarun', 25);
INSERT INTO user_seq (name, age) VALUES ('Lohith', 28);
INSERT INTO user_seq (name, age) VALUES ('Deepthi', 22);
INSERT INTO user_seq (name, age) VALUES ('Raju', 29);
INSERT INTO user_seq (name, age) VALUES ('Santhosh', 30);

Step 4: Open the application.properties file and put the below code for the MongoDb database configuration and server port assigning to the application.

spring.datasource.url=jdbc:mysql://localhost:3306/querydsl
spring.datasource.username=root
spring.datasource.password=
server.port=8083
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect
spring.main.banner-mode=off
spring.jpa.hibernate.ddl-auto=none
logging.pattern.console=%d{dd-MM-yyyy HH:mm:ss} %magenta([%thread]) %highlight(%-5level) %logger.%M - %msg%n

Step 5: Create the new package and it named as model in that package create the java class named as User.

Go to src > com.gfg.querydslcrudapplication > model > User and put the below code.

Java

import jakarta.persistence.*;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Objects;

// User class representing a user in the application
@Entity
@Data
@Table(name = "user_seq")
@AllArgsConstructor
@NoArgsConstructor
public class User {

    // Declaring instance variables
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)

      // User ID
    private Long id;

      // User Name
    private String name;
      // User Age
    private Integer age;

    // Overriding equals method
    @Override
    public boolean equals(Object obj) {
          // If objects are the same
        if (this == obj) {
            return true;
        }

          // If object is null
        if (obj == null) {
            return false;
        }

          // If class types are different
        if (getClass() != obj.getClass()) {
            return false;
        }
        final User other = (User) obj;

          // Casting object to User type
        // Comparing instance variables
        if (!Objects.equals(this.id, other.id)) {
            return false;
        }
        if (!Objects.equals(this.name, other.name)) {
            return false;
        }
        return Objects.equals(this.age, other.age);
    }

    // Overriding toString method
    @Override
    public String toString() {

          // StringBuilder for constructing string representation
        var builder = new StringBuilder();

        // Appending instance variables to the builder
        builder.append("User{id=").append(id).append(", name=").append(name)
                .append(", age=").append(age).append("}");

          // Returning the string representation
        return builder.toString();
    }
}

Step 6: Create the new package and it named as repository in that package create the java interface named as UserRepository.

Go to src > com.gfg.querydslcrudapplication > repository > UserRepository and put the below code.

Java

import com.gfg.querydslcrudapplication.model.User;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

// UserRepository interface extending CrudRepository and QuerydslPredicateExecutor
@Repository
public interface UserRepository extends CrudRepository<User, Long>, QuerydslPredicateExecutor<User> {
}

Step 7: Create the java class named as MyRunner that can be implements the CommandLineRunner.

Go to src > com.gfg.querydslcrudapplication > MyRunner and put the below code.

Java

import com.gfg.querydslcrudapplication.model.QUser;
import com.gfg.querydslcrudapplication.repository.UserRepository;
import com.gfg.querydslcrudapplication.model.User;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;
import java.util.Random;

// MyRunner class implementing CommandLineRunner interface
@Component
public class MyRunner implements CommandLineRunner {

    // Initializing Logger instance
    private static final Logger logger = LoggerFactory.getLogger(MyRunner.class);

    // Autowiring UserRepository instance
    @Autowired
    private UserRepository userRepository;

    // Overriding run() method
    @Override
    public void run(String... args) throws Exception {
        // Creating QUser instance
        var qUser = QUser.user;

        // Creating and saving initial user
        User initialUser = new User(null, "John Doe", 30);
        initialUser = userRepository.save(initialUser);
        // Logging initial user insertion
        logger.info("Initial user inserted: {}", initialUser);

        // Generating and saving 25 sample users
        List<User> sampleUsers = generateSampleUsers(25);
        sampleUsers = (List<User>) userRepository.saveAll(sampleUsers);
        // Logging sample users insertion
        logger.info("25 sample users inserted: {}", sampleUsers);

        // Querying for users with age less than 29 and name ending with "est"
        List<User> users = (List<User>) userRepository.findAll(
                qUser.name.endsWith("est").and(qUser.age.lt(29))
        );
        // Logging users query result
        logger.info("Users with age less than 29 and name ending with 'est': {}", users);

        // Querying for users with age greater than or equal to 30
        List<User> usersWithAgeAbove30 = (List<User>) userRepository.findAll(
                qUser.age.goe(30)
        );
        // Logging users query result
        logger.info("Users with age greater than or equal to 30: {}", usersWithAgeAbove30);
    }

    // Method to generate sample users
    private List<User> generateSampleUsers(int count) {
        List<User> users = new ArrayList<>();
        Random random = new Random();
        for (int i = 0; i < count; i++) {
            String name = "User" + (i + 1);
            int age = random.nextInt(100); // Random age between 0 and 99
            users.add(new User(null, name, age));
        }
        return users;
    }
}

Step 8: Open the main class and put the below code.

Java

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

// QueryDslCrudApplication class with main method
@SpringBootApplication
public class QueryDslCrudApplication {

    // Main method to run the application
    public static void main(String[] args) {
        // Running the application
        SpringApplication.run(QueryDslCrudApplication.class, args);
    }

}

Step 9: Once completed the spring project runs the application as spring project then it runs on the port 8083 then you will find the output of the project and this project can generates the sample users and inserted data can show the output. Refer the below image for the better understanding.

We can follow the above step then you can successfully complete the demonstration of the QueryDSL application of the spring project.