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:

public static final String FETCH_CUSTOMER_BY_EMAIL = 
    "SELECT {" + CustomerModel.PK + "} " +
    "FROM {" + CustomerModel._TYPECODE + "} " +
    "WHERE {" + CustomerModel.EMAIL + "} = ?email";        

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?

public static final String FETCH_CUSTOMER_BY_EMAIL =  String.format(
    "SELECT {%s} FROM {%s} WHERE {%s} = ?email",
      CustomerAccountRequestModel.PK,
      CustomerAccountRequestModel._TYPECODE,
      CustomerAccountRequestModel.EMAIL
);        

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

"SELECT {%s} FROM {%s} WHERE {%s} = ?email"        

…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:

String query = new CustomQueryBuilder()
    .select(CustomerModel.PK)
    .from(CustomerModel._TYPECODE)
    .where(CustomerModel.EMAIL + " = ?email")
    .build();        

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

/**
 * CustomQueryBuilder is a utility class designed to help construct Flexible SQL queries in a 
 * more readable and programmatic way, avoiding the need for manual string concatenation.
 */
public class CustomQueryBuilder {

    private StringBuilder query = new StringBuilder();

    public CustomQueryBuilder select(String... fields) {
        query.append("SELECT ");
        query.append(String.join(", ", fields));
        query.append(" ");
        return this;
    }

    public CustomQueryBuilder from(String table) {
        query.append("FROM ").append(table).append(" ");
        return this;
    }

    public CustomQueryBuilder where(String condition) {
        query.append("WHERE ").append(condition).append(" ");
        return this;
    }

    public String build() {
        return query.toString();
    }
}
        

Why Use a Query Builder?

• Readable: Each method (select, from, where) 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 ORDER BY or LIMIT, 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 and OR clauses. For example:

String query = new CustomQueryBuilder()
    .select(CustomerModel.PK)
    .from(CustomerModel._TYPECODE)
    .where(CustomerModel.EMAIL + " = ?email")
    .and(CustomerModel.STATUS + " = ?status")
    .or(CustomerModel.LASTLOGIN + " > ?lastLogin")
    .build();
        

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

/**
 * CustomQueryBuilder is a utility class designed to help construct Flexible SQL queries in a *more   readable  and programmatic way, avoiding the need for manual string concatenation.
 */
public class CustomQueryBuilder {

    private StringBuilder query = new StringBuilder();
    private boolean hasWhereClause = false;

    public CustomQueryBuilder select(String... fields) {
        query.append("SELECT ");
        query.append(String.join(", ", fields));
        query.append(" ");
        return this;
    }

    public CustomQueryBuilder from(String table) {
        query.append("FROM ").append(table).append(" ");
        return this;
    }

    public CustomQueryBuilder where(String condition) {
        if (!hasWhereClause) {
            query.append("WHERE ").append(condition).append(" ");
            hasWhereClause = true;
        }
        return this;
    }

    public CustomQueryBuilder and(String condition) {
        if (hasWhereClause) {
            query.append("AND ").append(condition).append(" ");
        }
        return this;
    }

    public CustomQueryBuilder or(String condition) {
        if (hasWhereClause) {
            query.append("OR ").append(condition).append(" ");
        }
        return this;
    }

    public String build() {
        return query.toString().trim();
    }
}
        

Expanding QueryBuilder with More Methods

What if we want even more flexibility? By adding methods like join() and orderBy(), 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:

String query = new QueryBuilder()
    .select(CustomerModel.PK, CustomerModel.NAME)
    .from(CustomerModel._TYPECODE)
    .join(OrderModel._TYPECODE, CustomerModel.PK + " = " +  OrderModel.CUSTOMER)
    .where(CustomerModel.EMAIL + " = ?email")
    .and(CustomerModel.STATUS + " = ?status")
    .orderBy(true, CustomerModel.LASTLOGIN)
    .build();
        

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

Here’s the complete QueryBuilder class with these added methods:

/**
 * QueryBuilder is a utility class designed to help construct flexible SQL queries in a 
 * more readable and programmatic way, avoiding the need for manual string concatenation.
 */
public class QueryBuilder {

    private StringBuilder query = new StringBuilder();
    private boolean hasWhereClause = false;

    public QueryBuilder select(String... fields) {
        query.append("SELECT ");
        query.append(String.join(", ", fields));
        query.append(" ");
        return this;
    }

    public QueryBuilder from(String table) {
        query.append("FROM ").append(table).append(" ");
        return this;
    }

    public QueryBuilder join(String table, String condition) {
        query.append("JOIN ").append(table).append(" ON ").append(condition).append(" ");
        return this;
    }

    public QueryBuilder where(String condition) {
        if (!hasWhereClause) {
            query.append("WHERE ").append(condition).append(" ");
            hasWhereClause = true;
        }
        return this;
    }

    public QueryBuilder and(String condition) {
        if (hasWhereClause) {
            query.append("AND ").append(condition).append(" ");
        }
        return this;
    }

    public QueryBuilder or(String condition) {
        if (hasWhereClause) {
            query.append("OR ").append(condition).append(" ");
        }
        return this;
    }

    public QueryBuilder orderBy(boolean ascending, String... fields) {
        query.append("ORDER BY ").append(String.join(", ", fields)).append(" ");
        query.append(ascending ? "ASC " : "DESC ");
        return this;
    }

    public String build() {
        return query.toString().trim();
    }
}
        

How it Works

• where: Adds the initial condition and marks that the WHERE clause is active.
• and and or: Adds additional conditions only if a WHERE clause has been defined.
• build: Returns the constructed query, trimmed to remove any extra spaces.\

In Summary

With this extended QueryBuilder, 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.