SQL字段解析

一、背景

项目中存在着一个需求，创建数据表时需要根据SQL进行创建，这里定义的SQL为查询语句。所以需要将查询语句中的结果字段摘取出来作为建表所使用到的字段(简单/复杂查询都涉及)

二、相关SQL解析库的实践

一开始在网上找了java中sql解析的库，重点研究了下jsqlparser，效果如下

<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>4.9</version>
</dependency>

String dataSql = "SELECT \n" +
    "    u.biz_line AS biz_group, \n" +
    "    u.realname AS user_name, \n" +
    "    DATE_FORMAT(s.reviewedPlanDate, '%Y-%m') AS month, \n" +
    "    COUNT(s.id) AS story_count, \n" +
    "    SUM(estimate) AS PD_estimated_hours,\n" +
    "    SUM(s.prdEstimate) AS prd_estimated_hours, \n" +
    "    SUM(s.prdConsumed) AS prd_actual_hours\n" +
    "FROM \n" +
    "    zt_story s\n" +
    "JOIN \n" +
    "    zt_user u ON s.productOwner = u.account\n" +
    "WHERE \n" +
    "    DATE_FORMAT(s.reviewedPlanDate, '%Y-%m') IN ('2024-05', '2024-06', '2024-07')\n" +
    "GROUP BY \n" +
    "    u.biz_line, \n" +
    "    u.realname, \n" +
    "    DATE_FORMAT(s.reviewedPlanDate, '%Y-%m')\n" +
    "ORDER BY \n" +
    "    u.biz_line, \n" +
    "    u.realname, \n" +
    "    month;";

Statement statement = CCJSqlParserUtil.parse(dataSql);
if(statement instanceof Select){
    Select selectStatement = (PlainSelect) statement;
    System.out.println("==> JsqlParser SQL: "+ selectStatement.toString());
    PlainSelect plainSelect = selectStatement.getPlainSelect();
    System.out.println("==> FromItem: "+ plainSelect.getFromItem());
    List<SelectItem<?>> selectItems = plainSelect.getSelectItems();
    List<Alias> collect = selectItems.stream().map(SelectItem::getAlias).collect(Collectors.toList());
    System.out.println("==> SelectItem: "+ selectItems);
    System.out.println("==> select items: "+collect);
    List<String> columns = new ArrayList<>();
    for (Alias alias:collect){
        String name = alias.getName();
        columns.add(name);
    }
    System.out.println("==> columns: "+columns);
    System.out.println("==> Where: "+plainSelect.getWhere());
}

结果如下

上图中看到，针对于这种简单的select查询语句来说，字段确实有效解析出了

WITH PlannedTasks AS (
    SELECT
        task_res_user_name,
        DATE_FORMAT(finish_plan_time, '%Y-%m') AS month,
        COUNT(*) AS planned_count
    FROM
        power_dpm_associated_task
    WHERE
        finish_plan_time BETWEEN '2024-01-01' AND '2024-07-31'
    GROUP BY
        task_res_user_name,
        DATE_FORMAT(finish_plan_time, '%Y-%m')
),
CompletedTasks AS (
    SELECT
        t.task_res_user_name,
        DATE_FORMAT(o.create_time, '%Y-%m') AS month,
        COUNT(*) AS completed_count
    FROM
        power_dpm_associated_task t
    JOIN
        power_dpm_approver_opinion o ON t.id = o.task_id
    WHERE
        o.check_result = 'PASS'
        AND o.create_time BETWEEN '2024-01-01' AND '2024-07-31'
        AND o.create_time = (
            SELECT MAX(o2.create_time)
            FROM power_dpm_approver_opinion o2
            WHERE o2.task_id = o.task_id
            AND o2.check_result = 'PASS'
        )
    GROUP BY
        t.task_res_user_name,
        DATE_FORMAT(o.create_time, '%Y-%m')
)
SELECT
    COALESCE(p.task_res_user_name, c.task_res_user_name) AS task_res_user_name,
    COALESCE(p.month, c.month) AS month,
    COALESCE(p.planned_count, 0) AS planned_count,
    COALESCE(c.completed_count, 0) AS completed_count
FROM
    PlannedTasks p
LEFT JOIN
    CompletedTasks c ON p.task_res_user_name = c.task_res_user_name AND p.month = c.month
UNION
SELECT
    COALESCE(p.task_res_user_name, c.task_res_user_name) AS task_res_user_name,
    COALESCE(p.month, c.month) AS month,
    COALESCE(p.planned_count, 0) AS planned_count,
    COALESCE(c.completed_count, 0) AS completed_count
FROM
    PlannedTasks p
RIGHT JOIN
    CompletedTasks c ON p.task_res_user_name = c.task_res_user_name AND p.month = c.month
ORDER BY
    task_res_user_name,
    month;

但是下面的这种with关联的复杂sql查询语句，使用上述方法就得不到想要的结果了。因此，经过了一系列尝试后，选择了jdbi库中的方法实现。

三、JDBI实现SQL解析（重点）

pom依赖及Java代码实现如下

<dependency>
    <groupId>org.jdbi</groupId>
    <artifactId>jdbi3-sqlobject</artifactId>
    <version>3.45.0</version>
</dependency>
<!-- gradle依赖在此 implementation 'org.jdbi:jdbi3-sqlobject:3.45.0' ->

public List<String> getQueryColumns(Jdbi jdbi, String sql) {
    log.info("执行查询的sql语句: {}", sql);
    return jdbi.withHandle((Handle h) -> {
        // 执行查询并获取列名
        return h.createQuery(sql).map(new ColumnNameMapper()).first();
    });
}

/**
  * 内部类
  */
private static class ColumnNameMapper implements RowMapper<List<String>> {
    @Override
    public List<String> map(ResultSet rs, StatementContext ctx) throws SQLException {
        List<String> columnNames = new ArrayList<>();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        for (int i = 1; i <= columnCount; i++) {
            columnNames.add(metaData.getColumnName(i));
        }
        return columnNames;
    }
}

效果展示

1.select *

2.select具体字段

3.with的复杂查询

效果见上图，项目中所用到的SQL查询场景这三种情况基本能覆盖，字段也都正常解析出，所以需求能满足。