The Aggregation Query Solution in DoytoQuery

This article introduces the aggregate query solution in DoytoQuery in detail. Through the prefix mapping of aggregation functions, @GroupBy annotation, Having interface and dynamic query construction, DoytoQuery maps objects to aggregate query statements to complete aggregate queries.

1. Introduction

The aggregate query is another commonly used query function provided by the database for statistics and calculation of data, which summarizes information from multiple rows by providing a series of aggregate functions.

DoytoQuery uses prefix mapping to map field names to aggregate functions, and then cooperates with the @GroupBy annotation, Having interface, and Query object to complete the mapping of the entire aggregate query statement.

2. Aggregate Query Mapping

2.1. Prefix Mapping

Aggregate functions are the core features of the aggregate query. Commonly used aggregate functions are as follows：

count
sum
max
min
avg
first
last
stddev_pop
stddev_samp
stddev
addToSet
push

The columns queried by the aggregation functions also need to be mapped to fields of the POJO. We can take advantage of this feature when defining the fields of the POJO, and concatenate the aggregate functions' keywords with the column name in big hump format. This field can be used not only to map query statements but also to hold the returned data, killing two birds with one stone. For example, if we want to calculate the average value of the column score, we can define the field name as avgScore, which will be mapped to avg(score) AS avgScore when a query statement is constructed, and so on.

2.2. GROUP BY

Grouping for some table columns is indispensable in aggregate queries, and these table columns usually need to be queried. So these table columns can also be defined in the POJO object and need to be distinguished from other aggregated fields.

So these table columns can also be defined in the POJO object, and need to be distinguished from other aggregated fields. In DoytoQuery, it is distinguished by adding a @GroupBy annotation.

@Target(FIELD)
@Retention(RUNTIME)
public @interface GroupBy {
}

Demo：

@Entity(name = "t_score")
private class ScoreGroupByStudentView {
    @GroupBy
    private Long studentId;
    private Double avgScore;
}

The class ScoreGroupByStudentView will be mapped to the following SQL statements：

SELECT student_id AS studentId, avg(score) AS avgScore FROM t_score GROUP BY student_id

2.3. HAVING

The SQL statement uses the HAVING clause to filter the aggregated data. DoytoQuery maps the object which implements the Having interface to the HAVING clause. Field mapping adopts prefix mapping and suffix mapping at the same time. For example, the field avgScoreGe defined in a class implementing the Having interface will be mapped to HAVING avg(score) >= ?.

public interface Having extends Serializable {
}

2.4. Dynamic Query

The mapping for the WHERE clause can refer to The Query Mapping Solution in DoytoQuery。

In addition, an AggregationQuery interface is provided for the Query object to construct the HAVING condition.

public interface AggregationQuery extends DoytoQuery {
    Having getHaving();
}

2.5. The Aggregate Query API

Finally, the interface of aggregate query in DoytoQuery is defined as follows:

public interface DataQueryClient {
    // ...

    <V, Q extends AggregationQuery>
    List<V> aggregate(Q query, Class<V> viewClass);
}

3. A Full Case

Now let's use a complete example to demonstrate how DoytoQuery implements the aggregate query for the table t_student_score.

create table t_student_score
(
    id          bigint generated by default as identity primary key,
    school_term varchar(100)   not null,
    subject     varchar(100)   not null,
    student_no  varchar(10)    not null,
    score       numeric(10, 2) not null,
    is_deleted  boolean        not null default false
);

1. Create StudentScoreStatView for aggregate functions and GROUP BY clause mapping.

@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity(name = "t_student_score")
public class StudentScoreStatView {
    @SuppressWarnings("java:S116")
    @GroupBy
    private String school_term;
    @GroupBy
    private String subject;

    private Integer countScore;
    private Double minScore;
    private Double maxScore;
    private Double avgScore;
}

1. Create StudentScoreHaving for HAVING clause mapping.

public class StudentScoreHaving implements Having {
    private Integer countGt;
}

1. Create StudentScoreStatViewQuery for WHERE clause mapping.

public class StudentScoreStatViewQuery extends PageQuery implements AggregationQuery {

    private String schoolTermGe;
    private List<String> subjectIn;
    private Double scoreGe;
    private Boolean isDeleted;

    private StudentScoreHaving studentScoreHaving;

    @Override
    public Having getHaving() {
        return studentScoreHaving;
    }
}

1. Create the unit test for evaluation.

@SpringBootTest
class StudentScoreStatTest {
    @Resource
    private DataQueryClient dataQueryClient;

    @Test
    void aggregateStudentScore() {
        StudentScoreStatViewQuery statQuery = StudentScoreStatViewQuery
                .builder()
                .schoolTermGe("2000")
                .subjectIn(Arrays.asList("Chinese", "Math", "English"))
                .scoreGe(60.)
                .isDeleted(false)
                .studentScoreHaving(StudentScoreHaving.builder().countGt(1).build())
                .sort("school_term,asc;subject,asc")
                .build();

        SqlAndArgs sqlAndArgs = RelationalQueryBuilder.buildSelectAndArgs(statQuery, StudentScoreStatView.class);

        assertThat(sqlAndArgs.getSql()).isEqualTo(
                "SELECT school_term, subject, count(score) AS countScore, min(score) AS minScore, " +
                        "max(score) AS maxScore, avg(score) AS avgScore " +
                        "FROM t_student_score " +
                        "WHERE school_term >= ? AND subject IN (?, ?, ?) AND score >= ? AND is_deleted = ? " +
                        "GROUP BY school_term, subject " +
                        "HAVING count(*) > ? " +
                        "ORDER BY school_term asc, subject asc");
        assertThat(sqlAndArgs.getArgs()).containsExactly("2000", "Chinese", "Math", "English", 60., false, 1);

        List<StudentScoreStatView> statList = dataQueryClient.aggregate(statQuery, StudentScoreStatView.class);
        assertThat(statList).hasSize(3)
                            .first().isEqualTo(new StudentScoreStatView("2022", "Chinese", 3, 85., 93., 89.));
    }

}

The complete code can refer to GitHub。

DOYToWin