时间戳

首页 > 解决方案 > BadSqlGrammarException: PreparedStatementCallback; 糟糕的 SQL 语法，当我给出时间戳参数时

java - BadSqlGrammarException: PreparedStatementCallback; 糟糕的 SQL 语法，当我给出时间戳参数时

问题描述

在我的 Java 应用程序中，我曾经有一个 sql 查询，如下所示：

INSERT INTO "KPI_MEASURE" (
        id,
        created_at,
        kpi_project_id,
        kpi_frequency_id,
        kpi_metric_id,
        branch,
        value
    )
SELECT
    nextval('"KPI_MEASURE_ID_seq"'::regclass),
    now(),
    kpi_measure.kpi_project_id,
    kpi_measure.kpi_frequency_id,
    kpi_measure.kpi_metric_id ,
    kpi_measure.branch ,
    sum(kpi_measure.value)
FROM "KPI_MEASURE" kpi_measure
    INNER JOIN "KPI_METRIC" kpi_metric                      ON kpi_measure.kpi_metric_id = kpi_metric.id
    INNER JOIN "KPI_PROJECT" kpi_project                    ON kpi_measure.kpi_project_id = kpi_project.id
    INNER JOIN "KPI_AGGREGATION_PROJECT" kpi_agg_project    ON kpi_project.name = kpi_agg_project.child_project_name
    WHERE kpi_metric.aggregated = false
GROUP BY kpi_measure.branch, kpi_measure.kpi_metric_id, kpi_measure.kpi_project_id, kpi_project.name, kpi_measure.kpi_frequency_id;

我用 执行了那个 sql，jdbcTemplate.update它起作用了。但是最近我通过一个参数
更改了值： ，我用我的代码给出了这个参数：now():today_date

Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put(TODAY_DATE, today); // TODAY_DATE = today_date

jdbcTemplate.update(sql, parameters);

但是现在，它会产生一个错误：

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; 糟糕的 SQL 语法
...
嵌套异常是 org.postgresql.util.PSQLException: No hstore extension installed。

我不明白为什么这是一个错误，因为这是我被告知要做的，当我们想用 jdbc 查询添加参数时。

编辑：

列数据类型是timestamp without time zone，而today变量是String类型。
我想，我应该使用Timestamp，所以我改变了我的代码：

Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put(TODAY_DATE, new Timestamp(System.currentTimeMillis())); // TODAY_DATE = today_date

jdbcTemplate.update(sql, parameters);

现在我只有错误

“PSQLException：未安装 hstore 扩展。”

也许，我需要添加一个 hstore，但我似乎无法理解为什么。

标签： javasqljdbc

解决方案

您正在使用带有命名参数的 JdbcTemplate，但 JdbcTemplate 仅支持定位参数 (?)。将 JdbcTemplate 替换为NamedParameterJdbcTemplate或将您的查询重写为：

INSERT INTO "KPI_MEASURE" (
        id,
        created_at,
        kpi_project_id,
        kpi_frequency_id,
        kpi_metric_id,
        branch,
        value
    )
SELECT
    nextval('"KPI_MEASURE_ID_seq"'::regclass),
    ?,
    kpi_measure.kpi_project_id,
    kpi_measure.kpi_frequency_id,
    kpi_measure.kpi_metric_id ,
    kpi_measure.branch ,
    sum(kpi_measure.value)
FROM "KPI_MEASURE" kpi_measure
    INNER JOIN "KPI_METRIC" kpi_metric                      ON kpi_measure.kpi_metric_id = kpi_metric.id
    INNER JOIN "KPI_PROJECT" kpi_project                    ON kpi_measure.kpi_project_id = kpi_project.id
    INNER JOIN "KPI_AGGREGATION_PROJECT" kpi_agg_project    ON kpi_project.name = kpi_agg_project.child_project_name
    WHERE kpi_metric.aggregated = false
GROUP BY kpi_measure.branch, kpi_measure.kpi_metric_id, kpi_measure.kpi_project_id, kpi_project.name, kpi_measure.kpi_frequency_id;

并打电话jdbcTemplate.update(sql, new Timestamp(System.currentTimeMillis()));

推荐阅读