Introduction: Querydsl vs. JPA Criteria

I've used JPA Criteria for many years. The main advantage is its type-safe approach (compared to JPQL), but the code is quite complicated and not always straightforward. Recently, I came across a Querydsl framework, and I saw its benefits immediately. The main benefits for me are:

• Type-safe approach.
• Fluent API with SQL style — the code looks pretty much like SQL queries.
• No limitation to JPA technology — there are modules for other technologies.

The idea of this series is to shed light on the Querydsl framework and compare it with the JPA Criteria. The main goal is to demonstrate the differences in some standard cases. This series is planned to have these parts:

• Introduction (this article)
• Build queries
• Projections
• Collection module
• Lucene module

In This Article, You Will Learn:

• What is Querydsl
• How to setup a project for the Spring Data JPA and the Querydsl
• How to use different types of queries with the Spring Data JPA

What Is Querydsl?

Querydsl was born out of the need to maintain HQL queries in a typesafe way. Incremental construction of HQL queries requires String concatenation and results in hard to read code. Unsafe references to domain types and properties via plain Strings were another issue with String based HQL construction.    

• JPA — The popular choice for SQL persistence with a focus on CRUD and simple queries for object loading. 
• SQL — The alternative SQL abstraction with a focus on SQL operations and the full expressivity of the SQL standard.
• MongoDB — ODM support via Morphia or Spring Data for MongoDB, the NoSQL of choice for many. 
• JDO — JDO support for Object, SQL, and NoSQL storage abstraction. 
• Lucene — Full-text indexing via Lucene, the most popular full-text index for Java.
• Collections — Java Collection querying for Java Beans and POJOs.

Project Setup

There are many articles (including the official website) about using Querydsl. I recommend checking these links:

• Query DSL
• Spring Data JPA + QueryDSL: Taking the Best From Both Worlds
• Advanced Spring Data JPA Specifications and QueryDSL

This article starts the series with a very quick demonstration of JPA Criteria and Querydsl usage.

Note: all the examples in this series use Lombok in order to simplify and reduce the code. 

Maven Configuration

Spring Data JPA

In order to start using the JPA criteria, the spring-boot-starter-data-jpa dependency has to be added into pom.xml as shown below. The latest available version can be found in the Maven Central repository. However, there's no need to define the exact version. We can use the version defined by Spring Boot (as in our case).

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

QueryDSL

Besides that, we also need to add querydsl-jpa and querydsl-apt dependencies and apt-maven-plugin plugin into our Maven project (pom.xml) in order to enable Querydsl features. We don't need to care about the version as it's defined by Spring Boot (see ${querydsl.version} property). The latest version can be found in the Maven Central repository.

<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>
...
<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>

Note: these artifacts are only for the JPA Criteria and Querydsl. We might need more dependencies (e.g., for Spring Data JPA, Lombok, Liquibase, etc.), but it's not the purpose of this article to provide a full setup. 

Database

In this article and probably the whole series, we use a simple Country domain.

Note: we don't need a complicated domain model in order to demonstrate Querydsl features. 

Country Domain

The Country domain contains country and city tables. Their relationship is depicted below.

Initialization

All the DB changes are realized with Liquibase. We start with the DB tables definition (DDL) by adding this changelog:

databaseChangeLog:
  - changeSet:
      id: DDL
      author: arnost.havelka
      changes:
        - createTable:
            tableName: country
            columns:
              - column:
                  name: id
                  type: int
                  autoIncrement: true
                  constraints:
                    primaryKey: true
                    nullable: false
              - column:
                  name: name
                  type: varchar(255)
                  constraints:
                    nullable: false
        - createTable:
            tableName: city
            columns:
              - column:
                  name: id
                  type: int
                  autoIncrement: true
                  constraints:
                    primaryKey: true
                    nullable: false
              - column:
                  name: name
                  type: varchar(255)
                  constraints:
                    nullable: false
              - column:
                  name: state
                  type: varchar(255)
                  constraints:
                    nullable: true
              - column:
                  name: country_id
                  type: varchar(255)
                  constraints:
                    nullable: false
            addForeignKeyConstraint:
              - baseColumnNames: country_id
              - baseTableName: country
              - constraintName: city_fk1
              - deferrable: false
              - initiallyDeferred: false
              - onDelete: RESTRICT
              - onUpdate: RESTRICT
              - referencedColumnNames: id
              - referencedTableName: country
              - validate: true

Next, we need to populate some data (DML) with this changelog:

databaseChangeLog:
  - changeSet:
      id: Init data
      author: arnost.havelka
      changes:
        - insert:
            tableName: country
            columns:
              - column:
                  name: name
                  value: Australia
        - insert:
            tableName: city
            columns:
              - column:
                  name: name
                  value: Brisbane
              - column:
                  name: state
                  value: Queensland
              - column:
                  name: country_id
                  value: "1"
        - insert:
            tableName: city
            columns:
              - column:
                  name: name
                  value: Melbourne
              - column:
                  name: state
                  value: Victoria
              - column:
                  name: country_id
                  value: "1"

Note: a quick tutorial to start with Liquibase can be found here.

JPA Configurations

Which are mapped as Country and City JPA entities.

We use Spring Data JPA dependency, as already mentioned above. With that, we need to create entities and repositories for our DB tables from the Country domain.

Note: the Country table is used for the demonstration of the Querydsl, and the City table is for the JPA Criteria.

Entity Mappings

Create a Country entity for the COUNTRY table as:

@Entity
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
public class Country implements Serializable {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long id;

	@Column(nullable = false)
	private String name;

	@OneToMany(mappedBy = "country", cascade = { CascadeType.PERSIST, CascadeType.MERGE })
	@ToString.Exclude
	private List<City> cities;

}

Consequently, adding a City entity for the CITY table as:

@Entity
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
public class City implements Serializable {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long id;

	@Column(nullable = false)
	private String name;

	@Column(nullable = true)
	private String state;

	@ManyToOne
	@JoinColumn(name = "COUNTRY_ID")
	@NonNull
	private Country country;

}

Repository

The last piece in our project setup is the creation of Spring Data JPA repositories. With them, we can access and manage our data with a very simple implementation. 

Note: you can find more details of the Spring Data usage in my previous article, Introduction to Spring Data Elasticsearch 4.1.

Named Queries

Our first repository is represented by the CountryRepository interface, which extends the JpaRepository interface with these types:

• Country — the managed JPA entity by the repository
• Long — the type of primary key

Now we can add some query methods:

• getByName — a method to get one instance of an entity matched by the exact name
• findByNameLikeIgnoreCase — a method to find all entities matched by case insensitive like expression for the name attribute.

The CountryRepository repository is defined for the Country entity as:

public interface CountryRepository extends JpaRepository<Country, Long> {

	Country getByName(String name);

	List<Country> findByNameLikeIgnoreCase(String name);

}

Similarly, the CityRepository repository is defined for the City entity as:

public interface CityRepository extends JpaRepository<City, Long> {

	City getByName(String name);
	
	City findByNameAndCountryNameAllIgnoringCase(@NonNull String name, @NonNull String country);

}

Note: we can simply add more methods just by defining the correct name pattern (see Spring docs).

So far, we used the approach where neither JPA Criteria nor Querydsl is used. Let's look at the advanced queries where we use these technologies.

Dynamic Queries

Many times the named query (i.e., statically defined query described above) is not sufficient because the input arguments have to be used by the query. The easiest way to achieve this is to extend our repository class with a specific interface for the used technology and add the desired query feature as a default method.

JPA Specification

JPA Criteria uses a Specification interface for this purpose. We have to follow these steps:

1. Extend the JpaSpecificationExecutor interface (line 1). This enables several methods to accept the Specification interface as an argument. Optionally, you can use Pageable or Sort arguments.
2. Add a specWithState method (lines 7-9) with a query constraint. In our case, the goal of the constraint is to find cities with an empty value in the City.state attribute. 
3. Finally, we need to use the findAll method (line 4) available from the JpaSpecificationExecutor interface. It's demonstrated in the findAllWithState method (lines 3-5).

public interface CityRepository extends JpaRepository<City, Long>, JpaSpecificationExecutor<City> {

	default List<City> findAllWithState() {
		return findAll(specWithState());
	}

	default Specification<City> specWithState() {
		return (cityRoot, q, cb) -> cb.not(cb.isNull(cityRoot.get(state)));
	}

}

Note: this query can be written with the named query as well.

The implementation of the specWithState method contains a Lambda expression (line 8) for the toPredicate method from the Specification interface. With this, we can query data in a simple way as we have available instances of the Root, CriteriaQuery and CriteriaBuilder classes.

Querydsl Predicate

Querydsl uses a similar approach but with Predicate return type instead. We have to follow these steps:

1. Extend the QuerydslPredicateExecutor interface (line 1). This enables several methods to accept the Predicate interface as an argument. Optionally, you can also use additional arguments (e.g. Pageable or Sort).
2. Add a predicateWithoutCities method (lines 7-10) with a query constraint. The goal is to define a constraint to find countries without any city. 
3. Finally, we also need to use the findAll method (line 4) available from theQuerydslPredicateExecutor interface. The usage is demonstrated in the findAllWithoutCities method (lines 3-5).

public interface CountryRepository extends JpaRepository<Country, Long>, QuerydslPredicateExecutor<Country> {

	default Iterable<Country> findAllWithoutCities() {
		return findAll(predicateWithoutCities());
	}

	default Predicate predicateWithoutCities() {
		return new BooleanBuilder()
				.and(country.cities.isEmpty());
	}

}

The implementation of the predicateWithoutCities (lines 7-10) is based on the BooleanBuilder class which implements the Predicate interface. Here, we create an instance of the BooleanBuilder (line 8) and apply a constraint on the Country.country (line 9).

Custom Queries

When all options mentioned above are not sufficient, then we need to create a custom query. This approach is useful for very complex queries (e.g., projection, grouping, multiple joins of the same entity, sub-queries, etc.). The recommended approach for writing the custom query is described in Chapter 4.6.1 in the official documentation.

JPA Criteria

First, we need to create a new CityCustomRepository interface where the desired method (the findAllCitiesBy in our case) is defined.

public interface CityCustomRepository {

	List<City> findAllCitiesBy(@NonNull String name, @NonNull String state, @NonNull String countryName);

}

Next, we need to implement this method. For that, we create a CityCustomRepositoryImpl class with the implementation of the findAllCitiesBy method.

@Repository
@RequiredArgsConstructor
public class CityCustomRepositoryImpl implements CityCustomRepository {

	@PersistenceContext
	private final EntityManager em;

	public List<City> findAllCitiesBy(@NonNull String cityName, @NonNull String cityState, @NonNull String countryName) {
		var cb = em.getCriteriaBuilder();
		var query = cb.createQuery(City.class);
		Root<City> cityRoot = query.from(City.class);
		List<Predicate> predicates = new ArrayList<>();

		predicates.add(cb.like(cityRoot.get("name"), cityName));
		predicates.add(cb.like(cityRoot.get("state"), cityState));
		predicates.add(cb.equal(cityRoot.get("country").get("name"), cb.literal(countryName)));

		query.where(predicates.toArray(new Predicate[0]));
		return em.createQuery(query).getResultList();
	}

}

As you can see in the CityCustomRepositoryImpl class, we need to follow these steps:

1. Inject an instance of EntityManager into the em variable (line 6).
2. Retrieve an instance of CriteriaBuilder from em (line 9).
3. Create a query with CriteriaQuery<City> type from cb (line 10).
4. Get a Root<City> from the query (line 11).
5. Define the desired constraints into the predicates variable (lines 12-16).
6. Add the predicates into the query (line 18).
7. Trigger the query by calling getResultList (line 19). Note: there are available other methods for different purposes (e.g. getSingleResult, getFirstResult, etc.).

Finally, we need to extend the CityRepository with the newly created CityCustomRepository interface.

public interface CityRepository extends CityCustomRepository, JpaRepository<City, Long> {
	...
}

Querydsl

Similarly, we need to create a new CountryCustomRepository interface with the desired method (the findAllCitiesBy method in this case).

public interface CountryCustomRepository {

	List<Country> findAllCountriesBy(@NonNull String cityName, @NonNull String cityState);

}

We create the implementation of the findAllCountriesBy method in the CountryCustomRepositoryImpl class.

@Repository
@RequiredArgsConstructor
public class CountryCustomRepositoryImpl implements CountryCustomRepository {

	@PersistenceContext
	private final EntityManager em;

	public List<Country> findAllCountriesBy(@NonNull String cityName, @NonNull String cityState) {
		return new JPAQuery<Country>(em)
				.select(city.country)
				.from(city)
				.where(city.name.like(cityName)
						.and(city.state.like(cityState)))
				.fetch();
	}

}

As you can see in the CountryCustomRepositoryImpl class, we need to follow these steps:

1. Inject an instance of EntityManager into the em variable (line 6).
2. Create a JpaQuery<City> query from em (line 9).
3. Define the SQL-like query constraints with the fluent API approach (lines 10-13).
4. Finally trigger the query by calling fetch (line 14). Note: there are available other methods for different purposes (e.g. fetchOne, fetchFirst, etc.).

At the end, we need to extend the CountryRepository with the newly created CountryCustomRepository interface.

public interface CountryRepository extends CountryCustomRepository, JpaRepository<Country, Long> {

	...
}

Conclusion

This article has covered what the Querydsl framework is and how to add it to our project. We began with the setup by creating a DB with two tables and some data with the Liquibase library. Next, we explained several options for creating the query with Spring Data JPA. The custom query for the CITY table was demonstrated with the  JPA Criteria, and the custom query for the COUNTRY table was demonstrated with the Querydsl. This part is most important as it will be the topic of the next article.

The complete source code presented above is available in my GitHub repository.