使用jdbctemplate获取一系列日期之间的ip数

Question

我有这个表:

CREATE TABLE IF NOT EXISTS client (
id BIGSERIAL NOT NULL,
ip VARCHAR(39) NOT NULL, -- 7+8x4=39
data TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT client_pk PRIMARY KEY(id),
CONSTRAINT client_uk UNIQUE (ip)
);

我想搜索两个不同日期之间的记录数量:

@Transactional(readOnly=true)
public Long trovaIpTraDateEdOre(LocalDateTime dataOraInizialeInclusa, LocalDateTime dataOraFinaleEsclusa) {
try{
String sql = "SELECT COUNT(*) FROM client WHERE data >= TIMESTAMP WITH TIME ZONE ? AND data < TIMESTAMP WITH TIME ZONE ?;";
int[] tipi = {Types.TIMESTAMP_WITH_TIMEZONE, Types.TIMESTAMP_WITH_TIMEZONE};
return jdbcTemplate.queryForObject(sql, Long.class, tipi);
}catch (Exception e){
return null;
}
}

如果我运行这个查询，我得到7，所以问题在别处:

SELECT COUNT(*) FROM client WHERE data >= TIMESTAMP WITH TIME ZONE '2021-10-07T00:00' AND data < TIMESTAMP WITH TIME ZONE '2022-01-01T00:00';

我不知道如何处理LocalDateTime。

我得到'null'。在'try'循环中，我得到:

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [SELECT COUNT(*) FROM client WHERE data >= TIMESTAMP WITH TIME ZONE ? AND data < TIMESTAMP WITH TIME ZONE ?;]; Nessun valore specificato come parametro 2.; nested exception is org.postgresql.util.PSQLException: Nessun valore specificato come parametro 2.

Answer 1

您的方法没有使用您通过dataOraInizialeInclusa和dataOraFinaleEsclusa传递的参数。您需要将其重写为如下内容:

String sql = "SELECT COUNT(*) FROM client WHERE data >= :from AND data < :to";
Map<String, Object> params = new HashMap<>();
params.put("from", dataOraInizialeInclusa);
params.put("to", dataOraFinaleEsclusa);
return jdbcTemplate.queryForObject(sql, params, Integer.class);

我返回一个整数，因为我不知道Long是否会工作(我认为它会)，当你返回一个计数Integer会更有意义(imo)。你不需要将添加到SQL查询的postgres jdbc驱动程序应该推断出正确的数据类型,只要你不使用老版本。