java - Java/MySQL 数据库查询结果存储、更新和重用的方法

Question

目前，我有一个生成报告的场景，我需要在从特定表检索数据时分别或同时应用多个但可选的过滤器和条件。目前，我正在检查每个过滤器并通过为每个场景创建单独的 SQL 语句来应用它，如下所示:

For instance: Reporting requirements: sort by column x, filter where column y = "foo", date between a and b.

Scenario 1: All filter applied.
SELECT * FROM REPORT_TABLE WHERE y='foo' AND date>a AND date<b ORDER BY x ASC

Scenario 2: Sort and date filter applied.
SELECT * FROM REPORT_TABLE WHERE date>a AND date<b ORDER BY x ASC

Scenario 3: Sort and 'foo' type filter applied.
SELECT * FROM REPORT_TABLE WHERE y='foo' ORDER BY x ASC

Scenario 4: Only sort applied
SELECT * FROM REPORT_TABLE ORDER BY x ASC

//So on...

上述方法的问题在于，随着过滤器的增加，查询表的场景可能会越来越多。所以，有没有一种方法可以通过简单地重用查询结果来完成上述操作。

例如，即使我有一个或多个，检查每个过滤器并相应地应用。

Scenario: provide all required filter as list

    //first get all the rows and store it variable.
    result = SELECT * FROM report_table;

    //Now loop through each filter from given filter list and apply it.
        //and store the updated query result
        result = SELECT * FROM result WHERE //apply filter 1.
    //loop end.
    //finally all filter applied, return the result.

Answer 1

看看myBatis dynamic sql .它是专门为解决您要解决的问题而创建的。