[Querydsl Spring Boot] Using Querydsl for Type-safe Database Query in Java Spring Boot

Posted on 2021-02-13 Edited on 2023-09-02 In Programming Language , Java , Spring , Spring Boot Views: Word count in article: 8.7k Reading time ≈ 8 mins.

Querydsl

Querydsl is a framework which enables the construction of type-safe SQL-like queries for multiple backends including JPA, MongoDB and SQL in Java.

Instead of writing queries as inline strings or externalizing them into XML files they are constructed via a fluent API.

The goal here is to give you the practical tools to add Querydsl into your project, understand the structure and purpose of the generated classes, and get a basic understanding of how to write type-safe database queries for most common scenarios.

Installation

First, create or edit gradle file gradle.files/querydsl.gradle.

// gradle.files/querydsl.gradle

ext {
    queryDslVersion = '4.4.0'
}

dependencies {
    implementation("com.querydsl:querydsl-core:${queryDslVersion}")
    implementation("com.querydsl:querydsl-jpa:${queryDslVersion}")
    annotationProcessor("com.querydsl:querydsl-apt:${queryDslVersion}:jpa")

//    For org.hibernate.annotations.Entity (Deprecated)
//    annotationProcessor("org.hibernate.javax.persistence:hibernate-jpa-2.1-api:1.0.2.Final")
    
//    For javax.persistence.Entity
    annotationProcessor("javax.annotation:javax.annotation-api:1.3.2")
}

Then, apply that in gradle file bundle.gradle.

1
2
3

// bundle.gradle

apply from: 'gradle.files/querydsl.gradle'

Or with maven.

Add the following dependencies to your Maven project:

<dependency>
  <groupId>com.querydsl</groupId>
  <artifactId>querydsl-apt</artifactId>
  <version>${querydsl.version}</version>
  <scope>provided</scope>
</dependency>

<dependency>
  <groupId>com.querydsl</groupId>
  <artifactId>querydsl-jpa</artifactId>
  <version>${querydsl.version}</version>
</dependency>

And now, configure the Maven APT plugin:

<project>
  <build>
  <plugins>
    ...
    <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>
            <!-- For org.hibernate.annotations.Entity (Deprecated) -->
            <!-- <processor>com.querydsl.apt.hibernate.HibernateAnnotationProcessor</processor> -->

            <!-- For javax.persistence.Entity -->
            <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
          </configuration>
        </execution>
      </executions>
    </plugin>
    ...
  </plugins>
  </build>
</project>

The JPAAnnotationProcessor finds domain(or entity, model) types annotated with the javax.persistence.Entity annotation and generates query types(Prefix with Q letter) for them.

(Deprecated) If you use Hibernate annotations with org.hibernate.annotations.Entity in your domain types you should use the APT processor com.querydsl.apt.hibernate.HibernateAnnotationProcessor instead.

Run clean install and you will get your Query types generated into target/generated-sources/java.

Now you are able to construct JPA query instances and instances of the query domain model.

Configuration

Configuration data source.

# application.properties

# spring.datasource
spring.datasource.url=jdbc:postgresql://postgres:5432/cloudolife_development
spring.datasource.username=cloudolife
spring.datasource.password=cloudolife

# Output SQL to console
; #spring.jpa.show-sql=true
; #spring.jpa.properties.hibernate.format_sql=true

Or application.yml.

# application.yml

# spring.datasource
spring:
  datasource:
    url: jdbc:postgresql://postgres:5432/cloudolife_development
    username: cloudolife
    password: password

# Output SQL to console
spring:
  jpa:
    show-sql: true
    properties:
      hibernate: 
        format_sql: true

Usages

Entity(or Model)

// User.java

package com.cloudolife.examples.models;

import lombok.*;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import java.util.UUID;

@Data
@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue
    private Long id;

    private String code;
    private String name;
}

Querydsl will generate a query type with the simple name QUser into the same package as User. QUser can be used as a statically typed variable in Querydsl queries as a representative for the User type.

QUser has a default instance variable which can be accessed as a static field:

1	QUser user = QUser.user;

Alternatively you can define your own User variables like this:

1	QUser user = new QUser("myUser");

Main

package com.cloudolife.examples;

import com.querydsl.jpa.impl.JPAQueryFactory;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

import javax.persistence.EntityManager;

@SpringBootApplication
public class ColAgreementServiceApplication {

    public static void main(String[] args) {
        SpringApplication.run(ColAgreementServiceApplication.class, args);
    }

    @Bean
    public JPAQueryFactory jpaQueryFactory(EntityManager entityManager) {
        return new JPAQueryFactory(entityManager);
    }
}

Service

package com.cloudolife.examples.services;

import com.cloudolife.examples.models.User;
import com.cloudolife.examples.models.QUser;
import com.querydsl.jpa.impl.JPAQueryFactory;
import lombok.extern.java.Log;

import java.util.List;

@Service
@Log
public class AgreementService {

    private final JPAQueryFactory jpaQueryFactory;

    public AgreementService(JPAQueryFactory jpaQueryFactory) {
        this.jpaQueryFactory = jpaQueryFactory;
    }

    public Agreement queryOne() {
        log.info("By QueryDSL");

        QAgreement qagreement = QAgreement.agreement;
        Agreement agreement = jpaQueryFactory.selectFrom(QAgreement.agreement).
                where(QAgreement.agreement.code.eq("Code")).
                fetchFirst();

        return agreement;
    }
}

UnitTest

package com.cloudolife.examples;

import com.cloudolife.examples.services.UserService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class ColAgreementServiceApplicationTests {

    @Test
    void contextLoads() {
    }

    @Autowired
    private UserService userService;

    @Test
    void testUserServiceQueryOne() {
        userService.queryOne();
    }

}

FAQs

java.lang.NoClassDefFoundError: com/querydsl/core/types/Predicate

Check your gradle file to avodie that error.

dependencies {
    implementation("com.querydsl:querydsl-core:${queryDslVersion}")
    implementation("com.querydsl:querydsl-jpa:${queryDslVersion}")
    annotationProcessor("com.querydsl:querydsl-apt:${queryDslVersion}:jpa")

    annotationProcessor("org.hibernate.javax.persistence:hibernate-jpa-2.1-api:1.0.2.Final")
    annotationProcessor("javax.annotation:javax.annotation-api:1.3.2")
}

Parameter 1 of constructor in com.cloudolife.examples.services.UserService required a bean of type ‘com.querydsl.jpa.impl.JPAQueryFactory’ that could not be found.

Add Bean annotation and public JPAQueryFactory jpaQueryFactory(EntityManager entityManager) method to main class.

package com.cloudolife.examples;

import com.querydsl.jpa.impl.JPAQueryFactory;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

import javax.persistence.EntityManager;

@SpringBootApplication
public class ColAgreementServiceApplication {

    public static void main(String[] args) {
        SpringApplication.run(ColAgreementServiceApplication.class, args);
    }

    @Bean
    public JPAQueryFactory jpaQueryFactory(EntityManager entityManager) {
        return new JPAQueryFactory(entityManager);
    }
}