代码之家 › 专栏 › 技术社区 › Mohammed

Oracle查询中的H2加减间隔秒

intervals h2 oracle java

Mohammed · 技术社区 · 7 年前

我有服务器模式的h2数据库用于测试。

当我的请求在SQLDeveloper中工作时,我无法在spring batch writer中为日期添加/减去间隔

h2配置:

private DataSource createH2DataSource() {
    String jdbcUrl = String.format("jdbc:h2:%s/target/db/h2;AUTO_SERVER=TRUE;MODE=Oracle", System.getProperty("user.dir"));
    JdbcDataSource ds = new JdbcDataSource();
    ds.setURL(jdbcUrl);
    ds.setUser("sa");
    ds.setPassword("");

    return ds;
}

液化酶:

<column name="DATE_DEBUT_BI" type="TIMESTAMP" />
<column name="DATE_FIN_BI" type="TIMESTAMP" />

我的请求:

UPDATE MY_TABLE SET DATE_FIN_BI = :dateFinBi - INTERVAL '1' SECOND WHERE DATE_J = :dateJ AND CODE_EDE = :codeEde AND STATUT_EFFACEMENT = :statutEffacement

错误:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [UPDATE MY_TABLE  SET DATE_FIN_BI = ? - INTERVAL '1' SECOND WHERE DATE_J = ? AND CODE_EDE = ? AND STATUT_EFFACEMENT = ?]; nested exception is org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "UPDATE MY_TABLE  SET DATE_FIN_BI = ? - INTERVAL '1'[*] SECOND WHERE DATE_J = ? AND CODE_EDE = ? AND STATUT_EFFACEMENT = ?"; SQL statement:
UPDATE MY_TABLE  SET DATE_FIN_BI = ? - INTERVAL '1' SECOND WHERE DATE_J = ? AND CODE_EDE = ? AND STATUT_EFFACEMENT = ? [42000-196]
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:662)
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:950)
at org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils.executeBatchUpdateWithNamedParameters(NamedParameterBatchUpdateUtils.java:40)
...
Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "UPDATE MY_TABLE  SET DATE_FIN_BI = ? - INTERVAL '1'[*] SECOND WHERE DATE_J = ? AND CODE_EDE = ? AND STATUT_EFFACEMENT = ?"; SQL statement:
UPDATE MY_TABLE  SET DATE_FIN_BI = ? - INTERVAL '1' SECOND WHERE DATE_J = ? AND CODE_EDE = ? AND STATUT_EFFACEMENT = ? [42000-196]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:179)
at org.h2.message.DbException.get(DbException.java:155)
at org.h2.message.DbException.getSyntaxError(DbException.java:191)
at org.h2.command.Parser.getSyntaxError(Parser.java:534)
at org.h2.command.Parser.prepareCommand(Parser.java:261)
at org.h2.engine.Session.prepareLocal(Session.java:578)
at org.h2.engine.Session.prepareCommand(Session.java:519)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1204)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:73)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:288)
at org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator.createPreparedStatement(JdbcTemplate.java:1524)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:627)
... 36 more

“解决方案”:

谢谢大家,所以我在java中提取了减法来进行一个简单的查询。

1 回复 | 直到 7 年前

Mohammed 7 年前

为此,我使用了JdbcItemWriter中的ItemSqlParameterSourceProvider

ItemSqlParameterSourceProvider<NebefEffEdePdh> itemSqlParameter = nebefEffEdePdh -> new MapSqlParameterSource()
            .addValue("dateDebutBiMoins1Second",Utils.getDateMinusSeconds(nebefEffEdePdh.getDateDebutBi(), DELTA_DATE_DEBUT_FIN_BI))
            .addValue("dateJ", nebefEffEdePdh.getDateJ())
            .addValue("codeEde", nebefEffEdePdh.getCodeEde())
            .addValue("statutEffacement", nebefEffEdePdh.getStatutEffacement());
writer.setItemSqlParameterSourceProvider(itemSqlParameter);

我的请求:

UPDATE MY_TABLE  SET DATE_FIN_BI = :dateDebutBiMoins1Second WHERE TRUNC(DATE_J) = TRUNC(:dateJ) AND CODE_EDE = :codeEde AND STATUT_EFFACEMENT = :statutEffacement AND DATE_FIN_BI IS NULL