Empowering Complex Queries with Querydsl: A Java-Centric Approach to Advanced Database Interactions in Spring Data JPA

image by from Caspar Camille Rubin UnSplash

Why Querydsl?

From the last read regarding Spring Data JPA, it may seem that many problems of pure JPA were solved, as it provides convenient abstractions and automated CRUD operations for basic use cases. However, as the complexity of queries increases, writing JPQL directly in the code becomes inevitable.

public List<Campaign> getAllCampaignWithManagerNames(Pageable pageable) {
return em.createQuery(
        "SELECT c.campaignId, c.name, c.status, c.postingStatus," +
                " c.reportingStatus, c.campaignAmount, c.budgetAmount," +
                " c.recruitmentDifficulty, c.recruitmentNumber," +
                " c.memo, c.currentSituation," +
                " (select m.name from Member m" + 
                " where m.id = c.operationManagerId) as operationManagerName," +
                " (select m.name from Member m" + 
                " where m.id = c.salesManagerId) as salesManagerName" +
                " from Campaign c", Campaign.class)
        .setFirstResult(pageable.getPageNumber() * pageable.getPageSize())
        .setMaxResults(pageable.getPageSize())
        .getResultList();
}

This query was created from a long story. Due to business reasons, Member and Campaign tables never got mapped to each other. However, using JSESSIONID and JPA-Auditing, I saved createdId and updatedId in the database. One top of that, while creating a Campaign, a user is able to assign an operation manager, and a sales manager to the campaign.

So, each managers are saved as BIGINT type numerics in the database, and I had to send out the actual names(String) saved in the Member table. That’s why subqueries had to be used and that’s why this JPQL became somewhat long.

Although this is a completely working query, what’s wrong with this?

• Once it gets long, code maintenance becomes very complicated
• Since it’s a string query, it’s not checked during compile time
• the worst error, which happens at the time when the method is called, occurs

So using those downfalls, we can finally make a list of why Querydsl should be used

• Uses Java Code to write readable, clean code
• better than using String queries
• if you don’t know, just press the dot
• catches error during compile time

Consequently, querydsl gets transforms into JPQL!

Querydsl

In my opinion, the usage of querydsl is very intuitive and doesn’t need much explanation. If you know JPA and JPQL, it’s very to understand what querydsl is trying to do. One difference is that, when you build your application, it creates Q-type classes that is used instead of entities.

For example,

Member findMember = queryFactory
                .select(QMember.member)
                .from(QMember.member)
                .where(QMember.member.username.eq("member1"))
                .fetchOne();

In the class QMember, you can find a static method called member, which indicates the target to be used as.

Qmember.member can be imported statically and be written as the following which makes the code more readable.

Member findMember = queryFactory
                .select(member)
                .from(member)
                .where(member.username.eq("member1"))
                .fetchOne();   

Querydsl: Result Handling

The querydsl package contains a special data type called Tuple . Querydsl’s default for multi-columned result is a Tuple.

List<Tuple> result = queryFactory
                .select(member.username, member.age)
                .from(member)
                .fetch();

Since there are 2 items(columns) in the select part, it gets returned as a Tuple. (If it was just member.username it is returned as List<String>)

Since Tuple is a data type inside the querydsl package, it shouldn’t be present inside other layers. So it could be formatted to a DTO before it is passed down.

//results with a Tuple
List<Tuple> results = queryFactory
        .select(member, team)
        .from(member)
        .leftJoin(team)
        .on(member.username.eq(team.name))
        .fetch();

//initializes Array 
List<MemberTeamDto> memberTeamDto = new ArrayList<>();

//use for loop to insert data from Tuple into array
for (Tuple result : results) {
    Member member = result.get(0, Member.class);
    Team team = result.get(1, Team.class);

    if (team == null) continue;

    MemberTeamDto dto = new MemberTeamDto(
            member.getId(),
            member.getUsername(),
            member.getAge(),
            team.getId(),
            team.getName()
    );

    memberTeamDto.add(dto);
}

//passes down to service layer
return memberTeamDto;

Instead of using Tuple, we can use other methods provided by querydsl.

• Projections.bean: uses setter to enter data into DTO.

I really don’t like this method since it requires us to activate setter, which we should really avoid.

• Projections.fields: uses the column names to enter the data.

The field name of the DTO, and column names from the database must be equal in order for this method to work. Few difference can be solved using .as("name") however, I think it’s really not ideal for code maintenance.

• Projections.constructor: Refers to the type of the data, and the order must match.

This is set through the constructor in the DTO. Since types and order must match, it could become complicated

• QueryProjection: Uses the generated Q-type class DTO.

There are a few pros and cons to this method. We have to use @QueryProjections annotation all the way down at the DTO section of the code.

@QueryProjection
public MemberDto(String username, int age) {
    this.username = username;
    this.age = age;
}

That means our application is becoming more and more dependent to the querydsl package, which is not very good.

My conclusion?

We can leverage both Query-Projection and Projections.constructor and use each in appropriate situation to minimize the dependency of the package and increase code readability

Dynamic Queries

I don’t want to forget about dynamic queries. One of the strengths of querydsl is that it excels when building dynamic queries.

There are 2 ways: Using BooleanBuilder or the Where Clause

BooleanBuilder

BooleanBuilder builder = new BooleanBuilder();
if (hasText(condition.getUsername())) {
    builder.and(member.username.eq(condition.getUsername()));
}
if (hasText(condition.getTeamName())) {
    builder.and(team.name.eq(condition.getTeamName()));
}
if (condition.getAgeGoe() != null) {
    builder.and(member.age.goe(condition.getAgeGoe()));
}
if (condition.getAgeLoe() != null) {
    builder.and(member.age.loe(condition.getAgeLoe()));
}

return queryFactory
        .select(new QMemberTeamDto(
                member.id,
                member.username,
                member.age,
                team.id,
                team.name))
        .from(member)
        .leftJoin(member.team, team)
                      .where(builder)
                      .fetch();

It’s very straight forward. A Booleanbuilder type ‘builder’ is created and we enter values into it using builder.and or builder.or depending on the business logic. Though it can be separated or be done using ternary operators, countless if statements can be followed. This destroys readability. Also, since we only enter the parameter called ‘builder’ into the query, we must understand find and understand what is being contained inside the parameter, which can be very complicated.

Although it’s a good way, I don’t prefer the above method due to the following reasons:

• Difficult to understand what .where(builder) really contains
• when it’s simple it’s simple, but once it starts building more and more components, it can get out of hand
• Must be able to understand the cluster of codes above to understand

Where Clause

The Code:

List<MemberTeamDto> content = queryFactory
        .select(new QMemberTeamDto(
                member.id.as("memberId"),
                member.username,
                member.age,
                team.id.as("teamId"),
                team.name.as("teamName")))
        .from(member)
        .leftJoin(member.team, team)
        .where(
                usernameEq(condition.getUsername()),
                teamNameEq(condition.getTeamName()),
                ageGoe(condition.getAgeGoe()),
                ageLoe(condition.getAgeLoe())
        )
        .offset(pageable.getOffset())
        .limit(pageable.getPageSize())
        .fetch();

JPAQuery<Long> countQuery = queryFactory
        .select(Wildcard.count)
        .from(member)
        .leftJoin(member.team, team)
        .where(
                usernameEq(condition.getUsername()),
                teamNameEq(condition.getTeamName()),
                ageGoe(condition.getAgeGoe()),
                ageLoe(condition.getAgeLoe())
        );

return PageableExecutionUtils.getPage(content, pageable, () -> {
    return countQuery.fetch().get(0);
});

The Components:

private BooleanExpression usernameEq(String username) {
    return hasText(username) ? member.username.contains(username) : null;
}

private BooleanExpression teamNameEq(String teamName) {
    return hasText(teamName) ? team.name.contains(teamName) : null;
}

private BooleanExpression ageGoe(Integer ageGoe) {
    return ageGoe != null ? member.age.goe(ageGoe) : null;
}

private BooleanExpression ageLoe(Integer ageLoe) {
    return ageLoe != null ? member.age.loe(ageLoe) : null;
}

At first glance, one may think this is more complicated than using the BooleanBuilder but breaking down the code especially inside the where clause,

.where(
        usernameEq(condition.getUsername()),
        teamNameEq(condition.getTeamName()),
        ageGoe(condition.getAgeGoe()),
        ageLoe(condition.getAgeLoe())
);

it’s easier to understand what conditions are being used to narrow down the data instead of just undering everything as once as builder. If it’s null, the where clause automatically ignores it which makes this method even more convenient. Also, we can bundle all the conditions into a method and maybe enter one line into the where clause like this

.where(
      personFilter(condition);
};

and apply or take out different filters to match our business logic.

Reasons to use Where Clause:

• Code reusability: components can be used in other parts of the code
• Code flexibility: components can be used or replaced easily anytime
• ignores ‘null’ values
• Increase in code readability

Conclusion

In conclusion, Querydsl is a valuable tool that offers significant advantages over using raw JPQL strings, especially when dealing with complex queries and dynamic conditions. It promotes clean and readable code, enhances type safety, and enables easier code maintenance. By combining Querydsl’s Q-type classes with appropriate result handling methods and employing the Where Clause for dynamic queries, developers can achieve a more efficient and maintainable codebase.