Handle Null Values in QueryDSL Projections

QueryDSL is a powerful library for building type-safe SQL-like queries in Java, especially in conjunction with JPA (Java Persistence API). When working with projections, it’s common to encounter situations where some fields might be null in the database, and handling these cases effectively is crucial for obtaining accurate query results.

There is another post on spring data JPA projection if you want to understand different type of projection in Spring Boot.

In this blog post, we’ll discuss a common issue related to null values in QueryDSL projections and provide a solution to handle such scenarios.

The Problem

Consider a scenario where we are using QueryDSL to perform a projection on entities, and we want to concatenate multiple fields, such as names, titles, etc. If any part of the concatenation is null, the result may end up being null as well, leading to unexpected behavior.

Following is a simplified example of the problem:

Expressions.cases().when(entity.field1.isNotNull())
    .then(entity.field1.concat(" ").concat(entity.field2))
    .otherwise("Default Value")

In this example, if field1 is null, the whole concatenation becomes null, even though field2 might contain a value.

The Solution

To handle null values effectively during concatenation, we can use the coalesce function along with Expressions.cases(). Let’s look at an example:

Expressions.cases().when(entity.field1.isNotNull())
    .then(entity.field1.concat(" "))
    .otherwise("").concat(Expressions.cases().when(entity.field2.isNotNull())
        .then(entity.field2)
        .otherwise(""))

In this updated example, if field1 is null, an empty string is used for the first part of the concatenation, and the field2 value is appended only if it is not null.

Real-world Example

Let’s consider a real-world scenario where we have a User entity with createdBy and modifiedBy relationships to other User entities, and we want to project a DTO (UserProjection) with concatenated names (createdByName and modifiedByName). The code might look like this:

created.title.coalesce("").concat(" ")
	.concat(created.firstName.coalesce("")
.concat(" "))
	.concat(created.middleName.coalesce("")
.concat(" "))
	.concat(created.lastName.coalesce(""))

In this example, the coalesce function is used to handle null values for the titles and names, ensuring that the projection doesn’t result in unexpected null values.

The complete code look like below:

JPAQuery<UserProjection> query = new JPAQuery<>(entityManager);
		 
QUser user = QUser.user;
QUser created = new QUser("created"); // Alias for created user
QUser modified = new QUser("modified"); // Alias for modified user
		 	 
List<UserProjection> content = query
	    .select(new QUserProjection(
	        user.id,
                ...
		user.email,

		created.title.coalesce("").concat(" ")
	            .concat(created.firstName.coalesce("")
                .concat(" "))
	            .concat(created.middleName.coalesce("")
                .concat(" "))
	            .concat(created.lastName.coalesce(""))
		))
		.from(user)
		.leftJoin(created).on(user.createdBy.eq(created.id))
		.leftJoin(modified).on(user.modifiedBy.eq(modified.id))
		.fetch();

Important

If we don’t define the aliases to the join operations then the incorrect data will come. This is because the same user alias for all three join operations will lead to confusion and incorrect retrieval of data

Conclusion

To handle null values in QueryDSL projections is a common challenge, but with the use of coalesce and Expressions.cases(), we can build robust queries that handle null cases gracefully. It’s essential to be aware of these techniques to ensure our queries produce the expected results, especially when dealing with complex projections involving concatenations.

I hope this blog post helps you address null value issues in your QueryDSL projects. If you have any additional tips or experiences to share, feel free to leave a comment!