DOYToWin

Introduction

The dynamic queries is a long-standing pain point in ORM frameworks.
Solving this problem will have a profound impact on simplifying the interaction logic of the database and improving the maintainability of the code.
This article locates a structural duplication in dynamic queries and proposes a general reconstruction method to optimize the code related to dynamic queries.

Background

The interactive interfaces of information systems typically feature various input controls that allow users to enter query criteria. The backend then dynamically constructs SQL queries based on the user’s inputs.

To achieve query code reuse, multiple if statements are often employed to verify the validity of query parameters and combine the corresponding query conditions. A common approach for combining query conditions using if statements is illustrated as follows:

@RestController
@RequestMapping("user")
public class UserController {
    @Resource
    private JdbcTemplate jdbcTemplate;

    @GetMapping("/")
    public List<UserEntity> query(UserQuery query) {
        List<Object> argList = new ArrayList<>();
        StringJoiner where = new StringJoiner(" AND ", " WHERE ", "");
        where.setEmptyValue("");
        if (query.getName() != null && !query.getName().isBlank()) {
            where.add("name = ?");
            argList.add(query.getName());
        }
        if (query.getNameLike() != null && !query.getNameLike().isBlank()) {
            where.add("name LIKE CONCAT('%', ?, '%')");
            argList.add(query.getNameLike());
        }
        if (query.getAgeGt() != null) {
            where.add("age > ?");
            argList.add(query.getAgeGt());
        }
        String sql = "SELECT * FROM user" + where;
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(UserEntity.class), argList.toArray());
    }
}

However, as the number of query parameters increases, the code responsible for combining query conditions becomes increasingly lengthy and complex. This highlights the need for refactoring to improve maintainability and readability.

Analysis

The issue with this code is that every time we add a new query parameter, we can copy and paste an if statement, modifying the parameter name and query condition. This clearly violates the DRY (Don't Repeat Yourself) principle.

Each if statement follows a similar pattern: aside from the parameter name and condition, the rest of the code is nearly identical. I refer to this as property-related duplication, where the structure is duplicated but only the properties differ.

To address this duplication, we can refactor using the reflection replacement method. By leveraging reflection, we can access property values and use annotations to define the query conditions for each property.

If the parameters are directly passed into the method signature, as shown in the code below, reflection cannot be easily applied:

public List<UserEntity> query(String name, String nameLike, Integer ageGt) {
    //...
    if (ageGt != null) {
        where.add("age > ?");
        argList.add(ageGt);
    }
    //...
}

This example demonstrates a common code smell known as Long Parameter List, where multiple parameters are passed individually, making the method harder to maintain and extend.

To address this issue, we can first apply the Introduce Parameter Object refactoring pattern. This involves encapsulating the query parameters into a single object, improving clarity and making future changes easier. This new object is called a query object, which groups together all the fields necessary for querying and simplifies the method signature.

By introducing a query object, the code becomes more maintainable and aligns with best practices, allowing us to later apply more advanced refactoring patterns to handle query conditions dynamically.

Refactoring method

Let's start with the refactoring process.

Step 1: Cleanup Code

We extract the validation logic for query parameters into a helper method, isValid(), which checks if the parameter is valid:

private boolean isValid(Object value) {
    return value != null && (!(value instanceof String str) || !str.isBlank());
}

This allows us to streamline the if statements as shown:

String value = userQuery.getName();
String condition = "name = ?";
if (isValid(value)) {
    where.add(condition);
    argList.add(value);
}

Step 2: Reflection Replacement

We apply reflection to dynamically access the properties of the query object, and retrieve query conditions through annotations:

Field field = query.getClass().getDeclaredField("name");
field.setAccessible(true);
Object value = field.get(query);
String condition = field.getAnnotation(QueryField.class).and();
if (isValid(value)) {
    where.add(condition);
    argList.add(value);
}

Correspondingly, in the UserQuery class, we declare the query conditions using annotations to bind them to specific fields:

public class UserQuery {
    @QueryField(and = "name = ?")
    private String name;

    @QueryField(and = "name LIKE CONCAT('%', ?, '%')")
    private String nameLike;

    @QueryField(and = "age > ?")
    private Integer ageGt;
}

Step 4: Eliminate Duplication

Since we access each field in UserQuery through the getDeclaredField method, and the Class#getDeclaredFields method returns all the fields declared in the class, we can merge the code for concatenating query statements into a loop:

Since the getDeclaredFields() method returns all fields in the class, we can iterate over them and construct the query conditions in a loop:

for (Field field : query.getClass().getDeclaredFields()) {
    field.setAccessible(true);
    Object value = field.get(query);
    String condition = field.getAnnotation(QueryField.class).and();
    if (isValid(value)) {
        where.add(condition);
        argList.add(value);
    }
}

We can now extract this logic into a general-purpose method that works for any query object to build dynamic query conditions, not just UserQuery:

public static String buildWhere(Object query, List<Object> argList) throws Exception {
    StringJoiner where = new StringJoiner(" AND ", " WHERE ", "");
    where.setEmptyValue("");
    for (Field field : query.getClass().getDeclaredFields()) {
        //...
    }
    return where.toString();
}

Summary

With this refactored approach, we create a flexible algorithm for building dynamic query conditions:

1. Use reflection to access all fields in the query object.
2. Iterate through these fields and retrieve their values.
3. If a field’s value is valid, append the corresponding query condition defined in the field’s annotation, and add the value to the parameter list.

This refactoring eliminates the need for repetitive if statements and reduces code duplication. To add new query conditions, developers only need to introduce a new field in the UserQuery class and specify the condition using an annotation. No changes to the query construction logic are necessary, thus making the code more maintainable and scalable.

Similar cases

Similar property-related duplication can also be found in other scenarios. For example, the code that reads data from ResultSet and maps it to propertys follows this pattern:

public RoleEntity mapRow(ResultSet rs, int rowNum) throws SQLException {
    RoleEntity roleEntity = new RoleEntity();
    roleEntity.setId(rs.getInt("id"));
    roleEntity.setRoleName(rs.getString("roleName"));
    roleEntity.setRoleCode(rs.getString("roleCode"));
    roleEntity.setValid(rs.getBoolean("valid"));
    return roleEntity;
}

We can use the above refactoring method to traverse the fields of RoleEntity through reflection, read data and set values, and eliminate these duplicate codes. This is also the basis for developing an ORM framework.

Subsequent optimization

The buildWhere method can be further optimized to enhance flexibility and maintainability.

External optimization

Since the refactored buildWhere method can now dynamically build query clauses for any type of query object, we can extend this approach by extracting the SQL execution logic in UserController into a reusable, general-purpose data query method. This separation of concerns allows the SQL execution code to be reused across different controllers and services.

@RestController
@RequestMapping("user")
public class UserController {
    @Resource
    private JdbcDataAccess jdbcDataAccess;

    @GetMapping("/")
    public List<UserEntity> query(UserQuery query) throws Exception {
        return jdbcDataAccess.query(UserEntity.class, query);
    }
}

@Repository
@AllArgsConstructor
public class JdbcDataAccess {
    private JdbcTemplate jdbcTemplate;

    public <E> List<E> query(Class<E> clazz, Object query) throws Exception {
        RowMapper<E> rowMapper = new BeanPropertyRowMapper<>(clazz);
        List<Object> argList = new ArrayList<>();
        String where = buildWhere(query, argList);
        String table = clazz.getAnnotation(Table.class).name();
        String sql = "SELECT * FROM " + table + where;
        return jdbcTemplate.query(sql, rowMapper, argList.toArray());
    }
}

Internal optimization

Currently, query conditions are defined in annotations with text, where field names are embedded in the query strings. For example, the query condition name = ? contains the field name name. To simplify this, we can eliminate the need for annotations by deducing the column name and operator directly from the field definitions. This approach leads to the creation of a Domain-Specific Language (DSL) for dynamic query generation.

public class UserQuery {
    private String name; // name = ?
    private String nameLike; // name LIKE ?
    private Integer ageGt; // age > ?
}

Conclution

This article identifies and addresses the issue of property-related duplication in dynamic query code, where repetitive patterns emerge when constructing query conditions. By introducing a reflection-replacement refactoring method, we effectively separate the logic for defining query conditions from the logic for constructing them.

In the refactored query method, developers no longer need to modify the query construction logic when adding new conditions. Instead, they simply add fields to the query object, and the conditions are either inferred through field names or annotations, streamlining the process significantly.

This optimization not only simplifies the dynamic query code but also paves the way for the development of a domain-specific language (DSL) based on query objects. This DSL allows for more flexible, maintainable, and scalable dynamic query generation, making future extensions and modifications easier.