Refactor ORM 3: The PageQuery Object and DataAccess Interface

This article explores refactoring dynamic query interfaces by encapsulating pagination and sorting logic into a reusable superclass PageQuery to generate the dynamic components of the SQL statements.

Introduction

Dynamic query interfaces not only provide users with a variety of query parameters but also process query results based on pagination and sorting parameters.

This article builds upon the refactoring of dynamic query code to further explore the refactoring of pagination and sorting logic, aiming to design a streamlined and efficient data access interface.

Paging and Sorting

Assume we face the following query request:

/user/?ageGt=3&pageNumber=3&pageSize=10&sort=age,desc;name,asc

Here, pagination parameters are defined as pageNumber and pageSize. The parameter sort is a string. In the Controller layer, we define the corresponding query method as follows:

@GetMapping("/user/")
public List<UserEntity> query(String name, String nameLike, Integer ageGt, Integer pageNumber, Integer pageSize, String sort) {
    // ...
}

PageQuery Object

Following the previous refactoring process, we applied the Introduce Parameter Object technique to encapsulate pagination and sorting parameters into a query object, UserQuery.

Since pagination and sorting are common requirements, we further extracted these fields into a superclass PageQuery using the Extract Superclass refactoring technique. All query objects inherit from this superclass:

public class PageQuery {
    private Integer pageNumber;
    private Integer pageSize;
    private String sort;
}

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

This design also prevents pagination and sorting fields from being accessed when iterating over UserQuery fields.

Response Object

In addition to returning the query data list, paginated queries must also return the total data count (total) for the frontend to calculate the total number of pages. The commonly used formula is ⌈total / size⌉. Thus, we define the response object for paginated queries as follows:

public record PageList<T>(List<T> list, long total) {
}

Pagination Clause Construction

Based on the page index pageNumber and page size pageSize, the offset can be calculated as (pageNumber - 1) * pageSize, which is then used to construct the pagination clause LIMIT {pageSize} OFFSET {offset}. Assuming the page index starts from 1:

To support different database dialects in the future, we can define an interface Dialect to handle pagination for various databases:

public interface Dialect {
    String buildPageSql(String sql, int limit, long offset);
}

Sorting Clause Construction

For the sorting parameter sort=age,desc;name, commas (,) are replaced with spaces, semicolons (;) are replaced with commas (,), and the ORDER BY keyword is prefixed to create the sorting clause: ORDER BY age desc, name.

Since the sorting parameter is a string, to prevent SQL injection, we can validate the sort field using annotations provided by Validation.

Data Access Interface

Now, with the query object, we can construct the dynamic parts of a database query—query clauses, sorting clauses, and pagination clauses. Combined with static parts like table names and column names extracted from the entity object, we can generate complete SQL statements for single-table CRUD operations.

Table and column names do not change, so we can move entity-parsing logic into the constructor to cache table and column names, avoiding repetitive processing. The optimized query method would look like this:

public class JdbcDataAccess<E> {
    private JdbcTemplate jdbcTemplate;
    private RowMapper<E> rowMapper;
    private String table;
    private String columns;

    public JdbcDataAccess(JdbcTemplate jdbcTemplate, Class<E> clazz) {
        this.jdbcTemplate = jdbcTemplate;
        this.rowMapper = new BeanPropertyRowMapper<>(clazz);
        this.table = clazz.getAnnotation(Table.class).name();
        this.columns = QueryBuilder.buildColumns(clazz);
    }

    public List<E> query(PageQuery query) {
        String sql = "SELECT " + columns + " FROM " + table;
        ArrayList<Object> argList = new ArrayList<>();
        sql += QueryBuilder.buildWhere(query, argList); // WHERE ...
        sql += QueryBuilder.buildOrderBy(query); // ORDER BY ...
        sql += QueryBuilder.buildPaging(query); // LIMIT ... OFFSET ...
        return jdbcTemplate.query(sql, rowMapper, argList.toArray());
    }

    public long count(PageQuery query) {
        String sql = "SELECT count(*) FROM " + table;
        ArrayList<Object> argList = new ArrayList<>();
        sql += QueryBuilder.buildWhere(query, argList);
        return jdbcTemplate.queryForObject(sql, argList.toArray(), long.class);
    }

    public PageList<E> page(PageQuery query) {
        return new PageList<>(query(query), count(query));
    }
}

Next, inspired by Spring Data JPA's CrudRepository interface, we designed a new data access interface, DataAccess<E, I, Q>, for single-table interactions around entity objects and query objects. Here, E represents the entity type, and Q represents the query object type.

public interface DataAccess<E extends Persistable<I>, I extends Serializable, Q extends PageQuery> {
    List<E> query(Q query);
    long count(Q query);
    PageList<E> page(Q query);
    <V> List<V> queryColumns(Q q, Class<V> clazz, String... columns);
    int delete(Q query);  // delete filtered records
    int patch(E entity, Q query);  // update non-null fields for filtered records

    E get(I id);
    int delete(I id);
    void create(E e);
    int create(List<E> entities);
    int update(E e); // update all fields
    int patch(E e);  // update non-null fields
}

This interface provides a standardized set of data manipulation methods, enabling the Controller layer to implement a universal RESTful API:

public class AbstractController<E extends Persistable<I>, I extends Serializable, Q extends PageQuery> {
    @Resource
    private DataAccess<E, I, Q> dataAccess;

    @GetMapping("/")
    public PageList<E> page(Q query) {
        return dataAccess.page(query);
    }

    @GetMapping("/{id}")
    public E get(@PathVariable I id) {
        return dataAccess.get(id);
    }

    // POST/PUT/PATCH/DELETE methods
}

Conclusion

By effectively applying refactoring techniques, we created a query object that can be used to construct the dynamic parts of database query statements, including the where clause, sorting clause, and pagination clause. These components represent the dynamic part of the SQL statement. Meanwhile, the table name and column names, derived from the entity object, represent the static part of the SQL statement.

Thus, SQL statements are divided into dynamic and static parts, which are mapped respectively by the query object and the entity object. On this foundation, we designed a simple and comprehensive data access interface for single-table interactions in object-oriented development.

Clearly, we can also construct the dynamic parts of complex queries based on the query object. But how should the static parts of complex queries be constructed? In the next article, we will further explore an object-oriented way to construct complex query statements.

DOYToWin