I don't like DAO Queries to be written this way!

Dear SAP Commerce Developers,

Today, I want to share why I’ve never been a fan of writing DAO queries with long strings and concatenation. Here’s an example of what I mean:

This way of writing queries comes with several issues:

• Risk Of Mistakes: Missing a curly brace , condition Parameter ? or a sign can cause a build failure. These small details are easy to overlook but lead to errors.

• The trouble with Testing: Running this query in the HAC (Hybris Administration Console) requires removing all the concatenation parts, making validating and debugging harder for existing Queries.

• Hard to Maintain: Each time you want to update a query, you deal with a mess of string concatenations. This makes it time-consuming to change any part without risk.

So, is there a more efficient way to write these queries? Let’s explore how to make DAO queries simpler, clearer, and easier to work with.

How about rewriting that same query like this?

With this format, let’s look at some improvements:

• Easy to Convert: Once you have your query structure ready, it’s straightforward to fit it into this format.

• Fewer Chances for Mistakes: Writing the query this way significantly reduces the risk of build failures or runtime query errors since there’s less chance of syntax slip-ups.

Now, a common question comes up: “Is there any performance overhead?”

Since this is a string, it loads only once at runtime, so the performance impact is minimal—negligible.

But what about readability? In some cases, this format can reduce readability, especially with longer queries. When a developer glances at the code, it may take a moment to recognize what each placeholder represents in the below string format.

…it’s hard to immediately tell what each placeholder represents. It can be a guessing game to know what data this query is fetching.

So, is there an even better way to write this?

Let’s give it a try by creating a Query Builder. With a builder, we can make the query more readable, structured, and easier to maintain. This way, each part of the query is clearly defined and labeled, making it instantly recognizable and reducing the risk of mistakes.

Using the Builder Pattern for Flexible Queries:

By using the Builder Pattern, we can construct SQL queries in a more readable and programmatic way. This approach lets us avoid raw string concatenations, reducing potential errors and making our queries easier to read and maintain.

Here’s how we’d like our query builder to work:

In this setup, we’re clearly defining each part of the query—, , and —in a clean, step-by-step fashion. Now, let’s take a look at the CustomQueryBuilder class that makes this possible:

Why Use a Query Builder?

• Readable: Each method (, , ) is clear and easy to follow.

• Less Error-Prone: Reduces manual string concatenation, helping prevent syntax mistakes.

• Easy to Extend: If you need additional clauses like or , you can simply add new methods to the builder.

With this CustomQueryBuilder, we can create queries that are not only more readable but also simpler to manage as our applications grow.

Extending CustomQueryBuilder for Multiple Conditions

Let’s look at a common use case where we need to add multiple conditions using and clauses. For example:

To support this, we can add ` and ` methods in our builder that allow chaining multiple conditions. Here’s the updated class:

Expanding QueryBuilder with More Methods

What if we want even more flexibility? By adding methods like and , we can easily extend the builder to handle more complex queries without sacrificing readability or maintainability.

Here’s an example of what that might look like:

In this example, the does it all—selects fields, joins tables, applies conditions, and sorts results in a single, readable flow.

Here’s the complete class with these added methods:

How it Works

• : Adds the initial condition and marks that the clause is active.

• and : Adds additional conditions only if a clause has been defined.

• : Returns the constructed query, trimmed to remove any extra spaces.\

In Summary

With this extended , you’re well-equipped to construct flexible, readable, and error-resistant SQL queries without dealing with raw string concatenations. Plus, it’s easy to expand further if you need to add more SQL clauses in the future.

Thanks for reading! Hopefully, this builder pattern approach gives you new ideas for writing cleaner, more maintainable DAO queries in your projects.